All Things Oracle

¿Cómo deshacernos de un cursor en el Shared Pool?

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                 6
Para poder eliminar el curso del Shared Pool, primero debemos identificarlo y la forma más simple de lograrlo es consultando v$sql.
SYS@orcl> SELECT address, hash_value, executions
    FROM v$sql 
    WHERE sql_text LIKE 'SELECT /*+ mi query */%';

ADDRESS  HASH_VALUE EXECUTIONS
-------- ---------- ----------
30996000 1856305823          1
Con esta información ya podemos hacer uso del procedimiento purge y luego verificamos repitiendo la consulta anterior.
SYS@orcl> exec dbms_shared_pool.purge('30996000, 1856305823','C');

PL/SQL procedure successfully completed.

SYS@orcl> /

ADDRESS  HASH_VALUE EXECUTIONS
-------- ---------- ----------
30996000 1856305823          1
¿Qué pasó? Pues que en Oracle 10gR2, no es posible usar directamente el procedimiento purge, primero hay que habilitar el evento asociado al bug que ha venido a solucionar, es decir el 5614566. Lo haremos a continuación y repetiremos el procedimiento anterior.
SYS@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 selected

Ahora 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              0
Algo similar ocurre si queremos deshacernos de una secuencia, solo basta con proporcionar el nombre del mismo.
SCOTT@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              0

Con 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.1How To Flush an Object out the Library Cache
751876.1DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4.
¿Te pareció interesante este artículo?, ¿te quedaron algunas dudas?, ¿quieres sugerirme un tema a tratar?, pues déjame tus comentarios o ¡contáctame ahora mismo!

Agregue un comentario

Su dirección de correo no se hará público. Los campos requeridos están marcados *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Posts Relacionados

Primer post de una serie dedicada al arte de parchar Oracle. Empezamos con el parchado in-place, la forma más común y también la más peligrosa.
Aprenda a descargar los parches de Oracle, tanto manualmente como de forma automatizada, usando el utilitario getMOSPatch.
Link a articulo publicado en Toad World, sobre como aplicar un patch out-of-place a Grid Infrastructure, usando un Golden Image.

¿Necesitas Ayuda?

Completa estos datos y estaré en contacto a la brevedad.