Como remover um SQL Statement da Shared Pool

Imaginem um cenário onde é necessário limpar da shared pool um comando SQL, para que na próxima execução seja realizado um hard parse deste SQL.

Sabemos que para limpar toda a shared pool, podemos utilizar o comando ALTER SYSTEM FLUSH SHARED_POOL. Porém, ao executar este comando em um ambiente de produção haverá um impacto significativo.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

Para resolver este problema, no Oracle11G, foi introduzida a procedure PURGE na package DBMS_SHARED_POOL.

De acordo com a documentação, para limpar um SQL Statement, o parâmetro name, que é  obrigatório, é a concatenação das colunas address e hash_value da view V$SQL_AREA. O parâmetro flag deve ser setado para um valor diferente de P, T ,R ou Q. Então, nesse caso vamos utilizar ‘C‘.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '9uay37ag292zh';

ADDRESS HASH_    VALUE
---------------- ----------
00000000DA8B10F0 2653195248

SQL> exec DBMS_SHARED_POOL.PURGE ('00000000DA8B10F0, 2653195248','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '9uay37ag292zh'; 

no rows selected

Mudando o parâmetro flag, também podemos remover outros tipos de objetos, como packages, procedures, functions, types, triggers e sequences.

O  valor default para o parâmetro flag é 'P', mas podemos utilizar:

• P - nome da package/procedure/function
• T - nome do type
• R - nome da trigger
• Q - nome da sequence

Limpando uma procedure da shared pool:

CREATE OR REPLACE PROCEDURE procedure_teste IS
BEGIN
NULL;
END;
/
Procedure created.


SQL> exec procedure_teste;
PL/SQL procedure successfully completed.

SQL> exec dbms_shared_pool.purge('PROCEDURE_TESTE');
PL/SQL procedure successfully completed.

Para algumas versões do banco 10GR2, isto está disponível através do Patch 5614566.

Referências:

  http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_shared_pool.htm#ARPLS055

Abraço,

Alex Zaballa.