Below are the steps to use SQL Tuning Advisor for particular SQL.
1) Create tuning task using SQL_ID
2) Execute tuning task
3) Generate report for the tuning task
STEP 1:
CREATE tuning tasks for SQL_ID picked from <<<<AWR>>>>>
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 11716,
end_snap => 11717,
sql_id => ‘f4am5zd2tc2ys’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 6000,
task_name => ‘dyfp8d71pjym8_tuning_task’,
description => ‘Tuning task for statement f4am5zd2tc2ys in AWR.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/
CREATE tuning tasks for SQL_ID for running session <<<<<Cursor Cache>>>>>
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => ‘f4am5zd2tc2ys’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 6000,
task_name => ‘f4am5zd2tc2ys_tuning_task’,
description => ‘Tuning task for statement f4am5zd2tc2ys’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/
STEP 2:
SELECT task_name, status FROM dba_advisor_log WHERE task_name like ‘f4am5zd2tc2ys_tuning_task%’;
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘f4am5zd2tc2ys_tuning_task’);
STEP 3:
SET lines 200 pages 1000
SET LONG 999999999
SET longchunksize 200
SELECT DBMS_SQLTUNE.report_tuning_task(‘f4am5zd2tc2ys_tuning_task’) from dual;
STEP 4: To drop the sql tuning tasks (OPTIONAL)
