Otimizando query distribuída com o hint NO_MERGE

 
Olá pessoal,

     No post de hoje vou dar uma dica rápida de como otimizar uma query distribuída com o uso do hint NO_MERGE.

 
     Recentemente resolvi um problema de desempenho em um SQL que estava sendo executado em um determinado BD de produção que administro, onde este SQL, que acessava tabelas locais e tabelas de mais 2 BDs remotos, estava demorando em média 583s para executar. O tempo era considerado muito alto pelos usuários do sistema, então o pessoal da equipe de Desenvolvimento pediu ajuda à equipe de DBAs, e eu peguei esse incidente para resolver. Segue abaixo o SQL que estava sendo executado (totalmente desfigurado com nomes fictícios de tabelas, colunas e funções, e também com valores de filtros alterados):

select      distinct l.cd, lc.cdt,
            l.nl, l.ct, b.nb
            lc.nl, tl.dtl
from        l as l
left join   tl@bd_a as tl
    on      l.cd = tl.cd
inner join  zb@bd_a as zb
    on      l.cd = zb.cd
inner join  b@bd_a as b
    on      zb.cd = b.cd
inner join  lc@bd_a lc 
   on       lc.cd = b.cd
where       lc.cd IN
               ( select cd_ol
                 from abc.lf
                 where lf.cdf = 
                        (select abc.f(trim('STRING')) from dual) )
and         lc.cdlt = 10001
and         lc.s = 1 
and         b.s = 1 
and         l.s = 1;

     Ao analisar o SQL, desconfiei por causa de problemas anteriores com queries distribuídas que eu já tinha enfrentado, que a lentidão era decorrente de algum caminho de acesso aos objetos remotos, portanto, fui executando partes dele até descobrir que a lentidão era decorrente da execução da subquery, mais especificamente, ao executar a função "abc.f". Analisei o Plano de Execução, mas não consegui identificar nele o que exatamente estava ruim, portanto, resolvi apelar para algum hint que pudesse me ajudar a evitar a transformação que o CBO estava fazendo ao executar a OUTER QUERY e a VIEW INLINE "select abc.f(trim('STRING')) from dual", transformando-as em um único SQL. Conforme informações do doc Tuning Distributed Queries, acrescentei no SQL o hint NO_MERGE e vi instantaneamente o tempo cair de 583,706s para 0,472s. Veja como ficou o SQL após a alteração:

select      distinct l.cd, lc.cdt,
            l.nl, l.ct, b.nb
            lc.nl, tl.dtl

from        l as l

left join   tl@bd_a as tl

    on      l.cd = tl.cd

inner join  zb@bd_a as zb

    on      l.cd = zb.cd

inner join  b@bd_a as b

    on      zb.cd = b.cd

inner join  lc@bd_a lc 

   on       lc.cd = b.cd

where       lc.cd IN

               ( select /*+ NO_MERGE(lf) */ cd_ol

                 from abc.lf

                 where lf.cdf = 

                        (select abc.f(trim('STRING')) from dual) )

and         lc.cdlt = 10001

and         lc.s = 1 

and         b.s = 1 

and         l.s = 1;

     Sugiro a leitura dos links das referências para aprender mais detalhes sobre o hint NO_MERGE, e a leitura do SQL TUNING GUIDE para aprender mais sobre as transformações que o CBO pode fazer em SQLs contendo visões.

Imagem 01 - Sintaxe do hint NO_MERGE

Referências:

     - no_merge hint tips;
     - Oracle 12c Hints;     - Five Hints     - Tuning Distributed Queries