Tune SQL Statements

Por Alex Zaballa Oracle ACE Director
Postado em Junho 2016

Revisado por Marcelo Pivovar - Solution Architect

Este é mais um artigo da série que irei escrever enquanto me preparo para o exame de atualização do OCM para a versão 12c. Estes artigos serão criados antes da prova e serão baseados apenas nos tópicos do exame.

Indice de artigos do tema Data and Performance Management:


No Oracle Database 12c existem algumas novas funcionalidades que visam a melhoria na parte de performance and tuning, dentre elas:

  • Adaptive Query Optimization
    • Adaptive Plans
      • Adaptive Join Methods
      • Adaptive Parallel Distribution Methods
    • Adaptive Statistics
      • Dynamic statistics
      • Automatic Reoptimization
      • SQL plan directives 
  • Optimizer Statistics Management
    •    New types of histograms
      • Top-Frequency histograms
      • Hybrid histograms
    • Online statistics gathering for bulk load operations
    • Concurrent statistics gathering
    • Session level statistics on Global Temporary Tables

Neste artigo iremos verificar como funcionam as SQL plan directives.

SQL Plan Directives

SQL plan directives funcionam como uma espécie de lembrete para o otimizador, para avisá-lo que na execução anterior ocorreu a escolha de um plano de execução que não era o ideal. Geralmente este problema ocorre devido a estimativas incorretas de cardinalidade.
SQL plan directives são ligados a query expressions e não a SQL IDs específicos.

Vamos a um exemplo.

Criando uma tabela de testes e inserindo alguns dados:

Confirmando que não existem estatísticas para a tabela e colunas:

Executando a query:


Verificando o plano de execução, podemos observar que o número de linhas estimado está completamente errado:

Após rodar algumas vezes a nossa query, ocorreu a criação do CHILD número 2 com o flag is_reoptimizable = Y:

Persistindo a SQL Plan Directive de forma manual:

Verificando as SQL Plan Directives geradas:

 
Verificando o plano de execução do Child número 2, podemos observar que o número de linhas estimado agora está correto:

 


Alex Zaballa, formado em Análise de Sistemas, é especialista em Banco de Dados Oracle com sólidos conhecimentos em Servidores de Aplicação e Sistemas Operacionais; trabalha com Oracle há 16 anos, é Oracle ACE Director, certificado OCM Database 11G/Cloud e conta com mais de 200 outras certificações em produtos da Oracle.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.