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.
DBMS_SHARED_POOL.PURGE(
name VARCHAR2,
flag CHAR DEFAULT ‘P’,
heaps NUMBER DEFAULT 1)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 selectedMudando 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
Abraço
