91
DBMS_SQLTUNE

The DBMS_SQLTUNE package provides the interface to tune SQL statements.

The chapter contains the following topics:

• Using DBMS_SQLTUNE
  • Overview
  • Types
  • Operational Notes
• Summary of DBMS_SQLTUNE Subprograms

---

Using DBMS_SQLTUNE

• Overview
• Types
• Operational Notes

---

Overview

SQL Tuning Sets is a new object for capturing SQL workload information. SQL Tuning Sets provide a common 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.

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

• The cursor cache.
• AWR selecting top SQL statements executed during some prior interval
• A user-defined workload.
• Other SQL Tuning Sets.

---

Types

• SqlSet_cursor

SqlSet_cursor

This defines a cursor type for SQL statements with their related data. This type is mainly used by the LOAD_SQLSET procedure as an argument to populate a SqlSet from a possible data source. See the LOAD_SQLSET Procedure for more details.

It is important to keep in mind that this cursor is weakly defined.

---

Operational Notes

Under normal mode, the Cost Based Optimizer (CBO) produces the best possible execution plan with whatever information is currently available for the query. It does not have time to gather additional information that might improve the plan because the CBO has to generate an execution plan in a fraction of a second under normal mode. However, in the Plan Tuning Analysis mode, the CBO has time to gather additional information for the query, in the form of a SQL Profile, and this profile allows the generation of a superior execution plan that is well tuned.

In the Plan Tuning Analysis mode, the CBO collects the SQL Profile for the query automatically and the DBA can then decide whether to activate the profile or not. Once activated, the CBO uses the SQL Profile under normal mode to generate the well-tuned plan, that is superior to the original.

Note that SQL Profile is stored persistently in data dictionary, and hence this form of tuning does not require any application code changes.

---

Summary of DBMS_SQLTUNE Subprograms

Table 91-1  DBMS_SQLTUNE Package Subprograms  

Subprogram	Description
ACCEPT_SQL_PROFILE Procedure	Create a SQL Profile for the specified tuning task
ADD_SQLSET_REFERENCE Function	Adds a new reference to an existing SqlSet to indicate its use by a client
ALTER_SQL_PROFILE Procedure	Alters specific attributes of an existing SQL Profile object
CANCEL_TUNING_TASK Procedure	Cancels the currently executing tuning task
CREATE_SQLSET Procedure	Creates a SqlSet object in the database
CREATE_TUNING_TASK Functions	Prepares the tuning of a single statement or SqlSet
DELETE_SQLSET Procedure	Deletes a set of SQL statements from a SqlSet
DROP_SQL_PROFILE Procedure	Drops the named SQL Profile from the database
DROP_SQLSET Procedure	Drops a SqlSet if it is not active
DROP_TUNING_TASK Procedure	Drops a SQL tuning task
EXECUTE_TUNING_TASK Procedure	Executes a previously created tuning task
INTERRUPT_TUNING_TASK Procedure	Interrupts the currently executing tuning task to allow access intermediate result data
LOAD_SQLSET Procedure	Populates the SqlSet with a set of selected SQL
REMOVE_SQLSET_REFERENCE Procedure	Deactivates a SqlSet to indicate it is no longer used by the client
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 tuning task
SELECT_SQLSET Function	Collects SQL statements from the cursor cache
SELECT_WORKLOAD_REPOSITORY Functions	Collects SQL statements from workload repository
UPDATE_SQLSET Procedures	Updates whether selected string fields for a SQL statement in a SqlSet or the set numerical attributes of a SQL in a SqlSet

---

ACCEPT_SQL_PROFILE Procedure

This procedure accepts 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);

Parameters

Table 91-2  ACCEPT_SQL_PROFILE Procedure Parameters

Parameter	Description
task_name	The (mandatory) name of the SQL tuning task.
object_id	The (optional) identifier of the 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 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 create a unique key for a SQL Profile. An ACCEPT_SQL_PROFILE will fail if this combination is duplicated.

Usage Notes

