Criando SQL Plan baselines utilizando o SQL*Plus

Revisado por Marcelo Pivovar - Solution Architect

O SQL Plan Management foi introduzido no Oracle 11g para prover uma estratégia de seleção planos conservadora para o otimizador.

No Oracle Database 12c surgiu o Adaptive SQL Plan Management, onde ocorreu uma mudança no processo de evolução (evolving) dos SQL plan baselines. Existe uma nova tarefa automática chamada SYS_AUTO_SPM_EVOLVE_TASK, que roda na janela de manutenção default do banco de dados (default maintenance window).

Formas de capturar os planos:

Automatic plan capture à A forma automática é habilitada através do parâmetro de banco de dados OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES. Para habilitar basta modifica-lo para TRUE (O default é FALSE).

Manual plan capture à Carrega os planos de execução para os comandos SQL de forma manual, através de:

• SQL tuning set (STS)
• Shared SQL area
• Staging table
• Stored outline

Criando SQL Plan baselines

No banco de dados Oracle Database 12c a evolução das baselines existentes é automatizada através de uma tarefa chamada SYS_AUTO_SPM_EVOLVE_TASK, que faz parte do “sql tuning advisor”.

Parâmetros desta tarefa:

Para alterar os valores, podemos utilizar a procedure SET_EVOLVE_TASK_PARAMETER:

  
BEGIN
 DBMS_SPM.set_evolve_task_parameter(
 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
 parameter => 'TIME_LIMIT',
 value     => 2400);
 END;
 /
 
 

Para mostrar as informações dos planos que sofreram evolução durante a execução desta tarefa automática, podemos utilizar a função REPORT_AUTO_EVOLVE_TASK.

Evolução manual das SQL Plan Baselines

No banco de dados Oracle 12c, a função DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE foi “aposentada” e agora devemos utilizar

a nova API chamada de SPM evolve advisor.

Tipicamente, devemos seguir os seguintes passos:

• CREATE_EVOLVE_TASK
• EXECUTE_EVOLVE_TASK
• REPORT_EVOLVE_TASK
• IMPLEMENT_EVOLVE_TASK

Vamos à um exemplo.

Criando uma tabela para os testes:

Coletando as estatísticas da tabela:

Executando a query:

Mostrando o plano de execução:

Utilizando o SQL_ID da query para carregar o SQL plan baseline:

Verificando se o plano foi carregado:

Criando um índice para melhorar o plano de execução:

Executando a query novamente:

Ao verificar o plano, constatamos que o índice não foi utilizado e que o SQL plan baseline foi usado.

Verificando a DBA_SQL_PLAN_BASELINES, podemos verificar que existe um novo plano, mas ainda não foi “aceito”.

Para que ele se torne “aceito”, devemos esperar a “maintenance window” ou fazer a evolução manual deste SQL plan baseline.

Criando uma tarefa para evolução manual do plano:

Verificando o resultado da execução:

Podemos verificar que o novo plano oferece uma melhoria com relação ao plano anterior e pode ser implementado:

Agora o novo plano de execução está com o status de “aceito”:

Executando novamente a query:

Verificando o novo plano de execução:

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.