Criando SQL Plan baselines utilizando o Oracle Enterprise Manager Total Cloud Control 12c

Por Alex Zaballa
Postado em Maio 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:


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”.

Para verificar se está habilitada, podemos utilizar o Oracle Enterprise Manager Cloud Control 12c no seguinte caminho: Administration à Oracle Scheduler à Automated Maintenance Tasks.

Infelizmente no Oracle Enterprise Manager Cloud Control 12c R4, que é utilizado durante o exame, não está disponível a alteração dos parâmetros desta tarefa via interface gráfica.

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.

Como podemos observar, não existem SQL Plan Baselines criados:

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:

Após selecionar o SQL_ID, basta dar OK:

Detalhes do JOB que foi agendado:

Verificando o plano 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 antigo foi usado.

Verificando a a aba “Plan Control”, 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:


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.