Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1)

Part Number B28419-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

125 DBMS_SQLTUNE

The DBMS_SQLTUNE package provides the interface to tune SQL statements.

The chapter contains the following topics:


Using DBMS_SQLTUNE


Overview

The DBMS_SQLTUNE package provides a number interrelated areas of functionality:

SQL Tuning Advisor

The SQL Tuning Advisor is one of a suite of Advisors, a set of expert systems that identifies and helps resolve database performance problems. Specifically, the SQL Tuning Advisor automates the tuning process of problematic SQL statements. That is, it takes one or more SQL statements as input and gives precise advice on how to tune the statements. The advice is provided is in the form of precise SQL actions for tuning the SQL along with their expected performance benefit.

The group of SQL Tuning Advisor Subprograms provide a task-oriented interface that lets you access the Advisor. You can call the following subprograms in the order given to use some of the SQL Tuning Advisor's features:

  1. You use the CREATE_TUNING_TASK Functions to create a tuning task for tuning a single statement or a group of SQL statements.

  2. The EXECUTE_TUNING_TASK Function & Procedure executes a previously created tuning task.

  3. The REPORT_TUNING_TASK Function displays the results of a tuning task.

  4. You use the SCRIPT_TUNING_TASK Function to create a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations

SQL Profile Subprograms

The SQL Tuning Advisor may recommend the creation of a SQL Profile to improve the performance of a statement. SQL Profiles consist of auxiliary statistics specific to the statement. The query optimizer makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount, resulting in poor execution plans. The SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to adjust these estimates.

The group of SQL Profile Subprograms provides a mechanism for delivering statistics to the optimizer that targets one particular SQL statement, and helps the optimizer make good decisions for that statement by giving it the most accurate statistical information possible. For example:

SQL Tuning Sets

The SQL Tuning Advisor input can be a single SQL statement or a set of statements. When tuning multiple statements in one advisor task, you give the input in the form of a SQL Tuning Set (STS). A SQL Tuning Set is a database object that stores SQL statements along with their execution context in a system-provided schema. SQL Tuning Sets provide an infrastructure for dealing with SQL workloads and simplify tuning of a large number of SQL statements.

SQL Tuning Sets store SQL statements along with

SQL Tuning Sets can be created by filtering or ranking SQL statements from several sources:

The complete group of SQL Tuning Set Subprograms facilitates this functionality. As examples:

Import/Export SQL Tuning Sets and SQL Profiles

You use DBMS_SQLTUNE subprograms to move SQL Profiles and SQL Tuning Sets from one system to another using a common programmatic model. In both cases, you create a staging table on the source system and populate that staging table with the relevant data. You then move that staging table to the destination system following the method of your choice (such as datapump, import/export, or database link), where it is used to reconstitute the objects in their original form. These steps are implemented by means of subprograms included in this package:

  1. Call the CREATE_STGTAB_SQLPROF Procedure or the CREATE_STGTAB_SQLSET Procedure to create the staging table on the source system.

  2. Call the PACK_STGTAB_SQLPROF Procedure or PACK_STGTAB_SQLSET Procedure to populate the staging table with information from the source system.

  3. Once you have moved the staging table to the destination system, you call the UNPACK_STGTAB_SQLPROF Procedure or the UNPACK_STGTAB_SQLSET Procedure to recreate the object on the new system.

See Also:

Oracle Database Performance Tuning Guide for more information about programmatic flow.

Automatic Tuning Task Functions

There is a reserved system task "SYS_AUTO_SQL_TUNING_TASK" that performs SQL Tuning in the maintenance window, within the Autotask framework. It automatically chooses a set of high-load SQL from AWR and runs the SQL Tuning Advisor upon them. It performs the same comprehensive analysis of any other SQL Tuning Task. In addition, it tests any SQL Profiles it finds by executing both the old and new query plan and implements the ones with a large benefit. SQL Profiles are implemented immediately at the time of tuning so the system can automatically benefit from the new plan. In each maintenance window, the task stores its results as a new execution, so that all results over the lifetime of the task are available, connected to each other through the task name but kept distinct by their execution names. You can use the DBA_ADVISOR_EXECUTIONS views to see information about the task's executions.The automatic task is created by the system as part of the catalog scripts. It has its own special interface for displaying the report (the REPORT_AUTO_TUNING_TASK Function), and it shares the other interface with the standard tuning tasks. The task has its own report interface for viewing reports that span multiple executions.To set parameters of the automatic task, use the SET_TUNING_TASK_PARAMETER Procedures. To execute the task immediately, use the function version of the EXECUTE_TUNING_TASK Function & Procedure. Disabling the task is done by means of the DBMS_AUTO_TASK_ADMIN package.

Real-time SQL Monitoring

Real-time SQL Monitoring allows DBAs or performance analysts to monitor the execution of long running SQL statements while they are executing. Both cursor statistics (such as CPU times and IO times) and execution plan statistics (such as number of output rows, memory and temp space used) are updated close to real-time during statement execution. These statistics are exposed by two new fixed views, V$SQL_MONITOR and V$SQL_PLAN_MONITOR. In addition, DBMS_SQLTUNE provides a subprogram REPORT_SQL_MONITOR() to report on monitoring information.


Security Model

This package is available to PUBLIC and performs its own security checking:


Data Structures

The DBMS_SQLTUNE package defines the following OBJECT type

Object Types


SQLSET_ROW Object Type

The SQLSET_ROW object models the content of a SQL Tuning Set for the user. Logically, a SQL Tuning Set is a collection of SQLSET_ROWs where each SQLSET_ROW contains a single SQL statement along with its execution context, statistics, binds and plan. The SELECT_XXX subprograms each model a data source as a collection of SQLSET_ROWs, unique by (sql_id, plan_hash_value). Similarly, the LOAD_SQLSET procedure takes as input a cursor whose row type is SQLSET_ROW, treating each SQLSET_ROW in isolation according to the policies requested by the user.

Several subprograms in the DBMS_SQLTUNE package accept basic filters on the content of a SQL tuning set or data source. These filters are expressed in terms of the attributes within the SQLSET_ROW as defined.

Syntax

CREATE TYPE sqlset_row AS object (
  sql_id                   VARCHAR(13),
  force_matching_signature NUMBER,
  sql_text                 CLOB,
  object_list              sql_objects,
  bind_data                RAW(2000),
  parsing_schema_name      VARCHAR2(30),
  module                   VARCHAR2(48),
  action                   VARCHAR2(32),
  elapsed_time             NUMBER,
  cpu_time                 NUMBER,
  buffer_gets              NUMBER,
  disk_reads               NUMBER,
  direct_writes            NUMBER,
  rows_processed           NUMBER,
  fetches                  NUMBER,
  executions               NUMBER,
  end_of_fetch_count       NUMBER,
  optimizer_cost           NUMBER,
  optimizer_env            RAW(2000),
  priority                 NUMBER,
  command_type             NUMBER,
  first_load_time          VARCHAR2(19),
  stat_period              NUMBER,
  active_stat_period       NUMBER,
  other                    CLOB,
  plan_hash_value          NUMBER,
  sql_plan                 sql_plan_table_type,
  bind_list                sql_binds)

Attributes

Table 125-1 SQLSET_ROW Attributes

Attribute Description
sql_id Unique SQL ID
forcing_matching_signature Signature with literals, case, and whitespace removed
sql_text Full text for the statement
object_list Currently not implemented
bind_data Bind data as captured for this SQL. Note that you cannot stipulate an argument for this parameter and also for bind_list - they are mutually exclusive.
parsing_schema Schema where the SQL is parsed
module Last application module for the SQL
action Last application action for the SQL
elapsed_time Sum total elapsed time for this SQL statement
cpu_time Sum total CPU time for this SQL statement
buffer_gets Sum total number of buffer gets
disk_reads Sum total number of disk reads
direct_writes Sum total number of direct writes
rows_processed Sum total number of rows processed by this SQL
fetches Sum total number of fetches
executions Total executions of this SQL
end_of_fetch_count Number of times the statement was fully executed with all of its rows fetched
optimizer_cost Optimizer cost for this SQL
optimizer_env Optimizer environment for this SQL statement
priority User-defined priority (1,2,3)
command_type Statement type, such as INSERT or SELECT.
first_load_time Load time of parent cursor
stat_period Period of time (seconds) when the statistics of this SQL statement were collected
active_stat_period Effective period of time (in seconds) during which the SQL statement was active
other Other column for user defined attributes
plan_hash_value Plan hash value of the plan
sql_plan Explain plan
bind_list List of user specified binds for SQL This is used for user-specified workloads. Note that you cannot stipulate an argument for this parameter and also for bind_data - they are mutually exclusive.


Subprogram Groups

DBMS_SQLTUNE subprograms are grouped by function:


SQL Tuning Advisor Subprograms

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

Table 125-2 SQL Tuning Task Subprograms

Subprogram Description
CANCEL_TUNING_TASK Procedure
Cancels the currently executing tuning task
CREATE_TUNING_TASK Functions
Creates a tuning of a single statement or SQL tuning set for either the SQL Tuning Advisor
DROP_TUNING_TASK Procedure
Drops a SQL tuning task
EXECUTE_TUNING_TASK Function & Procedure
Executes a previously created tuning task
IMPLEMENT_TUNING_TASK Function
Implements a set of SQL Profile recommendations made by the SQL Tuning Advisor
INTERRUPT_TUNING_TASK Procedure
Interrupts the currently executing tuning task
REPORT_AUTO_TUNING_TASK Function
Displays a report from the automatic tuning task, reporting on a range of executions
REPORT_TUNING_TASK Function
Displays the results of a tuning task
RESET_TUNING_TASK Procedure
Resets the currently executing tuning task to its initial state
RESUME_TUNING_TASK Procedure
Resumes a previously interrupted task that was created to process a SQL tuning set.
SCRIPT_TUNING_TASK Function
Creates a SQL*PLUS script which can then be executed to implement a set of SQL Tuning Advisor recommendations
SET_TUNING_TASK_PARAMETER Procedures
Updates the value of a SQL tuning parameter of type VARCHAR2 or NUMBER.

