How to Run DBMS_SQLTUNE for Specific SQL

SQL Tuning Advisor

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 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

Using DBMS_SQLTUNE.CREATE_TUNING_TASK.

2. Executing

Using DBMS_SQLTUNE.EXECUTE_TUNING_TASK.

3. Reporting

Using DBMS_SQLTUNE.REPORT_TUNING_TASK.

CREATE_TUNING_TASK

First of all, we create a task of SQL tuning for a specific SQL ID. The subprogram DBMS_SQLTUNE.CREATE_TUNING_TASK is actually a function and returns the task name it created.

SQL> select DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_21zq47mj49f7w_0105_01') task_name from dual;

TASK_NAME
------------------------------
SQLTUNE_21zq47mj49f7w_0105_01

Note: The task name is limited to 30 characters.

ORA-13780

If you got error ORA-13780, which means that the SQL ID does not exist in memory (SQL area) at this moment.

In such situation, you may specify an interval of AWR snapshot to indicate the existence period of the SQL statement. The interval can by set by BEGIN_SNAP and END_SNAP.

In order to set the two parameters (in NUMBER) properly, you have to check the most recent AWR snapshot ID in advance.

Let's see an example.

SQL> select DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_21zq47mj49f7w_0105_01', begin_snap => 100, end_snap => 124 ) task_name from dual;

Please refer to: DBMS_SQLTUNE.CREATE_TUNING_TASK Functions of 19c database.

EXECUTE_TUNING_TASK

Now we can execute the task.

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( task_name => 'SQLTUNE_21zq47mj49f7w_0105_01');

PL/SQL procedure successfully completed.

Check the status of this tuning task.

SQL> select status from dba_advisor_tasks where task_name = 'SQLTUNE_21zq47mj49f7w_0105_01';

STATUS
-----------
EXECUTING

REPORT_TUNING_TASK

To get the report, you'd better output the result by GUI tools.

SQL> select dbms_sqltune.report_tuning_task( 'SQLTUNE_21zq47mj49f7w_0105_01' ) report from dual;

For example, "HUGECLOB" is displayed in the data grid if you were using Toad for Oracle.

Just double click it to view the whole report.

Another way to generate report by a PL/SQL block:

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.

ACCEPT_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.

CREATE_SQL_PLAN_BASELINE

If you found specific plan is good enough to be 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);

The last argument is the most optimized plan hash value that you want it to be the baseline. The value can be found in the report.

Sometimes, you may not need to run the SQL tuning report for specific SQL, because SYS's auto SQL tuning task may already have run it for you.