Criando SQL Plan baselines utilizando o SQL*Plus

Por Alex Zaballa
Postado em Maio 2016

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:

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.