Executando o SQL Tuning Advisor – DBMS_SQLTUNE

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
Blog auxiliar:

Deixe um comentário

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detectado !

Verificamos que está usando alguma extensão para bloquear os anúncios. O GPO (Grupo de Profissionais Oracle) obtém a sua renda através dos anúncios, para assim manter toda a estrutura dedicada a universalização do conhecimento.

Se você gosta de nosso trabalho, pedimos por gentileza que desabilite o ads blocker. Trabalhamos somente com o Google Adsense e tentamos ao máximo exibir apenas o necessário.

Agradecemos de antemão ! :)

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock