Tune SQL Statements

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:

• Modificando Materialized Views
• Transportable Database / Tablespaces
• Criando Tabelas Particionadas no Oracle Database 12c
• Flashback Data Archive
• Resource Manager utilizando o SQL*Plus
• Tune SQL statements
• Perform real application testing
• Criando SQL Plan baselines utilizando o OEM Total Cloud Control 12c

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.