SQL Tuning Advisor against sql_id's in AWR

We were in a situation very recently to run SQL Tuning Advisor against a bunch of SQL statements that appeared in the AWR's ADDM recommendations report. The initial effort to launch SQL Tuning Advisor against the SQL_ID couldn't go through as the SQL didn't exist in the shared pool.

Since the sql_id was present in the AWR report, thought of running the advisory against the AWR data, and found a very nice and precisely explained at the following blog:

http://www.redstk.com/running-sql-tuning-advisor-against-awr-data/


---- Example how to run SQL Tuning advisor against sql_id in AWR

variable stmt_task VARCHAR2(64);
SQL> exec :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK (begin_snap => 4118, end_snap => 4119, sql_id => 'caxcavmq6zkv9' , scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'sql_tuning_task01' );

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task01');

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_task01';

set long 50000
set longchunksize 500000
SET LINESIZE 150
Set pagesize 5000
 
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task01') FROM DUAL;


SQL> exec DBMS_SQLTUNE.drop_tuning_task(task_name =>'sql_tuning_task01');


References:
https://docs.oracle.com/database/121/ARPLS/d_sqltun.htm#ARPLS220
https://uhesse.com/2013/10/11/oracle-sql-tuning-advisor-on-the-command-line/



Happy reading/learning.