Tuning SQL queries

For the Oracle databases version 10.x.x.x or later

TO CREATE TUNNING TASK:

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => 'sql_id',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 180,

                          task_name   => 'task_name',

                          description => 'Tuning task for statement sql_id .');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

Note:

sql_id       --->sql_id of the query to be tuned which you can get it from AWR report or ADDM report

Task_name---> Name of the task (can be any name as per your choice)

TO CHECK STATUS :

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name ='task1';

TO EXECUTE TASK  

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'task1');

TO CHECK STATUS COMPLETED :

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name ='task1';

TO GET OUTPUT:

SET LINES 150

SET pages 50000

SET long 5000000

SET longc 5000000

SELECT DBMS_SQLTUNE.report_tuning_task('task1') AS recommendations FROM dual;

After Checking the recommendations from the above query don't forget to drop the task using below procedure

TO DROP THE TASK:

BEGIN

  DBMS_SQLTUNE.drop_tuning_task (task_name => 'task1');

END;

/