DBMS_SQLTUNE

The DBMS_SQLTUNE package is the interface for tuning SQL on demand. The related package DBMS_AUTO_SQLTUNE package provides the interface for SQL Tuning Advisor run as an automated task.

DBMS_SQLTUNE Subprogram Groups 

Below are DBMS_SQLTUNE subprograms which are grouped by function.

DBMS_SQLTUNE SQL Tuning Advisor Subprograms
DBMS_SQLTUNE SQL Profile Subprograms
DBMS_SQLTUNE SQL Tuning Set Subprograms
DBMS_SQLTUNE Real-Time SQL Monitoring Subprograms
DBMS_SQLTUNE SQL Performance Reporting Subprograms

Let we discuss and practice for below Subprograms.

DBMS_SQLTUNE SQL Tuning Advisor Subprograms

This subprogram group provides an interface to manage SQL tuning tasks.

A=> CREATE_TUNING_TASK Functions

This function creates a SQL Tuning Advisor task.

You can use different forms of this function to:

Create a tuning task for a single statement given its text.
Create a tuning task for a single statement from the shared SQL area given its identifier.
Create a tuning task for a single statement from the workload repository given a range of snapshot identifiers.
Create a tuning task for a SQL tuning set.
Create a tuning task for SQL Performance Analyzer.

In all cases, the function mainly creates a SQL Tuning Advisor task and sets its parameters.

=> Create a tuning task for a single statement given its text.

set long 1000000000
Col recommendations for a200
DECLARE
  l_sql_tune_sql_text   VARCHAR2(900);
BEGIN
  l_sql_tune_sql_text  := DBMS_SQLTUNE.create_tuning_task (
    sql_text       => 'SELECT A.EMPLOYEE_ID,A.FIRST_NAME,A.LAST_NAME FROM EMPS A JOIN DEPS B USING (DEPARTMENT_ID) WHERE  A.JOB_ID IN (''SH_CLERK'',''HR_REP'')',
    scope       => DBMS_SQLTUNE.scope_comprehensive,
    time_limit  => 180,
    task_name   => 'tuning_task_for_sql_text ',
    description => 'Tuning task for statement sql_text ');
  DBMS_OUTPUT.put_line('l_sql_tune_sql_text: ' || l_sql_tune_sql_text);
END;
/
l_sql_tune_sql_text: tuning_task_for_sql_text

PL/SQL procedure successfully completed.

=> Create a tuning task for a single statement from the shared SQL area given its identifier

SQL> set long 1000000000
Col recommendations for a200
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
    sql_id      => '3d9sgg4jmra12',
    scope       => DBMS_SQLTUNE.scope_comprehensive,
    time_limit  => 180,
    task_name   => 'tuning_task_for_3d9sgg4jmra12',
    description => 'Tuning task for statement 3d9sgg4jmra12');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
l_sql_tune_task_id: tuning_task_for_3d9sgg4jmra12

PL/SQL procedure successfully completed.

=> Create a tuning task for a single statement from the workload repository given a range of snapshot identifiers.

SQL> set long 1000000000
Col recommendations for a200
DECLARE
  l_sql_tune_AWR VARCHAR2(100);
BEGIN
  l_sql_tune_AWR := DBMS_SQLTUNE.create_tuning_task (
    begin_snap => 202,
    end_snap => 203,
    sql_id      => '6hnhqahphpk8n',    
    scope       => DBMS_SQLTUNE.scope_comprehensive,
    time_limit  => 180,
    task_name   => 'tuning_task_for_AWR',
    description => 'Tuning task for statement awr');
  DBMS_OUTPUT.put_line('l_sql_tune_AWR: ' || l_sql_tune_AWR);
END;
/
PL/SQL procedure successfully completed.
SQL>

=> Create a tuning task for a SQL tuning set.

Get details of available STS as below
SQL> SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM USER_SQLSET;

NAME           SQLCNT DESCRIPTION
------------------------------
SQLT_FROM_AWR     1 STS From awr

SQL> set long 1000000000
Col recommendations for a200
DECLARE
  l_sql_tune_sts VARCHAR2(100);
BEGIN
  l_sql_tune_sts := DBMS_SQLTUNE.create_tuning_task (
    sqlset_name => 'SQLT_FROM_AWR',
    scope       => DBMS_SQLTUNE.scope_comprehensive,
    time_limit  => 180,
    task_name   => 'tuning_task_for_sts',
    description => 'Tuning task for statement sts');
  DBMS_OUTPUT.put_line('l_sql_tune_sts: ' || l_sql_tune_sts);
END;
/
PL/SQL procedure successfully completed.

SQL> SQL> col description FOR a40
SELECT task_name, description, advisor_name, execution_start, execution_end, status
     FROM dba_advisor_tasks
     WHERE owner='ABHI_TEST' AND task_name IN ('tuning_task_for_sql_text ','tuning_task_for_3d9sgg4jmra12','tuning_task_for_AWR','tuning_task_for_sts')
     ORDER BY task_id DESC;SQL>   2    3    4  

TASK_NAME					   DESCRIPTION				    ADVISOR_NAME				       EXECUTION EXECUTION STATUS
-------------------------------------------------- ---------------------------------------- -------------------------------------------------- --------- --------- -----------
tuning_task_for_sts				   Tuning task for statement sts	    SQL Tuning Advisor							   INITIAL
tuning_task_for_AWR				   Tuning task for statement awr	    SQL Tuning Advisor							   INITIAL
tuning_task_for_3d9sgg4jmra12			   Tuning task for statement 3d9sgg4jmra12  SQL Tuning Advisor							   INITIAL
tuning_task_for_sql_text			   Tuning task for statement sql_text	    SQL Tuning Advisor							   INITIAL

