How to Use SQL Tuning Advisor for a Specific SQL Statement in Oracle


The SQL Tuning Advisor helps identify and resolve performance issues with SQL queries. Below are the step-by-step instructions to tune a SQL statement using its SQL_ID.

Step 1: Create the Tuning Task

You can create a tuning task either using AWR snapshot information or directly for a running session.

a) Using AWR Snapshots:

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 11716,
                          end_snap    => 11717,
                          sql_id      => 'f4am5zd2tc2ys',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 6000,
                          task_name   => 'dyfp8d71pjym8_tuning_task',
                          description => 'Tuning task for statement f4am5zd2tc2ys in AWR.');
  DBMS_OUTPUT.put_line('Task ID: ' || l_sql_tune_task_id);
END;
/

b) For a Currently Running Session:

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'f4am5zd2tc2ys',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 6000,
                          task_name   => 'f4am5zd2tc2ys_tuning_task',
                          description => 'Tuning task for statement f4am5zd2tc2ys');
  DBMS_OUTPUT.put_line('Task ID: ' || l_sql_tune_task_id);
END;
/

Step 2: Execute the Tuning Task

Check the task status and execute it:

SELECT task_name, status
FROM dba_advisor_log
WHERE task_name LIKE 'f4am5zd2tc2ys_tuning_task%';

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

Step 3: Generate the Tuning Report

To review the tuning recommendations, run:

SET lines 200 pages 1000
SET LONG 999999999
SET longchunksize 200

SELECT DBMS_SQLTUNE.report_tuning_task('f4am5zd2tc2ys_tuning_task')
FROM dual;

Step 4: (Optional) Drop the Tuning Task

After reviewing the report, you may remove the tuning task to clean up:

EXEC DBMS_SQLTUNE.drop_tuning_task(task_name => 'f4am5zd2tc2ys_tuning_task');

Conclusion:

By following these steps, you can effectively leverage the SQL Tuning Advisor to analyze and optimize query performance using SQL_ID. Always review the advisor's suggestions carefully before applying them in production.

at