Quando eseguiamo il tuning advisor di Oracle, specificando il sql_id di una query SQL, possiamo ottenere prezioni suggerimenti per migliorare le prestazioni delle istruzioni SQL.
In questo tutorial spiegherò come eseguire sql tuning advisor avendo a disposizione il sql_id di uno statement SQL.
Supponiamo che l’ID sql sia - 87s8z2zzpsg88
- Creazione un tuning task
Per prima cosa si crea un task
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '87s8z2zzpsg88',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '87s8z2zzpsg88_tuning_task11',
description => 'Tuning task1 for statement 87s8z2zzpsg88');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
- Esecuzione del tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '87s8z2zzpsg88_tuning_task11');
- Recupero dei suggerimenti del tuning advisor
set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('87s8z2zzpsg88_tuning_task11') from dual;
- Vedere la lista dei task esistenti
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = '87s8z2zzpsg88_tuning_task11';
- Eliminare un task esistente
execute dbms_sqltune.drop_tuning_task('87s8z2zzpsg88_tuning_task11');
Se il sql_id non è presente nel cursore, ma lo è nello snap AWR
Per prima cosa dobbiamo trovare lo snapshot di inizio e fine in cui si trova il sql_id
select
a.instance_number inst_id,
a.snap_id,a.plan_hash_value,
to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime,
abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions,
round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)"
from
dba_hist_SQLSTAT a,
dba_hist_snapshot b
where
sql_id='&sql_id'
and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by
snap_id desc, a.instance_number;
Da cui otteniamo
- begin_snap -> 235
- end_snap -> 240
- Ora creiamo il task del tuning advisor
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 235,
end_snap => 240,
sql_id => '24pzs2d6a6b13',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '24pzs2d6a6b13_AWR_tuning_task',
description => 'Tuning task for statement 24pzs2d6a6b13 in AWR');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
- Eseguiamo il tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '24pzs2d6a6b13_AWR_tuning_task');
- Recuperiamo i suggerimenti del tuning advisor
SET LONG 10000000;
SET PAGESIZE 100000000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('24pzs2d6a6b13_AWR_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24