Conceito
O SQL Tuning Advisor é um recurso do Oracle Database que automatiza a atividade de tuning básica em instruções SQL, analisando a sugerindo possíveis índices, pequenas mudanças no código ou simplesmente gerando um novo plano de execução mais otimizado que o atual, o qual a gente pode implementar aceitando o profile criado pelo Advisor.
Existem vários posts na internet demonstrando como utilizar este recurso, mas neste post o objetivo não é mostrar como usar o Tuning Advisor em si, mas sim compartilhar uma abordagem que eu utilizo no dia a dia para “automatizar” e simplificar a criação de novas tarefas para um SQL ID, sem ficar copiando script para um lado e para o outro durante uma situação de crise.
Se você já utilizou esse recurso pelo Enterprise Manager, já deve ter notado que após selecionar todas as opções na interface web e submeter a tarefa para execução, o EM cria um JOB em segundo plano em que você não precisa ficar aguardando a conclusão com tela “presa” naquela operação, você simplesmente pode voltar depois e analisar o resultado.
Na prática, o que EM faz é criar um JOB via DBMS_SCHEDULER de execução única e AUTO-DROP no banco de dados, passando todo o script de execução do SQL Tuning Advisor dentro do JOB_ACTION como bloco PL/SQL anônimo. Essa abordagem me inspirou a fazer algo parecido com scripts que possam ser executados via SQLPLUS ou SQLcl, e é exatamente isso que quero demonstrar neste post.
Scripts utilizados
1) sta.sql
Esse script recebe como parâmetro apenas o SQL ID da query que você quer executar o SQL Tuning Advisor, e:
- Cria uma Tarefa no SQL Tuning Advisor
- Executa a tarefa em background via Oracle Scheduler (JOB com DBMS_SCHEDULER)
/*
Maicon Carneiro 04/11/2022
Script para executar o SQL Tune Advisor para um SQL ID automaticamente em Background
Exemplo: SQL> @sta <SQL_ID>
*/
SET SERVEROUTPUT ON;
declare
stmt_task VARCHAR2(40);
stmt_sqlid VARCHAR2(40) := '&1';
vJobName VARCHAR2(100);
begin
stmt_task := dbms_sqltune.create_tuning_task(sql_id => stmt_sqlid);
vJobName := 'STA_' || stmt_task || '_' || stmt_sqlid;
dbms_scheduler.create_job
(
job_name => vJobName,
job_type => 'PLSQL_BLOCK',
job_action => 'begin dbms_sqltune.execute_tuning_task(task_name => '''|| stmt_task ||'''); end;',
start_date => systimestamp,
enabled => true,
auto_drop => true,
comments => 'Run SQL Tuning Advisor Task ' || stmt_task || ' for SQL ID ' || stmt_sqlid
);
dbms_output.put_line ('JOB ' || vJobName || ' executing Task ' || stmt_task);
end;
/
Se o SQL não estiver disponível na Shared Pool, você pode criar um tarefa pegando o SQL ID a partir do AWR (Você precisa conhecer um intervalo de Snap ID em que o SQL foi executado). Eu manteno essa versão criada como sta.2sql:
/*
Maicon Carneiro 24/11/2022
Script para executar o SQL Tune Advisor para um SQL ID automaticamente em Background pegando dados do AWR
Exemplo: SQL> @sta2 <SQL_ID> <begin snap> <end snap>
*/
SET SERVEROUTPUT ON;
declare
stmt_task VARCHAR2(40);
stmt_sqlid VARCHAR2(40) := '&1';
vJobName VARCHAR2(100);
begin
stmt_task := dbms_sqltune.create_tuning_task(begin_snap => &2, end_snap=> &3, sql_id => stmt_sqlid);
vJobName := 'STA_' || stmt_task || '_' || stmt_sqlid || '_awr';
dbms_scheduler.create_job
(
job_name => vJobName,
job_type => 'PLSQL_BLOCK',
job_action => 'begin dbms_sqltune.execute_tuning_task(task_name => '''|| stmt_task ||'''); end;',
start_date => systimestamp,
enabled => true,
auto_drop => true,
comments => 'Run SQL Tuning Advisor Task ' || stmt_task || ' for SQL ID ' || stmt_sqlid
);
dbms_output.put_line ('JOB ' || vJobName || ' executing Task ' || stmt_task);
end;
/
2) stalist.sql
Essa query consulta as views da DBMS_SCHEDULER para mostrar na tela quais JOBs criados pelo sta.sql estão em execução, ou que foram executados nas últimas 24 horas.
set feedback off
SET SQLFORMAT
SET PAGES 50
SET LINES 400
COL JOB_NAME FORMAT A40
COL START_TIME FORMAT A20
COL STATUS FORMAT A15
COL STATE FORMAT A15
COL SQL_ID FORMAT A20
COL COMMENTS FORMAT A70
PROMP Jobs Pendentes:
SELECT JOB_NAME,
STATE,
TO_CHAR(START_DATE,'DD/MM/YYYY HH24:MI:SS') AS START_DATE,
SUBSTR(COMMENTS,1,70) AS COMMENTS
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME LIKE 'STA%';
PROMP
PROMP Jobs Finalizados:
SELECT JOB_NAME,
TO_CHAR(LOG_DATE,'DD/MM/YYYY HH24:MI:SS') AS LOG_DATE,
STATUS
FROM DBA_SCHEDULER_JOB_LOG
WHERE JOB_NAME LIKE 'STA%'
AND LOG_DATE >= SYSTIMESTAMP-1
ORDER BY LOG_ID DESC;
PROMP
3) starpt.sql
Nada demais aqui, é a query que todo mundo já tem em casa. Apenas crio esse arquivo para facilitar na hora de consultar o resultado de uma tarefa sem precisar ficar copiando e colando query para um lado para o outro 🙂
set long 10000000;
set longchunksize 1000
set pagesize 10000
set lines 1000
set heading off
select dbms_sqltune.report_tuning_task(task_name=> UPPER('&1'), OWNER_NAME=> UPPER(USER) ) as recomendacoes from dual;
set heading on;
Demonstração
1) Assumindo que você já tem o SQL ID em mãos, execute o sta:
SQL> @sta 16yub4nbu4mvc
JOB STA_TASK_430540_16yub4nbu4mvc executing Task TASK_430540
Dica: Você anota o nome da Task apenas se quiser, ela vai aparecer no nome do JOB listado a seguir.
2) Acompanhe a execução do JOB recém criado ou consulte os que foram completados nas últimas 24 horas (a menos que tenha customizado o filtro):
SQL> @stalist
Jobs Pendentes:
JOB_NAME STATE START_DATE COMMENTS
---------------------------------------- --------------- ------------------- ----------------------------------------------------------------------
STA_TASK_430540_16YUB4NBU4MVC RUNNING 18/01/2023 21:09:38 Run SQL Tuning Advisor Task TASK_430540 for SQL ID 16yub4nbu4mvc
Jobs Finalizados:
JOB_NAME LOG_DATE STATUS
---------------------------------------- ------------------- ---------------
STA_TASK_430518_16YUB4NBU4MVC 18/01/2023 21:08:19 SUCCEEDED
STA_TASK_430379_3NWDYJ9P9CTUT 18/01/2023 12:03:35 SUCCEEDED
STA_TASK_430375_GT5DJ6B874TH9 18/01/2023 11:52:42 SUCCEEDED
STA_TASK_430380_G2GHB3J4GBS8D 18/01/2023 12:06:46 SUCCEEDED
STA_TASK_430376_1MYFXCU4K8D7Z 18/01/2023 12:22:55 SUCCEEDED
STA_TASK_430364_2S7KBMWM3WA4C 18/01/2023 11:21:15 SUCCEEDED
STA_TASK_430378_0DHX223NH3BVR 18/01/2023 12:03:22 SUCCEEDED
STA_TASK_430363_GW5RAZQV35BMS 18/01/2023 11:25:18 SUCCEEDED
3) Uma vez que o JOB tenha concluído com sucesso, podemos copiar o nome da Task no resultado do stalist e executar o starpt:
SQL> @starpt TASK_430518
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_430518
Tuning Task Owner : SYSTEM
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 01/18/2023 20:57:38
Completed at : 01/18/2023 21:08:04
-------------------------------------------------------------------------------
Schema Name: EXEMPLO
SQL ID : 16yub4nbu4mvc
SQL Text : SELECT * FROM EXEMPLO
........
Recommendation
2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 89.15%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_430518',
task_owner => 'SYSTEM', replace => TRUE);
Conclusão
Com essa abordagem, caso você tenha uma lista de SQL ID os quais deseja rodar o advisor, pode simplesmente executar o “@sta.sql <SQL ID>” para cada consulta, o que vai resultar em vários JOBs independentes executando em paralelo. Ou seja, em 10 segundos você cria 10 tarefas sem muito esforço, e sem se preocupar em fechar ou perder a conexão com o banco de dados enquanto a tarefa está em execução.
Observe que a parte do script que cria a tarefa do Tuning Advisor é bem simples, usando todos os parâmetros default da DBMS_SQLTUNE. Você pode customizar isso colocando os parâmetros que você geralmente utiliza, uma vez que o objetivo principal dos scripts disponibilizados neste post é apresentar uma abordagem mais produtiva para criação dos JOBs sob demanda.