When we run SQL tuning advisor against a SQL statement or sql_id, it provides tuning recommendations that can be done that query to improve performance. It might give suggestion to create few indexes or accepting a SQL profile.

Diagnostic and Tuning license is required to use this feature

In this below tutorial we will explain how to run sql tuning advisor against sql_Id.

Suppose the sql id is – 87s8z2zzpsg88

1. Create Tuning Task

2. Execute Tuning task:

3. Get the Tuning advisor report.

 

4. Get list of tuning task present in database:

We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG

5. Drop a tuning task:

 

What if the sql_id is not present in the cursor , but present in AWR snap?

SQL_ID =24pzs2d6a6b13

First we need to find the begin snap and end snap of the sql_id.

From here we can get the begin snap and end snap of the sql_id.

begin_snap -> 235
end_snap -> 240

1. Create the tuning task:

2. Execute the tuning task:

3. Get the tuning task recommendation report

 

SEE ALSO – COLLECTION OF USEFUL DATABASE MONITORING SCRIPT