The "CREATE ANY SQL PROFILE" privilege is required.

---

ADD_SQLSET_REFERENCE Function

This procedure adds a new reference to an existing SqlSet to indicate its use by a client.

Syntax

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

Parameters

Table 91-3  ADD_SQLSET_REFERENCE Function Parameters

Parameter	Description
sqlset_name	The SqlSet name.
description	The description of the usage of SqlSet.

Return Values

The identifier of the added reference.

---

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

Table 91-4  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.

---

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

Table 91-5  CANCEL_TUNING_TASK Procedure Parameters

Parameter	Description
task_name	The identifier of the task to execute.

---

CREATE_SQLSET Procedure

This procedure creates a SqlSet object in the database.

Syntax

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

Parameters

Table 91-6  CREATE_SQLSET Procedure Parameters

Parameter	Description
sqlset_name	The SqlSet name.
description	The description of the SqlSet.

---

CREATE_TUNING_TASK Functions

You can use different forms of this function to:

• Prepare the tuning of a single statement given its text.
• Prepare the tuning of a single statement from the Cursor Cache given its identifier.
• Prepare the tuning of a single statement from the workload repository given a range of snapshot identifiers.
• Prepare the tuning of a SqlSet.

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

Syntax

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;

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;

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;

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

Parameters

Table 91-7  CREATE_TUNING_TASK Function Parameters

Parameter	Description
sql_text	The text of a SQL statement.
begin_snap	Begin snapshot identifier.
end_snap	End snapshot identifier.
sql_id	The identifier of a SQL statement.
bind_list	A a set of bind values.
plan_hash_value	The hash value of the SQL execution plan.
sqlset_name	The SqlSet name.
basic_filter	The SQL predicate to filter the SQL from the STS.
object_filter	The object filter.
rankn	An order-by clause on the selected SQL.
result_percentage	A percentage on the sum of a ranking measure
result_limit	The top L(imit) SQL from the (filtered/ranked) SQL.
user_name	The username for whom the statement or SQL set is to be tuned.
scope	Tuning scope (limited/comprehensive).
time_limit	The maximum duration in seconds for the tuning session.
task_name	An optional tuning task name.
description	A description of the SQL tuning session to a maximum of 256 characters.

Return Values

A SQL tune task identifier.

---

DELETE_SQLSET Procedure

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

Syntax

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

Parameters

Table 91-8  DELETE_SQLSET Procedure Parameters

Parameter	Description
sqlset_name	The SqlSet name
basic_filter	The QL predicate to filter the SQL from the SqlSet. This basic filter is used as a where clause on the SqlSet content to select a desired subset of SQL from the Tuning Set.

---

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

Table 91-9  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.

---

DROP_SQLSET Procedure

This procedure drops a SqlSet if it is not active.

Syntax

