viernes, 28 de noviembre de 2008

¿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
  2  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
  2  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 del mismo.

SCOTT@orcl > execute demo_prc

PL/SQL procedure successfully completed.

SYS@orcl > SELECT namespace, type, sharable_mem
  2  from v$db_object_cache
  3  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
  2  from v$db_object_cache
  3  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 del Note 457309.1 How To Flush an Object out the Library Cache, y el Note 751876.1 DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4.

Posts Relacionados:

¿Te pareció interesante este artículo?, ¿te quedaron algunas dudas?, ¿quieres sugerirme un tema a tratar?, pues déjame tus comentarios o envíame un email y para que NO te pierdas ningún Post, suscríbete por email ahora mismo!

4 comentarios, agrega el tuyo!

Ibañez Juan Sebastian dijo...

Realmente muy bueno tu articulo.
Te cuento que soy un poco nuevo como DBA. En que caso nos serviria eliminar una query de la shared pool? Gracias.-

Enrique Orbegozo dijo...

Hola Juan, pues la principal razón es deshacerte de un cursor que tiene un plan de ejecución que te está trayendo problemas, esto debido muy posiblemente al efecto del bind peeking, del cual he tratado en un post previo que te recomiendo leer.
Gracias por participar!

Anónimo dijo...

Hola Enrique muy interesante tu post.
Puedo hacer esto en una BD version 9.2.0.8.0 o de que otra forma puedo hacerlo en esta version?
Mil gracias de antemano.
Victor Manuel.

Enrique Orbegozo dijo...

Hola "anónimo", pues tal como se indica, solo es posible aplicar el procedimiento reseñado a partir de Oracle 10.2. Para el caso de versiones anteriores tendrías que recurrir a metodos como un flush del shared pool.