Devo limpar a Shared Pool via comando ALTER SYTEM FLUSH SHARED POOL?

     Algo que não é muito comum nem indicado executarmos, mas que em algumas situações (as exceções) somos obrigados a executar para resolver algum problema no Banco de Dados, é o comando ALTER SYTEM FLUSH SHARED POOL. Você sabe em que situações ele pode ser útil e deve ser executado?

   

     Antes de entrar no assunto principal, vamos entender o que é a Shared Pool. Shared Pool é uma área de memória, que faz parte da SGA, onde o Oracle armazena, principalmente, as instruções SQL, packages e informações de objetos que foram executados ou acessados. O que nos interessa saber neste artigo é que em ambientes onde os sistemas não fazem uso de "variáveis bind" em seus SQLs, e estes, por sua vez, são executados inúmeras vezes com filtros literais similares, pode ocorrer ao longo do tempo a fragmentação da Shared Pool, e isso normalmente acarretará em performance ruim no Banco de Dados. Um workaround para resolver esse problema é executar o comando ALTER SYTEM FLUSH SHARED POOL para limpar a Shared Pool, mas a solução definitiva é orientar os desenvolvedores a utilizarem variáveis bind em suas aplicações (ou configurar o parâmetro CURSOR_SHARING, mas este assunto não falarei aqui, abordo ele em detalhes nos treinamentos de SQL Tuning e Database Performance Tuning), evitando deste modo, o uso de literais na cláusula WHERE de SQLs que são executados repetidas vezes. 

Texto extraído do MOS Doc ID 62143.1
"Troubleshooting: Understanding and Tuning the Shared Pool"

     Para exemplificar, pense nos SQLs de um site de uma grande empresa de Ecommerce, tal como o Submarino (gosto de usá-la como exemplo, pois já trabalhei lá).  Imagine que no site do Submarino existam 1 milhão de produtos à venda e ao clicar numa página do site para ver detalhes de um determinado produto, o sistema execute a seguinte instrução:

         SELECT *  -- o ideal aqui seria ter somente o nome das colunas necessárias
    FROM   ECOMMERCE.PRODUTO
    WHERE  CD_PRODUTO = 15;
   

     Se há 1 milhão de produtos e cada produto possui um código (cd_produto) diferente, poderão existir 1 milhão de SQLs similares na Shared Pool, e cada vez que um SQL com código diferente for executado, ocorrerá um Hard Parse. Em termos de performance e uso de memória isso é bem ruim. Com o uso de variáveis bind o Oracle poderia armazenar 1 único SQL na Shared Pool, o que geraria uma boa economia de memória, e também evitaria a fragmentação dela, decorrente de muitos SQLs sendo armazenados e retirados da Shared Pool constantemente.
   

     Há alguns meses atrás eu estava instalando e configurando o Statspack em uma instância de produção e percebi "logo de cara" uma grande lentidão (demorando horas em um processo que deveria ser executado em no máximo alguns minutos) na coleta de snapshots que deveria ser realizada a cada 1 hora. Ao ler o arquivo spdoc.txt (arquivo que contém uma boa documentação sobre o Statspack e está localizado em $ORACLE_HOME/rdbms/admin) encontrei o trecho abaixo que me ajudou a entender (e conferir após executar alguns SQLs em algumas visões de performance dinâmicas) que a Shared Pool dessa instância (que tem muitas aplicações que usam filtros com valores literais) tinha muitos SQLs e estava fragmentada:

"In a level 5 snapshot (or above), note that the time required for the

snapshot to complete is dependent on the shared_pool_size and on the

number of SQL statements in the shared pool at the time the snapshot

is taken: the larger the shared pool, the longer the time taken to

complete the snapshot."

      Logo após ler esse doc executei no "SQL Developer" o comando  ALTER SYTEM FLUSH SHARED POOL, e em seguida executei: exec statspack.snap; para verificar se o tempo de coleta do snapshot  havia reduzido. Para a minha surpresa, o tempo caiu de (várias) horas para menos de 2 minutos. Problema resolvido!
   

     Existem ainda diversos bugs que possuem como "workaround" a limpeza da Shared Pool (via comando ALTER SYTEM FLUSH SHARED POOL). Para mais informações sobre alguns desses bugs, consulte os seguintes docs no MOS (My Oracle Support):

         - Error in the Alert Log: ORA-7445[kprccu()+60] (Doc ID 2496566.1)

         - Bug 4567767 - Execution Plan changes upon rowcache reload (Doc ID 4567767.8)
         - Opd2: ORA 600 [qsmoGetSqlMgmtObjByType:1] (Doc ID 1473593.1)

         - Bug Issues Known to Affect the Shared Pool (Doc ID 102305.1): esse é o documento mais interessante, pois nele você verá uma lista de mais 200 bugs que afetam a Shared Pool.


Por hoje é só!

Se tiver qualquer dúvida sobre o assunto, é só deixar um comentário que eu irei lhe responder o mais breve possível.

[]s