Flush de uma única SQL da library cache [ SHARED_POOL ]

Uma instance de banco de dados Oracle contém varias estruturas de memória, uma delas é a SHARED POOL composta pela library cache(cache de biblioteca), dictionary cache(cache de dicionário), result cache(cache de resultado), buffers de mensagens de execução paralela e estruturas de controle.

Dentro da library cache encontramos basicamente os SQLs compartilhados, functions, procedures, packages, (…) e planos de execução. É comum vermos DBAs executando um ALTER SYSTEM FLUSH SHARED_POLL para “limpar” esta área simplesmente para forçar um hard parse de um único SQL, mas como descrito acima, um flush na shared pool vai limpar varias outras coisas (sql, planos, functions, packages,…) o que pode gerar um alto custo para um banco carregar toda esta estrutura novamente.

A partir desta necessidade, a Oracle implementou a partir da versão Oracle database 11g a procedure PURGE dentro da package DBMS_SHARED_POOL que permite efetuar a liberação de uma única SQL, package, sequence… da library cache.

SINTAXE:

PARÂMETROS:

Parâmetro	Descrição
NAME	Nome do objeto a ser eliminado. Este valor é identificado pela concatenação das colunas ADDRESS e HASH_VALUE da v$sqlarea. Atualmente, objetos como tabelas e views não podem ser eliminados.
FLAG	(Opcional) Se não for especificado, então o pacote pressupõe que o primeiro parâmetro é o nome de um pacote/procedure/function.
HEAPS	Heaps a ser purgado. Default é 1, ou seja, pilha 0, o que significa que todo o objeto seria purgado.
SCHEMA	Nome do usuário ou do esquema ao qual pertence o objeto.
OBJNAME	Nome do objeto a ser eliminado.
NAMESPACE	Parâmetro numerico que indica o namespace da library cache em que o objeto está sendo procurado.
HASH	Valor de hash de 16bytes para o objeto.

Exemplo:

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where sql_text like '%1 from dual';
 
no rows selected
 
SQL> select 1 from dual;
 
         1
----------
         1
 
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where sql_text like '%1 from dual';
 
ADDRESS          HASH_VALUE
---------------- ----------
C000000760B1DB38 2866845384
 
SQL> exec DBMS_SHARED_POOL.PURGE ('C000000760B1DB38,2866845384','C');
 
PL/SQL procedure successfully completed.
 
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where sql_text like '%1 from dual';
 
no rows selected

NOTA: Melhorias disponibilizadas no 10gR2

No patchset 10.2.0.4 foi incluido a procedure PURGE na package DBMS_SHARED_POOL.
No 10.2.0.2 e 10.2.0.3 é necessário efetuar o download e instalação do RDBMS path 5614566 para ter acesso as melhorias na package DBMS_SHARED_POOL. (verificar nota 751876.1)

Referências:

http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_shared_pool.htm#CHDCBEBB
MOS (MY ORACLE SUPPORT): How To Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package (Doc ID 457309.1)
DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (Doc ID 751876.1)
DBMS_SHARED_POOL Not Installed or Package Body Missing (ORA-6550, ORA-4067) (Doc ID 121571.1)