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.