The Summary of DBMS_SQLTUNE Subprograms contains a complete listing of all subprograms in the package.


SQL Profile Subprograms

This subprogram group provides an interface to manage SQL Profiles.

Table 125-3 SQL Profile Subprograms

Subprogram Description
ACCEPT_SQL_PROFILE Procedure and Function
Creates a SQL Profile for the specified tuning task
ALTER_SQL_PROFILE Procedure
Alters specific attributes of an existing SQL Profile object
CREATE_STGTAB_SQLPROF Procedure
Creates the staging table used for copying SQL profiles from one system to another.
DROP_SQL_PROFILE Procedure
Drops the named SQL Profile from the database
PACK_STGTAB_SQLPROF Procedure
Moves profile data out of the SYS schema into the staging table
REMAP_STGTAB_SQLPROF Procedure
Changes the profile data values kept in the staging table prior to performing an unpack operation
SQLTEXT_TO_SIGNATURE Function
Returns a SQL text's signature
UNPACK_STGTAB_SQLPROF Procedure
Uses the profile data stored in the staging table to create profiles on this system

The Summary of DBMS_SQLTUNE Subprograms contains a complete listing of all subprograms in the package.


SQL Tuning Set Subprograms

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

Table 125-4 SQL Tuning Set Subprograms

Subprogram Description
ADD_SQLSET_REFERENCE Function
Adds a new reference to an existing SQL tuning set to indicate its use by a client
CAPTURE_CURSOR_CACHE_SQLSET Procedure
Over a specified time interval incrementally captures a workload from the cursor cache into a SQL tuning set
CREATE_SQLSET Procedure and Function
Creates a SQL tuning set object in the database
CREATE_STGTAB_SQLSET Procedure
Creates a staging table through which SQL Tuning Sets are imported and exported
DELETE_SQLSET Procedure
Deletes a set of SQL statements from a SQL tuning set
DROP_SQLSET Procedure
Drops a SQL tuning set if it is not active
LOAD_SQLSET Procedure
Populates the SQL tuning set with a set of selected SQL
PACK_STGTAB_SQLSET Procedure
Copies tuning sets out of the SYS schema into the staging table
REMOVE_SQLSET_REFERENCE Procedure
Deactivates a SQL tuning set to indicate it is no longer used by the client
SELECT_CURSOR_CACHE Function
Collects SQL statements from the cursor cache
SELECT_SQLSET Function
Collects SQL statements from an existing SQL tuning set
SELECT_WORKLOAD_REPOSITORY Functions
Collects SQL statements from the workload repository
UNPACK_STGTAB_SQLSET Procedure
Copies one or more SQL tuning sets from the staging table
UPDATE_SQLSET Procedures
Updates whether selected string fields for a SQL statement in a SQL tuning set or the set numerical attributes of a SQL in a SQL tuning set

The Summary of DBMS_SQLTUNE Subprograms contains a complete listing of all subprograms in the package.


Real-time SQL Monitoring Subprograms

This subprogram group provides function to report on monitoring data collected in V$SQL_MONITOR and V$SQL_PLAN_MONITOR.

Table 125-5 SQL Tuning Set Subprograms

Subprogram Description
REPORT_SQL_MONITOR Function
Reports on real-time SQL Monitoring


Summary of DBMS_SQLTUNE Subprograms

Table 125-6 DBMS_SQLTUNE Package Subprograms

Subprogram Description Group
ACCEPT_SQL_PROFILE Procedure and Function
Create a SQL Profile for the specified tuning task SQL Profile Subprograms
ADD_SQLSET_REFERENCE Function
Adds a new reference to an existing SQL tuning set to indicate its use by a client SQL Tuning Set Subprograms
ALTER_SQL_PROFILE Procedure
Alters specific attributes of an existing SQL Profile object SQL Profile Subprograms
CANCEL_TUNING_TASK Procedure
Cancels the currently executing tuning task SQL Tuning Advisor Subprograms
CAPTURE_CURSOR_CACHE_SQLSET Procedure
Over a specified time interval incrementally captures a workload from the cursor cache into a SQL tuning set SQL Tuning Set Subprograms
CREATE_SQLSET Procedure and Function
Creates a SQL tuning set object in the database SQL Tuning Set Subprograms
CREATE_STGTAB_SQLPROF Procedure
Creates the staging table used for copying SQL profiles from one system to another. SQL Profile Subprograms
CREATE_STGTAB_SQLSET Procedure
Creates a staging table through which SQL Tuning Sets are imported and exported SQL Tuning Set Subprograms
CREATE_TUNING_TASK Functions
Creates a tuning of a single statement or SQL tuning set for either the SQL Tuning Advisor SQL Tuning Advisor Subprograms
DELETE_SQLSET Procedure
Deletes a set of SQL statements from a SQL tuning set SQL Tuning Set Subprograms
DROP_SQL_PROFILE Procedure
Drops the named SQL Profile from the database SQL Profile Subprograms
DROP_SQLSET Procedure
Drops a SQL tuning set if it is not active SQL Tuning Set Subprograms
DROP_TUNING_TASK Procedure
Drops a SQL tuning task SQL Tuning Advisor Subprograms
EXECUTE_TUNING_TASK Function & Procedure
Executes a previously created tuning task SQL Tuning Advisor Subprograms
IMPLEMENT_TUNING_TASK Function
implements a set of SQL Profile recommendations made by the SQL Tuning Advisor SQL Tuning Advisor Subprograms
INTERRUPT_TUNING_TASK Procedure
Interrupts the currently executing tuning task SQL Tuning Advisor Subprograms
LOAD_SQLSET Procedure
Populates the SQL tuning set with a set of selected SQL SQL Tuning Set Subprograms
PACK_STGTAB_SQLPROF Procedure
Moves profile data out of the SYS schema into the staging table SQL Profile Subprograms
PACK_STGTAB_SQLSET Procedure
Moves tuning sets out of the SYS schema into the staging table SQL Tuning Set Subprograms
REMAP_STGTAB_SQLPROF Procedure
Changes the profile data values kept in the staging table prior to performing an unpack operation SQL Profile Subprograms
REMAP_STGTAB_SQLSET Procedure
Changes the tuning set names and owners in the staging table so that they can be unpacked with different values than they had on the host system SQL Tuning Set Subprograms
REMOVE_SQLSET_REFERENCE Procedure
Deactivates a SQL tuning set to indicate it is no longer used by the client SQL Tuning Set Subprograms
REPORT_AUTO_TUNING_TASK Function
Displays a report from the automatic tuning task, reporting on a range of subtasks SQL Tuning Set Subprograms
REPORT_SQL_MONITOR Function
Displays a report on real-time SQL monitoring Real-time SQL Monitoring Subprograms
REPORT_TUNING_TASK Function
Displays the results of a tuning task SQL Tuning Set Subprograms
RESET_TUNING_TASK Procedure
Resets the currently executing tuning task to its initial state SQL Tuning Advisor Subprograms
RESUME_TUNING_TASK Procedure
Resumes a previously interrupted task that was created to process a SQL tuning set. SQL Tuning Advisor Subprograms
SCRIPT_TUNING_TASK Function
Creates a SQL*PLUS script which can then be executed to implement a set of SQL Tuning Advisor recommendations SQL Tuning Advisor Subprograms
SELECT_CURSOR_CACHE Function
Collects SQL statements from the cursor cache SQL Tuning Set Subprograms
SELECT_SQLSET Function
Collects SQL statements from an existing SQL tuning set SQL Tuning Set Subprograms
SELECT_WORKLOAD_REPOSITORY Functions
Collects SQL statements from the workload repository SQL Tuning Set Subprograms
SET_TUNING_TASK_PARAMETER Procedures
Updates the value of a SQL tuning parameter of type VARCHAR2 or NUMBER. SQL Tuning Set Subprograms
SQLTEXT_TO_SIGNATURE Function
Returns a SQL text's signature SQL Profile Subprograms
UNPACK_STGTAB_SQLPROF Procedure
Uses the profile data stored in the staging table to create profiles on this system SQL Profile Subprograms
UNPACK_STGTAB_SQLSET Procedure
Moves one or more SQL tuning sets from the staging table SQL Tuning Set Subprograms
UPDATE_SQLSET Procedures
Updates selected fields for a SQL statement in a SQL tuning set SQL Tuning Set Subprograms


ACCEPT_SQL_PROFILE Procedure and Function

This procedure creates a SQL Profile recommended by the SQL Tuning Advisor. The SQL text is normalized for matching purposes though it is stored in the data dictionary in de-normalized form for readability. SQL text is provided through a reference to the SQL Tuning task. If the referenced SQL statement doesn't exist, an error is reported.

See Also:

SQL Profile Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name    IN  VARCHAR2,
   object_id    IN  NUMBER   := NULL,
   name         IN  VARCHAR2 := NULL,
   description  IN  VARCHAR2 := NULL,
   category     IN  VARCHAR2 := NULL);
   task_owner   IN VARCHAR2  := NULL,
   replace      IN BOOLEAN   := FALSE,
   force_match  IN BOOLEAN   := FALSE); 

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name    IN  VARCHAR2,
   object_id    IN  NUMBER   := NULL,
   name         IN  VARCHAR2 := NULL,
   description  IN  VARCHAR2 := NULL,
   category     IN  VARCHAR2 := NULL;
   task_owner   IN VARCHAR2  := NULL,
   replace      IN BOOLEAN   := FALSE,
   force_match  IN BOOLEAN   := FALSE)
 RETURN VARCHAR2;

Parameters

Table 125-7 ACCEPT_SQL_PROFILE Procedure and Function Parameters