SQL> 

B=> EXECUTE_TUNING_TASK Function and Procedure

SQL>  EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tuning_task_for_sql_text ',execution_desc => ' this is execution for sql_text');

PL/SQL procedure successfully completed.

check status as below.

SQL> col description FOR a40
SELECT task_name, description, advisor_name, execution_start, execution_end, status
     FROM dba_advisor_tasks
     WHERE owner='ABHI_TEST' AND task_name IN ('tuning_task_for_sql_text ','tuning_task_for_3d9sgg4jmra12','tuning_task_for_AWR','tuning_task_for_sts')
     ORDER BY task_id DESC;SQL>   2    3    4  

TASK_NAME					   DESCRIPTION				    ADVISOR_NAME				       EXECUTION EXECUTION STATUS
-------------------------------------------------- ---------------------------------------- -------------------------------------------------- --------- --------- -----------
tuning_task_for_sts				   Tuning task for statement sts	    SQL Tuning Advisor							   INITIAL
tuning_task_for_AWR				   Tuning task for statement awr	    SQL Tuning Advisor							   INITIAL
tuning_task_for_3d9sgg4jmra12			   Tuning task for statement 3d9sgg4jmra12  SQL Tuning Advisor							   INITIAL
tuning_task_for_sql_text			   Tuning task for statement sql_text	    SQL Tuning Advisor				       30-JAN-22 30-JAN-22 COMPLETED

C=> REPORT_TUNING_TASK Function

This function displays the results of a ON DEMAND tuning task. By default the report is in text format.

SET LONG 10000000
SET PAGESIZE 50000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('tuning_task_for_sql_text ','TEXT', 'TYPICAL', 'ALL') AS recommendations FROM dual;

D=> IMPLEMENT_TUNING_TASK Procedure

This procedure implements a set of SQL profile recommendations made by SQL Tuning Advisor.

This Function is valid when there is recommendation for SQL_PROFILE in STA.

SQL> EXEC DBMS_SQLTUNE.IMPLEMENT_TUNING_TASK(task_name => 'tuning_task_for_sql_text ');

PL/SQL procedure successfully completed.

E=> SCRIPT_TUNING_TASK Function

This function creates a SQL*Plus script which can then be executed to implement a set of SQL Tuning Advisor recommendations.
SQL> select dbms_sqltune.script_tuning_task('tuning_task_for_sql_text ', 'INDEXES') from dual;

DBMS_SQLTUNE.SCRIPT_TUNING_TASK('TUNING_TASK_FOR_SQL_TEXT','INDEXES')
--------------------------------------------------------------------------------
-----------------------------------------------------------------
-- Script generated by DBMS_SQLTUNE package, advisor framework --
-- Use this script to implement some of the recommendations    --
-- made by the SQL tuning advisor.                   --
--                                   --
-- NOTE: this script may need to be edited for your system     --
--     (index names, privileges, etc) before it is executed. --
-----------------------------------------------------------------
create index ABHI_TEST.IDX$$_02330001 on ABHI_TEST.EMPS("JOB_ID");
create index ABHI_TEST.IDX$$_02330002 on ABHI_TEST.DEPS("DEPARTMENT_ID");

SQL> select dbms_sqltune.script_tuning_task('tuning_task_for_sql_text ', 'ALL' ) from dual;

DBMS_SQLTUNE.SCRIPT_TUNING_TASK('TUNING_TASK_FOR_SQL_TEXT','ALL')
--------------------------------------------------------------------------------
-----------------------------------------------------------------
-- Script generated by DBMS_SQLTUNE package, advisor framework --
-- Use this script to implement some of the recommendations    --
-- made by the SQL tuning advisor.                   --
--                                   --
-- NOTE: this script may need to be edited for your system     --
--     (index names, privileges, etc) before it is executed. --
-----------------------------------------------------------------
create index ABHI_TEST.IDX$$_02330001 on ABHI_TEST.EMPS("JOB_ID");
create index ABHI_TEST.IDX$$_02330002 on ABHI_TEST.DEPS("DEPARTMENT_ID");

F=> DROP_TUNING_TASK Procedure

This procedure drops a SQL tuning task. The task and all its result data are deleted.

SQL> EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'tuning_task_for_sql_text ');

PL/SQL procedure successfully completed.
SQL>

SQL> col description FOR a40
SELECT task_name, description, advisor_name, execution_start, execution_end, status
     FROM dba_advisor_tasks
     WHERE owner='ABHI_TEST' AND task_name IN ('tuning_task_for_sql_text ','tuning_task_for_3d9sgg4jmra12','tuning_task_for_AWR','tuning_task_for_sts')
     ORDER BY task_id DESC;SQL>   2    3    4  

TASK_NAME					   DESCRIPTION				    ADVISOR_NAME				       EXECUTION EXECUTION STATUS
-------------------------------------------------- ---------------------------------------- -------------------------------------------------- --------- --------- -----------
tuning_task_for_sts				   Tuning task for statement sts	    SQL Tuning Advisor							   INITIAL
tuning_task_for_AWR				   Tuning task for statement awr	    SQL Tuning Advisor							   INITIAL
tuning_task_for_3d9sgg4jmra12			   Tuning task for statement 3d9sgg4jmra12  SQL Tuning Advisor							   INITIAL