125 DBMS_SQLTUNE

Using DBMS_SQLTUNE

• Overview

• Security Model

---

Overview

The DBMS_SQLTUNE package provides a number interrelated areas of functionality:

• SQL Tuning Advisor Subprograms

• SQL Profile Subprograms

• SQL Tuning Set Subprograms

• Real-time SQL Monitoring Subprograms

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:

• You can use the ACCEPT_SQL_PROFILE Procedure and Function to accept a SQL Profile recommended by the SQL Tuning Advisor.

• You can alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an existing SQL Profile with the ALTER_SQL_PROFILE Procedure.

• You can drop a SQL Profile with the DROP_SQL_PROFILE Procedure.

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

• The execution context, such as the parsing schema name and bind values

• Execution statistics such as average elapsed time and execution count

• Execution plans - which are the sequence of operations Oracle performs to run SQL statements

• Row source statistics such as the number of rows processed for each operation executed within the plan

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

• The cursor cache using the SELECT_CURSOR_CACHE Function

• Top SQL statements from the Automatic Workload Repository using the SELECT_WORKLOAD_REPOSITORY Functions

• Other SQL Tuning Sets using the SELECT_SQLSET Function

• A user-defined workload

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

• You use the CREATE_SQLSET Procedure and Function to creates a SQL tuning set object in the database

• The LOAD_SQLSET Procedure populates the SQL tuning set with a set of selected SQL

• The CAPTURE_CURSOR_CACHE_SQLSET Procedure collects SQL statements from the cursor cache over a specified time interval, attempting to build a realistic picture of system workload.

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.

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:

• As the SQL Tuning advisor relies on the advisor framework, all tuning task interfaces (XXX_TUNING_TASK) require privilege ADVISOR.

• SQL Tuning Set subprograms (XXX_SQLSET) require either the ADMINISTER SQL TUNING SET or the ADMINISTER ANY SQL TUNING SET privilege. Users having the ADMINISTER SQL TUNING SET privilege can only create and modify a SQL tuning set they own, while the ADMINISTER ANY SQL TUNING SET privilege allows them to operate upon all SQL tuning sets, even those owned by other users. For example, using the CREATE_SQLSET Procedure and Function you can create a SQL tuning set to be owned by another user. In this case, the user need not necessarily have the ADMINISTER SQL TUNING SET privilege to operate upon her tuning set.

• Previously, three different privileges were needed to invoke subprograms concerned with SQL Profiles:

  • CREATE ANY SQL PROFILE

  • ALTER ANY SQL PROFILE

  • DROP ANY SQL PROFILE

  These have now been deprecated in favor of ADMINISTER SQL MANAGEMENT OBJECT

Data Structures

The DBMS_SQLTUNE package defines the following OBJECT type

Object Types

• SQLSET_ROW Object Type

---

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

Subprogram Groups

DBMS_SQLTUNE subprograms are grouped by function:

• SQL Tuning Advisor Subprograms

• SQL Profile Subprograms

• SQL Tuning Set Subprograms

• Real-time SQL Monitoring Subprograms

---

SQL Tuning Advisor Subprograms

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

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.

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.

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.

Summary of DBMS_SQLTUNE 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.

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

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.

Syntax

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

Parameters

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

• "STATUS" can be set to "ENABLED" or "DISABLED"

• "NAME" can be reset to a valid name which must be a valid Oracle identifier and must be unique.

• "DESCRIPTION" can be set to any string of size no more than 500 characters

• "CATEGORY" can be reset to a valid category name which must be a valid Oracle identifier and must be unique when combined with normalized SQL text)

Syntax

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

Parameters

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.

Syntax

DBMS_SQLTUNE.CANCEL_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

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.

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

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.

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

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.

Syntax

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

Parameters

Usage Notes

• Call this procedure once before issuing a call to the PACK_STGTAB_SQLPROF Procedure.

• This procedure can be called multiple times if you would like to have different SQL profiles in different staging tables.

• Note that this is a DDL operation, so it does not occur within a transaction.

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

Syntax

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

Parameters

Usage Notes