Parameter Description
task_name The (mandatory) name of the SQL tuning task
object_id The identifier of the advisor framework object representing the SQL statement associated with the tuning task
name The name of the SQL Profile. It cannot contain double quotation marks. The name is case sensitive. If not specified, the system will generate a unique name for the SQL Profile.
description A user specified string describing the purpose of the SQL Profile. The description is truncated if longer than 256 characters. The maximum size is 500 characters.
category This is the category name which must match the value of the SQLTUNE_CATEGORY parameter in a session for the session to use this SQL Profile. It defaults to the value "DEFAULT". This is also the default of the SQLTUNE_CATEGORY parameter. The category must be a valid Oracle identifier. The category name specified is always converted to upper case. The combination of the normalized SQL text and category name creates a unique key for a SQL Profile. An ACCEPT_SQL_PROFILE will fail if this combination is duplicated.
task_owner Owner of the tuning task. This is an optional parameter that has to be specified to accept a SQL Profile associated to a tuning task owned by another user. The current user is the default value.
replace If the profile already exists, it will be replaced if this argument is TRUE. It is an error to pass a name that is already being used for another signature/category pair, even with replace set to TRUE.
force_match If TRUE this causes SQL Profiles to target all SQL statements which have the same text after normalizing all literal values into bind variables. (Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.) This is analogous to the matching algorithm used by the FORCE option of the cursor_sharing parameter.

If FALSE, literals are not transformed. This is analogous to the matching algorithm used by the EXACT option of the cursor_sharing parameter.


Return Values

The name of the SQL profile.

Usage Notes

The CREATE ANY SQL PROFILE privilege is required.

Examples

You use both the procedure and the function versions of the subprogram in the same way except you must specify a return value to invoke the function. Here we give examples of the procedure only.

In this example, you tune a single SQL statement form the workload repository and you create the SQL profile recommended by SQL Tuning Advisor.

variable stmt_task VARCHAR2(64);
variable sts_task VARCHAR2(64);
 
-- create a tuning task tune the statement 
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
   begin_snap  => 1, -
   end_snap    => 2, -
   sql_id      => 'ay1m3ssvtrh24');
 
-- execute the resulting task 
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);
 
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(:stmt_task);
 

Note that you do not have to specify the ID (that is, object_id) for the advisor framework object created by SQL Tuning Advisor to represent the tuned SQL statement.

You might also want to accept the recommended SQL profile in a different category, (for example, TEST), so that it will not be used by default.

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name  =>  :stmt_task, -
   category   =>  'TEST');

You can use command ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST' to see how this profile behaves.

The following call creates a SQL profile that targets any SQL statement with the same force_matching_signature as the tuned statement.

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name   =>  :stmt_task, -
                                      force_match =>  TRUE);

In the following example, you tune a SQL tuning set, and you create a SQL profile for only one of the SQL statements in the SQL tuning set. The SQL statement is represented by an advisor framework object with ID equal to '5'. Please notice that you must pass an object id to the ACCEPT_SQL_PROFILE procedure because there are potentially many SQL profiles for the tuning task. This object id is given along with the report.

EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK ( -
  sqlset_name   => 'my_workload',  -
  rank1         => 'ELAPSED_TIME', -
  time_limit    => 3600,           -
  description   => 'my workload ordered by elapsed time');
 
-- execute the resulting task 
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:sts_task);

 -- create the profile for the sql statement corresponding to object_id = 5.
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name  =>  :sts_task, -
   object_id  =>  5);

ADD_SQLSET_REFERENCE Function

This procedure adds a new reference to an existing SQL tuning set to indicate its use by a client.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.ADD_SQLSET_REFERENCE (
   sqlset_name  IN  VARCHAR2,
   description  IN  VARCHAR2 := NULL)
 RETURN NUMBER;

Parameters

Table 125-8 ADD_SQLSET_REFERENCE Function Parameters

Parameter Description
sqlset_name The SQL tuning set name
description The description of the usage of SQL tuning set. The description is truncated if longer than 256 characters.

Return Values

The identifier of the added reference.

Examples

You can add reference to a SQL tuning set. This prevents the tuning set from being modified while it is being used. References are automatically added when you invoke SQL Tuning Advisor on the SQL tuning set, so you should use this function for custom purposes only.The function returns a reference ID that is used to remove it later. You use the REMOVE_SQLSET_REFERENCE Procedure to delete references to a SQL tuning set.

variable rid number; 
EXEC :rid := DBMS_SQLTUNE.ADD_SQLSET_REFERENCE( -
                                sqlset_name => 'my_workload', -
                                description => 'my sts reference');

You can use the views USER/DBA_SQLSET_REFERENCES to find all references on a given SQL tuning set.


ALTER_SQL_PROFILE Procedure

This procedure alters specific attributes of an existing SQL Profile object. The following attributes can be altered (using these attribute names):

See Also:

SQL Profile Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.ALTER_SQL_PROFILE (
   name                 IN  VARCHAR2,
   attribute_name       IN  VARCHAR2,
   value                IN  VARCHAR2);

Parameters

Table 125-9 ALTER_SQL_PROFILE Procedure Parameters

Parameter Description
name The (mandatory) name of the existing SQL Profile to alter
attribute_name The (mandatory) attribute name to alter (case insensitive) using valid attribute names
value The (mandatory) new value of the attribute using valid attribute values

Usage Notes

Requires the "ALTER ANY SQL PROFILE" privilege.

Examples

-- Disable a profile, so it will be not be used by any sessions.
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name             =>  :pname,   -
                                     attribute_name   =>  'STATUS', -
                                     value            =>  'DISABLED');
 
-- Enable it back:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name            =>   :pname,   -
                                      attribute_name  =>   'STATUS', -
                                      value           =>   'ENABLED');
 
-- Change the category of the profile so it will be used only by sessions
-- with category set to TEST.
-- Use ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST' to see how this profile 
-- behaves.
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name            =>  :pname,   -
                                      attribute_name  =>  'CATEGORY', -
                                      value           =>  'TEST');
 
-- Change it back:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name            =>  :pname,   -
                                      attribute_name  =>  'CATEGORY', -
                                      value           =>  'DEFAULT');

CANCEL_TUNING_TASK Procedure

This procedure cancels the currently executing tuning task. All intermediate result data is deleted.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.CANCEL_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

Table 125-10 CANCEL_TUNING_TASK Procedure Parameters

Parameter Description
task_name The name of the task to cancel

Examples

You cancel a task when you need to stop it executing and do not require to view any already-completed results.

EXEC DBMS_SQLTUNE.CANCEL_TUNING_TASK(:my_task);

CAPTURE_CURSOR_CACHE_SQLSET Procedure

Over a specified time interval this procedure incrementally captures a workload from the cursor cache into a SQL tuning set. The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (
    sqlset_name         IN VARCHAR2, 
    time_limit          IN POSITIVE := 1800,
    repeat_interval     IN POSITIVE := 300,
    capture_option      IN VARCHAR2 := 'MERGE',
    capture_mode        IN NUMBER   := MODE_REPLACE_OLD_STATS,
    basic_filter        IN VARCHAR2 := NULL,
    sqlset_owner        IN VARCHAR2 := NULL);

Parameters

Table 125-11 CAPTURE_CURSOR_CACHE_SQLSET Procedure Parameters

Parameter Description
sqlset_name The SQL tuning set name
time_limit The total amount of time, in seconds, to execute
repeat_interval The amount of time, in seconds, to pause between sampling
capture_option During capture, either insert new statements, update existing ones, or both. 'INSERT', 'UPDATE', or 'MERGE' just like load_option in load_sqlset
capture_mode capture mode (UPDATE and MERGE capture options).Possible values:
  • MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions seen is greater than that stored in the SQL tuning set

  • MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. Note that this mode detects if a statement has been aged out, so the final value for a statistics will be the sum of the statistics of all cursors that statement existed under.

basic_filter Filter to apply to cursor cache on each sampling (see select_xxx subprograms)
sqlset_owner The owner of the SQL tuning set or NULL for current schema owner

Examples

In this example capture takes place over a 30-second period, polling the cache once every five seconds. This will capture all statements run during that period but not before or after. If the same statement appears a second time, the process replaces the stored statement with the new occurrence.

Note that in production systems the time limit and repeat interval would be set much higher. You should tune the time_limit and repeat_interval parameters based on the workload time and cursor cache turnover properties of your system.

EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
                                        sqlset_name     => 'my_workload', -
                                        time_limit      =>  30, -
                                        repeat_interval =>  5);

In the following call you accumulate execution statistics as you go. This option produces an accurate picture of the cumulative activity of each cursor, even across age-outs, but it is more expensive than the previous example.

EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
                         sqlset_name     => 'my_workload', -
                         time_limit      => 30, -
                         repeat_interval => 5, -
                         capture_mode    => dbms_sqltune.MODE_ACCUMULATE_STATS);
 

This call performs a very inexpensive capture where you only insert new statements and do not update their statistics once they have been inserted into the SQL tuning set

EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
                                    sqlset_name     => 'my_workload', -
                                    time_limit      => 30, -
                                    repeat_interval => 5, -
                                    capture_option  => 'INSERT');

CREATE_SQLSET Procedure and Function

The procedure creates a SQL tuning set object in the database.

The function causes the system t o generate a name for the SQL Tuning Set.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.CREATE_SQLSET (
   sqlset_name  IN  VARCHAR2,
   description  IN  VARCHAR2 := NULL
   sqlset_owner IN  VARCHAR2 := NULL);

DBMS_SQLTUNE.CREATE_SQLSET (
   sqlset_name  IN  VARCHAR2 := NULL,
   description  IN  VARCHAR2 := NULL,
   sqlset_owner IN  VARCHAR2 := NULL)
 RETURN VARCHAR2;

Parameters

Table 125-12 CREATE_SQLSET Procedure Parameters

Parameter Description
sqlset_name The SQL tuning set name
description The description of the SQL tuning set
sqlset_owner The owner of the SQL tuning set, or NULL for the current schema owner

Examples

EXEC DBMS_SQLTUNE.CREATE_SQLSET(- 
  sqlset_name => 'my_workload', -
  description => 'complete application workload');

CREATE_STGTAB_SQLPROF Procedure

This procedure creates the staging table used for copying SQL profiles from one system to another.

See Also:

SQL Profile Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
   table_name            IN VARCHAR2,   schema_name           IN VARCHAR2 := NULL,   tablespace_name       IN VARCHAR2 := NULL);

Parameters

Table 125-13 CREATE_STGTAB_SQLPROF Procedure Parameters

