martes, 6 de julio de 2010

Eliminar una consulta de la Shared Pool

A partir de Oracle 10.2.0.4 en adelante, el paquete DBMS_SHARED_POOL contiene nuevos procedimientos. Uno de esos procedimientos se llama PURGE y es el encargado de eliminar determinados objetos de la Shared Pool. Estos objetos pueden ser: cursores (consultas SQL), paquetes, procedimientos, funciones, triggers, secuencias y tipos.
En versiones anterior, esto no podíamos hacerlo y lo que debíamos hacer era eliminar todas las consultas de la Shared Pool mediante la sentencia "ALTER SYSTEM FLUSH SHARED_POOL".

La creación del paquete DBMS_SHARED_POOL podemos encontrarla en $ORACLE_HOME/rdbms/admin/dbmspool.sql

En Oracle 10.2.0.4 existe el bug 5614566 que provoca el mal funcionamiento del procedimiento PURGE. Pero, tenemos un manera de evitar este bug en esa versión.

Comencemos viendo un ejemplo en 11gR1:

SQL> desc DBMS_SHARED_POOL

PROCEDURE ABORTED_REQUEST_THRESHOLD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
THRESHOLD_SIZE NUMBER IN
PROCEDURE KEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
PROCEDURE PURGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
HEAPS NUMBER IN DEFAULT

PROCEDURE SIZES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MINSIZE NUMBER IN
PROCEDURE UNKEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT

Para ejecutar el procedimiento PURGE, debemos pasar como valor en el parámetro NAME el ADDRESS junto con el HASH_VALUE separados por una coma(,).
Estos valores podemos encontrarlos en la vista V$SQLAREA.

SQL> SELECT /* prueba */ 1 FROM DUAL;

1
----------
1

SQL> SELECT address||','||hash_value name
FROM v$sqlarea
WHERE sql_text = 'SELECT /* prueba */ 1 FROM DUAL';

NAME
-------------------
2E10E868,4198164882

Como podemos ver, lo que hicimos fue ejecutar una consulta para que sea parseada por primera vez y almacenada en la Shared Pool. Luego buscamos en la vista V$SQLAREA los valores que debo pasar en el parámetro NAME del procedimiento PURGE.

Ahora vamos a eliminar la consulta de la Shared Pool y a verificar que se haya eliminado.

SQL> exec dbms_shared_pool.purge('2E10E868,4198164882','C',1);

PL/SQL procedure successfully completed.

SQL> SELECT address||','||hash_value name
FROM v$sqlarea
WHERE sql_text = 'SELECT /* prueba */ 1 FROM DUAL';

no rows selected

La consulta fue eliminada con éxito!

Para hacer funcionar el procedimiento en 10.2.0.4, debemos realizar el siguiente alter en la sesión antes de ejecutar el procedimiento PURGE:

SQL> alter session set events '5614566 trace name context forever';


Pueden utilizar el siguiente script para hacer el purge de un determinado cursor de la Shared Pool:

DECLARE
name varchar2(50);
version varchar2(3);
BEGIN
select regexp_replace(version,'\..*')
into version
from v$instance;

if version = '10' then
execute immediate
q'[alter session set events '5614566 trace name context forever']'; -- bug 5614566
end if;

select address||','||hash_value
into name
from v$sqlarea
where sql_id like '&sql_id';

dbms_shared_pool.purge(name,'C',1);
END;
/

3 comentarios:

Raul dijo...

Muy interesante el artículo, y muy útil en entornos de pruebas :)

Carlos dijo...

Probe eso que hiciste anteriormente aqui en el trabajo, unicamente la prueba del query y lo de la busqueda en la vista v$sqlarea, y al parecer nisiquiera tengo esa vista, eso quiere decir que el share pool esta desactivado o algo por el estilo? de esto ser cierto la desactivacion del share pool implica ganancias en el rendimiento cuando manipulamos grandes cantidades de data?
Gracias !!!

Leonardo Horikian dijo...

Hola Carlos,

No puedes ver las vistas seguramente porque el usuario que estas utilizando no tiene los privilegios suficientes para hacerlo.
Para ver las vistas debes tener el privilegio SELECT ANY DICTIONARY. Este privilegio te permitirá tener acceso a todos los objetos del esquema SYS.
Como alternativa, si tienes el rol SELECT_CATALOG_ROLE puedes acceder a todas las vistas del esquema SYS.

Saludos