Automatic SQL Tuning in Oracle Database

This article is about automatic Sql Tunning in Oracle database, it is available from 10g version.

First step is to create the tunning task. To do that we need to know sql_id of the query, a begin snapshot and end snapshot in which this query was executed:

SQL> DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
  2    3                 begin_snap  => 27409 ,
                         end_snap    => 27451,
                          sql_id      => '6ay80wrrk0a5w',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 560,
                          task_name   => '6ay80wrrk0a5w_AWR_tuning_task',
                          description => 'Tuning task for statement 6ay80wrrk0a5w in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

  4    5    6    7    8    9   10   11   12   13   14
PL/SQL procedure successfully completed.

Also we can create the tunning task from cursor cache, to do that we need this query was executed recently and in located in cursor cache...

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '6ay80wrrk0a5w',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 560,
                          task_name   => '6ay80wrrk0a5w_AWR_tuning_task',
                          description => 'Tuning task for statement 6ay80wrrk0a5w.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Second step is to execute previously created tuning task:

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '6ay80wrrk0a5w_AWR_tuning_task');

PL/SQL procedure successfully completed.

Third step we can see recommendations generated by oracle with this query:

SET LONG 500000;
SET PAGESIZE 50000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('6ay80wrrk0a5w_AWR_tuning_task', 'TEXT', 'ALL', 'FINDINGS') from dual;
SET PAGESIZE 24

And finally, we can drop task by doing:

EXEC DBMS_SQLTUNE.drop_tuning_task (task_name => '6ay80wrrk0a5w_AWR_tuning_task');