Seguramente en alguna oportunidad has necesitado que un procedimiento almacenado o una secuencia permanezca en el Shared Pool y has terminado usando el package DBMS_SHARED_POOL, mientras que si lo que deseabas era deshacerte de ellos, la única alternativa era recurrir a alter system flush shared_pool, sentencia que limpia todo el Shared Pool, con lo que ello implica.
La buena noticia es que con la aparición de 11g se introdujo un método adicional al package DBMS_SHARED_POOL, mismo que permite la eliminación selectiva de objetos residentes en el Shared Pool, pero ¿sabías que este método también está disponible en 10gR2?, pues así es, pero su uso requiere de un paso adicional, que explicaré a continuación con algunos ejemplos.
El método del cual estamos hablando es el procedimiento purge, que permite eliminar del Shared Pool cursores, secuencias, packages, triggers, etc.; como les indicara, apareció con 11g pero viene incluido también con 10.2.0.4, y para las versiones 10.2.0.2 y 10.2.0.3 lo puedes obtener como un patch, el 5614566, pero debes tener en cuenta que en esta forma no está disponible para todas las plataformas.
La mejor forma de aprender a usarlo es mediante algunos ejemplos, para ello empezaremos viendo la forma de eliminar un cursor específico, en este caso se trata del cursor asociado al siguiente query:
SELECT /*+ mi query */
dname, count(*) employees
FROM dept JOIN emp USING ( deptno )
GROUP BY dname;
DNAME EMPLOYEES
------------ ----------
ACCOUNTING 3
RESEARCH 5
SALES 6SYS@orcl> SELECT address, hash_value, executions
FROM v$sql
WHERE sql_text LIKE 'SELECT /*+ mi query */%';
ADDRESS HASH_VALUE EXECUTIONS
-------- ---------- ----------
30996000 1856305823 1SYS@orcl> exec dbms_shared_pool.purge('30996000, 1856305823','C');
PL/SQL procedure successfully completed.
SYS@orcl> /
ADDRESS HASH_VALUE EXECUTIONS
-------- ---------- ----------
30996000 1856305823 1SYS@orcl> alter session set events '5614566 trace name context forever';
Session altered.
SYS@orcl> exec dbms_shared_pool.purge('30996000, 1856305823','C');
PL/SQL procedure successfully completed.
SYS@orcl > SELECT address, hash_value, executions
FROM v$sql
WHERE sql_text LIKE 'SELECT /*+ mi query */%';
no rows selectedAhora sí funcionó todo a la perfección, logramos eliminar el cursor que deseábamos, sin tocar nada más del Shared Pool. Lo mismo se puede hacer con un procedimiento almacenado, solo que en lugar de alimentar la dirección que ocupa en el Shared Pool, es suficiente con proporcionar el nombre de la misma.
SCOTT@orcl> execute demo_prc
PL/SQL procedure successfully completed.
SYS@orcl> SELECT namespace, type, sharable_mem
from v$db_object_cache
where owner = 'SCOTT' and name = 'DEMO_PRC';
NAMESPACE TYPE SHARABLE_MEM
---------------- ------------ ------------
TABLE/PROCEDURE PROCEDURE 13710
SYS@orcl> execute dbms_shared_pool.purge('SCOTT.DEMO_PRC','P')
PL/SQL procedure successfully completed.
SYS@orcl> /
NAMESPACE TYPE SHARABLE_MEM
---------------- ------------ ------------
TABLE/PROCEDURE NOT LOADED 0SCOTT@orcl> select demo_seq.nextval from dual;
NEXTVAL
----------
1
SYS@orcl> SELECT namespace, type, sharable_mem
from v$db_object_cache
where owner = 'SCOTT' and name = 'DEMO_SEQ';
NAMESPACE TYPE SHARABLE_MEM
---------------- ------------ ------------
TABLE/PROCEDURE SEQUENCE 1422
SYS@orcl> execute dbms_shared_pool.purge('SCOTT.DEMO_SEQ','Q')
PL/SQL procedure successfully completed.
SYS@orcl> /
NAMESPACE TYPE SHARABLE_MEM
---------------- ----------- ------------
TABLE/PROCEDURE NOT LOADED 0Con esto concluimos la presentación de este novedoso método, que de seguro nos ha de servir en muchas oportunidades, sobre todo cuando queramos deshacernos de aquellos cursores indeseables producto de algún problema con el bind peeking, tal como les comentara en un Post previo.
Para complementar lo aquí expuesto, les recomiendo la lectura de las notas:
| 457309.1 | How To Flush an Object out the Library Cache |
| 751876.1 | DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4. |