Sql tuning advisor was introduced by oracle from 10g onwards.  DBMS_SQLTUNE is the package , which helps the DBA’s in generating recommendations for bad sql queries.  Here we will use a simple query for sql tuning advisor and see what suggestion it is providing .

We will take below query:

select count(*) from bigtab where weight in ( select distinct( weight)  from bigtab);


This query is taking around 19 seconds.

Lets generate the explain for this .



Now time to use the sql tuning advisor manually. It has three steps.

1. creating the tuning task

2. executing the tuning task

3. reporting tuning task.


Creating tuning task:

Executing Tuning task:


Reporting tuning task:


Below recommendation is provided in the report.

Recommendation (estimated benefit<=10%)
– Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
‘TEST_sql_tuning_task1’, task_owner => ‘SYS’, replace => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);


Lets implement this recommendation and check the performance again.


We can see the changes in the execution plan . Also the query which was taking 19 seconds before, is now taking 11 seconds.