Parameter Description
table_name The name of the table to create (case-insensitive unless double quoted). Required.
schema_name The schema to create the table in, or NULL for current schema (case-insensitive unless double quoted)
tablespace_name The tablespace to store the staging table within, or NULL for current user's default tablespace (case-insensitive unless double quoted)

Usage Notes

Examples

Create a staging table to store profile data that can be moved to another system.

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name  => 'PROFILE_STGTAB');

CREATE_STGTAB_SQLSET Procedure

This procedure creates a staging table through which SQL Tuning Sets are imported and exported

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (
   table_name           IN VARCHAR2,
   schema_name          IN VARCHAR2 := NULL,
   tablespace_name      IN VARCHAR2 := NULL);

Parameters

Table 125-14 CREATE_STGTAB_SQLSET Procedure Parameters

Parameter Description
table_name The name of the table to create (case-sensitive)
schema_name The schema in which to create the table in, or NULL for current schema (case-sensitive)
tablespace_name The tablespace in which to store the staging table, or NULL for current user's default tablespace (case-sensitive)

Usage Notes

Examples

Create a staging table for packing and eventually exporting a SQL tuning sets

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name => 'STGTAB_SQLSET');

CREATE_TUNING_TASK Functions

You can use different forms of this function to:

In all cases, the function mainly creates an advisor task and sets its parameters.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

SQL text format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_text         IN CLOB,
  bind_list        IN sql_binds := NULL,
  user_name        IN VARCHAR2  := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;

SQL ID format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;

Workload Repository format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  begin_snap       IN NUMBER,
  end_snap         IN NUMBER,
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;

SQLSET format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sqlset_name       IN VARCHAR2,
  basic_filter      IN VARCHAR2 :=  NULL,
  object_filter     IN VARCHAR2 :=  NULL,
  rank1             IN VARCHAR2 :=  NULL,
  rank2             IN VARCHAR2 :=  NULL,
  rank3             IN VARCHAR2 :=  NULL,
  result_percentage IN NUMBER   :=  NULL,
  result_limit      IN NUMBER   :=  NULL,
  scope             IN VARCHAR2 :=  SCOPE_COMPREHENSIVE,
  time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
  task_name         IN VARCHAR2 :=  NULL,
  description       IN VARCHAR2 :=  NULL
  plan_filter       IN VARCHAR2 :=  'MAX_ELAPSED_TIME',
  sqlset_owner      IN VARCHAR2 :=  NULL)
RETURN VARCHAR2;

Parameters

Table 125-15 CREATE_TUNING_TASK Function Parameters

Parameter Description
sql_text Text of a SQL statement
begin_snap Begin snapshot identifier
end_snap End snapshot identifier
sql_id Identifier of a SQL statement
bind_list An ordered list of bind values in ANYDATA type
plan_hash_value Hash value of the SQL execution plan
sqlset_name SQL tuning set name
basic_filter SQL predicate to filter the SQL from the SQL tuning set
object_filter Object filter
rank(i) Order-by clause on the selected SQL
result_percentage Percentage on the sum of a ranking measure
result_limit Top L(imit) SQL from the (filtered/ranked) SQL
user_name Username for whom the statement is to be tuned
scope Tuning scope (limited/comprehensive)
time_limit The maximum duration in seconds for the tuning session
task_name Optional tuning task name
description Description of the SQL tuning session to a maximum of 256 characters
plan_filter Plan filter. It is applicable in case there are multiple plans (plan_hash_value) associated with the same statement. This filter allows for selecting one plan (plan_hash_value) only. Possible values are:
  • LAST_GENERATED: plan with the most recent timestamp

  • FIRST_GENERATED: plan with the earliest timestamp, the opposite to LAST_GENERATED

  • LAST_LOADED: plan with the most recent first_load_time statistics information

  • FIRST_LOADED: plan with the earliest first_load_time statistics information, the opposite to LAST_LOADED

  • MAX_ELAPSED_TIME: plan with the maximum elapsed time

  • MAX_BUFFER_GETS: plan with the maximum buffer gets

  • MAX_DISK_READS: plan with the maximum disk reads

  • MAX_DIRECT_WRITES: plan with the maximum direct writes

  • MAX_OPTIMIZER_COST: plan with the maximum optimizer cost

sqlset_owner The owner of the SQL tuning set, or NULL for the current schema owner

Return Values

A SQL tuning task name that is unique by user (two different users can give the same name to their advisor tasks).

Usage Notes

With regard to the form of this subprogram that takes a SQL Tuning Set, filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.

Examples

variable stmt_task VARCHAR2(64);
variable sts_task  VARCHAR2(64);
 
-- Sql text format
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
  sql_text => 'select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-00'')');
 
-- Sql id format (cursor cache)
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24');
 
-- tune in limited scope 
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', -
   scope => 'LIMITED');
 
-- only give 10 minutes for tuning statement
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', -
   time_limit => 600);
 
-- Workload repository format
exec :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 1, -
   end_snap => 2, sql_id => 'ay1m3ssvtrh24');
 
-- Sql tuning set format (first we need to load an STS, then tune it)
 
-- Tune our statements in order by buffer gets, time limit of one hour
-- the default ranking measure is elapsed time.
EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
  sqlset_name  => 'my_workload', -
  rank1        => 'BUFFER_GETS', -
  time_limit   => 3600, -
  description  => 'tune my workload ordered by buffer gets');

DELETE_SQLSET Procedure

This procedure deletes a set of SQL statements from a SQL tuning set.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.DELETE_SQLSET (
   sqlset_name   IN  VARCHAR2,
   basic_filter  IN  VARCHAR2 := NULL,
   sqlset_owner  IN  VARCHAR2 := NULL);

Parameters

Table 125-16 DELETE_SQLSET Procedure Parameters

Parameter Description
sqlset_name The SQL tuning set name
basic_filter SQL predicate to filter the SQL from the SQL tuning set. This basic filter is used as a where clause on the SQL tuning set content to select a desired subset of SQL from the Tuning Set.
sqlset_owner The owner of the SQL tuning set, or NULL for current schema owner

Examples

-- Delete all statements in a sql tuning set.
EXEC DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name   => 'my_workload');
 
-- Delete all statements in a sql tuning set which ran for less than a second
EXEC DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name   => 'my_workload', -
                                basic_filter  => 'elapsed_time < 1000000');

DROP_SQL_PROFILE Procedure

This procedure drops the named SQL Profile from the database.

See Also:

SQL Profile Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.DROP_SQL_PROFILE (
   name          IN  VARCHAR2,
   ignore        IN  BOOLEAN  := FALSE);

Parameters

Table 125-17 DROP_SQL_PROFILE Procedure Parameters

Parameter Description
name The (mandatory) name of SQL Profile to be dropped. The name is case sensitive.
ignore Ignores errors due to object not existing

Usage Notes

Requires the "DROP ANY SQL PROFILE" privilege.

Examples

-- Drop the profile:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(:pname);

DROP_SQLSET Procedure

This procedure drops a SQL tuning set if it is not active.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.DROP_SQLSET (
   sqlset_name   IN  VARCHAR2,
   sqlset_owner  IN  VARCHAR2 := NULL); 

Parameters

Table 125-18 DROP_SQLSET Procedure Parameters

Parameter Description
sqlset_name The SQL tuning set name
sqlset_owner The owner of the SQL tuning set, or NULL for current schema owner

Usage Notes

You cannot drop a SQL tuning set when it is referenced by one or more clients.

Examples

-- Drop the sqlset.
EXEC DBMS_SQLTUNE.DROP_SQLSET ('my_workload');

DROP_TUNING_TASK Procedure

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

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.DROP_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

Table 125-19 DROP_TUNING_TASK Procedure Parameters

Parameter Description
task_name The name of the tuning task to drop


EXECUTE_TUNING_TASK Function & Procedure

This function and procedure executes a previously created tuning task. Both the function and the procedure run in the context of a new task execution. The difference is that the function version returns that new execution name.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
   task_name         IN VARCHAR2,
   execution_name    IN VARCHAR2               := NULL,
   execution_params  IN dbms_advisor.argList   := NULL,
   execution_desc    IN VARCHAR2               := NULL)
 RETURN VARCHAR2;

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
   task_name         IN VARCHAR2,
   execution_name    IN VARCHAR2               := NULL,
   execution_params  IN dbms_advisor.argList   := NULL,
   execution_desc    IN VARCHAR2               := NULL);

Parameters

Table 125-20 EXECUTE_TUNING_TASK Function & Procedure Parameters

Parameter Description
task_name Name of the tuning task to execute
execution_name A name to qualify and identify an execution. If not specified, it will be generated by the advisor and returned by function.
execution_params List of parameters (name, value) for the specified execution. The execution parameters have effect only on the execution for which they are specified. They will override the values for the parameters stored in the task (set via the SET_TUNING_TASK_PARAMETER Procedures).
execution_desc A 256-length string describing the execution

Usage Notes

A tuning task can be executed multiples times without having to reset it.

Examples

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);

IMPLEMENT_TUNING_TASK Function

This function implements a set of SQL Profile recommendations made by the SQL Tuning Advisor. Call this subprogram is equivalent to calling the SCRIPT_TUNING_TASK Function and then running the script.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.IMPLEMENT_TUNING_TASK(
    task_name      IN VARCHAR2,
    rec_type       IN VARCHAR2 := REC_TYPE_SQL_PROFILES,
    owner_name     IN VARCHAR2 := NULL,
    execution_name IN VARCHAR2 := NULL);

Parameters

Table 125-21 IMPLEMENT_TUNING_TASK Function Parameters

Parameter Description
task_name Name of the tuning task for which to implement recommendations
rec_type Filter the types of recommendations to implement. Only 'PROFILES' is supported.
owner_name Owner of the relevant tuning task or NULL for the current user.
execution_name name of the task execution to use. If NULL, recommendations from the last task execution will be implemented.


INTERRUPT_TUNING_TASK Procedure

This procedure interrupts the currently executing tuning task. The task will end its operations as it would at normal exit so that the user will be able access the intermediate results.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

