Skip to content

How to Run SQL Tuning Advisor for Specific SQL

DBMS_SQLTUNE

I know there could be more convenient to run SQL tuning reports by DBMS_SQLTUNE in tools like Enterprise Manager or TOAD. But in this post, I will talk about how to generate SQL tuning reports solely by executing literal statements for specific SQL ID in case you can't use tools, and the second, how to accept and implement the recommendations of SQL tuning thereafter.

Running SQL Tuning Advisor Report

In short, there're 3 actions in this procedure to generate SQL tuning report:

  1. Creating
  2. Executing
  3. Reporting

1. Creating

First of all, we create a task of SQL tuning for a specific SQL ID.

SET SERVEROUTPUT ON
DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_21zq47mj49f7w_0105_01');
  DBMS_OUTPUT.PUT_LINE('Task Name: ''' || task_name || ''' Tuning Task Created');
END;
/

Note: The task name is limited to 30 characters.

If you get the following error, which means that the SQL ID does not exist in memory (cursor cache) at this moment.

ORA-13780: SQL statement does not exist.

To solve this error, you need additionally to specify AWR snapshot interval (begin_snap and end_snap) for it. Please refer to: DBMS_SQLTUNE.CREATE_TUNING_TASK Functions of 19c database.

2. Executing

Now we can execute the task.

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( task_name => 'SQLTUNE_21zq47mj49f7w_0105_01');
END;
/

Check the status of this tuning task.

SELECT STATUS FROM DBA_ADVISOR_TASKS WHERE TASK_NAME = 'SQLTUNE_21zq47mj49f7w_0105_01';

3. Reporting

Get the report, you can either output the report in terminals or GUI tools. Normally, the later is better, but I show you how to format it in a terminal below.

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'SQLTUNE_21zq47mj49f7w_0105_01' ) FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE_21ZQ47MJ49F7W_0105_01')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : SQLTUNE_21zq47mj49f7w_0105_01
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 01/05/2018 19:35:41
Completed at       : 01/05/2018 19:37:00


DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE_21ZQ47MJ49F7W_0105_01')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: FIAP
SQL ID     : 21zq47mj49f7w
SQL Text   : SELECT  ...,

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE_21ZQ47MJ49F7W_0105_01')
----------------------------------------------------------------------------------------------------

Another way to generate report which may contain exhausted details is as this:

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
  tuning_report CLOB;
BEGIN
  tuning_report := DBMS_SQLTUNE.REPORT_TUNING_TASK ( task_name => 'SQLTUNE_21zq47mj49f7w_0105_01' , type => 'TEXT' , level => 'TYPICAL' , section => 'ALL' );
  DBMS_OUTPUT.PUT_LINE(tuning_report);
END;
/

There could be some recommendations in this report that you can implement all or part of them for performance benefit. For example, the advisor may recommend to create some appropriate indexes.

Please note that, it will be more readable if you get the report in database tools like SQL Developer or Toad for Oracle.

Some recommendations may contain alternate SQL profile. Next, we will see how we accept the SQL profile.

Accepting New SQL Profile

If any SQL profile is recommended in the report, you can implement and accept it like this:

SQL> exec dbms_sqltune.accept_sql_profile(task_name => 'SQLTUNE_21zq47mj49f7w_0105_01', task_owner => 'SYS', replace => TRUE, name => 'sql_profile_for_21zq47mj49f7w');

PL/SQL procedure successfully completed.

Check new profile status.

SQL> column name format a30;
SQL> column category format a15;
SQL> column status format a15;
SQL> select name, category, status from dba_sql_profiles;

NAME                           CATEGORY        STATUS
------------------------------ --------------- ---------------
sql_profile_for_21zq47mj49f7w  DEFAULT         ENABLED

Different SQL profile is just like another portrait of a person from a different angle, it may consider a different execution route for the same SQL statement.

Creating New SQL Baseline

If you found specific plan is good enough to set the baseline, you can accept the tuning result by this:

SQL> exec dbms_sqltune.create_sql_plan_baseline(task_name => 'SQLTUNE_21zq47mj49f7w_0105_01', owner_name => 'SYS', plan_hash_value => 1283456102);

4 thoughts on “How to Run SQL Tuning Advisor for Specific SQL”

Leave a Reply

Your email address will not be published.