168 DBMS_SQLTUNE
The DBMS_SQLTUNE package is the interface for tuning SQL on demand. The related package DBMS_AUTO_SQLTUNE package provides the interface for SQL Tuning Advisor run as an automated task.
The chapter contains the following topics:
See Also:
168.1 DBMS_SQLTUNE Overview
The DBMS_SQLTUNE package provides a number of interrelated areas of functionality.
This section contains the following topics:
SQL Tuning Advisor
SQL Tuning Advisor is one of a suite of advisors, a set of expert systems that identifies and helps resolve database performance problems. Specifically, SQL Tuning Advisor automates tuning of problematic SQL statements. It takes one or more SQL statements as input and gives precise advice on how to tune the statements. The advisor provides the advice in the form of SQL actions for tuning the SQL along with their expected performance benefit.
The group of DBMS_SQLTUNE SQL Tuning Advisor Subprograms provide a task-oriented interface that enables you to access the advisor. You can call the following subprograms in the order given to use some of SQL Tuning Advisor's features:
-
CREATE_TUNING_TASK Functions creates a tuning task for tuning one or more SQL statements.
-
The EXECUTE_TUNING_TASK Function and Procedure executes a previously created tuning task.
-
The REPORT_TUNING_TASK Function displays the results of a tuning task.
-
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
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 DBMS_SQLTUNE 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 SQL Tuning Advisor.
-
You can alter the
STATUS,NAME,DESCRIPTION, andCATEGORYattributes 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
SQL tuning sets store SQL statements along with the following information:
-
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 that the database performs to run SQL statements
-
Row source statistics such as the number of rows processed for each operation executed within the plan
You can create SQL tuning sets by filtering or ranking SQL statements from several sources:
-
The shared SQL area using the SELECT_CURSOR_CACHE Function
-
Top SQL statements from the Automatic Workload Repository using the SELECT_WORKLOAD_REPOSITORY Function
-
Other SQL tuning sets using the SELECT_SQLSET Function
-
SQL Performance Analyzer task comparison results using the SELECT_SQLPA_TASK Function
-
SQL Trace files using the SELECT_SQL_TRACE Function
-
A user-defined workload
The complete group of DBMS_SQLTUNE SQL Tuning Set Subprograms facilitates this functionality. As examples:
-
The CREATE_SQLSET Procedure and Function 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 shared SQL area over a specified time interval, attempting to build a realistic picture of database workload.
Note:
When manipulating SQL tuning sets, you can use DBMS_SQLSET as an alternative to DBMS_SQLTUNE.
Import and Export of SQL Tuning Sets and SQL Profiles
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 database and populate this staging table with the relevant data. You then move that staging table to the destination system following the method of your choice (such as Oracle Data Pump, or a database link), where it is used to reconstitute the objects in their original form. The following steps are implemented by means of subprograms included in this package:
-
To create the staging table on the source system, call the CREATE_STGTAB_SQLPROF Procedure or the CREATE_STGTAB_SQLSET Procedure.
-
To populate the staging table with information from the source system, call the PACK_STGTAB_SQLPROF Procedure or PACK_STGTAB_SQLSET Procedure.
-
Move the staging table to the destination system.
-
To re-create the object on the new system, call the UNPACK_STGTAB_SQLPROF Procedure or the UNPACK_STGTAB_SQLSET Procedure.
See Also:
Oracle Database SQL Tuning Guide for more information about programmatic flow
Automatic Tuning Task Functions
The automated system task SYS_AUTO_SQL_TUNING_TASK is created by the database as part of the catalog scripts. This task automatically chooses a set of high-load SQL from AWR and runs SQL Tuning Advisor on this SQL. The automated task performs the same comprehensive analysis as any other SQL Tuning task.
You can obtain a report on the activity of the Automatic SQL Tuning task through the DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK API.
See Also:
DBMS_AUTO_SQLTUNE for the list of subprograms that you can use to manage the automated SQL tuning task.
Real-Time SQL Monitoring
Real-time SQL Monitoring enables 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 in almost real time during statement execution. The V$SQL_MONITOR and V$SQL_PLAN_MONITOR views expose these statistics. In addition, DBMS_SQLTUNE provides the REPORT_SQL_MONITOR and REPORT_SQL_MONITOR_LIST functions to report monitoring information.
Note:
DBMS_SQL_MONITOR also contains the REPORT_SQL_MONITOR and REPORT_SQL_MONITOR_LIST functions.
Tuning a Standby Database Workload
In some cases, a standby database can assume a reporting role in addition to its data protection role. The standby database can have its own workload of queries, some of which may require tuning. You can issue SQL Tuning Advisor statements on a standby database, which is read-only. A standby-to-primary database link enables DBMS_SQLTUNE to write data to and read data from the primary database. The procedures that are eligible for tuning standby workloads include the database_link_to parameter.
168.2 DBMS_SQLTUNE Security Model
This package is available to PUBLIC and performs its own security checking.
Note the following:
-
Because SQL Tuning Advisor relies on the advisor framework, all tuning task interfaces (
*_TUNING_TASK) require theADVISORprivilege. -
SQL tuning set subprograms (
*_SQLSET) require either of the following privileges:-
ADMINISTER SQL TUNING SETYou can only create and modify a SQL tuning set that you own.
-
ADMINISTER ANY SQL TUNING SETYou can operate on all SQL tuning sets, even those owned by other users.
-
-
In earlier releases, three different privileges were needed to invoke subprograms involving SQL profiles:
-
CREATE ANY SQL PROFILE -
ALTER ANY SQL PROFILE -
DROP ANY SQL PROFILE
The preceding privileges have been deprecated in favor of
ADMINISTER SQL MANAGEMENT OBJECT. -
168.3 DBMS_SQLTUNE Data Structures
The SELECT_* subprograms in the DBMS_SQLTUNE package return objects of the SQLSET_ROW type.
Object Types
168.3.1 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_ROW objects. Each SQLSET_ROW contains a single SQL statement along with its execution context, statistics, binds, and plan. The SELECT_* subprograms each model a data source as a collection of SQLSET_ROW objects, with each object uniquely identified 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 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,
con_dbid NUMBER,
last_exec_start_time VARCHAR2(19))Attributes
Table 168-1 SQLSET_ROW Attributes
| Attribute | Description |
|---|---|
|
|
Unique SQL ID. |
|
|
Signature with literals, case, and whitespace removed. |
|
|
Full text for the SQL statement. |
|
|
Currently not implemented. |
|
|
Bind data as captured for this SQL. Note that you cannot stipulate an argument for this parameter and also for |
|
|
Schema where the SQL is parsed. |
|
|
Last application module for the SQL. |
|
|
Last application action for the SQL. |
|
|
Sum total elapsed time for this SQL statement. |
|
|
Sum total CPU time for this SQL statement. |
|
|
Sum total number of buffer gets. |
|
|
Sum total number of disk reads. |
|
|
Sum total number of direct path writes. |
|
|
Sum total number of rows processed by this SQL. |
|
|
Sum total number of fetches. |
|
|
Total executions of this SQL statement. |
|
|
Number of times the SQL statement was fully executed with all of its rows fetched. |
|
|
Optimizer cost for this SQL. |
|
|
Optimizer environment for this SQL statement. |
|
|
User-defined priority (1,2,3). |
|
|
Statement type, such as |
|
|
Load time of the parent cursor. |
|
|
Period of time (seconds) when the statistics of this SQL statement were collected. |
|
|
Effective period of time (in seconds) during which the SQL statement was active. |
|
|
Other column for user-defined attributes. |
|
|
Plan hash value of the plan. |
|
|
Execution plan for the SQL statement. |
|
|
List of user-specified binds for the SQL statement. This is used for user-specified workloads. Note that you cannot stipulate an argument for this parameter and also for |
|
|
DBID of the PDB or CDB root. |
|
|
Most recent execution start time of this SQL statement. |
168.4 DBMS_SQLTUNE Subprogram Groups
DBMS_SQLTUNE subprograms are grouped by function.
168.4.1 DBMS_SQLTUNE SQL Tuning Advisor Subprograms
This subprogram group provides an interface to manage SQL tuning tasks.
Table 168-2 SQL Tuning Task Subprograms
| Subprogram | Description |
|---|---|
|
Cancels the currently executing tuning task |
|
|
Creates a SQL plan baseline for an existing plan |
|
|
Creates a tuning of a single statement or SQL tuning set for either SQL Tuning Advisor |
|
|
Drops a SQL tuning task |
|
|
Executes a previously created tuning task |
|
|
Implements a set of SQL profile recommendations made by SQL Tuning Advisor |
|
|
Interrupts the currently executing tuning task |
|
|
Displays a report from the automatic tuning task, reporting on a range of executions |
|
|
Displays the results of a tuning task |
|
|
Resets the currently executing tuning task to its initial state |
|
|
Resumes a previously interrupted task that was created to process a SQL tuning set |
|
|
Creates a tuning task and schedules its execution as a scheduler job |
|
|
Creates a SQL*Plus script which can then be executed to implement a set of SQL Tuning Advisor recommendations |
|
|
Updates the value of a SQL tuning parameter of type |
"Summary of DBMS_SQLTUNE Subprograms" contains a complete listing of all subprograms in the package.
168.4.2 DBMS_SQLTUNE SQL Profile Subprograms
This subprogram group provides an interface to manage SQL profiles.
Table 168-3 SQL Profile Subprograms
| Subprogram | Description |
|---|---|
|
Accepts all SQL profiles recommended by a specific execution of a tuning task |
|
|
Creates a SQL profile for the specified tuning task |
|
|
Alters specific attributes of an existing SQL profile object |
|
|
Creates the staging table used for copying SQL profiles from one system to another |
|
|
Drops the named SQL profile from the database |
|
|
Moves profile data out of the |
|
|
Changes the profile data values kept in the staging table prior to performing an unpack operation |
|
|
Returns a SQL text's signature |
|
|
Uses the profile data stored in the staging table to create profiles on this system |
"Summary of DBMS_SQLTUNE Subprograms" contains a complete listing of all subprograms in the package.
168.4.3 DBMS_SQLTUNE SQL Tuning Set Subprograms
This subprogram group provides an interface to manage SQL tuning sets.
Table 168-4 SQL Tuning Set Subprograms
| Subprogram | Description |
|---|---|
|
Adds a new reference to an existing SQL tuning set to indicate its use by a client |
|
|
Over a specified time interval incrementally captures a workload from the shared SQL area into a SQL tuning set |
|
|
Creates a SQL tuning set object in the database |
|
|
Creates a staging table through which SQL Tuning Sets are imported and exported |
|
|
Deletes a set of SQL statements from a SQL tuning set |
|
|
Drops a SQL tuning set if it is not active |
|
|
Populates the SQL tuning set with a set of selected SQL |
|
|
Copies tuning sets out of the |
|
|
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 |
|
|
Deactivates a SQL tuning set to indicate it is no longer used by the client |
|
|
Collects SQL statements from the shared SQL area |
|
|
Reads the content of one or more trace files and returns the SQL statements it finds in the format of |
|
|
Collects SQL statements from a SQL performance analyzer comparison task |
|
|
Collects SQL statements from an existing SQL tuning set |
|
|
Collects SQL statements from the workload repository |
|
|
Copies one or more SQL tuning sets from the staging table |
|
|
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.
168.4.4 DBMS_SQLTUNE 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 168-5 Real-Time SQL Monitoring Subprograms
| Subprogram | Description |
|---|---|
|
Reports on Real-Time SQL Monitoring |
|
|
Builds a report for all or a subset of statements monitored by Oracle Database |
|
|
Builds an XML report for all or a subset of statements monitored by Oracle Database |
168.4.5 DBMS_SQLTUNE SQL Performance Reporting Subprograms
This subprogram group provides detailed reports on SQL performance using statistics from the shared SQL area and automatic workload repository (AWR).
Table 168-6 SQL Performance Reporting Subprograms
| Subprogram | Description |
|---|---|
|
This function reports on a specific SQL ID. |
|
|
This function builds a report (text, simple HTML, active HTML, XML) for the monitoring information collected on behalf of the targeted statement execution. |
|
|
This function builds a report for all or a sub-set of statements monitored by Oracle. For each statement, the subprogram gives key information and associated global statistics. |
|
|
This function displays the results of a tuning task. |
|
|
This function displays an XML report of a tuning task. |
168.5 Summary of DBMS_SQLTUNE Subprograms
This table lists the DBMS_SQLTUNE subprograms and briefly describes them.
Table 168-7 DBMS_SQLTUNE Package Subprograms
| Subprogram | Description | Group |
|---|---|---|
|
Accepts all SQL profiles recommended by a particular execution of a particular tuning task |
||
|
Creates a SQL profile for the specified tuning task |
||
|
Adds a new reference to an existing SQL tuning set to indicate its use by a client |
||
|
Alters specific attributes of an existing SQL profile object |
||
|
Cancels the currently executing tuning task |
||
|
Over a specified time interval incrementally captures a workload from the shared SQL area into a SQL tuning set |
||
|
Creates a SQL plan baseline for an existing plan |
||
|
Creates a SQL tuning set object in the database |
||
|
Creates the staging table used for copying SQL profiles from one system to another |
||
|
Creates a staging table through which SQL tuning sets are imported and exported |
||
|
Creates a tuning of a single statement or SQL tuning set for either SQL Tuning Advisor |
||
|
Deletes a set of SQL statements from a SQL tuning set |
||
|
Drops the named SQL profile from the database |
||
|
Drops a SQL tuning set if it is not active |
||
|
Drops a SQL tuning task |
||
|
Executes a previously created tuning task |
||
|
implements a set of SQL profile recommendations made by SQL Tuning Advisor |
||
|
Interrupts the currently executing tuning task |
||
|
Populates the SQL tuning set with a set of selected SQL |
||
|
Moves profile data out of the |
||
|
Moves tuning sets out of the |
||
|
Changes the profile data values kept in the staging table prior to performing an unpack operation |
||
|
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 |
||
|
Deactivates a SQL tuning set to indicate it is no longer used by the client |
||
|
Displays a report from the automatic tuning task, reporting on a range of subtasks |
||
|
Reports on a specific SQL ID |
||
|
Builds a report (text, simple HTML, active HTML, XML) for the monitoring information collected on behalf of the targeted statement execution |
||
|
Builds a report for all or a subset of statements monitored by Oracle Database. For each statement, the subprogram gives key information and associated global statistics |
||
|
Equivalent to the |
||
|
Displays the results of a tuning task |
||
|
Displays an XML report of a tuning task |
||
|
Resets the currently executing tuning task to its initial state |
||
|
Resumes a previously interrupted task that was created to process a SQL tuning set |
||
|
Creates a SQL tuning task and schedule its execution as a scheduler job |
||
|
Creates a SQL*Plus script which can then be executed to implement a set of SQL Tuning Advisor recommendations |
||
|
Collects SQL statements from the shared SQL area |
||
|
Reads the content of one or more trace files and returns the SQL statements it finds in the format of |
||
|
Collects SQL statements from a SQL Performance Analyzer comparison task |
||
|
Collects SQL statements from an existing SQL tuning set |
||
|
Collects SQL statements from the workload repository |
||
|
Updates the value of a SQL tuning parameter of type |
||
|
Returns a SQL text's signature |
||
|
Uses the profile data stored in the staging table to create profiles on this system |
||
|
Moves one or more SQL tuning sets from the staging table |
||
|
Updates selected fields for a SQL statement in a SQL tuning set |
168.5.1 ACCEPT_ALL_SQL_PROFILES Procedure
This procedure accepts all SQL profiles recommended by a specific execution of a tuning task, and sets the attributes of the SQL profiles according to the parameter values passed by the user.
See Also:
DBMS_SQLTUNE SQL Profile Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.ACCEPT_ALL_SQL_PROFILES (
task_name IN VARCHAR2,
category IN VARCHAR2 := NULL,
replace IN BOOLEAN := FALSE,
force_match IN BOOLEAN := FALSE,
profile_type IN VARCHAR2 := REGULAR_PROFILE,
autotune_period IN NUMBER := NULL,
execution_name IN VARCHAR2 := NULL,
task_owner IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL);Parameters
Table 168-8 ACCEPT_ALL_SQL_PROFILES Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The (mandatory) name of the SQL tuning task |
|
|
This is the category name which must match the value of the |
|
|
If the profile already exists, it is replaced if this argument is |
|
|
If If |
|
|
Options:
|
|
|
The time period for the automatic SQL tuning. This setting applies only to the automatic SQL Tuning Advisor task. Possible values are as follows:
The procedure interprets any other value as the time of the most recent task execution minus the value of this argument. |
|
|
Name of the task execution to use. If null, then the procedure generates the report for the most recent task execution. |
|
|
Owner of the tuning task. This is an optional parameter that must be specified to accept a SQL profile associated to a tuning task owned by another user. The current user is the default value. |
|
|
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. |
database_link_to |
Name of a database link that exists on a standby database. The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local. Use The |
Security Model
The ADMINISTER SQL MANAGEMENT OBJECT privilege is required. The CREATE ANY SQL PROFILE privilege is deprecated.
168.5.2 ACCEPT_SQL_PROFILE Procedure and Function
This subprogram creates a SQL profile recommended by SQL Tuning Advisor.
The SQL text is normalized for matching purposes although it is stored in the data dictionary in denormalized form for readability. SQL text is provided through a reference to the SQL Tuning task. If the referenced SQL statement does not exist, then the database reports an error.
See Also:
DBMS_SQLTUNE 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,
profile_type IN VARCHAR2 := REGULAR_PROFILE);
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,
profile_type IN VARCHAR2 := REGULAR_PROFILE,
database_link_to IN VARCHAR2 := NULL)
RETURN VARCHAR2;Parameters
Table 168-9 ACCEPT_SQL_PROFILE Procedure and Function Parameters
| Parameter | Description |
|---|---|
|
|
The (mandatory) name of the SQL tuning task |
|
|
The identifier of the advisor framework object representing the SQL statement associated with the tuning task |
|
|
The name of the SQL profile. It cannot contain double quotation marks. The name is case sensitive. If not specified, the system generates a unique name for the SQL profile. |
|
|
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. |
|
|
The category name. This name must match the value of the |
|
|
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. |
|
|
If the profile already exists, it is replaced if this argument is |
|
|
If If |
|
|
Options:
|
database_link_to |
Name of a database link that exists on a standby database. The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local. Use The |
Return Values
The name of the SQL profile.
Usage Notes
The ADMINISTER SQL MANAGEMENT OBJECT privilege is required. The CREATE ANY SQL PROFILE privilege is deprecated.
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 is not 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. 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);168.5.3 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:
DBMS_SQLTUNE 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
The parameters are identical for DBMS_SQLTUNE.ADD_SQLSET_REFERENCE and DBMS_SQLSET.ADD_REFERENCE.
Table 168-10 ADD_SQLSET_REFERENCE and ADD_REFERENCE Function Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the SQL tuning set. |
|
|
Provides an optional description of the usage of SQL tuning set. The description is truncated if longer than 256 characters. |
|
|
Specifies the owner of the SQL tuning set, or |
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 DBA_SQLSET_REFERENCES view to find all references on a given SQL tuning set.
168.5.4 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):
-
STATUScan be set toENABLEDorDISABLED. -
NAMEcan be reset to a valid name which must be a valid Oracle identifier and must be unique. -
DESCRIPTIONcan be set to any string of size no more than 500 characters. -
CATEGORYcan be reset to a valid category name which must be a valid Oracle identifier and must be unique when combined with normalized SQL text).
See Also:
DBMS_SQLTUNE 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 168-11 ALTER_SQL_PROFILE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The (mandatory) name of the existing SQL profile to alter |
|
|
The (mandatory) attribute name to alter (case insensitive) using valid attribute names |
|
|
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 is 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 is 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');168.5.5 CANCEL_TUNING_TASK Procedure
This procedure cancels the currently executing tuning task. All intermediate result data is deleted.
See Also:
DBMS_SQLTUNE SQL Tuning Advisor Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.CANCEL_TUNING_TASK (
task_name IN VARCHAR2);Parameters
Table 168-12 CANCEL_TUNING_TASK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies 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);168.5.6 CAPTURE_CURSOR_CACHE_SQLSET Procedure
This procedure captures a workload from the shared SQL area into a SQL tuning set.
The procedure polls the cache multiple times over a time period, and updates the workload data stored there. It can execute over as long a period as required to capture an entire system workload.
See Also:
DBMS_SQLTUNE 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,
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL);Parameters
The parameters are the same for both DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET and DBMS_SQLSET.CAPTURE_CURSOR_CACHE.
Table 168-13 CAPTURE_CURSOR_CACHE_SQLSET and CAPTURE_CURSOR_CACHE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the SQL tuning set name |
|
|
Defines the total amount of time, in seconds, to execute. |
|
|
Defines the amount of time, in seconds, to pause between sampling. |
|
|
Specifies whether to insert new statements, update existing statements, or both. Values are |
|
|
Specifies the capture mode (
|
|
|
Defines a filter to apply to the shared SQL area for each sample. If |
|
|
Specifies the owner of the SQL tuning set or |
|
|
Defines a filter that includes recursive SQL in the SQL tuning set ( |
Examples
In this example capture takes place over a 30-second period, polling the cache once every five seconds. This captures 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 shared SQL area 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');168.5.7 CREATE_SQL_PLAN_BASELINE Procedure
This procedure creates a SQL plan baseline for an execution plan. It can be used in the context of an Alternative Plan Finding made by SQL Tuning Advisor.
See Also:
DBMS_SQLTUNE SQL Tuning Advisor Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE (
task_name IN VARCHAR2,
object_id IN NUMBER := NULL,
plan_hash_value IN NUMBER,
owner_name IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL); Parameters
Table 168-14 CREATE_SQL_PLAN_BASELINE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the task for which to get a script |
|
|
Object ID to which the SQL corresponds |
|
|
Plan to create plan baseline |
|
|
Owner of the relevant tuning task. Defaults to the current schema owner. |
database_link_to |
Name of a database link that exists on a standby database. The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local. Use The |
168.5.8 CREATE_SQLSET Procedure and Function
This procedure or function creates a SQL tuning set object in the database.
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 168-15 CREATE_SQLSET Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the created SQL tuning set. The name is the name passed to the function. If no name is passed to the function, then the function generates an automatic name. |
|
|
Provides an optional description of the SQL tuning set. |
|
|
Specifies the owner of the SQL tuning set, or |
Examples
EXEC DBMS_SQLTUNE.CREATE_SQLSET(-
sqlset_name => 'my_workload', -
description => 'complete application workload');168.5.9 CREATE_STGTAB_SQLPROF Procedure
This procedure creates the staging table used for copying SQL profiles from one system to another.
See Also:
DBMS_SQLTUNE 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 168-16 CREATE_STGTAB_SQLPROF Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the table to create (case-insensitive unless double quoted). |
|
|
The schema to create the table in, or |
|
|
The tablespace to store the staging table within, or |
Usage Notes
-
Call this procedure once before issuing a call to the PACK_STGTAB_SQLPROF Procedure.
-
To put different SQL profiles in different staging tables, you can call this procedure multiple times.
-
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');168.5.10 CREATE_STGTAB_SQLSET Procedure
This procedure creates a staging table through which SQL tuning sets are imported and exported.
See Also:
DBMS_SQLTUNE 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,
db_version IN NUMBER := NULL);Parameters
Table 168-17 CREATE_STGTAB_SQLSET and CREATE_STGTAB Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the of the table to create. The name is case sensitive. |
|
|
Defines the schema in which to create the table, or |
|
|
Specifies the tablespace in which to store the staging table, or |
|
|
Specifies the database version that determines the format of the staging table. You can also create an older database version staging table to export an STS to an older database version. Use one of the following values:
|
Security Model
You must have CREATE TABLE permissions in the specified schema and tablespace.
Usage Notes
-
Call this procedure once before packing the SQL set.
-
To have different tuning sets in different staging tables, you can call this procedure multiple times.
-
This is a DDL operation, so it does not occur within a transaction.
-
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 a staging table to pack a SQL tuning set in Oracle Database 11g Release 2 (11.2) format
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
table_name => 'STGTAB_SQLSET'
, db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION );
END;168.5.11 CREATE_TUNING_TASK Functions
This function creates a SQL Tuning Advisor task.
You can use different forms of this function to:
-
Create a tuning task for a single statement given its text.
-
Create a tuning task for a single statement from the shared SQL area given its identifier.
-
Create a tuning task for a single statement from the workload repository given a range of snapshot identifiers.
-
Create a tuning task for a SQL tuning set.
-
Create a tuning task for SQL Performance Analyzer.
In all cases, the function mainly creates a SQL Tuning Advisor task and sets its parameters.
See Also:
DBMS_SQLTUNE 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,
con_name IN VARCHAR2 := NULL,
database_link_to 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,
con_name IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL)
RETURN VARCHAR2;
AWR 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,
con_name IN VARCHAR2 := NULL,
dbid IN NUMBER := NULL,
database_link_to IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQL tuning set 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,
database_link_to IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQL Performance Analyzer format:
DBMS_SQLTUNE.CREATE_TUNING_TASK (
spa_task_name IN VARCHAR2,
spa_task_owner IN VARCHAR2 := NULL,
spa_compare_exec IN VARCHAR2 := NULL,
basic_filter IN VARCHAR2 := NULL,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2; Parameters
Table 168-18 CREATE_TUNING_TASK Function Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the text of a SQL statement. |
|
|
Specifies the begin snapshot identifier. |
|
|
Specifies the end snapshot identifier. |
|
|
Specifies the identifier of a SQL statement. |
|
|
Defines an ordered list of bind values in NOTE: This parameter is not supported on a standby database. |
|
|
Specifies the hash value of the SQL execution plan. |
|
|
Specifies the SQL tuning set name. |
|
|
Specifies the predicate used to filter the SQL from the SQL tuning set. |
|
|
Specifies the object filter. |
|
|
Specifies an |
|
|
Specifies the percentage on the sum of a ranking measure. |
|
|
Specifies the top L(imit) SQL from the filtered or ranked SQL. |
|
|
Specifies the user name for whom the statement is to be tuned. |
|
|
Specifies the tuning scope:
|
|
|
Specifies the maximum duration in seconds for the tuning session. |
|
|
Specifies an optional tuning task name. |
|
|
Provides a description of the SQL tuning session, up to a maximum of 256 characters. |
|
|
Specifies the plan filter. It is applicable when multiple plans (
|
|
|
Specifies the owner of the SQL tuning set, or |
|
|
Specifies the name of the SQL Performance Analyzer task whose regressions are to be tuned. |
|
|
Specifies the owner of specified SQL Performance Analyzer task or |
|
|
Specifies the execution name of the Compare Performance trial of SQL Performance Analyzer task. If |
dbid |
Specifies the DBID for imported or PDB-level AWR data. If |
|
|
Specifies the container for the tuning task. The semantics depend on the function format:
The following statements are true of all function formats:
|
database_link_to |
The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local. Use The |
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.
Example 168-1 Examples
The following examples assume the following variable definitions:
VARIABLE stmt_task VARCHAR2(64);
VARIABLE sts_task VARCHAR2(64);
VARIABLE spa_tune_task VARCHAR2(64);Example 168-2 Create Tuning Task with 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'')');Example 168-3 Create Tuning Task with SQL ID Format
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24');
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', -
scope => 'LIMITED');
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', -
time_limit => 600);Example 168-4 Create Tuning Task with AWR Snapshot Format
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 1, -
end_snap => 2, sql_id => 'ay1m3ssvtrh24');Example 168-5 Create Tuning Task with SQL Tuning Set Format
This example creates a task that tunes SQL statements in order by buffer gets, and also sets a 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');Example 168-6 Create Tuning Task with SPA Task Format
This example tunes the SQL statement that were reported as having regressed from the compare performance execution of the SQL Performance Analyzer task named task_123.
EXEC :spa_tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
spa_task_name => 'task_123',
spa_task_owner => 'SCOTT',
spa_compare_exec => 'exec1');Example 168-7 Creating SQL Tuning Task on Standby Database
This example creates a tuning task on the standby database. The tune_stby_wkld task uses the lnk_to_primary database link to write data to the primary database, which is open read/write.
VAR tname VARCHAR2(30);
VAR query VARCHAR2(500);
EXEC :tname := 'tune_stby_wkld';
EXEC :query := 'SELECT /*+ FULL(t)*/ col1 FROM table1 t WHERE col1=9000';
EXEC :tname := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => :query,-
task_name => :tname, database_link_to => 'lnk_to_primary');
168.5.12 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
Table 168-19 DELETE_SQLSET Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the SQL tuning set. |
|
|
Specifies the 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 SQL tuning set. |
|
|
Specifies the owner of the SQL tuning set, or |
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');168.5.13 DROP_SQL_PROFILE Procedure
This procedure drops the named SQL profile from the database.
See Also:
DBMS_SQLTUNE SQL Profile Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.DROP_SQL_PROFILE (
name IN VARCHAR2,
ignore IN BOOLEAN := FALSE);Parameters
Table 168-20 DROP_SQL_PROFILE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The (mandatory) name of SQL profile to be dropped. The name is case sensitive. |
|
|
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);168.5.14 DROP_SQLSET Procedure
This procedure drops a SQL tuning set if it is not active.
See Also:
DBMS_SQLTUNE 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 168-21 DROP_SQLSET Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the SQL tuning set. |
|
|
Specifies the owner of the SQL tuning set, or |
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');168.5.15 DROP_TUNING_TASK Procedure
This procedure drops a SQL tuning task. The task and all its result data are deleted.
See Also:
DBMS_SQLTUNE SQL Tuning Advisor Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.DROP_TUNING_TASK (
task_name IN VARCHAR2);Parameters
Table 168-22 DROP_TUNING_TASK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies name of the tuning task to drop. |
168.5.16 EXECUTE_TUNING_TASK Function and 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:
DBMS_SQLTUNE 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,
database_link_to 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,
database_link_to IN VARCHAR2 := NULL);Parameters
Table 168-23 EXECUTE_TUNING_TASK Function & Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the tuning task to execute. |
|
|
A name to qualify and identify an execution. If not specified, it is generated by the advisor and returned by function. |
|
|
List of parameters (name, value) for the specified execution. The execution parameters have effect only on the execution for which they are specified. They override the values for the parameters stored in the task (set through the SET_TUNING_TASK_PARAMETER Procedures). |
|
|
A 256-length string describing the execution. |
database_link_to |
Name of a database link that exists on a standby database. The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local. Use The |
Usage Notes
A tuning task can be executed multiples times without having to reset it.
Examples
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);168.5.17 IMPLEMENT_TUNING_TASK Procedure
This procedure implements a set of SQL profile recommendations made by SQL Tuning Advisor.
Executing IMPLEMENT_TUNING_TASK is equivalent to executing the SCRIPT_TUNING_TASK Function and then running the script.
See Also:
DBMS_SQLTUNE 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,
database_link_to IN VARCHAR2 := NULL);Parameters
Table 168-24 IMPLEMENT_TUNING_TASK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the tuning task for which to implement recommendations. |
|
|
Filter the types of recommendations to implement. Only ' |
|
|
Owner of the relevant tuning task or |
|
|
Name of the task execution to use. If |
database_link_to |
Name of a database link that exists on a standby database. The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local. Use The |
168.5.18 INTERRUPT_TUNING_TASK Procedure
This procedure interrupts the currently executing tuning task. The task ends its operations as it would at normal exit so that the user can access the intermediate results.
See Also:
DBMS_SQLTUNE SQL Tuning Advisor Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.INTERRUPT_TUNING_TASK (
task_name IN VARCHAR2);Parameters
Table 168-25 INTERRUPT_TUNING_TASK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the tuning task to interrupt |
Examples
EXEC DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(:my_task);168.5.19 LOAD_SQLSET Procedure
This procedure populates the SQL tuning set with a set of selected SQL statements. You can call the procedure multiple times to add new SQL statements or replace attributes of existing statements.
See Also:
DBMS_SQLTUNE 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 168-26 LOAD_SQLSET Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of SQL tuning set to be loaded. |
|
|
Specifies the cursor reference to the SQL tuning set to be loaded. |
|
|
Specifies which statements are loaded into the SQL tuning set. The possible values are:
|
|
|
Specifies how existing SQL statements are updated. This parameter is considered only if
|
|
|
Specifies when to perform the update. The procedure only performs the update when the specified condition is satisfied. 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:
|
|
|
Specifies the list of SQL statement attributes to update during a merge or update. The possible values are:
|
|
|
Specifies whether to update attributes when the new value is If |
|
|
Specifies whether to commit statements after DML. If a value is provided, then the load commits after each specified number of statements is inserted. If Providing a value for this argument enables you to monitor the progress of a SQL tuning set load operation in the |
|
|
Defines the owner of the SQL tuning set, or the current schema owner (or |
Exceptions
-
This procedure returns an error when
sqlset_nameis invalid, or a corresponding SQL tuning set does not exist, or thepopulate_cursoris 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 shared SQL area 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 loads only 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 shared SQL area
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;
/168.5.20 PACK_STGTAB_SQLPROF Procedure
This procedure copies profile data from the SYS. schema into the staging table.
See Also:
DBMS_SQLTUNE 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 168-27 PACK_STGTAB_SQLPROF Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the profile to pack (% wildcards acceptable, case-sensitive) |
|
|
The category to pack profiles from (% wildcards acceptable, case-sensitive) |
|
|
The name of the table to use (case-insensitive unless double quoted). Required. |
|
|
The schema where the table resides, or |
Security Model
This procedures requires ADMINISTER SQL MANAGEMENT OBJECT privilege and INSERT privilege on the staging table.
Usage Notes
This function issues a COMMIT after packing each SQL profile. If an error is raised mid-execution, then 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 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 moves 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');168.5.21 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:
DBMS_SQLTUNE 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,
db_version IN NUMBER := NULL);Examples
Put all SQL tuning sets on the database in the staging table:
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => '%'
, sqlset_owner => '%'
, staging_table_name => 'STGTAB_SQLSET');
END;Put only those SQL tuning sets owned by the current user in the staging table:
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => '%'
, staging_table_name => 'STGTAB_SQLSET');
END;Pack a specific SQL tuning set:
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'my_workload'
, staging_table_name => 'STGTAB_SQLSET');
END;Pack a second SQL tuning set:
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'workload_subset'
, staging_table_name => 'STGTAB_SQLSET');
END;Pack the STS my_workload_subset into a staging table stgtab_sqlset created for Oracle Database 11g Release 1 (11.2):
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'workload_subset'
, staging_table_name => 'STGTAB_SQLSET'
, db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION);
END;168.5.22 REMAP_STGTAB_SQLPROF Procedure
This procedure changes the profile data values kept in the staging table prior to performing an unpack operation.
See Also:
DBMS_SQLTUNE 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 168-28 REMAP_STGTAB_SQLPROF Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the profile to target for a remap operation (case-sensitive) |
|
|
The new name of the profile, or |
|
|
The new category for the profile, or |
|
|
The name of the table on which to perform the remap operation (case-sensitive). Required. |
|
|
The schema where the table resides, or |
Security Model
This procedure requires the UPDATE privilege on the staging table.
Examples
Change the name of a profile before we unpack, to avoid conflicts
BEGIN
DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF(
old_profile_name => :pname
, new_profile_name => 'IMP' || :pname
, staging_table_name => 'PROFILE_STGTAB');
END;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.
BEGIN
DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF(
old_profile_name => :pname
, new_profile_category => 'TEST'
, staging_table_name => 'PROFILE_STGTAB');
END;168.5.23 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.
See Also:
DBMS_SQLTUNE 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,
staging_schema_owner IN VARCHAR2 := NULL
old_con_dbid IN NUMBER := NULL,
new_con_dbid IN NUMBER := NULL);
);Parameters
The parameters are identical for the DBMS_SQLTUNE.REMAP_STGTAB_SQLSET and DBMS_SQLSET.REMAP_SQLSET procedures.
Table 168-29 REMAP_STGTAB_SQLSET and REMAP_SQLSET Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the tuning set to target for a remap operation. Wildcard characters ( |
|
|
Specifies the new name of the tuning set owner to target for a remap operation. |
|
|
Specifies the new name for the tuning set, or |
|
|
Specifies the new owner for the tuning set, or |
|
|
Specifies the name of the table on which to perform the remap operation. The value is case sensitive. |
|
|
Specifies the name of staging table owner, or |
|
|
Specifies the old container DBID to be remapped to a new container DBID. Specify |
|
|
Specifies the new container DBID to replace with the old container DBID. Specify |
Usage Notes
Call this procedure multiple times to remap more than one tuning set name or owner. This procedure only handles one tuning set per call.
Examples
-- Change the name of an STS in the staging table before unpacking it.
BEGIN
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 unpacking it.
DBMS_SQLTUNE.REMAP_STGTAB_SQLSET(
old_sqlset_name => 'imp_workload'
, old_sqlset_owner => 'SH'
, new_sqlset_owner => 'SYS'
, staging_table_name => 'STGTAB_SQLSET');
END;168.5.24 REMOVE_SQLSET_REFERENCE Procedure
This procedure deactivates a SQL tuning set to indicate that it is no longer used by the client.
See Also:
DBMS_SQLTUNE SQL Tuning Set Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE (
sqlset_name IN VARCHAR2,
reference_id IN NUMBER,
sqlset_owner IN VARCHAR2 := NULL,
force_remove IN NUMBER := 0);Parameters
The parameters are identical for the DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE and DBMS_SQLSET.REMOVE_REFERENCE procedures.
Table 168-30 REMOVE_SQLSET_REFERENCE and REMOVE_REFERENCE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the SQL tuning set. |
|
|
Specifies the identifier of the reference to remove. |
|
|
Specifies the owner of the SQL tuning set (or |
|
|
Specifies whether references can be removed for other users ( Setting this parameter to |
Examples
You can remove references on a given SQL tuning set when you finish using it and want to make it writable again. The following example removes the reference to my_workload:
EXEC DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE( -
sqlset_name => 'my_workload', -
reference_id => :rid,
sqlset_owner => NULL,
force_remove => 0);
To find all references to a given SQL tuning set, query the DBA_SQLSET_REFERENCES view.
168.5.25 REPORT_AUTO_TUNING_TASK Function
This function displays a report from the automatic tuning task.
This function reports on a range of task executions, whereas the REPORT_TUNING_TASK Function reports on a single execution. Note that this function is deprecated with Oracle Database 11g Release 2 (11.2) in favor of DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK.
See Also:
-
DBMS_SQLTUNE SQL Tuning Set 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)
RETURN CLOB;Parameters
Table 168-31 REPORT_AUTO_TUNING_TASK Function Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the execution from which to begin the report. |
|
|
Specifies the name of the execution at which to end the report. |
|
|
Specifies the type of the report to produce. Possible values are |
|
|
Specifies the level of detail in the report:
|
|
|
Limits the report to a single section (
|
|
|
Specifies the advisor framework object ID that represents a single statement to restrict reporting to. Specify |
|
|
Specifies the maximum number of SQL statements to show in the report. |
Return Values
A CLOB containing the desired report.
168.5.26 REPORT_SQL_DETAIL Function
This function builds a report for a specific SQLID. For each SQLID it gives various statistics and details as obtained from the V$ views and AWR.
See Also:
DBMS_SQLTUNE SQL Performance Reporting Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.REPORT_SQL_DETAIL (
sql_id IN VARCHAR2 DEFAULT NULL,
sql_plan_hash_value IN NUMBER DEFAULT NULL,
start_time IN DATE DEFAULT NULL,
duration IN NUMBER DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL,
event_detail IN VARCHAR2 DEFAULT 'YES',
bucket_max_count IN NUMBER DEFAULT 128,
bucket_interval IN NUMBER DEFAULT NULL,
top_n IN NUMBER DEFAULT 10,
report_level IN VARCHAR2 DEFAULT 'TYPICAL',
type IN VARCHAR2 DEFAULT 'ACTIVE',
data_source IN VARCHAR2 DEFAULT 'AUTO',
end_time IN DATE DEFAULT NULL,
duration_stats IN NUMBER DEFAULT NULL,
con_name IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;Parameters
Table 168-32 REPORT_SQL_DETAIL Function Parameters
| Parameter | Description |
|---|---|
|
|
SQLID for which monitoring information should be displayed. If |
|
|
Displays SQL statistics and details for a specific |
|
|
If specified, shows SQL activity (from |
|
|
Duration of activity in seconds for the report. If |
|
|
Target instance to get SQL details from. If |
|
|
DBID from which to get SQL details. If |
|
|
When set to |
|
|
If specified, this should be the maximum number of histogram buckets created in the report. If not specified, a value of 128 is used. |
|
|
If specified, this represents the exact time interval in seconds, of all histogram buckets. If specified, |
|
|
Controls the number of entries to display per dimension in the top dimensions section. If not specified, a default value of 10 is used. |
|
|
Level of detail for the report, either In addition, individual report sections can also be enabled or disabled by using a +/-
In addition, SQL text can be specified at different levels:
The meanings of the three top-level report levels are:
Only one of these 4 levels can be specified and, if it is, it has to be at the start of the |
|
|
Report format: |
|
|
Determines the data source of SQL data based on one of the following values:
|
|
|
If specified, shows SQL activity from |
|
|
Duration of additional SQL execution statistics from AWR (in hours), for the report. If |
|
|
Name of the multitenant container database (CDB). |
Security Model
The invoker needs the EXECUTE privilege on the DBMS_XPLAN package.
Return Values
A CLOB containing the desired report.
Usage Notes
-
ACTIVEreports have a rich, interactive user interface similar to Enterprise Manager while not requiring any EM installation. The report file built is in HTML format, so it can be interpreted by most modern browsers. The code powering the active report is downloaded transparently by the web browser when the report is first viewed, hence viewing it requires outside connectivity. -
The invoker needs the
SELECTorREADprivilege on the following views:-
V$SESSION -
DBA_ADVISOR_FINDINGS -
V$DATABASE -
GV$ASH_INFO -
GV$ACTIVE_SESSION_HISTORY -
GV$SQLAREA_PLAN_HASH -
GV$SQL -
DBA_HIST_SNAPSHOT -
DBA_HIST_WR_CONTROL -
DBA_HIST_ACTIVE_SESS_HISTORY -
DBA_HIST_SQLSTAT -
DBA_HIST_SQL_BIND_METADATA -
DBA_HIST_SQLTEXT -
DBA_SQL_PLAN_BASELINES -
DBA_SQL_PROFILES -
DBA_ADVISOR_TASKS -
DBA_SERVICES -
DBA_USERS -
DBA_OBJECTS -
DBA_PROCEDURES
-
168.5.27 REPORT_SQL_MONITOR Function
This function builds a report (text, simple HTML, active HTML, XML) for the monitoring information collected on behalf of the targeted statement execution.
See Also:
Real-Time SQL Monitoring for other subprograms in this group
Syntax
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id IN VARCHAR2 DEFAULT NULL,
dbop_name IN VARCHAR2 DEFAULT NULL,
dbop_exec_id IN NUMBER DEFAULT NULL,
session_id IN NUMBER DEFAULT NULL,
session_serial IN NUMBER DEFAULT NULL,
sql_exec_start IN DATE DEFAULT NULL,
sql_exec_id IN NUMBER DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
start_time_filter IN DATE DEFAULT NULL,
end_time_filter IN DATE DEFAULT NULL,
instance_id_filter IN NUMBER DEFAULT NULL,
parallel_filter IN VARCHAR2 DEFAULT NULL,
plan_line_filter IN NUMBER DEFAULT NULL,
event_detail IN VARCHAR2 DEFAULT 'YES',
bucket_max_count IN NUMBER DEFAULT 128,
bucket_interval IN NUMBER DEFAULT NULL,
base_path IN VARCHAR2 DEFAULT NULL,
last_refresh_time IN DATE DEFAULT NULL,
report_level IN VARCHAR2 DEFAULT 'TYPICAL',
type IN VARCHAR2 DEFAULT 'TEXT',
sql_plan_hash_value IN NUMBER DEFAULT NULL,
con_name IN VARCHAR2 DEFAULT NULL,
report_id IN NUMBER DEFAULT NULL)
RETURN CLOB;Parameters
Table 168-33 REPORT_SQL_MONITOR Function Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
|
|
|
Execution ID for the composite database operation for which monitoring information is displayed. |
|
|
If not |
|
|
In addition to the |
|
|
This parameter, along with |
|
|
This parameter, along with |
|
|
Only considers statements started on the specified instance. Use -1 to target the login instance. |
|
|
If not |
|
|
If not |
|
|
Only applies when the execution runs parallel across multiple Oracle Real Application Cluster (Oracle RAC) instances. This parameter allows to only report the activity of the specified instance. Use a |
|
|
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:
The following examples show how to target a subset of the parallel processes:
|
|
|
When value is ' |
|
|
If specified, this should be the maximum number of histogram buckets created in the report |
|
|
If specified, this represents the exact time interval in seconds, of all histogram buckets. If specified, |
|
|
URL path for flex HTML resources since flex HTML format is required to access external files (java scripts and the flash SWF file itself) |
|
|
If not
|
|
|
Level of detail for the report: In addition, individual report sections can also be enabled or disabled by using a +/-
In addition, SQL text can be specified at different levels:
|
|
|
The meanings of the three top-level report levels are:
Only one of these 4 levels can be specified and, if it is, it has to be at the start of the |
|
|
Report format, |
|
|
Target only those SQL executions with the specified |
|
|
Name of the multitenant container database (CDB). |
|
|
ID of the report in auto-report repository. Report IDs can be found in |
Return Values
A CLOB containing the desired report.
Usage Notes
-
The target SQL statement for this report can be:
-
The most recent SQL statement monitored by Oracle Database. This is the default behavior, so there is no need to specify any parameter.
-
The most recent SQL statement 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 =>for the current session orsession_id => 20, session_serial => 103for session ID 20, serial number 103. -
The most recent 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_startandsql_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_CATALOGrole).-
GV$SQL_MONITOR -
GV$SQL_PLAN_MONITOR -
GV$SQL_PLAN -
GV$ACTIVE_SESSION_HISTORY -
GV$SESSION_LONGOPS -
GV$SQL
-
-
The
bucket_max_countandbucket_intervalparameters control the activity histogram.By default, the maximum number of buckets is set to 128. The database derives the
bucket_intervalvalue based on this count. Thebucket_interval(value is in seconds) is computed such that it is the smallest possible power of 2 value (starting at 1 second) without exceeding the maximum number of buckets. For example, if the query has executed for 600 seconds, then the database selects abucket_intervalof 8 seconds (a power of two). The database chooses the value of 8 because 600/8 = 74, which is less than 128 buckets maximum. Smaller than 8 seconds would be 4 seconds, which would lead to more buckets than the 128 maximum. Ifbucket_intervalis specified, then the database uses the specified value instead of deriving it frombucket_max_count. -
ACTIVEreports have a rich, interactive user interface similar to Enterprise Manager, while not requiring any EM installation.The report file is in HTML format. The code powering the active report is downloaded transparently by the web browser when the report is first viewed. Therefore, viewing the report requires outside connectivity.
See Also:
Oracle Database SQL Tuning Guide for more information about SQL real-time monitoring.
168.5.28 REPORT_SQL_MONITOR_LIST Function
This function builds a report for all or a subset of statements monitored by Oracle Database. For each statement, the subprogram gives key information and associated global statistics.
Use the REPORT_SQL_MONITOR Function to get detailed monitoring information for a single SQL statement.
See Also:
Real-Time SQL Monitoring for other subprograms in this group
Syntax
DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST(
sql_id IN VARCHAR2 DEFAULT NULL,
session_id IN NUMBER DEFAULT NULL,
session_serial IN NUMBER DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
active_since_date IN DATE DEFAULT NULL,
active_since_sec IN NUMBER DEFAULT NULL,
active_before_date IN DATE DEFAULT NULL,
last_refresh_time IN DATE DEFAULT NULL,
dbop_name IN VARCHAR2 DEFAULT NULL,
monitor_type IN NUMBER DEFAULT MONITOR_TYPE_ALL,
max_sqltext_length IN NUMBER DEFAULT NULL,
top_n_count IN NUMBER DEFAULT NULL,
top_n_rankby IN VARCHAR2 DEFAULT 'LAST_ACTIVE_TIME',
report_level IN VARCHAR2 DEFAULT 'TYPICAL',
auto_refresh IN NUMBER DEFAULT NULL,
base_path IN VARCHAR2 DEFAULT NULL,
type IN VARCHAR2 DEFAULT 'TEXT',
con_name IN VARCHAR2 DEFAULT NULL,
top_n_detail_count IN NUMBER DEFAULT NULL)
RETURN CLOB;Parameters
Table 168-34 REPORT_SQL_MONITOR_LIST Function Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
If not |
|
|
In addition to the |
|
|
Only considers statements started on the specified instance. Use -1 to target the login instance. |
|
|
If not |
|
|
Same as |
|
|
If not |
|
|
If not |
|
|
DB operation name. Specify |
|
|
Type of the SQL Monitor operation. Specify one of the following values:
|
|
|
Maximum length of the SQL text. Default is |
|
|
Limits the number of top-N SQL statements that need to be included in the report. |
|
|
Specifies the attribute to rank the SQL statements. Specify this value when
|
|
|
Level of detail for the report. The level is one of the following:
|
|
|
Currently non-operational, reserved for future use. |
|
|
URL path for flex HTML resources because flex HTML format is required to access external files (java scripts and the flash SWF file itself). |
|
|
Report format: |
|
|
Name of the multitenant container database (CDB) |
|
|
Limits the number of top-N SQL statements for which the SQL monitor details need to be included in the report. |
Return Values
A report for the list of SQL statements that have been monitored. The report type is text, XML, or HTML.
Usage Notes
You must have the privilege to access the following fixed views: GV$SQL_MONITOR and GV$SQL.
See Also:
Oracle Database SQL Tuning Guide for more information about SQL real-time monitoring.
168.5.29 REPORT_TUNING_TASK Function
This function displays the results of a tuning task. By default the report is in text format.
Syntax
See Also:
DBMS_SQLTUNE SQL Performance Reporting Subprograms for other subprograms in this group
DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name IN VARCHAR2,
type IN VARCHAR2 := 'TEXT',
level IN VARCHAR2 := 'TYPICAL',
section IN VARCHAR2 := ALL,
object_id IN NUMBER := NULL,
result_limit IN NUMBER := NULL,
owner_name IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL)
RETURN CLOB;Parameters
Table 168-35 REPORT_TUNING_TASK Function Parameters
| Parameter | Description |
|---|---|
|
|
Name of the tuning task. |
|
|
Type of the report to produce. Possible values are |
|
|
Level of detail in the report:
|
|
|
Section of the report to include. You can limit the report to any of the following single sections (
|
|
|
Advisor framework object ID that represents a single statement to restrict reporting to. |
|
|
Maximum number of SQL statements to show in the report. |
|
|
Owner of the relevant tuning task. The default is the current schema owner. |
|
|
Name of the task execution to use. If |
database_link_to |
Name of a database link that exists on a standby database. The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local. Use The |
Return Values
A CLOB containing the desired report.
Examples
-- Display the report for a single statement.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:stmt_task)
FROM DUAL;
-- Display the summary for a SQL tuning set.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY')
FROM DUAL;
-- Display the findings for a specific statement.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL','FINDINGS', 5)
FROM DUAL;168.5.30 REPORT_TUNING_TASK_XML Function
This function displays an XML report of a tuning task.
Syntax
See Also:
DBMS_SQLTUNE SQL Performance Reporting Subprograms for other subprograms in this group
DBMS_SQLTUNE.REPORT_TUNING_TASK_LIST_XML(
task_name IN VARCHAR2 := NULL,
level IN VARCHAR2 := LEVEL_TYPICAL,
section IN VARCHAR2 := SECTION_ALL,
object_id IN NUMBER := NULL,
result_limit IN NUMBER := 160,
owner_name IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL,
autotune_period IN NUMBER := NULL,
report_tag IN VARCHAR2 := NULL)
RETURN XMLTYPE;
Parameters
Table 168-36 REPORT_TUNING_TASK_XML Function Parameters
| Parameter | Description |
|---|---|
|
|
Name of the tuning task. |
|
|
Level of detail in the report:
|
|
|
Section of the report to include. You can limit the report to any of the following single sections (
|
|
|
Advisor framework object ID that represents a single statement to restrict reporting to. |
|
|
The number of statements in a SQL tuning set or snapshot range for which the report is generated. The default is 160 (20 statements * 8 categories). The categories are as follows:
|
|
|
Owner of the relevant tuning task. The default is the current schema owner. |
|
|
Name of the task execution to use. If |
|
|
The time period for the automatic SQL tuning. This setting applies only to the automatic SQL Tuning Advisor task. Possible values are as follows:
The procedure interprets any other value as the time of the most recent task execution minus the value of this argument. |
|
|
The name of the root XML tag. By default, the tag is the report reference generated by the reporting framework. |
Return Values
A CLOB containing the desired report.
168.5.31 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:
DBMS_SQLTUNE SQL Tuning Advisor Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.RESET_TUNING_TASK(
task_name IN VARCHAR2);Parameters
Table 168-37 RESET_TUNING_TASK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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);168.5.32 RESUME_TUNING_TASK Procedure
This procedure resumes a previously interrupted task that was created to process a SQL tuning set.
See Also:
DBMS_SQLTUNE 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 168-38 RESUME_TUNING_TASK Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the tuning task to resume. |
|
|
A SQL predicate to filter the SQL from the SQL tuning set. Note that this filter is applied in conjunction with the parameter |
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);168.5.33 SCHEDULE_TUNING_TASK Function
This function creates a tuning task for a single SQL statement and schedules a DBMS_SCHEDULER job to execute the tuning task. One form of the function finds the information about the statement to be tuned in the shared SQL area, whereas the other finds the information in AWR.
See Also:
DBMS_SQLTUNE SQL Tuning Advisor Subprograms for other subprograms in this group
Syntax
Shared SQL Area Format:
DBMS_SQLTUNE.SCHEDULE_TUNING_TASK(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
start_date IN TIMESTAMP WITH TIME ZONE := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
con_name IN VARCHAR2 := NULL)
RETURN VARCHAR2;AWR Format:
DBMS_SQLTUNE.SCHEDULE_TUNING_TASK(
begin_snap IN NUMBER,
end_snap IN NUMBER,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
start_date IN TIMESTAMP WITH TIME ZONE := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
con_name IN VARCHAR2 := NULL,
dbid IN NUMBER := NULL)
RETURN VARCHAR2;Parameters
Table 168-39 SCHEDULE_TUNING_TASK Function Parameters
| Parameter | Description |
|---|---|
|
|
The beginning snapshot identifier. The range is exclusive, which means that SQL statements in this snapshot ID are not included. |
|
|
The end snapshot identifier. The range is inclusive, which means that SQL statements in this snapshot ID are included. |
|
|
The SQL ID of the statement to be tuned. |
|
|
The plan hash value of the statement to be tuned. For example, the tuning job fetches captured binds for this SQL plan. |
|
|
The date on which the schedule becomes valid. If null, then SQL Tuning Advisor immediately executes the task. |
|
|
The scope of the tuning job: limited, or comprehensive. |
|
|
The maximum duration in seconds for the SQL tuning session. |
|
|
Optional SQL tuning task name. |
|
|
Description of the SQL tuning session. The description can contain a maximum of 256 characters. |
|
|
The container from which SQL Tuning Advisor accesses the SQL statement information. |
|
|
DBID for imported or PDB-level AWR data. If |
Security Model
The caller must possess the CREATE JOB privilege for the job.
Return Values
A SQL tuning task name that is unique for each user. Multiple users can assign the same name to their advisor tasks.
Usage Notes
-
The task is scheduled only once.
-
The name of the scheduler job is created as follows:
sqltune_job_taskid_orahash(systimestamp).
168.5.34 SCRIPT_TUNING_TASK Function
This function creates a SQL*Plus script which can then be executed to implement a set of SQL Tuning Advisor recommendations.
See Also:
DBMS_SQLTUNE 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 NUMBER := NULL,
owner_name IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL)
RETURN CLOB;Parameters
Table 168-40 SCRIPT_TUNING_TASK Function Parameters
| Parameter | Description |
|---|---|
|
|
Name of the tuning task for which to apply a script. |
|
|
Filter the script by types of recommendations to include. You can use any subset of the following values, separated by commas: ' |
|
|
Optionally filters by a single object ID. |
|
|
Optionally shows commands for only top n SQL (ordered by |
|
|
Owner of the relevant tuning task. Defaults to the current schema owner. |
|
|
Name of the task execution to use. If |
database_link_to |
Name of a database link that exists on a standby database. The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local. Use The |
Return Values
Returns a script in the form of a CLOB.
Usage Notes
-
After the script is returned, check it before executing it.
-
Wrap with a call to
DBMS_ADVISOR.CREATE_FILEto 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 only the sql profiles we should create.
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'PROFILES') FROM DUAL;
-- Get a script of only stale / missing stats
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'STATISTICS') FROM DUAL;
-- Get a script with recommendations about only one SQL statement when we have
-- tuned an entire STS.
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:sts_task, 'ALL', 5) FROM DUAL;168.5.35 SELECT_CURSOR_CACHE Function
This function collects SQL statements from the shared SQL area.
See Also:
DBMS_SQLTUNE 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,
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;Parameters
Table 168-41 SELECT_CURSOR_CACHE Function Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the SQL predicate that filters the SQL from the shared SQL area defined on attributes of the If |
|
|
Currently not supported. |
|
|
Defines an |
|
|
Specifies a filter that picks the top n% according to the supplied ranking measure. The value applies only if one ranking measure is supplied. |
|
|
Defines the top limit SQL from the filtered source ranked by the ranking measure. |
|
|
Specifies the list of SQL statement attributes to return in the result. Possible values are:
|
|
|
Specifies that the filter must include recursive SQL in the SQL tuning set ( |
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 shared SQL area 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 is called after opening a cursor, along the
-- lines given below
-- Select all statements in the shared SQL area.
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 shared SQL area 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 shared SQL area. 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;
/168.5.36 SELECT_SQL_TRACE Function
This table function reads the content of one or more trace files and returns the SQL statements it finds in the format of sqlset_row.
See Also:
DBMS_SQLTUNE SQL Tuning Set Subprograms for other subprograms in this group
Syntax
DBMS_SQLTUNE.SELECT_SQL_TRACE (
directory IN VARCHAR2,
file_name IN VARCHAR2 := NULL,
mapping_table_name IN VARCHAR2 := NULL,
mapping_table_owner IN VARCHAR2 := NULL,,
select_mode IN POSITIVE := SINGLE_EXECUTION,
options IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
pattern_start IN VARCHAR2 := NULL,
pattern_end IN VARCHAR2 := NULL,
result_limit IN POSITIVE := NULL)
RETURN sys.sqlset PIPELINED;Parameters
Table 168-42 SELECT_SQL_TRACE Function Parameters
| Parameter | Description |
|---|---|
|
|
Defines the directory object containing the trace files. This field is mandatory. |
|
|
Specifies all or part of the name of the trace files. If |
|
|
Specifies the mapping table name. Note that the mapping table name is case insensitive. If the mapping table name is |
|
|
Specifies the mapping table owner. If it is |
|
|
Specifies the mode for selecting SQL from the trace. Possible values are:
|
|
|
Specifies which types of SQL statements are returned.
|
|
|
Specifies the delimiting pattern of the trace file sections to consider. CURRENTLY INOPERABLE. |
|
|
Specifies the closing delimiting pattern of the trace file sections to process. CURRENTLY INOPERABLE. |
|
|
Specifies the top SQL from the filtered source. Default to |
Return Values
This function returns a SQLSET_ROW object.
Usage Notes
The ability to create a directory object for the system directory creates a potential security issue. For example, in a CDB, all containers write trace files to the same directory. A local user with SELECT privileges on this directory can read the contents of trace files belonging to any container.
To prevent this type of unauthorized access, copy the files from the default SQL trace directory into a different directory, and then create a directory object. Use the PATH_PREFIX clause of the CREATE PLUGGABLE DATABASE statement to ensure that all directory object paths associated with the PDB are restricted to the specified directory or its subdirectories.
Examples
The following code shows how to enable SQL trace for a few SQL statements and load the results into a SQL tuning set:
-- turn on the SQL trace in the capture database
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'
-- run sql statements
SELECT 1 FROM DUAL;
SELECT COUNT(*) FROM dba_tables WHERE table_name = :mytab;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
-- create mapping table from the capture database
CREATE TABLE mapping AS
SELECT object_id id, owner, substr(object_name, 1, 30) name
FROM dba_objects
WHERE object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT',
'FUNCTION', 'INDEXTYPE', 'JAVA CLASS',
'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY',
'LOB', 'OPERATOR', 'PACKAGE',
'PACKAGE BODY', 'PROCEDURE', 'QUEUE',
'RESOURCE PLAN', 'TRIGGER', 'TYPE',
'TYPE BODY')
UNION ALL
SELECT user_id id, username owner, NULL name
FROM dba_users;
-- create the directory object where the SQL traces are stored
CREATE DIRECTORY SQL_TRACE_DIR as '/home/foo/trace';
-- create the STS
EXEC DBMS_SQLTUNE.CREATE_SQLSET('my_sts', 'test purpose');
-- load the SQL statements into STS from SQL TRACE
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT value(p)
FROM TABLE(
DBMS_SQLTUNE.SELECT_SQL_TRACE(
directory=>'SQL_TRACE_DIR',
file_name=>'%trc',
mapping_table_name=>'mapping')) p;
DBMS_SQLTUNE.LOAD_SQLSET('my_sts', cur);
CLOSE cur;
END;
/ See Also:
Oracle Database SQL Language Reference to learn more about the PATH_PREFIX clause
168.5.37 SELECT_SQLPA_TASK Function
This function collects SQL statements from a SQL Performance Analyzer comparison task.
See Also:
-
DBMS_SQLTUNE SQL Tuning Set Subprograms for other subprograms in this group
-
Oracle Database Testing Guide for a
SELECT_SQLPA_TASKexample
Syntax
DBMS_SQLTUNE.SELECT_SQLPA_TASK(
task_name IN VARCHAR2,
task_owner IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL,
level_filter IN VARCHAR2 := 'REGRESSED',
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
attribute_list IN VARCHAR2 := 'TYPICAL')
RETURN sys.sqlset PIPELINED;Parameters
Table 168-43 SELECT_SQLPA_TASK Function Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the SQL Performance Analyzer task. |
|
|
Specifies the owner of the SQL Performance Analyzer task. If |
|
|
Specifies the name of the SQL Performance Analyzer task execution (type |
|
|
Specifies which subset of SQL statements to include. Same format as
|
|
|
Specifies the SQL predicate to filter the SQL in addition to the level filters. |
|
|
Currently not supported. |
|
|
Defines the SQL statement attributes to return in the result. Possible values are:
|
Return Values
This function returns a SQL tuning set object.
Usage Notes
For example, you can use this function to create a SQL tuning set containing the subset of SQL statements that regressed during a SQL Performance Analyzer (SPA) experiment. You can also specify other arbitrary filters.
168.5.38 SELECT_SQLSET Function
This is a table function that reads the contents of a SQL tuning set.
See Also:
DBMS_SQLTUNE 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,
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;Parameters
Table 168-44 SELECT_SQLSET Function Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the SQL tuning set to query. |
|
|
Specifies the SQL predicate to filter the SQL from the SQL tuning set defined on attributes of the |
|
|
Currently not supported. |
|
|
Specifies an |
|
|
Specifies a filter that picks the top n% according to the supplied ranking measure. Note that this parameter applies only if one ranking measure is supplied. |
|
|
The top limit SQL from the filtered source, ranked by the ranking measure. |
|
|
Defines the SQL statement attributes to return in the result. The possible values are:
|
|
|
Specifies the plan filter. This parameter enables you to select a single plan when a statement has multiple plans. Possible values are:
|
|
|
Specifies the owner of the SQL tuning set, or |
|
|
Specifies that the filter must include recursive SQL in the SQL tuning set ( |
Return Values
This function returns 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;
/168.5.39 SELECT_WORKLOAD_REPOSITORY Function
This function collects SQL statements from the workload repository.
The overloaded forms enable you to collect SQL statements from the following sources:
-
Snapshots between
begin_snapandend_snap -
A workload repository baseline
Syntax
DBMS_SQLTUNE.SELECT_WORKLOAD_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,
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL,
dbid IN NUMBER := NULL)
RETURN sys.sqlset PIPELINED;
DBMS_SQLTUNE.SELECT_WORKLOAD 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,
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL,
dbid IN NUMBER := NULL)
RETURN sys.sqlset PIPELINED;Parameters
Table 168-45 SELECT_WORKLOAD_REPOSITORY Function Parameters
| Parameter | Description |
|---|---|
|
|
Defines the beginning AWR snapshot (non-inclusive). |
|
|
Defines the ending AWR snapshot (inclusive). |
|
|
Specifies the name of the AWR baseline period. |
|
|
Specifies the SQL predicate to filter the SQL from the workload repository. The filter is defined on attributes of the If |
|
|
Currently not supported. |
|
|
Defines an |
|
|
Specifies a filter that picks the top n% according to the supplied ranking measure. Note that this percentage applies only if one ranking measure is given. |
|
|
Specifies the top limit SQL from the source according to the supplied ranking measure. |
|
|
Specifies the SQL statement attributes to return in the result. The possible values are:
|
|
|
Specifies the filter that includes recursive SQL in the SQL tuning set ( |
|
|
Specifies the DBID for imported or PDB-level AWR data. If |
Return Values
This function returns 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;
/168.5.40 SET_TUNING_TASK_PARAMETER Procedures
This procedure updates the value of a SQL tuning parameter of type VARCHAR2 or NUMBER.
See Also:
DBMS_SQLTUNE 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,
database_link_to IN VARCHAR2);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value IN NUMBER,
database_link_to IN VARCHAR2);
);
Parameters
Table 168-46 SET_TUNING_TASK_PARAMETER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Identifier of the task to execute |
|
|
Name of the parameter to set. The possible tuning parameters that can be set by this procedure using the parameter in the form
|
|
|
New value of the specified parameter |
database_link_to |
Name of a database link that exists on a standby database. The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local. Use The |
Usage Notes
When setting automatic tuning task parameters, use the SET_AUTO_TUNING_TASK_PARAMETER Procedures in the DBMS_AUTO_SQLTUNE package.
168.5.41 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:
DBMS_SQLTUNE 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 168-47 SQLTEXT_TO_SIGNATURE Function Parameters
| Parameter | Description |
|---|---|
|
|
SQL text whose signature is required. Required. |
|
|
If |
Return Values
This function returns the signature of the specified SQL text.
168.5.42 UNPACK_STGTAB_SQLPROF Procedure
This procedure copies profile data stored in the staging table to create profiles on the system.
See Also:
DBMS_SQLTUNE 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 168-48 UNPACK_STGTAB_SQLPROF Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The name of the profile to unpack ( |
|
|
The category from which to unpack profiles ( |
|
|
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 |
|
|
The name of the table on which to perform the remap operation (case-insensitive unless double quoted). Required. |
|
|
The schema where the table resides, or |
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.
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => FALSE
, staging_table_name => 'PROFILE_STGTAB');
END;
-- If there is a failure during the unpack operation, you can find the profile
-- that caused the error and perform a remap_stgtab_sqlprof operation targeting it.
-- You can resume the unpack operation by setting replace to TRUE so that
-- the profiles that were already created are replaced.
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => TRUE
, staging_table_name => 'PROFILE_STGTAB');
END;168.5.43 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:
DBMS_SQLTUNE 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
The parameters are identical for DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET and DBMS_SQLSET.UNPACK_STGTAB.
Table 168-49 UNPACK_STGTAB_SQLSET and UNPACK_STGTAB Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the tuning set to unpack (not null). Wildcard characters ( |
|
|
Specifies the name of tuning set owner, or |
|
|
Specifies whether to replace an existing SQL tuning set. If |
|
|
Specifies the name of the staging table, moved after a call to the |
|
|
Specifies the name of staging table owner, or |
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');168.5.44 UPDATE_SQLSET Procedures
This overloaded procedure updates selected fields for SQL statements in a SQL tuning set.
See Also:
DBMS_SQLTUNE 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 168-50 UPDATE_SQLSET Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the name of the SQL tuning set. |
|
|
Specifies the identifier of the SQL statement to be updated. |
|
|
Specifies the hash value of the execution plan for a SQL statement. Use this parameter when you want to update the attribute for a specific plan for a statement, but not all plans for the statement. |
|
|
Specifies the name of the attribute to be modified. You can update the text field for If a statement has multiple plans, then the procedure changes the attribute value for all plans. |
|
|
Specifies the new value of the attribute. |