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

setembro 10, 2013 - Alta disponibilidade, Oracle Database, Performance, PL/SQL - Tagged: address, cursor, DBMS_SHARED_POOL, flush, hash_value, LIBRARY CACHE, parse, purge, shared pool, v$sqlarea - 4 comments

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:

DBMS_SHARED_POOL.PURGE (
name VARCHAR2,
flag CHAR DEFAULT ‘P’,
heaps NUMBER DEFAULT 1);

DBMS_SHARED_POOL.PURGE (
schema VARCHAR2,
objname VARCHAR2,
namespace NUMBER,
heaps NUMBER);

DBMS_SHARED_POOL.PURGE (
hash VARCHAR2,
namespace NUMBER,
heaps NUMBER);

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)

Autor: Anderson Graf

Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Entusiasta da tecnologia Oracle, ACE Associate ♠, autor em vários blogs e OTN. Consultor Oracle Senior na Exímio Soluções em TI

Envolvido em soluções de:
– Implementação, migração, gerenciamento e suporte a produtos Oracle, multiplataforma
– Monitoramento de ambientes 24×7
– Backup e Recovery
– Performance e Tuning
– Alta disponibilidade (HA) – RAC, Data Guard
– EM database/grid/cloud control
– Particionamento & Advanced Compression
– Oracle Engineered Systems – ODA, Exadata

Blog pessoal: http://www.andersondba.com.br
Articulista na Oracle Technology Network (OTN) – https://goo.gl/99R6yW
ACE Associate – https://goo.gl/MBB51b
Articulista GPO – http://profissionaloracle.com.br

4 thoughts on “Flush de uma única SQL da library cache [ SHARED_POOL ]”

jhonata 28/01/2014 at 2014-01-28T15:54:05-02:000000000531201401

Com base na nota 121571.1 citada na referência, executei o processo de instalação dos pacotes para funcionamento do DBMS_SHARED_POOL em uma base 10.2.0.4 e funcionou sem problemas.

O procedimento é feito de forma online sem indisponibilidade dos serviços.

Reply
Jorge Luiz Vaz 02/09/2019 at 2019-09-02T10:45:29-03:000000002930201909

Eu tenho problemas com um número alto de Hard Parse na aplicação que é um produto Outsourcing, e que não tenho como evitá-los. No meus relatórios produzidos para verificação desse fato encontro nos indicadores de Eficiência de Instância ,um Execute do parse com o valor baixo, demonstrando com isso que grande parte dos comandos disparados está fazendo Hard Parse. A versão do Banco que eu utilizo é Oracle 11.2.04 Standard Edition, e resolvi considerar a utilização do parâmetro CURSOR_SHARING com o parâmetro FORCE (o padrão colocado na instância é EXACT) e no caso desse usuário dessa aplicação eu faço a troca via triggers. Mas, isto não está resolvendo. Vc vê alguma solução “milagrosa”, para o problema ? Sei que essa pergunta chega a ser descabida, mas não encontro solução pra essa situação e não vejo como resolve-la.

Reply
- Leandro Lana (Admin) 18/01/2023 at 2023-01-18T16:38:22-03:000000002231202301
  
  Jorge, nos perdoe pela resposta tardia.
  No seu caso se você já faz hard parse, o force ou o exaxt não irá lhe auxiliar em nada, o que pode melhorar é adicionar binds variables nos SQL´s o que depende da aplicação.
  
  Espero que tenha me feito entender. Grato.
  
  Reply
Jorge Luiz Vaz 02/09/2019 at 2019-09-02T10:47:04-03:000000000430201909

Eu tenho problemas com um número alto de Hard Parse na aplicação que é um produto Outsourcing, e que não tenho como evitá-los. No meus relatórios produzidos para verificação desse fato encontro nos indicadores de Eficiência de Instância ,um Execute do parse com o valor baixo, demonstrando com isso que grande parte dos comandos disparados está fazendo Hard Parse. A versão do Banco que eu utilizo é Oracle 11.2.04 Standard Edition, e resolvi considerar a utilização do parâmetro CURSOR_SHARING com o parâmetro FORCE (o padrão colocado na instância é EXACT) e no caso desse usuário dessa aplicação eu faço a troca via triggers. Mas, isto não está resolvendo. Vc vê alguma solução “milagrosa”, para o problema ?

Reply