How to Run DBMS_SQLTUNE for Specific SQL

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.

DBMS_SQLTUNE

Let's see how we use its procedures to manipulate SQL tuning task and get the report:

1. CREATE_TUNING_TASK
2. EXECUTE_TUNING_TASK
3. REPORT_TUNING_TASK
4. DROP_TUNING_TASK
5. ACCEPT_SQL_PROFILE
6. DROP_SQL_PROFILE
7. CREATE_SQL_PLAN_BASELINE

For convenience, we usually run the package in the root container if it's in a multitenant environment.

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.

DBA_ADVISOR_TASKS

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.

Get Report in SQL*Plus

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.

DROP_TUNING_TASK

If you don't like the tuning task, either its name or its report, you may drop it, all result data will be gone.

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

PL/SQL procedure successfully completed.

Some recommendations may contain alternate SQL profile. Next, we will see how we accept the SQL profile. If there's no recommended one, you may consider to create a SQL profile for the statement.

ACCEPT_SQL_PROFILE

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.

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.

V$SQL

To make sure that the SQL profile has been used by the SQL_ID, we can query V$SQL.

SQL> column sql_profile format a30;
SQL> select plan_hash_value, sql_profile from v$sql where sql_id = '21zq47mj49f7w';

PLAN_HASH_VALUE SQL_PROFILE
--------------- ------------------------------
      297152330 sql_profile_for_21zq47mj49f7w

It may need some times to reflect the change to the SGA. If SQL_PROFILE field is empty, please check it later.

GV$SQL

In a RAC system, it may have different PLAN_HASH_VALUE.

SQL> select inst_id, plan_hash_value, sql_profile from gv$sql where sql_id = '21zq47mj49f7w';

   INST_ID PLAN_HASH_VALUE SQL_PROFILE
---------- --------------- ------------------------------
         1      3860155831 sql_profile_for_21zq47mj49f7w
         2       297152330 sql_profile_for_21zq47mj49f7w

DBA_SQL_PROFILES

Check new profile status. Please note that, if you're in a multitenant container, you have to switch to the PDB.

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

DROP_SQL_PROFILE

If you don't like it, just drop it.

SQL> exec DBMS_SQLTUNE.DROP_SQL_PROFILE ( 'sql_profile_for_21zq47mj49f7w' );

PL/SQL procedure successfully completed.

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.

DBMS_ADVISOR

In the SQL tuning report, it may suggest you to consider running the Access Advisor to improve the physical schema design or creating the recommended index. The procedure may be tricky for tuning only a single SQL statement.

SYS_AUTO_SQL_TUNING_TASK

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 in maintenance windows everyday.