Table 125-22 INTERRUPT_TUNING_TASK Procedure Parameters

Parameter Description
task_name Name of the tuning task to interrupt

Examples

EXEC DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(:my_task);

LOAD_SQLSET Procedure

This procedure populates the SQL tuning set with a set of selected SQL. You can call the procedure multiple times to add new SQL statements or replace attributes of existing statements.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.LOAD_SQLSET (
   sqlset_name       IN  VARCHAR2,
   populate_cursor   IN  sqlset_cursor,
   load_option       IN VARCHAR2 := 'INSERT', 
   update_option     IN VARCHAR2 := 'REPLACE', 
   update_condition  IN VARCHAR2 :=  NULL,
   update_attributes IN VARCHAR2 :=  NULL,
   ignore_null       IN BOOLEAN  :=  TRUE,
   commit_rows       IN POSITIVE :=  NULL,
   sqlset_owner      IN VARCHAR2 := NULL);

Parameters

Table 125-23 LOAD_SQLSET Procedure Parameters

Parameter Description
sqlset_name The SQL tuning set name to populate
populate_cursor The cursor reference from which to populate
load_option Specifies how the statements will be loaded into the SQL tuning set. The possible values are:
  • INSERT (default) - add only new statements

  • UPDATE - update existing the SQL statements and ignores any new statements

  • MERGE - this is a combination of the two other options. This option inserts new statements and updates the information of the existing ones.

update_option Specifies how the existing statements will be updated. This parameter is considered only if load_option is specified with 'UPDATE'/'MERGE' as an option. The possible values are:
  • REPLACE (default) - update the statement using the new statistics, bind list, object list, and so on.

  • ACCUMULATE - when possible combine attributes (for example, statistics like elapsed_time, and so on) otherwise just replace the old values (for example, module, action, and so on) by the new provided ones. The SQL statement attributes that can be accumulated are: elapsed_time, buffer_gets, direct_writes, disk_reads, row_processed, fetches, executions, end_of_fetch_count, stat_period and active_stat_period.

update_condition Specifies a where clause to execute the update operation. The update is performed only if the specified condition is true. The condition can refer to either the data source or destination. The condition must use the following prefixes to refer to attributes from the source or the destination:
  • OLD - to refer to statement attributes from the SQL tuning set (destination)

  • NEW - to refer to statements attributes from the input statements (source)

update_attributes Specifies the list of a SQL statement attributes to update during a merge or update operation.The possible values are:
  • NULL (default) - the content of the input cursor except the execution context. On other terms, it is equivalent to ALL without execution context like module, action, and so on.

  • BASIC - statistics and binds only

  • TYPICAL - BASIC + SQL plans (without row source statistics) and without object reference list

  • ALL - all attributes including the execution context attributes like module, action, and so on.

  • List of comma separated attribute names to update - EXECUTION_CONTEXT, EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN, SQL_PLAN_STATISTICS (similar to SQL_PLAN + row source statistics)

ignore_null If TRUE do not update an attribute if the new value is NULL. That is, do not override with NULL values unless intentional.
commit_rows If a value is provided, the load will commit after each set of that many statements is inserted. If NULL is provided, the load will commit only once, at the end of the operation.
sqlset_owner The owner of the SQL tuning set, or the current schema owner or NULL for current owner

Exceptions

Usage Notes

Rows in the input populate_cursor must be of type SQLSET_ROW.

Examples

In this example, you create and populate a SQL tuning set with all cursor cache statements with an elapsed time of 5 seconds or more excluding statements that belong to SYS schema (to simulate an application user workload). You select all attributes of the SQL statements and load them in the tuning set using the default mode, which will only load new statements, since the SQL tuning set is empty.

-- create the tuning set
EXEC DBMS_SQLTUNE.CREATE_SQLSET('my_workload');
-- populate the tuning set from the cursor cache
DECLARE
 cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN cur FOR
   SELECT VALUE(P)
     FROM table(
       DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
         'parsing_schema_name <> ''SYS'' AND elapsed_time > 5000000',
          NULL, NULL, NULL, NULL, 1, NULL,
         'ALL')) P;
 
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload',
                        populate_cursor => cur);
 
END;
/ 

Suppose now you wish to augment this information with what is stored in the workload repository (AWR). You populate the tuning set with 'ACCUMULATE' as your update_option because it is assumed the cursors currently in the cache had aged out since the snapshot was taken.

You omit the elapsed_time filter because it is assumed that any statement captured in AWR is important, but still you throw away the SYS-parsed cursors to avoid recursive SQL.

DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
      FROM table(
        DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,2,
                                                'parsing_schema_name <> ''SYS''',
                                                NULL, NULL,NULL,NULL,
                                                1,
                                                NULL,
                                                'ALL')) P;

  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload',
                           populate_cursor => cur,
                           Using DBMS_SQLTUNE
                           load_option => 'MERGE',
                           update_option => 'ACCUMULATE');
END;

The following example is a simple load that only inserts new statements from the workload repository, skipping existing ones (in the SQL tuning set). Note that 'INSERT' is the default value for the load_option argument of the LOAD_SQLSET procedure.

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
  SELECT VALUE(P)
  FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,2)) P;
  
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload',
populate_cursor => cur);
END;
/

The next example demonstrates a load with UPDATE option. This updates statements that already exist in the SQL tuning set but does not add new ones. By default, old statistics are replaced by their new values.

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P;
  
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload',
                           populate_cursor => cur,
                           load_option => 'UPDATE');
END;
/

PACK_STGTAB_SQLPROF Procedure

This procedure copies profile data from the SYS. schema into the staging table.

See Also:

SQL Profile Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
   profile_name          IN VARCHAR2 := '%',
   profile_category      IN VARCHAR2 := 'DEFAULT',
   staging_table_name    IN VARCHAR2,
   staging_schema_owner  IN VARCHAR2 := NULL);

Parameters

Table 125-24 PACK_STGTAB_SQLPROF Procedure Parameters

Parameter Description
profile_name The name of the profile to pack (% wildcards acceptable, case-sensitive)
profile_category The category to pack profiles from (% wildcards acceptable, case-sensitive)
staging_table_name The name of the table to use (case-insensitive unless double quoted). Required.
staging_schema_owner The schema where the table resides, or NULL for current schema (case-insensitive unless double quoted)

Usage Notes

Examples

Put only those profiles in the DEFAULT category into the staging table. This corresponds to all profiles that will be used by default on this system.

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROFILE_STGTAB');
 

This is another example where you put all profiles into the staging table. Note this will even move profiles that are not currently being used by default but are in other categories, such as for testing purposes.

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_category => '%', -
                                      staging_table_name => 'PROFILE_STGTAB');

PACK_STGTAB_SQLSET Procedure

This procedure copies one or more SQL tuning sets from their location in the SYS schema to a staging table created by the CREATE_STGTAB_SQLSET Procedure.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
   sqlset_name          IN VARCHAR2,
   sqlset_owner         IN VARCHAR2 := NULL,
   staging_table_name   IN VARCHAR2,
   staging_schema_owner IN VARCHAR2 := NULL);

Parameters

Table 125-25 PACK_STGTAB_SQLSET Procedure Parameters

Parameter Description
sqlset_name The name of the SQL Tuning Set to pack (% wildcards acceptable, case-sensitive)
sqlset_owner The category from which to pack SQL Tuning Sets (% wildcards acceptable, case-sensitive)
staging_table_name The name of the table to use (case-sensitive)
staging_schema_owner The schema where the table resides, or NULL for current schema (case-sensitive)

Usage Notes

Examples

Put all SQL tuning sets on the system in the staging table (to create a staging table, see the CREATE_STGTAB_SQLSET Procedure).

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name        => '%', -
                                     sqlset_owner       => '%', -
                                     staging_table_name => 'STGTAB_SQLSET');
 

Put only those SQL tuning sets owned by the current user in the staging table.

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name        => '%', -
                                     staging_table_name => 'STGTAB_SQLSET');
 

Pack a specific SQL tuning set.

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name        => 'my_workload', -
                                     staging_table_name => 'STGTAB_SQLSET');
 

Pack a second SQL tuning set.

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name        => 'workload_subset', -
                                     staging_table_name => 'STGTAB_SQLSET');

REMAP_STGTAB_SQLPROF Procedure

This procedure allows DBAs to change the profile data values kept in the staging table prior to performing an unpack operation. The procedure can be used to change the category of a profile.It can be used to change the name of a profile if one already exists on the system with the same name.

See Also:

SQL Profile Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF (
  old_profile_name      IN VARCHAR2,
  new_profile_name      IN VARCHAR2 := NULL,
  new_profile_category  IN VARCHAR2 := NULL,
  staging_table_name    IN VARCHAR2,
  staging_schema_owner  IN VARCHAR2 := NULL);

Parameters

Table 125-26 REMAP_STGTAB_SQLPROF Procedure Parameters

Parameter Description
old_profile_name The name of the profile to target for a remap operation (case-sensitive)
new_profile_name The new name of the profile, or NULL to remain the same (case-sensitive)
new_profile_category The new category for the profile, or NULL to remain the same (case-sensitive)
staging_table_name The name of the table on which to perform the remap operation (case-sensitive). Required.
staging_schema_owner The schema where the table resides, or NULL for current schema (case-sensitive)

Usage Notes

Using this procedure requires the UPDATE privilege on the staging table.

Examples

Change the name of a profile before we unpack, to avoid conflicts

EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF(old_profile_name    => :pname,          -
                                       new_profile_name    => 'IMP' || :pname, -
                                       staging_table_name  => 'PROFILE_STGTAB'); 
 

Change the SQL profile in the staging table to be 'TEST' category before we import it. This way users can test the profile on the new system before it is active.

EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF(old_profile_name     => :pname,       -
                                       new_profile_category => 'TEST',       -
                                       staging_table_name   => 'PROFILE_STGTAB');

REMAP_STGTAB_SQLSET Procedure

This procedure changes the tuning set names and owners in the staging table so that they can be unpacked with different values than they had on the host system.

See Also:

