SQL Tuning

How to Run SQL Tuning Report for Specific SQL ID Manually

I know there could be more convenient to run SQL tuning reports 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 Report

In short, there’re 3 steps in this procedure to generate SQL tuning report: (1) Creating, (2) Executing and (3) Reporting.

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.

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

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. Please note that, it will be more readable if you execute the statements in database tools like SQL Developer.

Accepting New SQL Profile

If any sql profile is recommended, 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

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

2 thoughts on “How to Run SQL Tuning Report for Specific SQL ID Manually

Leave a Reply

Your email address will not be published. Required fields are marked *