Tuning SQL Statements Using SQL Tuning Advisor (without Enterprise Manager)

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)