• Call this procedure once before issuing a call to the PACK_STGTAB_SQLSET Procedure.

• This procedure can be called multiple times if you would like to have different tuning sets in different staging tables.

• Note that this is a DDL operation, so it does not occur within a transaction.

• Users issuing the call must have permission to CREATE TABLE in the schema provided and the relevant tablespace.

• Please note that the staging table contains nested table columns and indexes, so it should not be renamed.

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:

• Create a tuning task for a single statement given its text.

• Create a tuning task for a single statement from the Cursor Cache given its identifier.

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

• Create a tuning task for a SQL tuning set.

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

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

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.

Syntax

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

Parameters

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.

Syntax

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

Parameters

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.

Syntax

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

Parameters

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.

Syntax

DBMS_SQLTUNE.DROP_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

---

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.

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

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.

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

---

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.

Syntax

DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

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.

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

Exceptions

• This procedure returns an error when sqlset_name is invalid, or a corresponding SQL tuning set does not exist, or the populate_cursor is incorrect and cannot be executed.

• Exceptions are also raised when invalid filters are provided. Filters can be invalid either because they don't parse (for example, they refer to attributes not in sqlset_row), or because they violate the user's privileges.

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.

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

Usage Notes

• This procedures requires ADMINISTER SQL MANAGEMENT OBJECT privilege and INSERT privilege on the staging table.

• Note that this function issues a COMMIT after packing each SQL profile, so if an error is raised mid-execution, clear the staging table by deleting its rows.

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.

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

Usage Notes

• This procedure can be called several times to move more than one SQL tuning set. Users can then move the populated staging table to another system using any method, such as database link or datapump. Users can then call the UNPACK_STGTAB_SQLSET Procedure create the SQL tuning set on the other system.

• Note that this function issues a COMMIT after packing each SQL tuning set, so if an error is raised mid-execution, clear the staging table by deleting its rows.

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.

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

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.

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

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.

Syntax

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

Parameters

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.

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

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.

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

Return Values

A CLOB containing the desired report.

Usage Notes

• The target SQL statement for this report can be:

  • The last SQL monitored by Oracle (this is the default behavior, so there is no need to specify any parameter)The last SQL executed by a specific session and monitored by Oracle. The session is identified by its session id and optionally it serial number. For example, use session_id=>USERENV('SID') for the current session or session_id=>20, session_serial=>103 for session ID 20, serial number 103.The last execution of a specific statement identified by its sql_id.A specific execution of a SQL statement identified by its execution key (sql_id, sql_exec_start and sql_exec_id).

• This report produces performance data exposed by several fixed views, listed below. For this reason, the invoker of the report function must have privilege to select data from these fixed views (such as the SELECT_CATALOG role).

  • GV$SQL_MONITOR

  • GV$SQL_PLAN_MONITOR

  • GV$SQL_PLAN

  • GV$ACTIVE_SESSION_HISTORY

  • GV$SESSION_LONGOPS

  • GV$SQL

---

REPORT_TUNING_TASK Function

This procedure displays the results of a tuning task.

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

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.

Syntax

DBMS_SQLTUNE.RESET_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

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.

Syntax

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

Parameters

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.

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

Return Values

Returns a script in the form of a CLOB.

Usage Notes

• Once the script is returned, it should then by checked by the DBA and executed.

• Wrap with a call to DBMS_ADVISOR.CREATE_FILE to put it into a file.

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.

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

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.

• Users need privileges on the cursor cache views.

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.

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

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:

• Collect SQL statements from all snapshots between begin_snap and end_snap.

• Collect SQL statements from a workload repository baseline.

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

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.

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

---

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.

Syntax

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

Parameters

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.

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

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.

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

Usage Notes

• Users can drop the staging table after this procedure completes successfully.

• The unpack procedure commits after successfully loading each SQL tuning set. If it fails with one tuning set, no part of that tuning set will have been unpacked, but those which the subprogram had already apprehended will continue to exist.

• When failures occur due to SQL tuning set name or owner conflicts, users should use the REMAP_STGTAB_SQLSET Procedure to patch the staging table, and then call this procedure again to unpack those tuning sets that remain.

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.

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