Identificando SQLs similares para substituir valores literais por valores bind

   Olá pessoal,

    No artigo de hoje vou mostrar como identificar SQLs similares que são candidatos a serem alterados para utilizarem "variáveis bind" (ao invés de valores literais), e desse modo, ajudar a economizar memória na Shared Pool, e reduzir os problemas diversos causados pelo excesso de "Hard Parses", alguns deles citados no artigo Devo limpar a Shared Pool via comando ALTER SYTEM FLUSH SHARED POOL?.

     A partir do Oracle Database 10G, o método mais eficiente para identificar os SQLs similares é montar uma consulta utilizando a coluna force_matching_signature da visão V$SQLAREA. O valor dessa coluna é um hash gerado a partir do texto SQL, onde é feita uma substituição dos valores literais por valores bind gerados pelo Oracle, e todos os espaços em branco redundantes são eliminados. Existe outra coluna chamada exact_matching_signature, que também é um hash gerado a partir do texto SQL, onde há apenas a eliminação de todos os espaços em branco redundantes, sem a substituição de valores literais. Quando o valor do parâmetro CURSOR_SHARING da instância é igual a FORCE e quando variáveis bind foram utilizadas no SQL (no nível da aplicação), o valor de ambas as colunas, exact_matching_signature e force_matching_signature é igual. 

    

     Os SQLs que são candidatos a serem reescritos para utilizarem variáveis bind possuem valor diferente de 0 (zero) na coluna force_matching_signature, portanto, são eles que serão retornados na consulta abaixo (extraída e adaptada do MOS Doc "Troubleshooting: Understanding and Tuning the Shared Pool" ID 62143.1), que filtra também os SQLs similares que tiveram mais de 20 execuções (esse valor pode ser alterado se necessário), exibindo-os em ordem decrescente de quantidade de execuções (ver valor da coluna "unshared count"), pois os primeiros a serem exibidos são prioritariamente aqueles que merecem mais atenção na substituição de valores literais por valores bind:

WITH c AS
    (SELECT FORCE_MATCHING_SIGNATURE,
            COUNT(*) cnt
     FROM   v$sqlarea
     WHERE  FORCE_MATCHING_SIGNATURE!=0
     GROUP BY FORCE_MATCHING_SIGNATURE
     HAVING COUNT(*) > 20
),
sq AS
    (SELECT     sql_text ,
                FORCE_MATCHING_SIGNATURE,
                row_number() over (partition BY FORCE_MATCHING_SIGNATURE 
                                   ORDER BY sql_id DESC) p
     FROM       v$sqlarea s
     WHERE      FORCE_MATCHING_SIGNATURE IN
                (SELECT FORCE_MATCHING_SIGNATURE
                 FROM   c
                )
    )
SELECT   sq.sql_text ,
         sq.FORCE_MATCHING_SIGNATURE,
         c.cnt "unshared count"
FROM     c
JOIN     sq
   ON    sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND      sq.p =1
ORDER BY c.cnt DESC;

     Cada SQL similar, por ter um texto parecido, mas não exatamente igual, possui um SQL_ID diferente. Para simplificar o entendimento do resultado, é realizado um agrupamento na consulta pelo valor da coluna FORCE_MATCHING_SIGNATURE, mostrando o texto somente do 1º SQL de um conjunto de SQLs que possuem SQL_ID diferentes e mesmo valor de FORCE_MATCHING_SIGNATURE. Caso queira ver uma lista de todos os SQLs similares que possuem o mesmo valor na coluna FORCE_MATCHING_SIGNATURE, execute o SQL abaixo, informando durante a sua execução, o valor de FORCE_MATCHING_SIGNATURE desejado:

SELECT  * 

FROM    V$SQLAREA 

WHERE   FORCE_MATCHING_SIGNATURe ='&FORCE_MATCHING_SIGNATURE';

Por hoje é só!

[]s


Referências:
- EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE Explained
- exact_matching_signature & force_matching_signature Tips