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:
- ¿Query dinámico?, pero sin perder de vista el contexto
- Cursor sharing, histograms y bind peeking, lo que deberías saber para evitar sorpresas
- El query dinámico de los pobres
Siga leyendo >> ¿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!