DBMS_SQLTUNE.DROP_SQLSET (
   sqlset_name   IN  VARCHAR2;

Parameters

Table 91-10  DROP_SQLSET Procedure Parameters

Parameter	Description
sqlset_name	The SqlSet name.

Usage Notes

You cannot drop a SqlSet when it is referenced by one or more clients (for example, SQL tune advisor).

---

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

Table 91-11  DROP_TUNING_TASK Procedure Parameters

Parameter	Description
task_name	The identifier of the current task.

---

EXECUTE_TUNING_TASK Procedure

This procedures executes a previously created tuning task.

Syntax

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

Table 91-12  EXECUTE_TUNING_TASK Procedure Parameters

Parameter	Description
task_name	The identifier of the task to execute.

---

INTERRUPT_TUNING_TASK Procedure

This procedure interrupts the currently executing tuning task and access intermediate result data.

Syntax

DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

Table 91-13  INTERRUPT_TUNING_TASK Procedure Parameters

Parameter	Description
task_name	The identifier of the current task.

---

LOAD_SQLSET Procedure

This procedure populates the SqlSet with a set of selected SQL.

Syntax

DBMS_SQLTUNE.LOAD_SQLSET (
   sqlset_name      IN  VARCHAR2,
   populate_cursor  IN  SqlSet_cursor);

Parameters

Table 91-14  LOAD_SQLSET Procedure Parameters

Parameter	Description
sqlset_name	The SqlSet name.
populate_cursor	The cursor reference from which to populate.

Usage Notes

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

---

REMOVE_SQLSET_REFERENCE Procedure

This procedure deactivates a SqlSet 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

Table 91-15  REMOVE_SQLSET_REFERENCE Procedure Parameters

Parameter	Description
sqlset_name	The SqlSet name.
reference_id	The identifier of the reference to remove.

---

REPORT_TUNING_TASK Function

This procedure displays the results of a tuning task.

Syntax

DBMS_SQLTUNE.REPORT_TUNING_TASK(
   task_name     IN  VARCHAR2 := NULL,
   object_id     IN  NUMBER   := NULL,
   result_limit  IN  NUMBER   := NULL,
   plan_format   IN  VARCHAR2 := FORMAT_TYPICAL,
   rec_format    IN  VARCHAR2 := FORMAT_TYPICAL)
RETURN CLOB;

Parameters

Table 91-16  REPORT_TUNING_TASK Function Parameters

Parameter	Description
task_name	The identifier of the task to report. If the task ID is not given by the caller, the function will generate a report for the results of the last tuning task run by the user.
object_id	The identifier of the advisor framework object that represents a given statement in the SqlSet.
result_limit	The number of statements in a SqlSet for which a report is generated.
plan_format	The format of the explain plans displayed in the report. Possible values are TYPICAL, BASIC, ALL and SERIAL.
rec_format	The format of the recommendations displayed in the report. Possible values are TYPICAL and BASIC.

Return Values

A text report.

---

RESET_TUNING_TASK Procedure

This procedure resets the currently executing tuning task to its initial state. All intermediate result data is deleted.

Syntax

DBMS_SQLTUNE.RESET_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

Table 91-17  RESET_TUNING_TASK Procedure Parameters

Parameter	Description
task_name	The identifier of the current task.

---

RESUME_TUNING_TASK Procedure

This procedure resumes a previously interrupted tuning task.

Syntax

DBMS_SQLTUNE.RESUME_TUNING_TASK(
 task_name         IN VARCHAR2);

Parameters

Table 91-18  RESUME_TUNING_TASK Procedure Parameters

Parameter	Description
task_name	The identifier of the current task.

---

SELECT_SQLSET Function

This function collects SQL statements from the cursor cache.

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)
 RETURN sys.sqlset PIPELINED;

Parameters

Table 91-19  SELECT_SQLSET Procedure Parameters

Parameter	Description
sqlset_name	The SqlSet name.
basic_filter	The SQL predicate to filter the SQL from the cursor cache.
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 percentage on the sum of a ranking measure.
result_limit	The top L(imit) SQL from the (filtered) source ranked by the ranking measure.

Return Values

This function returns a sqlset object.

---

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)
 RETURN sys.sqlset PIPELINED;

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)
 RETURN sys.sqlset PIPELINED;

Parameters

Table 91-20  SELECT_WORKLOAD_REPOSITORY Procedure Parameters

Parameter	Description
begin_snap	Begin snapshot.
end_snap	End snapshot.
baseline_name	The name of the baseline period.
basic_filter	The SQL predicate to filter the SQL from the serf.
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 percentage on the sum of a ranking measure.
result_limit	The top L(imit) SQL from the (filtered) source ranked by the ranking measure.

Return Values

This function returns a sqlset object.

---

UPDATE_SQLSET Procedures

There are two forms of this procedure:

• You use the first form of the procedure to update selected string fields for a SQL statement in a SqlSet, specifically to update the MODULE and ACTION fields.
• You use the second form of the procedure to update the set numerical attributes of a SQL in a SqlSet, specifically PRIORITY and PARSING_SCHEMA_ID.

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 91-21  UPDATE_SQLSET Function Parameters

Parameter	Description
sqlset_name	The SqlSet 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.