SQL Tuning Advisor

Hello friends,

Delayed blog update!

Worked on SQL Tuning Advisor recently, details as below:

The statements to be analyzed can be retrieved from the Automatic Workload Repository (AWR) or ADDM. Here, I have used the ADDM report to get the SQL statement for the mentioned period of time by the application team.

Ran the addmrpt.sql to generate the ADDM report.

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

For using the SQL tuning advisor, first create a new tuning task using the CREATE_TUNING_TASK function.

SET SERVEROUTPUT ON
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 2394,
                          end_snap    => 2406,
                          sql_id      => 'dsd586414tchz',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'dsd586414tchz_AWR_tuning_task',
                          description => 'Tuning task for statement dsd586414tchz in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

With the tuning task created, we now execute it using the EXECUTE_TUNING_TASK procedure.

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

The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view.

SELECT task_name, status FROM dba_advisor_log WHERE task_name='dsd586414tchz_AWR_tuning_task';

Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function.

SET LONG 999999999

SET PAGESIZE 500

SET LINESIZE 500

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

BEGIN

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

END;

/