Páginas

21 de ago. de 2013

Hints, usar ou não usar, eis a questão!

ATUALIZADO EM 14/12/2018

Olá pessoal,

     No artigo de hoje vou comentar sobre um assunto que gera bastante polêmica no mundo Oracle: o uso de hints nas instruções SQL. Muita gente usa, muitos condenam o seu uso e poucos realmente sabem qual é o seu impacto positivo e/ou negativo nas instruções SQL. Eu, particularmente, fico preocupado em 2 situações: Quando me falam que usam muitos hints e quando me falam que nunca jamais irão usar hints! Se uma pessoa diz que usa hint em muitas instruções SQL, provavelmente o seu conhecimento sobre eles (principalmente suas consequências) é muito superficial! Quando uma pessoa diz que jamais deve-se utilizar um hint, com certeza ela NUNCA passou por uma situação em que um hint praticamente "salvou a sua vida"!

     Um hint bem analisado e testado pode resolver grandes problemas de performance que o otimizador por si só não consegue algumas vezes (ele não é perfeito e talvez nunca seja). Veja por exemplo, os casos que cito nos artigos Otimizando a performance de SQL distribuído e Otimizando query distribuída com o hint NO_MERGE.

     Antes de continuar o assunto, vamos entender melhor o que são hints. Eles surgiram no Oracle 7, época em que existiam poucos recursos para influenciar o otimizador a melhorar o plano de execução, e são dicas que a gente insere em instruções SQL (através de comentários) para que o otimizador possa alterar o plano de execução delas, com o objetivo principal de otimizar o desempenho, mas infelizmente, nem sempre isso acontece! Os hints podem ser muito úteis se soubermos quando e qual usar, mas eles podem ser maléficos se não forem utilizados na situação correta ou sem muito conhecimento de suas ações e consequências! Nas últimas versões do SGBD Oracle, um hint obsoleto (Ex.: RULE), pode gerar um plano de execução ruim, e consequentemente, impactar negativamente na performance de instruções SQL.
    
     Nos treinamentos de SQL Tuning apresento vários hints, como por exemplo: APPEND, PARALLEL e    FIRST_ROWS, que são muito bons quando são utilizados nas situações adequadas! O hint APPEND, por exemplo, deve ser utilizado para otimizar cargas de dados via comando INSERT (fazendo carga direta) somente quando você tiver certeza de que outros usuários não estarão atualizando dados concorrentemente na tabela! Já o hint PARALLEL, só deve ser utilizado em consultas longas e quando houver recursos de processamento, memória e I/O disponíveis, ou seja, quando estes recursos, não estiverem sobrecarregados!

     Segue abaixo um exemplo de utilização do hint APPEND:

Imagem 01 - Hint APPEND

Obs.: Todo hint deve ser inserido após a primeira palavra da instrução (Ex.: INSERT, UPDATE, DELETE e SELECT), entre os caracteres /*+ e */.

     Na versão 11G do Oracle existem quase 300 hints (para ver uma lista completa, consulte a visão v$sql_hint), portanto, é bom conhecer os principais, aqueles que não estão obsoletos, e saber usá-los nas situações apropriadas, para não dar uma dica furada (que ao invés de otimizar o SQL irá piorar o seu desempenho) ao otimizador!

    É importante entender que o hint é apenas uma dica (e não uma diretiva), portanto, ele poderá ser ignorado! O otimizador de queries consegue montar um bom plano de execução na maior parte das instruções SQL que ele analisa, mas ele não é 100% eficiente! Se fosse, os hints já teriam deixado de existir!


Referências:
   - Oracle Database Performance Tuning Guide 11g Release 2 (11.2): Using Optimizer Hints
 

17 comentários:

  1. Uma dica de utilização do Hint Append, é quando são utilizados em inserções de tabelas temporárias, que geralmente, não são utilizadas no momento do processamento e depois de utilizadas, são "descartadas".

    ResponderExcluir
    Respostas
    1. Christian, apesar de ajudar nos casos de tabelas heap temporárias quando os dados são descartados sem o TRUNCATE TABLE, eu não indicaria o hint APPEND para este caso. Melhor do que ele, é criar a tabela como GTT (global temporary table) e nem precisar usar este hint.

      Eu indico o hint APPEND para ser usado principalmente nas cargas de dados em tabelas heap que possuem muitos blocos vazios! As tabelas heap temporárias que forem truncadas não terão blocos vazios e neste caso, em testes que eu já fiz, vc não verá muito diferença de performance!

      []s

      Excluir
  2. Fábio, estou de volta!
    Mais um excelente post! Parabéns!

    ResponderExcluir
  3. Olá Fabio, ótimo artigo, parabéns!
    Qual é a diferença do hint parallel e parallel auto? Qual é o impacto e ganho da utilização do hint parallel auto?
    Muito obrigada.

    ResponderExcluir
    Respostas
    1. Priscila, não dá para apenas falar a <> entre eles, pois tem conceitos e recursos que vc deve saber antes de pensar em usar parallel auto, por isso sugiro a leitura do link: http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm.

      []s

      Excluir
  4. Fabio, gostaria de tirar uma dúvida.

    O HINT APPEND no insert pode ser usado com Parallel?



    Obrigado.

    ResponderExcluir
    Respostas
    1. Se PARALLEL DML já estiver habilitado o APPEND faz paralelismo automaticamente.

      Excluir
  5. Parabéns Fabio! Sempre acompanho seu blog, obrigado por tudo.

    ResponderExcluir
  6. Hint é foda! Salvou minha pele hoje....

    ResponderExcluir
    Respostas
    1. Já salvou a minha várias vezes também, obrigado pelo comentário!

      Excluir