SQL Profile Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.REMAP_STGTAB_SQLSET (
   old_sqlset_name        IN VARCHAR2,
   old_sqlset_owner       IN VARCHAR2 := NULL,
   new_sqlset_name        IN VARCHAR2 := NULL,
   new_sqlset_owner       IN VARCHAR2 := NULL,
   staging_table_name     IN VARCHAR2,
   taging_schema_owner   IN VARCHAR2 := NULL);

Parameters

Table 125-27 REMAP_STGTAB_SQLSET Procedure Parameters

Parameter Description
old_sqlset_name The name of the tuning set to target for a remap operation. Wildcards are not supported.
old_sqlset_owner The new name of the tuning set owner to target for a remap operation. NULL for current schema owner
new_sqlset_name The new name for the tuning set, or NULL to keep the same tuning set name.
new_sqlset_owner The new owner for the tuning set, or NULL to remain the same owner name.
staging_table_name The name of the table on which to perform the remap operation (case-sensitive)
staging_schema_owner The name of staging table owner, or NULL for current schema owner (case-sensitive)

Usage Notes

You can call this procedure multiple times to remap more than one tuning set name or owner. Note that this procedure only handles one tuning set per call.

Examples

-- Change the name of an STS in the staging table before we unpack it.
EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLSET(old_sqlset_name    => 'my_workload', -
                                      old_sqlset_owner   => 'SH', -
                                      new_sqlset_name    => 'imp_workload', -
                                      staging_table_name => 'STGTAB_SQLSET');
 
-- Change the owner of an STS in the staging table before we unpack it.
EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLSET(old_sqlset_name    => 'imp_workload', -
                                      old_sqlset_owner   => 'SH', -
                                      new_sqlset_owner   => 'SYS', -
                                      staging_table_name => 'STGTAB_SQLSET');

REMOVE_SQLSET_REFERENCE Procedure

This procedure deactivates a SQL tuning set to indicate it is no longer used by the client.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE (
   sqlset_name   IN  VARCHAR2,
   reference_id  IN  NUMBER);

Parameters

Table 125-28 REMOVE_SQLSET_REFERENCE Procedure Parameters

Parameter Description
sqlset_name The SQL tuning set name
reference_id The identifier of the reference to remove

Examples

You can remove references on a given SQL tuning set when you finish using it and want to make it writable again.

EXEC DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE( -
                                sqlset_name   => 'my_workload', -
                                reference_id  => :rid);

Use views USER/DBA_SQLSET_REFERENCES to find all references on a given SQL tuning set.


REPORT_AUTO_TUNING_TASK Function

This function displays a report from the automatic tuning task. This differs from theREPORT_AUTO_TUNING_TASK Function in that it reports on a range of subtasks.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
    begin_exec     IN VARCHAR2  := NULL,
    end_exec       IN VARCHAR2  := NULL,
    type           IN VARCHAR2  := TYPE_TEXT,
    level          IN VARCHAR2  := LEVEL_TYPICAL,
    section        IN VARCHAR2  := SECTION_ALL,
    object_id      IN NUMBER    := NULL,
    result_limit   IN NUMBER    := NULL,
    segment_scheme IN VARCHAR2  := SEGMENT_NONE)
  RETURN CLOB;

Parameters

Table 125-29 REPORT_AUTO_TUNING_TASK Function Parameters

Parameter Description
begin_exec Name of execution from which to begin the report. NULL retrieves a report on the most recent run
end_exec Name of execution at which to end the report. NULL retrieves a report on the most recent run.
type Type of the report to produce. Possible values are TYPE_TEXT which produces a text report
level Level of detail in the report:
  • LEVEL_BASIC: simple version of the report. Just show info about the actions taken by the advisor.

  • LEVEL_TYPICAL: show information about every statement analyzed, including requests not implemented.

  • LEVEL_ALL: highly detailed report level, also provides annotations about statements skipped over.

section Optionally limit the report to a single section (ALL for all sections):
  • SECTION_SUMMARY - summary information

  • SECTION_FINDINGS - tuning findings

  • SECTION_PLAN - explain plans

  • SECTION_INFORMATION - general information

  • SECTION_ERROR - statements with errors

  • SECTION_ALL - all statements

object_id Advisor framework object id that represents a single statement to restrict reporting to. NULL for all statements. Only valid for reports that target a single execution.
result_limit Maximum number of SQL statements to show in the report
segment_scheme Currently non-operational

Return Values

A CLOB containing the desired report.


REPORT_SQL_MONITOR Function

This function builds a report (in Text, HTML or XML) to present the monitoring information collected with regard to the execution of a SQL statement. Note that this function is provided for ease of use and the structure or content of this report could change in future releases.

See Also:

Real-time SQL Monitoring Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.REPORT_SQL_MONITORK(
   sql_id                 IN VARCHAR2  := NULL,
   session_id             IN NUMBER    := NULL,
   session_serial         IN NUMBER    := NULL,
   sql_exec_start         IN DATE      := NULL,
   sql_exec_id            IN NUMBER    := NULL,
   inst_id                IN NUMBER    := -1,
   start_time_filter      IN DATE      := NULL,
   end_time_filter        IN DATE      := NULL,
   instance_id_filter     IN NUMBER    := NULL,
   parallel_filter        IN VARCHAR2  := NULL,
   event_detail           IN VARCHAR2  := 'YES',
   report_level           IN VARCHAR2  := 'TYPICAL',
   type                   IN VARCHAR2  := 'TEXT')
 RETURN CLOB;

Parameters

Table 125-30 REPORT_SQL_MONITOR Function Parameters

Parameter Description
sql_id SQL_ID for which monitoring information should be displayed. Use NULL (the default) to report on the last statement monitored by Oracle.
session_id If not NULL, this parameters targets only the sub-set of statements executed by the specified session. Default is NULL. Use USERENV('SID') for current session.
session_serial In addition to the session_id parameter, one can also specify its session serial to ensure that the desired session incarnation is targeted. This parameter is ignored when session_id is NULL.
sql_exec_start This parameter, along with sql_exec_id, is only applicable when sql_id is also specified. Jointly, they can be used to display monitoring information associated to any execution of the statement identified by sql_id, assuming that this statement was monitored. When NULL (the default), the last monitored execution of SQL sql_id is shown.
sql_exec_id This parameter, along with sql_exec_start, is only applicable when sql_id is also specified. Jointly, they can be used to display monitoring information associated to any execution of the statement identified by sql_id, assuming that this statement was monitored. When NULL (the default), the last monitored execution of SQL sql_id is shown.
inst_id Only considers statements started on the specified instance. Use -1 (the default) to target the login instance. NULL will target all instances.
start_time_filter If not NULL, the report will only consider the activity (from GV$ACTIVE_SESSION_HISTORY) recorded after the specified date. If NULL, the reported activity will start when the execution of the targeted SQL statement has started.
end_time_filter If not NULL, the report will show only the activity (from GV$ACTIVE_SESSION_HISTORY) collected before the date end_time_filter. If NULL, the reported activity will end when the targeted SQL statement execution has ended or is the current time if the statement is still executing.
instance_id_filter Only applies when the execution runs parallel across multiple Oracle Real Application Cluster (RAC) instances. This parameter allows to only report the activity of the specified instance. Use a NULL value (the default) to include the activity on all instances where the parallel query was executed.
parallel_filter Applies only to parallel execution and allows reporting the activity of only a subset of the processes involved in the parallel execution (Query Coordinator and/or Parallel eXecution servers). The value of this parameter can be:
  • NULL to target all processes

  • [qc][servers(<svr_grp>[,] <svr_set>[,] <srv_num>)]: 'qc' stands for query coordinator and servers() stipulate which PX servers to consider.

The following examples show how to target a subset of the parallel processes:

  • qc: targets only the query coordinator

  • servers(1): targets all parallel execution servers in group number 1. Note that statement running parallel have one main server group (group number 1) plus one additional group for each nested sub-query running parallel.

  • servers(,2): targets all parallel execution servers from any group but only running in set 1 of each group (each group has at most two set of parallel execution servers)

  • servers(1,1): consider only group 1, set 1

  • servers(1,2,4): consider only group 1, set 2, server number 4. This reports for a single parallel server process

  • qc servers(1,2,4): same as above by also including the query coordinator

event_detail When value is 'YES' (the default), reported activity from GV$ACTIVE_SESSION_HISTORY is aggregated by (wait_class, event_name). Use 'NO' to only aggregate by wait_class.
report_level Level of detail for the report, either 'BASIC', 'TYPICAL' or 'ALL'. Individual report sections can also be enabled/disabled by using a +/-<section_name>. Several sections are defined: 'PLAN', 'PARALLEL', 'SESSIONS', 'INSTANCE', and 'SQL_TEXT'. For example, use 'BASIC +PARALLEL' to show the basic report with an additional section reporting parallel information. Or use 'ALL -PLAN -INSTANCE' for a complete report excluding plan detail and instance information.
type Report format, 'TEXT' by default. Can be 'TEXT', 'HTML' or 'XML'.

Return Values

A CLOB containing the desired report.

Usage Notes

See Also:

Oracle Database Performance Tuning Guide for more information about SQL real-time monitoring.

REPORT_TUNING_TASK Function

This procedure displays the results of a tuning task.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.REPORT_TUNING_TASK(
   task_name       IN   VARCHAR2,
   type            IN   VARCHAR2   := TYPE_TEXT,
   level           IN   VARCHAR2   := LEVEL_TYPICAL,
   section         IN   VARCHAR2   := SECTION_ALL,
   object_id       IN   NUMBER     := NULL,
   result_limit    IN   NUMBER     := NULL,
   owner_name      IN    VARCHAR2  := NULL,
   execution_name  IN  VARCHAR2    := NULL))
RETURN CLOB;

Parameters

Table 125-31 REPORT_TUNING_TASK Function Parameters

Parameter Description
task_name Name of the tuning task to report
type Type of the report to produce. Possible values are TYPE TEXT which produces a text report.
level Level of detail in the report:
  • LEVEL_BASIC: simple version of the report. Just show info about the actions taken by the advisor.

  • LEVEL_TYPICAL: show information about every statement analyzed, including requests not implemented.

  • LEVEL_ALL: highly detailed report level, also provides annotations about statements skipped over.

