Executando o SQL Tuning Advisor – DBMS_SQLTUNE

Publicado: março 8, 2013 em SQL, Tunning
Tags:
Irei primeiramente fazer um explicação conceitual do SQL Tuning Advisor, que fazer parte do Tuning Pack (CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING), e depois realizaremos um exemplo utilizando os recuros de sua API, a package DBMS_SQLTUNE.
       
O SQL Tuning Advisor substitui o processo manual de SQL Tuning, que pode ser custoso para um DBA ou desenvolvedor. Ele executa uma completa análise da instrução SQL, constituindo-se em:
– Identificar estatísticas ultrapassadas ou até mesmo a sua não existência.
– Determinar melhores planos de execução. (SQL Profile)
– Idenficar melhores caminhos e objetos que satisfaçam a possibilidade de utilizá-los (indexes, materialized views).
– Restruturação da instrução.
       
O Advisor pode ser executado via EM no “Advisor Central” ou utilizando as procedures da package DBMS_SQLTUNE, o qual será abordado agora. Inclusive, o recurso “Automatic SQL Tuning”, que foi introduzido na versão 10g e que irei abordar em outro post, utiliza o Advisor para realizar o tuning.
       
A execução do Advisor utilizando o DBMS_SQLTUNE tem dois passos: a criação do SQL Tuning task e sua execução.
       
Após a execução, ele reporta várias recomendações para a melhoria da instrução, baseado nos pontos da análise citados anteriormente. Uma das recomendações é a aplicação do SQL Profile para gerar planos de execuções melhorados. O SQL Profile é associado à assinatura da instrução SQL criada através de uma função de hash, a qual normaliza a instrução. Colocando-a toda em maiúscula e retirando os espaços em branco extras antes de gerar a assinatura.
       
Agora vamos colocar o DBMS_SQLTUNE em campo!
       
Privilégios necessários
   
Para utilizar a API deve ser dado o privilégio ADVISOR para o usuário.
sqlplus / AS SYSDBA
SQL> GRANT ADVISOR TO MARCUS_SOARES;
   
Criação do SQL Tuning Task
   
Para a criação do SQL Tuning task, vou utilizar as informações encontradas no AWR, então irei informar o snap_id inicial e final para identificar o período em que o SQL foi executado.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 43996,
                          end_snap    => 43997,
                          sql_id      => '2sk15bdfc6gaf',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 1200,
                          task_name   => '2sk15bdfc6gaf_AWR_tuning_task',
                          description => 'Tuning task for statement 2sk15bdfc6gaf in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
   
Obs.: O paramêtro scope recebe o valor SCOPE_COMPREHENSIVE para que também seja gerados SQL Profiles, se possível.
   
Executando o SQL Tuning Task
   
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '2sk15bdfc6gaf_AWR_tuning_task');
 
Outros comandos usuais:
 
-- Interrompendo e reassumindo a execução
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => '2sk15bdfc6gaf_AWR_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => '2sk15bdfc6gaf_AWR_tuning_task'); 

-- Cancelando o SQL tuning task. 
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => '2sk15bdfc6gaf_AWR_tuning_task'); 

-- Reiniciando o SQL Tuning Task, permintindo sua execução novamente. 
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => '2sk15bdfc6gaf_AWR_tuning_task')
  
Verificando status do SQL Tuning Task
   
SELECT task_name, status 
  FROM dba_advisor_log 
 WHERE owner = 'MARCUS_SOARES';


TASK_NAME                      STATUS
------------------------------ -----------
2sk15bdfc6gaf_AWR_tuning_task   COMPLETED
   
Visualizando as recomendações geradas pelo SQL Tuning Task
   
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('2sk15bdfc6gaf_AWR_tuning_task') AS recommendations 
  FROM dual;
   
No caso, umas das recomendações a criação do aplicação do seguinte SQL Profile:   
   
6- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
 Foi encontrado um plano de execução potencialmente melhor para esta
 instrução.

Recommendation (estimated benefit: 98.92%)
 -----------------------------------------------------------------------
 - Considere a aceitação do perfil SQL recomendado.
 execute dbms_sqltune.accept_sql_profile(task_name =>
 '5h3s41pv9hxuk_AWR_tuning_task', task_owner => 'SYS', replace =>
 TRUE);
   
Com isso, para a aplicação do SQL Profile gerado, deve ser utilizado o comando recomendado. E terminanos a execução do SQL Tuning Advisor utilizando o DBMS_SQLTUNE.
   
Referências:
   
MOS notes:
Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor [ID 262687.1]
   
Outros:
Automatic SQL Tuning in Oracle 10g
   
comentários
  1. […]     Com isso, para a aplicação do SQL Profile gerado, deve ser utilizado o comando recomendado. E terminanos a execução do SQL Tuning Advisor utilizando o DBMS_SQLTUNE.     Referências:     MOS notes: Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor [ID 262687.1]     Outros: Automatic SQL Tuning in Oracle 10g http://www.oracle-base.com/articles/10g/automatic-sql-tuning-10g.php     Blog auxiliar: https://marcussoaresoracle.wordpress.com/2013/03/08/executando-o-sql-tuning-advisor-dbms_sqltune/#mor… […]

    • Germano Packer disse:

      Marcus, li vários de seus posts e gostei muito, muito bem explicado e passo a passo.
      com relação a esse post, tenho uma dúvida.
      No item 6, quando vc diz “Com isso, para a aplicação do SQL Profile gerado, deve ser utilizado o comando recomendado. E terminanos a execução do SQL Tuning Advisor utilizando o DBMS_SQLTUNE. “.
      1) Se eu aplicar o comando solicitado, o que o Oracle fará? Quando ele for processar um novo comando sql identico ao anterior, ele “perceberá” isso e substituirá o antigo pelo novo ?
      2) Se eu precisar verificar todas as alterações dessa forma que eu fiz e desfazer uma delas, como devo fazer ?

      • Boa tarde, Germano!
        Obrigado pelo elogio e continue vindo aqui, que estou preparando novos posts!
        Bem, ao aplicar o SQL Profile, o Oracle irá substituir o plano de execução antigo, normalmente de menor performance, pelo que foi criado através do SQL Tuning Advisor. Caso não obtenha o resultado esperado, você pode dropar o SQL Profile criado anteriormente, com a procedure DBMS_SQLTUNE.drop_sql_profile.

        Ex.:

        BEGIN
        DBMS_SQLTUNE.drop_sql_profile (
        name => ‘5h3s41pv9hxuk_AWR_tuning_task’,
        ignore => TRUE);
        END;
        /

  2. Valter disse:

    Ótimo post Marcus, obrigado por compartilhar sua experiência.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s