How to run SQL Tuning Advisor in Oracle

Steps to Run SQL Tuning Advisor Against Slow Running Queries in Oracle

===============================================================

STEP 1: Create tuning task for the specific SQL_ID:

declare
task_nm varchar2(100);
begin
task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '&SQL_ID',TASK_NAME => 'SQL_TUNNING_TASK_SQL_ID');
end;
/

NOTE: Replace sql_id in above statement


STEP 2: Check the status of newly created task:

SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';

STEP 3: Execute the newly created task:

exec dbms_sqltune.execute_tuning_task (TASK_NAME => '&TASK_NAME');
Note: Please replace the task name as mentioned in step 1

STEP 4: Check the status after executing the task:

SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';

*Task should be completed, it will take some time to complete.

STEP 5: Execute the Below Query to get the Advisory Report:


SET LONG 99999;
SET PAGESIZE 1000
SET LONGCHUNKSIZE 65536
SET LINESIZE 200

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;


NOTE: Replace task name in above query

STEP 6: To Drop the Tuning Task:

execute dbms_sqltune.drop_tuning_task('&TASK_NAME');


To execute tuning advisory using AWR snap ID if sql_id is not present in cursor:


STEP 7: Find the snap ID using below query:

SQL>
SELECT SQL_ID, PLAN_HASH_VALUE, TIMESTAMP FROM DBA_HIST_SQL_PLAN WHERE SQL_ID='&SQL_ID';

SQL>
select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='&SQL_ID' and plan_hash_value='&plan_hash_value' order by snap_id desc;



STEP 8: Create Tuning Task:

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 841,
end_snap => 891,
sql_id => '663zvxqjuxtqn',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '663zvxqjuxtqn_AWR_tuning_task',
description => 'Tuning task for statement 663zvxqjuxtqn in AWR');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

--NOTE: Replace the above highlighted values:



STEP 9: After creation of tuning task you can follow the above steps 

Monitor job progress :-

COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999
SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$ADVISOR_PROGRESS;