section Optionally limit the report to a single section (ALL for all sections):
  • SECTION_SUMMARY - summary information

  • SECTION_FINDINGS - tuning findings

  • SECTION_PLAN - explain plans

  • SECTION_INFORMATION - general information

  • SECTION_ERROR - statements with errors

  • SECTION_ALL - all statements

object_id Advisor framework object id that represents a single statement to restrict reporting to. NULL for all statements. Only valid for reports that target a single execution.
result_limit Maximum number of SQL statements to show in the report
owner_name Owner of the relevant tuning task. Defaults to the current schema owner.
execution_name Name of the task execution to use. If NULL, the report will be generated for the last task execution.

Return Values

A CLOB containing the desired report.

Examples

-- Get the whole report for the single statement case.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:stmt_task) from dual;
 
-- Show me the summary for the sts case.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY')
FROM DUAL;
 
-- Show me the findings for the statement I'm interested in.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;

RESET_TUNING_TASK Procedure

This procedure is called on a tuning task that is not currently executing to prepare it for re-execution.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.RESET_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

Table 125-32 RESET_TUNING_TASK Procedure Parameters

Parameter Description
task_name The name of the tuning task to reset

Examples

-- reset and re-execute a task
EXEC DBMS_SQLTUNE.RESET_TUNING_TASK(:sts_task);
 
-- re-execute the task
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:sts_task);

RESUME_TUNING_TASK Procedure

This procedure resumes a previously interrupted task that was created to process a SQL tuning set.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.RESUME_TUNING_TASK(
 task_name         IN VARCHAR2,
 basic_filter      IN VARCHAR2 := NULL);

Parameters

Table 125-33 RESUME_TUNING_TASK Procedure Parameters

Parameter Description
task_name The name of the tuning task to resume
basic_filter A SQL predicate to filter the SQL from the SQL Tuning Set. Note that this filter will be applied in conjunction with the basic filter (i.e., parameter basic_filter) when calling CREATE_TUNING_TASK Functions.

Usage Notes

Resuming a single SQL tuning task (a task that was created to tune a single SQL statement as compared to a SQL Tuning Set) is not supported.

Examples

-- Interrupt the task
EXEC DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(:conc_task);
 
-- Once a task is interrupted, we can elect to reset it, resume it, or check
-- out its results and then decide.  For this example we will just resume.
 
EXEC DBMS_SQLTUNE.RESUME_TUNING_TASK(:conc_task);

SCRIPT_TUNING_TASK Function

This function creates a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations.

See Also:

SQL Tuning Advisor Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.SCRIPT_TUNING_TASK(
  task_name         IN VARCHAR2,
  rec_type          IN VARCHAR2  := REC_TYPE_ALL,
  object_id         IN NUMBER    := NULL,
  result_limit      IN NUMNBER   := NULL,
  owner_name        IN VARCHAR2  := NULL,
  execution_name    IN VARCHAR2  := NULL)
 RETURN CLOB;

Parameters

Table 125-34 SCRIPT_TUNING_TASK Procedure Parameters

Parameter Description
task_name Name of the tuning task for which to apply a script
rec_type Filter the script by types of recommendations to include. Any subset of the following separated by commas: or 'ALL: ''PROFILES' ''STATISTICS' ''INDEXES'. For example, a script with profiles and statistics: 'PROFILES,STATISTICS'
object_id Optionally filters by a single object ID
result_limit Optionally shows commands for only top N SQL (ordered by object_id and ignored if an object_id is also specified)
owner_name Owner of the relevant tuning task. Defaults to the current schema owner
excution_name Name of the task execution to use. If NULL, the script will be generated for the last task execution.

Return Values

Returns a script in the form of a CLOB.

Usage Notes

Examples

SET LINESIZE 140
 
-- Get a script for all actions recommended by the task.
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task) FROM DUAL;
 
-- Get a script of just the sql profiles we should create.
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'PROFILES') FROM DUAL;
 
-- get a script of just stale / missing stats
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'STATISTICS') FROM DUAL;
 
-- Get a script with recommendations about just one SQL statement when we have
-- tuned an entire STS.
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:sts_task, 'ALL', 5) FROM DUAL;

SELECT_CURSOR_CACHE Function

This function collects SQL statements from the SQL Cursor Cache.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
  basic_filter        IN   VARCHAR2 := NULL,
  object_filter       IN   VARCHAR2 := NULL,
  ranking_measure1    IN   VARCHAR2 := NULL,
  ranking_measure2    IN   VARCHAR2 := NULL,
  ranking_measure3    IN   VARCHAR2 := NULL,
  result_percentage   IN   NUMBER   := 1,
  result_limit        IN   NUMBER   := NULL,
  attribute_list      IN   VARCHAR2 := NULL)
 RETURN sys.sqlset PIPELINED;

Parameters

Table 125-35 SELECT_CURSOR_CACHE Procedure Parameters

Parameter Description
sqlset_name The SQL tuning set name
basic_filter The SQL predicate to filter the SQL from the cursor cache defined on attributes of the SQLSET_ROW
object_filter Specifies the objects that should exist in the object list of selected SQL from the cursor cache
ranking_measure(n) An order-by clause on the selected SQL
result_percentage A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given.
result_limit The top L(imit) SQL from the (filtered) source ranked by the ranking measure
attribute_list List of SQL statement attributes to return in the result. The possible values are:
  • BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

  • TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

  • ALL - return all attributes

  • Comma separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS: similar to SQL_PLAN + row source statistics


Return Values

This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.

Usage Notes

Examples

-- Get sql ids and sql text for statements with 500 buffer gets.
SELECT sql_id, sql_text 
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500')) 
ORDER BY sql_id;
 
-- Get all the information we have about a particular statement.
SELECT * 
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j'''));
 
-- Notice that some statements can have multiple plans.  The output of the
-- SELECT_XXX table functions is unique by (sql_id, plan_hash_value).  This is
-- because a data source can store multiple plans per sql statement.
SELECT sql_id, plan_hash_value
FROM table(dbms_sqltune.select_cursor_cache('sql_id = ''ay1m3ssvtrh24'''))
ORDER BY sql_id, plan_hash_value;
 
-- PL/SQL examples: load_sqlset will be called after opening a cursor, along the
-- lines given below
 
-- Select all statements in the cursor cache.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT value(P) 
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P;
 
  -- Process each statement (or pass cursor to load_sqlset).
 
  CLOSE cur;
END;/
 
 
-- Look for statements not parsed by SYS.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur for
    SELECT VALUE(P) 
    FROM table(
     DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''')) P;
 
  -- Process each statement (or pass cursor to load_sqlset).
 
  CLOSE cur;
end;/
 
 
-- All statements from a particular module/action.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P) 
    FROM table(
      DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
         'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;/
 
 
-- all statements that ran for at least five seconds
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P) 
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
end;/
 
 
-- select all statements that pass a simple buffer_gets threshold and 
-- are coming from an APPS user
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P) 
    FROM table(
      DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
        'buffer_gets > 100 and parsing_schema_name = ''APPS'''))P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
end;/
 
 
-- select all statements exceeding 5 seconds in elapsed time, but also
-- select the plans (by default we only select execution stats and binds
-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row
-- is NULL) 
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P) 
    FROM table(dbms_sqltune.select_cursor_cache(
      'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL,
      'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN')) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;/
 
 
-- Select the top 100 statements in the cursor cache ordering by elapsed_time.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P) 
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL,
                                                NULL,
                                                'ELAPSED_TIME', NULL, NULL,
                                                1,
                                                100)) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
end;/
 
 
-- Select the set of statements which cumulatively account for 90% of the 
-- buffer gets in the cursor cache.  This means that the buffer gets of all
-- of these statements added up is approximately 90% of the sum of all 
-- statements currently in the cache.
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P) 
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL,
                                                NULL,
                                                'BUFFER_GETS', NULL, NULL,
                                                .9)) P;
 
  -- Process each statement (or pass cursor to load_sqlset).
 
  CLOSE cur;
END;
/

SELECT_SQLSET Function

This function reads SQLSET contents.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.SELECT_SQLSET (
  sqlset_name         IN   VARCHAR2,
  basic_filter        IN   VARCHAR2 := NULL,
  object_filter       IN   VARCHAR2 := NULL,
  ranking_measure1    IN   VARCHAR2 := NULL,
  ranking_measure2    IN   VARCHAR2 := NULL,
  ranking_measure3    IN   VARCHAR2 := NULL,
  result_percentage   IN   NUMBER   := 1,
  result_limit        IN   NUMBER   := NULL)
  attribute_list      IN   VARCHAR2 := NULL,
  plan_filter         IN   VARCHAR2 := NULL,
  sqlset_owner        IN   VARCHAR2 := NULL)
 RETURN sys.sqlset PIPELINED;

Parameters

Table 125-36 SELECT_SQLSET Procedure Parameters

Parameter Description
sqlset_name The SQL tuning set name
basic_filter The SQL predicate to filter the SQL from the SQL Tuning Set defined on attributes of the SQLSET_ROW
object_filter Specifies the objects that should exist in the object list of selected SQL from the cursor cache
ranking_measure(n) An order-by clause on the selected SQL
result_percentage A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given.
result_limit The top L(imit) SQL from the (filtered) source ranked by the ranking measure
attribute_list List of SQL statement attributes to return in the result. The possible values are:
  • BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

  • TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

  • ALL - return all attributes

  • Comma separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS (similar to SQL_PLAN + row source statistics)

plan_filter The plan filter
sqlset_owner The owner of the SQL tuning set, or NULL for the current schema owner

Return Values

This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.

Usage Notes

Filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.

Examples

-- select from a sql tuning set
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(dbms_sqltune.select_sqlset('my_workload')) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;
/

SELECT_WORKLOAD_REPOSITORY Functions

