Páginas

19 de set. de 2019

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
  
  

2 comentários:

  1. Olás, é possível zerar a "unshared count", após identificar os sql problemáticos e substituir por bind para conferir se a mudança foi efetiva?

    ResponderExcluir
    Respostas
    1. Só limpando o SQL da Shared Pool (via dbms_shared_pool.purge), ou limpando a Shared Pool inteira.

      Excluir