Toad World

SQL Tuning Advisor against sql_id’s in AWR

Avatar photo
Post author:Written By
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 was doesn’t exists 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 precised explanation 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');

Happy reading/learning.

Tags:

About the Author

Syed Jaffar Hussain

An Oracle Database Expert for over 15 years from his 20 years of Information Technology (IT) career. Over the past 15 years of Oracle journey, he involved with several local and large scaled international banks where he implemented and managed highly complex cluster and non-cluster environments with over 100’s of business critical databases. Recognizing his efforts and contribution towards the Oracle community, Oracle awarded him the prestigious ‘Best DBA of the year, 2011’ and Oracle ACE Director status. He also acquired industry best Oracle credentials, Oracle Certified Master (OCM), Oracle RAC Expert, OCP DBA 8i,9i,10g & 11g in addition to ITIL Expertise. Syed is an active Oracle speaker, regularly presents technical sessions and webinars on various Oracle database technologies at many Oracle events. You can visit his technical blog, http://jaffardba.blogspot.com where he discuss and writes the workaround/solution about the issues confronted from his day-to-day activities. Apart from being the part of the core Technical Review committee member for a few Oracle technology oriented books, he also co-authored an Oracle 11g R1/R2 Real Application Cluster Essentials and Oracle Expert RAC books. His blog can be found at http://jaffardba.blogspot.com/

Notable Replies

  1. says:
    Dave_H2

    Hello Syed,
    Is there an access advisor similar to the tuning advisor? I have looked at so many tables and dont see one and didnt know if i was missing something.

    Thanks
    Dave

Continue Discussion