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;
/