DBMS_SQLTUNE package to Run the Sql Tuning Advisor (ID 262687.1)

Check out the SQL TUNING document is from metalink (ID 262687.1).  It’s very handy when you need to get some advices to tune a poor SQL query using dbms_sqltune package. 

DECLARE

  my_task_name VARCHAR2(30);

  my_sqltext CLOB;

BEGIN

  my_sqltext := 'SELECT * '   ||

                'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

                           sql_text => my_sqltext,

                           user_name => 'SH',

                           scope => 'COMPREHENSIVE',

                           time_limit => 60,

                           task_name => ‘gls_sql_tuning_task’,

                           description => 'Task to tune a query on a specified PRODUCT');

END;

Execute the tuning task.:

Execute dbms_sqltune.Execute_tuning_task (task_name => ‘gls_sql_tuning_task’);

Check the status of the task using following query:

 select status from dba_advisor_log where task_name=‘gls_sql_tuning_task’;

View the Recommendations

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task(‘gls_sql_tuning_task’) from dual;

The report can suggest to add index, or gather statistics, or SQL profile.  In my case, the sql_profile was recommended and to accept it, the performance was improved 99.9%

execute dbms_sqltune.accept_sql_profile(task_name =>‘gls_sql_tuning_task’, task_owner => 'SYSTEM', replace => TRUE);