SQL Tuning Advisor: Uma explicação conceitual e exemplo de uso da API DBMS_SQLTUNE

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_taskCOMPLETED

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
• http://www.oracle-base.com/articles/10g/automatic-sql-tuning-10g.php