This function collects SQL statements from the workload repository. The overloaded forms let you:

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.SELECT_WORKLAOD_REPOSITORY (
  begin_snap        IN NUMBER,
  end_snap          IN NUMBER,
  basic_filter      IN VARCHAR2 := NULL,
  object_filter     IN VARCHAR2 := NULL,
  ranking_measure1  IN VARCHAR2 := NULL,
  ranking_measure2  IN VARCHAR2 := NULL,
  ranking_measure3  IN VARCHAR2 := NULL,
  result_percentage IN NUMBER   := 1,
  result_limit      IN NUMBER   := NULL
  attribute_list    IN VARCHAR2 := NULL)
 RETURN sys.sqlset PIPELINED;

DBMS_SQLTUNE.SELECT_WORKLAOD REPOSITORY (
  baseline_name     IN VARCHAR2,
  basic_filter      IN VARCHAR2 := NULL,
  object_filter     IN VARCHAR2 := NULL,
  ranking_measure1  IN VARCHAR2 := NULL,
  ranking_measure2  IN VARCHAR2 := NULL,
  ranking_measure3  IN VARCHAR2 := NULL,
  result_percentage IN NUMBER   := 1,
  result_limit      IN NUMBER   := NULL)
  attribute_list    IN VARCHAR2 := NULL)
 RETURN sys.sqlset PIPELINED;

Parameters

Table 125-37 SELECT_WORKLOAD_REPOSITORY Function Parameters

Parameter Description
begin_snap Begin snapshot (non-inclusive)
end_snap End snapshot (inclusive)
baseline_name The name of the baseline period
basic_filter The SQL predicate to filter the SQL from the workload repository defined on attributes of the SQLSET_ROW
object_filter Specifies the objects that should exist in the object list of selected SQL from the SWRF
ranking_measure(n) An order-by clause on the selected SQL
result_percentage A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given.
result_limit The top L(imit) SQL from the (filtered) source ranked by the ranking measure
attribute_list List of SQL statement attributes to return in the result. The possible values are:
  • BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

  • TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

  • ALL - return all attributes

  • Comma separated list of attribute names allowing return of only a subset of SQL attributes: EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS (similar to SQL_PLAN + row source statistics)


Return Values

This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.

Usage Notes

Filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.

Examples

-- select statements from snapshots 1-2
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(dbms_sqltune.select_workload_repository(1,2)) P;
 
  -- Process each statement (or pass cursor to load_sqlset)
 
  CLOSE cur;
END;
/

SET_TUNING_TASK_PARAMETER Procedures

This procedure updates the value of a SQL tuning parameter of type VARCHAR2 or NUMBER.

The task must be set to its initial state before calling this procedure.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.set_tuning_task_parameter(   task_name    IN  VARCHAR2,
   parameter    IN  VARCHAR2,
   value        IN  VARCHAR2);

DBMS_SQLTUNE.set_tuning_task_parameter(   task_name    IN  VARCHAR2,
   parameter    IN  VARCHAR2,
   value        IN  NUMBER);

Parameters

Table 125-38 SET_TUNING_TASK_PARAMETER Procedure Parameters

Parameter Description
task_name Identifier of the task to execute
parameter Name of the parameter to set. The possible tuning parameters that can be set by this procedure using the parameter in the form VARCHAR2:
  • MODE: tuning scope (comprehensive, limited)

  • USERNAME: username under which the statement will be parsed

  • DAYS_TO_EXPIRE: number of days until the task is deleted

  • EXECUTION_DAYS_TO_EXPIRE: number of days until the tasks's executions will be deleted (without deleting the task)

  • DEFAULT_EXECUTION_TYPE: the task will default to this type of execution when none is specified by the EXECUTE_TUNING_TASK Function & Procedure.

  • TIME_LIMIT: global time out (seconds)

  • LOCAL_TIME_LIMIT: per-statement time out (seconds)

  • TEST_EXECUTE: FULL/AUTO/OFF.

    * FULL - test-execute for as much time as necessary, up to the local time limit for the SQL (or the global task time limit if no SQL time limit is set)

    * AUTO - test-execute for an automatically-chosen time proportional to the tuning time

    * OFF - do not test-execute

  • BASIC_FILTER: basic filter for SQL tuning set

  • OBJECT_FILTER: object filter for SQL tuning set

  • PLAN_FILTER: plan filter for SQL tuning set (see SELECT_SQLSET for possible values)

  • RANK_MEASURE1: first ranking measure for SQL tuning set

  • RANK_MEASURE2: second possible ranking measure for SQL tuning set

  • RANK_MEASURE3: third possible ranking measure for SQL tuning set

  • RESUME_FILTER: a extra filter for SQL tuning sets besides BASIC_FILTER

  • SQL_LIMIT: maximum number of SQL statements to tune

  • SQL_PERCENTAGE: percentage filter of SQL tuning set statements

parameter (cont.) The following parameters are supported for the automatic tuning task only:
  • ACCEPT_SQL_PROFILES: TRUE/FALSE: whether the task should accept SQL Profiles automatically

  • MAX_AUTO_SQL_PROFILES: maximum number of automatic SQL profiles allowed on the system, in sum

  • MAX_SQL_PROFILES_PER_EXEC: maximum number of SQL profiles that can be automatically implemented per execution of the task.

value New value of the specified parameter


SQLTEXT_TO_SIGNATURE Function

This function returns a SQL text's signature. The signature can be used to identify SQL text in dba_sql_profiles.

See Also:

SQL Profile Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (
  sql_text    IN CLOB,  force_match IN BOOLEAN  := FALSE)
 RETURN NUMBER;

Parameters

Table 125-39 SQLTEXT_TO_SIGNATURE Function Parameters

Parameter Description
sql_text SQL text whose signature is required. Required.
force_match If TRUE, this returns a signature that supports SQL matching with literal values transformed into bind variables. If FALSE, returns the signature based on the text with literals not transformed

Return Values

This function returns the signature of the specified SQL text.


UNPACK_STGTAB_SQLPROF Procedure

This procedure copies profile data stored in the staging table to create profiles on the system.

See Also:

SQL Profile Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
   profile_name          IN VARCHAR2 := '%',   profile_category      IN VARCHAR2 := 'DEFAULT',   replace               IN BOOLEAN,
   staging_table_name    IN VARCHAR2,   staging_schema_owner  IN VARCHAR2 := NULL);

Parameters

Table 125-40 UNPACK_STGTAB_SQLPROF Procedure Parameters

Parameter Description
profile_name The name of the profile to unpack (% wildcards acceptable, case-sensitive)
profile_category The category from which to unpack profiles (% wildcards acceptable, case-sensitive)
replace The option to replace profiles if they already exist. Note that profiles cannot be replaced if one in the staging table has the same name as an active profile in a different SQL statement.If FALSE, this function raises errors if you try to create a profile that already exists
staging_table_name The name of the table on which to perform the remap operation (case-insensitive unless double quoted). Required.
staging_schema_owner The schema where the table resides, or NULL for current schema (case-insensitive unless double quoted)

Usage Notes

Using this procedure requires the CREATE ANY SQL PROFILE privilege and the SELECT privilege on staging table.

Examples

-- Unpack all profiles stored in a staging table
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace            => FALSE, -
                                        staging_table_name => 'PROFILE_STGTAB');
 
-- If there is a failure during the unpack operation, users can find the profile
-- we failed on and perform a remap_stgtab_sqlprof operation targeting it.  Then
-- they can resume the unpack operation by setting replace to TRUE so that
-- the profiles that were already created will just be replaced
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace            => TRUE, -
                                        staging_table_name => 'PROFILE_STGTAB');

UNPACK_STGTAB_SQLSET Procedure

This procedure copies one or more SQL tuning sets from their location in the staging table into the SQL tuning sets schema, making them proper SQL tuning sets.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
   sqlset_name          IN VARCHAR2 := '%',
   sqlset_owner         IN VARCHAR2 := NULL,
   replace              IN BOOLEAN,
   staging_table_name   IN VARCHAR2,
   staging_schema_owner IN VARCHAR2 := NULL);

Parameters

Table 125-41 UNPACK_STGTAB_SQLSET Procedure Parameters

Parameter Description
sqlset_name The name of the tuning set to unpack (not NULL). Wildcard characters ('%') are supported to unpack multiple tuning sets in a single call. For example, just specify '%' to unpack all tuning sets from the staging table.
sqlset_owner The name of tuning set owner, or NULL for current schema owner. Wildcards supported.
replace Replaces tuning set if they already exist.If FALSE, raises errors if you try to create a tuning set that already exists
staging_table_name The name of the staging table, moved after a call to the PACK_STGTAB_SQLSET Procedure (case-sensitive)
staging_schema_owner The name of staging table owner, or NULL for current schema owner (case-sensitive)

Usage Notes

Examples

-- unpack all STS in the staging table
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name         => '%', -
                                       sqlset_owner        => '%', -
                                       replace             => FALSE, -
                                       staging_table_name  => 'STGTAB_SQLSET');
 
-- errors can arise during STS unpack when a STS in the staging table has the
-- same name/owner as STS on the system.  In this case, users should call
-- remap_stgtab_sqlset to patch the staging table and with which to call unpack
-- Replace set to TRUE.
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name         => '%', -
                                       sqlset_owner        => '%', -
                                       replace             => TRUE, -
                                       staging_table_name  => 'STGTAB_SQLSET');

UPDATE_SQLSET Procedures

This procedure updates selected fields for SQL statement in a SQL tuning set.

See Also:

SQL Tuning Set Subprograms for other subprograms in this group

Syntax

DBMS_SQLTUNE.UPDATE_SQLSET (
   sqlset_name      IN  VARCHAR2,
   sql_id           IN  VARCHAR2,
   attribute_name   IN  VARCHAR2,
   attribute_value  IN  VARCHAR2 := NULL);

DBMS_SQLTUNE.UPDATE_SQLSET (
   sqlset_name      IN  VARCHAR2,
   sql_id           IN  VARCHAR2,
   attribute_name   IN  VARCHAR2,
   attribute_value IN NUMBER := NULL);

Parameters

Table 125-42 UPDATE_SQLSET Function Parameters

Parameter Description
sqlset_name The SQL tuning set name
sql_id The identifier of the statement to update
attribute_name The name of the attribute to modify
attribute_value The new value of the attribute