The DBMS_SQLTUNE package provides the interface to tune SQL statements.
The chapter contains the following topics:
Overview
Security Model
SQL Tuning Advisor Subprograms
SQL Profile Subprograms
SQL Tuning Set Subprograms
The DBMS_SQLTUNE
package provides three interrelated areas of functionality:
The SQL Tuning Advisor is one of a suite of Advisors, a set of expert systems that identifies and helps resolve database performance problems. Specifically, the SQL Tuning Advisor automates the tuning process of problematic SQL statements. That is, it takes one or more SQL statements as input and gives precise advice on how to tune the statements. The advice is provided is in the form of precise SQL actions for tuning the SQL along with their expected performance benefit.
The group of SQL Tuning Advisor Subprograms provide a task-oriented interface that lets you access the Advisor. You can call the following subprograms in the order given to use some of the SQL Tuning Advisor's features:
You use the CREATE_TUNING_TASK Functions to create a tuning task for tuning a single statement or a group of SQL statements.
The EXECUTE_TUNING_TASK 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
The SQL Tuning Advisor may recommend the creation of a SQL Profile to improve the performance of a statement. SQL Profiles consist of auxiliary statistics specific to the statement. The query optimizer makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount, resulting in poor execution plans. The SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to adjust these estimates.
The group of SQL Profile Subprograms provides a mechanism for delivering statistics to the optimizer that targets one particular SQL statement, and helps the optimizer make good decisions for that statement by giving it the most accurate statistical information possible. For example:
You can use the ACCEPT_SQL_PROFILE Procedure and Function to accept a SQL Profile recommended by the SQL Tuning Advisor.
You can alter the STATUS
, NAME
, DESCRIPTION
, and CATEGORY
attributes of an existing SQL Profile with the ALTER_SQL_PROFILE Procedure.
You can drop a SQL Profile with the DROP_SQL_PROFILE Procedure.
The SQL Tuning Advisor input can be a single SQL statement or a set of statements. When tuning multiple statements in one advisor task, you give the input in the form of a SQL Tuning Set (STS). A SQL Tuning Set is a database object that stores SQL statements along with their execution context in a system-provided schema. SQL Tuning Sets provide an infrastructure for dealing with SQL workloads and simplify tuning of a large number of SQL statements.
SQL Tuning Sets store SQL statements along with
The execution context, such as the parsing schema name and bind values
Execution statistics such as average elapsed time and execution count
Execution plans - which are the sequence of operations Oracle performs to run SQL statements
Row source statistics such as the number of rows processed for each operation executed within the plan
SQL Tuning Sets can be created by filtering or ranking SQL statements from several sources:
The cursor cache using the SELECT_CURSOR_CACHE Function
Top SQL statements from the Automatic Workload Repository using the SELECT_WORKLOAD_REPOSITORY Functions
Other SQL Tuning Sets using the SELECT_SQLSET Function
A user-defined workload
The complete group of SQL Tuning Set Subprograms facilitates this functionality. As examples:
You use the CREATE_SQLSET Procedure and Function to creates a SQL tuning set object in the database
The LOAD_SQLSET Procedure populates the SQL tuning set with a set of selected SQL
The CAPTURE_CURSOR_CACHE_SQLSET Procedure collects SQL statements from the cursor cache over a specified time interval, attempting to build a realistic picture of system workload.
Import/Export SQL Tuning Sets and SQL Profiles
You use DBMS_SQLTUNE
subprograms to move SQL Profiles and SQL Tuning Sets from one system to another using a common programmatic model. In both cases, you create a staging table on the source system and populate that staging table with the relevant data. You then move that staging table to the destination system following the method of your choice (such as datapump, import/export, or database link), where it is used to reconstitute the objects in their original form. These steps are implemented by means of subprograms included in this package:
Call the CREATE_STGTAB_SQLPROF Procedure or the CREATE_STGTAB_SQLSET Procedure to create the staging table on the source system.
Call the PACK_STGTAB_SQLPROF Procedure or PACK_STGTAB_SQLSET Procedure to populate the staging table with information from the source system.
Once you have moved the staging table to the destination system, you call the UNPACK_STGTAB_SQLPROF Procedure or the UNPACK_STGTAB_SQLSET Procedure to recreate the object on the new system.
See Also:
Oracle Database Performance Tuning Guide for more information about programmatic flow.This package is available to PUBLIC
and performs its own security checking:
As SQL
tuning advisor relies on the advisor framework, so all tuning task interfaces (XXX_TUNING_TASK
) require privilege ADVISOR
.
SQL Tuning Set subprograms (XXX_SQLSET
) require either the ADMINISTER
SQL
TUNING
SET
or the ADMINISTER
ANY
SQL
TUNING
SET
privilege. Users having the ADMINISTER
SQL
TUNING
SET
privilege can only create and modify a SQL tuning set they own, while the ADMINISTER
ANY
SQL
TUNING
SET
privilege allows them to operate upon all SQL tuning sets, even those owned by other users. For example, using the CREATE_SQLSET Procedure and Function you can create a SQL tuning set to be owned by another user. In this case, the user need not necessarily have the ADMINISTER
SQL
TUNING
SET
privilege to operate upon her tuning set.
Three different privileges are needed to invoke subprograms concerned with SQL Profiles:
CREATE
ANY
SQL
PROFILE
is needed to call the ACCEPT_SQL_PROFILE Procedure and Function
ALTER
ANY
SQL
PROFILE
is needed to call the ALTER_SQL_PROFILE Procedure
DROP
ANY
SQL
PROFILE
is needed to call the DROP_SQL_PROFILE Procedure
The DBMS_SQLTUNE
package defines the following 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
s where each SQLSET_ROW
contains a single SQL statement along with its execution context, statistics, binds and plan. The SELECT_XXX
subprograms each model a data source as a collection of SQLSET_ROWs
, unique by (sql_id
, plan_hash_value
). Similarly, the LOAD_SQLSET
procedure takes as input a cursor whose row type is SQLSET_ROW
, treating each SQLSET_ROW
in isolation according to the policies requested by the user.
Several subprograms in the DBMS_SQLTUNE
package accept basic filters on the content of a SQL tuning set or data source. These filters are expressed in terms of the attributes within the SQLSET_ROW
as defined.
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(1000), 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)
Table 101-1 SQLSET_ROW Attributes
Attribute | Description |
---|---|
|
Unique SQL ID |
|
Signature with literals, case, and whitespace removed |
|
Full text for the 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 writes |
|
Sum total number of rows processed by this SQL |
|
Sum total number of fetches |
|
Total executions of this SQL |
|
Number of times the 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 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 |
|
Explain plan |
|
List of user specified binds for SQL This is used for user-specified workloads. Note that you cannot stipulate an argument for this parameter and also for |
DBMS_SQLTUNE subprograms are grouped by function:
This subprogram group provides an interface to manage SQL tuning tasks.
Table 101-2 SQL Tuning Task Subprograms
Subprogram | Description |
---|---|
Cancels the currently executing tuning task |
|
Creates a tuning of a single statement or SQL tuning set |
|
Drops a SQL tuning task |
|
Executes a previously created tuning task |
|
Interrupts the currently executing tuning task |
|
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 tune a SQL tuning set. |
|
Creates a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations |
The Summary of DBMS_SQLTUNE Subprograms contains a complete listing of all subprograms in the package.
This subprogram group provides an interface to manage SQL Profiles.
Table 101-3 SQL Profile Subprograms
Subprogram | Description |
---|---|
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 |
The Summary of DBMS_SQLTUNE Subprograms contains a complete listing of all subprograms in the package.
This subprogram group provides an interface to manage SQL tuning sets.
Table 101-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 cursor cache 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 |
|
Deactivates a SQL tuning set to indicate it is no longer used by the client |
|
Collects SQL statements from the cursor cache |
|
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.
Table 101-5 DBMS_SQLTUNE Package Subprograms
Subprogram | Description | Group |
---|---|---|
Create 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 cursor cache into a SQL tuning set |
||
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 |
||
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 |
||
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 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 tune a SQL tuning set. |
||
Creates a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations |
||
Collects SQL statements from the cursor cache |
||
Collects SQL statements from an existing SQL tuning set |
||
Collects SQL statements from the workload repository |
||
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 |
This procedure creates a SQL Profile recommended by the SQL Tuning Advisor. The SQL text is normalized for matching purposes though it is stored in the data dictionary in de-normalized form for readability. SQL text is provided through a reference to the SQL Tuning task. If the referenced SQL statement doesn't exist, an error is reported.
See Also:
SQL Profile Subprograms for other subprograms in this groupDBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name IN VARCHAR2, object_id IN NUMBER := NULL, name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL); task_owner IN VARCHAR2 := NULL, replace IN BOOLEAN := FALSE, force_match IN BOOLEAN := FALSE);
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name IN VARCHAR2, object_id IN NUMBER := NULL, name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL; task_owner IN VARCHAR2 := NULL, replace IN BOOLEAN := FALSE, force_match IN BOOLEAN := FALSE) RETURN VARCHAR2;
Table 101-6 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 will generate 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. |
|
This is the category name which 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 will be replaced if this argument is |
|
If If |
The name of the SQL profile.
The CREATE
ANY
SQL PROFILE
privilege is required.
You use both the procedure and the function versions of the subprogram in the same way except you must specify a return value to invoke the function. Here we give examples of the procedure only.
In this example, you tune a single SQL statement form the workload repository and you create the SQL profile recommended by SQL tuning advisor.
variable stmt_task VARCHAR2(64); variable sts_task VARCHAR2(64); -- create a tuning task tune the statement EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( begin_snap => 1, - end_snap => 2, - sql_id => 'ay1m3ssvtrh24'); -- execute the resulting task EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task); EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(:stmt_task);
Note that you do not have to specify the ID (that is, object_id
) for the advisor framework object created by SQL tuning advisor to represent the tuned SQL statement.
You might also want to accept the recommended SQL profile in a different category, (for example, TEST
), so that it will not be used by default.
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => :stmt_task, - category => 'TEST');
You can use command ALTER
SESSION
SET
SQLTUNE_CATEGORY
= 'TEST'
to see how this profile behaves.
The following call creates a SQL profile that targets any SQL statement with the same force_matching_signature
as the tuned statement.
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name => :stmt_task, - force_match => TRUE);
In the following example, you tune a SQL tuning set, and you create a SQL profile for only one of the SQL statements in the SQL tuning set. The SQL statement is represented by an advisor framework object with ID equal to '5'. Please notice that you must pass an object id to the ACCEPT_SQL_PROFILE
procedure because there are potentially many SQL profiles for the tuning task. This object id is given along with the report.
EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK ( - sqlset_name => 'my_workload', - rank1 => 'ELAPSED_TIME', - time_limit => 3600, - description => 'my workload ordered by elapsed time'); -- execute the resulting task EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:sts_task); -- create the profile for the sql statement corresponding to object_id = 5. EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => :sts_task, - object_id => 5);
This procedure adds a new reference to an existing SQL tuning set to indicate its use by a client.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_SQLTUNE.ADD_SQLSET_REFERENCE ( sqlset_name IN VARCHAR2, description IN VARCHAR2 := NULL) RETURN NUMBER;
Table 101-7 ADD_SQLSET_REFERENCE Function Parameters
Parameter | Description |
---|---|
|
The SQL tuning set name |
|
The description of the usage of SQL tuning set. The description is truncated if longer than 256 characters. |
The identifier of the added reference.
You can add reference to a SQL tuning set. This prevents the tuning set from being modified while it is being used. References are automatically added when you invoke SQL tuning advisor on the SQL tuning set, so you should use this function for custom purposes only.The function returns a reference ID that is used to remove it later. You use the REMOVE_SQLSET_REFERENCE Procedure
to delete references to a SQL tuning set.
variable rid number; EXEC :rid := DBMS_SQLTUNE.ADD_SQLSET_REFERENCE( - sqlset_name => 'my_workload', - description => 'my sts reference');
You can use the views USER/DBA_SQLSET_REFERENCES
to find all references on a given SQL tuning set.
This procedure alters specific attributes of an existing SQL Profile object. The following attributes can be altered (using these attribute names):
"STATUS
" can be set to "ENABLED
" or "DISABLED
"
"NAME
" can be reset to a valid name which must be a valid Oracle identifier and must be unique.
"DESCRIPTION
" can be set to any string of size no more than 500 characters
"CATEGORY
" can be reset to a valid category name which must be a valid Oracle identifier and must be unique when combined with normalized SQL text)
See Also:
SQL Profile Subprograms for other subprograms in this groupDBMS_SQLTUNE.ALTER_SQL_PROFILE ( name IN VARCHAR2, attribute_name IN VARCHAR2, value IN VARCHAR2);
Table 101-8 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 |
Requires the "ALTER
ANY
SQL PROFILE
" privilege.
-- Disable a profile, so it will be not be used by any sessions. EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => :pname, - attribute_name => 'STATUS', - value => 'DISABLED'); -- Enable it back: EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name => :pname, - attribute_name => 'STATUS', - value => 'ENABLED'); -- Change the category of the profile so it will be used only by sessions -- with category set to TEST. -- Use ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST' to see how this profile -- behaves. EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name => :pname, - attribute_name => 'CATEGORY', - value => 'TEST'); -- Change it back: EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name => :pname, - attribute_name => 'CATEGORY', - value => 'DEFAULT');
This procedure cancels the currently executing tuning task. All intermediate result data is deleted.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupDBMS_SQLTUNE.CANCEL_TUNING_TASK( task_name IN VARCHAR2);
Table 101-9 CANCEL_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
|
The name of the task to cancel |
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);
Over a specified time interval this procedure incrementally captures a workload from the cursor cache into a SQL tuning set. The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_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);
Table 101-10 CAPTURE_CURSOR_CACHE_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The SQL tuning set name |
|
The total amount of time, in seconds, to execute |
|
The amount of time, in seconds, to pause between sampling |
|
During capture, either insert new statements, update existing ones, or both. ' |
|
capture mode (
|
|
Filter to apply to cursor cache on each sampling (see |
|
The owner of the SQL tuning set or |
In this example capture takes place over a 30-second period, polling the cache once every five seconds. This will capture all statements run during that period but not before or after. If the same statement appears a second time, the process replaces the stored statement with the new occurence.
Note that in production systems the time limit and repeat interval would be set much higher. You should tune the time_limit
and repeat_interval
parameters based on the workload time and cursor cache turnover properties of your system.
EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( - sqlset_name => 'my_workload', - time_limit => 30, - repeat_interval => 5);
In the following call you accumulate execution statistics as you go. This option produces an accurate picture of the cumulative activity of each cursor, even across age-outs, but it is more expensive than the previous example.
EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( - sqlset_name => 'my_workload', - time_limit => 30, - repeat_interval => 5, - capture_mode => dbms_sqltune.MODE_ACCUMULATE_STATS);
This call performs a very inexpensive capture where you only insert new statements and do not update their statistics once they have been inserted into the SQL tuning set
EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( - sqlset_name => 'my_workload', - time_limit => 30, - repeat_interval => 5, - capture_option => 'INSERT');
The procedure creates a SQL tuning set object in the database.
The function causes the system t o generate a name for the SQL Tuning Set.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_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;
Table 101-11 CREATE_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The SQL tuning set name |
|
The description of the SQL tuning set |
|
The owner of the SQL tuning set, or |
EXEC DBMS_SQLTUNE.CREATE_SQLSET(- sqlset_name => 'my_workload', - description => 'complete application workload');
This procedure creates the staging table used for copying SQL profiles from one system to another.
See Also:
SQL Profile Subprograms for other subprograms in this groupDBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( table_name IN VARCHAR2, schema_name IN VARCHAR2 := NULL, tablespace_name IN VARCHAR2 := NULL);
Table 101-12 CREATE_STGTAB_SQLPROF Procedure Parameters
Parameter | Description |
---|---|
|
The name of the table to create (case-sensitive). Required. |
|
The schema to create the table in, or |
|
The tablespace to store the staging table within, or |
Call this procedure once before issuing a call to the PACK_STGTAB_SQLPROF Procedure.
This procedure can be called multiple times if you would like to have different SQL profiles in different staging tables.
Note that this is a DDL operation, so it does not occur within a transaction.
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');
This procedure creates a staging table through which SQL Tuning Sets are imported and exported
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_SQLTUNE.CREATE_STGTAB_SQLSET ( table_name IN VARCHAR2, schema_name IN VARCHAR2 := NULL, tablespace_name IN VARCHAR2 := NULL);
Table 101-13 CREATE_STGTAB_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The name of the table to create (case-sensitive) |
|
The schema in which to create the table in, or |
|
The tablespace in which to store the staging table, or |
Call this procedure once before issuing a call to the PACK_STGTAB_SQLSET Procedure.
This procedure can be called multiple times if you would like to have different tuning sets in different staging tables.
Note that this is a DDL operation, so it does not occur within a transaction.
Users issuing the call must have permission to CREATE
TABLE
in the schema provided and the relevant tablespace.
Please note that the staging table contains nested table columns and indexes, so it should not be renamed.
Create a staging table for packing and eventually exporting a SQL tuning sets
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name => 'STGTAB_SQLSET');
You can use different forms of this function to:
Create a tuning task for a single statement given its text.
Create a tuning task for a single statement from the Cursor Cache given its identifier.
Create a tuning task for a single statement from the workload repository given a range of snapshot identifiers.
Create a tuning task for a SQL tuning set.
In all cases, the function mainly creates an advisor task and sets its parameters.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupDBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text IN CLOB, bind_list IN sql_binds := NULL, user_name IN VARCHAR2 := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2; DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2; DBMS_SQLTUNE.CREATE_TUNING_TASK( begin_snap IN NUMBER, end_snap IN NUMBER, sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2; DBMS_SQLTUNE.CREATE_TUNING_TASK( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, rank1 IN VARCHAR2 := NULL, rank2 IN VARCHAR2 := NULL, rank3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := NULL, result_limit IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME', sqlset_owner IN VARCHAR2 := NULL) RETURN VARCHAR2;
Table 101-14 CREATE_TUNING_TASK Function Parameters
Parameter | Description |
---|---|
|
The text of a SQL statement |
|
Begin snapshot identifier |
|
End snapshot identifier |
|
The identifier of a SQL statement |
|
An ordered list of bind values in ANYDATA type |
|
The hash value of the SQL execution plan |
|
The SQL tuning set name |
|
The SQL predicate to filter the SQL from the SQL tuning set |
|
The object filter |
|
An order-by clause on the selected SQL |
|
A percentage on the sum of a ranking measure |
|
The top L(imit) SQL from the (filtered/ranked) SQL |
|
The username for whom the statement is to be tuned |
|
Tuning scope (limited/comprehensive) |
|
The maximum duration in seconds for the tuning session |
|
An optional tuning task name |
|
A task of the SQL tuning session to a maximum of 256 characters |
|
Plan filter. It is applicable in case there are multiple plans (
|
|
The owner of the SQL tuning set, or |
A SQL tuning task name.
variable stmt_task VARCHAR2(64); variable sts_task VARCHAR2(64); -- Sql text format EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( - sql_text => 'select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-00'')'); -- Sql id format (cursor cache) EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24'); -- tune in limited scope EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', - scope => 'LIMITED'); -- only give 10 minutes for tuning statement EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', - time_limit => 600); -- Workload repository format exec :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 1, - end_snap => 2, sql_id => 'ay1m3ssvtrh24'); -- Sql tuning set format (first we need to load an STS, then tune it) -- Tune our statements in order by buffer gets, time limit of one hour -- the default ranking measure is elapsed time. EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( - sqlset_name => 'my_workload', - rank1 => 'BUFFER_GETS', - time_limit => 3600, - description => 'tune my workload ordered by buffer gets');
This procedure deletes a set of SQL statements from a SQL tuning set.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_SQLTUNE.DELETE_SQLSET ( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, sqlset_owner IN VARCHAR2 := NULL);
Table 101-15 DELETE_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The SQL tuning set name |
|
SQL predicate to filter the SQL from the SQL tuning set. This basic filter is used as a where clause on the SQL tuning set content to select a desired subset of SQL from the Tuning Set. |
|
The owner of the SQL tuning set, or |
-- 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');
This procedure drops the named SQL Profile from the database.
See Also:
SQL Profile Subprograms for other subprograms in this groupDBMS_SQLTUNE.DROP_SQL_PROFILE ( name IN VARCHAR2, ignore IN BOOLEAN := FALSE);
Table 101-16 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 |
Requires the "DROP
ANY
SQL PROFILE
" privilege.
-- Drop the profile: EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(:pname);
This procedure drops a SQL tuning set if it is not active.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_SQLTUNE.DROP_SQLSET ( sqlset_name IN VARCHAR2, sqlset_owner IN VARCHAR2 := NULL);
Table 101-17 DROP_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The SQL tuning set name |
|
The owner of the SQL tuning set, or |
You cannot drop a SQL tuning set when it is referenced by one or more clients (for example, SQL tuning advisor).
-- Drop the sqlset. EXEC DBMS_SQLTUNE.DROP_SQLSET ('my_workload');
This procedure drops a SQL tuning task.The task and all its result data are deleted.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupDBMS_SQLTUNE.DROP_TUNING_TASK( task_name IN VARCHAR2);
Table 101-18 DROP_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
|
The name of the tuning task to drop |
This procedures executes a previously created tuning task.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupDBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name IN VARCHAR2);
Table 101-19 EXECUTE_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
|
The name of the tuning task to execute |
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);
This procedure interrupts the currently executing tuning task. The task will end its operations as it would at normal exit so that the user will be able access the intermediate results.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupDBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name IN VARCHAR2);
Table 101-20 INTERRUPT_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
|
The name of the tuning task to interrupt |
EXEC DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(:my_task);
This procedure populates the SQL tuning set with a set of selected SQL. You can call the procedure multiple times to add new SQL statements or replace attributes of existing statements.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_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);
Table 101-21 LOAD_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The SQL tuning set name to populate |
|
The cursor reference from which to populate |
|
Specifies how the statements will be loaded into the SQL tuning set. The possible values are:
|
|
Specifies how the existing statements will be updated. This parameter is considered only if load_option is specified with '
|
|
Specifies a where clause to execute the update operation. The update is performed only if the specified condition is true. The condition can refer to either the data source or destination. The condition must use the following prefixes to refer to attributes from the source or the destination:
|
|
Specifies the list of a SQL statement attributes to update during a merge or update operation.The possible values are:
|
|
If |
|
If a value is provided, the load will commit after each set of that many statements is inserted. If |
|
The owner of the SQL tuning set, or the current schema owner or |
This procedure returns an error when sqlset_name
is invalid, or a corresponding SQL tuning set does not exist, or the populate_cursor
is incorrect and cannot be executed.
Exceptions are also raised when invalid filters are provided. Filters can be invalid either because they don't parse (for example, they refer to attributes not in sqlset_row), or because they violate the user's privileges.
Rows in the input populate_cursor
must be of type SQLSET_ROW
.
In this example, you create and populate a SQL tuning set with all cursor cache statements with an elapsed time of 5 seconds or more excluding statements that belong to SYS
schema (to simulate an application user workload). You select all attributes of the SQL statements and load them in the tuning set using the default mode, which will only load new statements, since the SQL tuning set is empty.
-- create the tuning set EXEC DBMS_SQLTUNE.CREATE_SQLSET('my_workload'); -- populate the tuning set from the cursor cache DECLARE cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'parsing_schema_name <> ''SYS'' AND elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload', populate_cursor => cur); END; /
Suppose now you wish to augment this information with what is stored in the workload repository (AWR). You populate the tuning set with 'ACCUMULATE'
as your update_option
because it is assumed the cursors currently in the cache had aged out since the snapshot was taken.
You omit the elapsed_time
filter because it is assumed that any statement captured in AWR is important, but still you throw away the SYS
-parsed cursors to avoid recursive SQL.
DECLARE cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,2, 'parsing_schema_name <> ''SYS''', NULL, NULL,NULL,NULL, 1, NULL, 'ALL')) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload', populate_cursor => cur, Using DBMS_SQLTUNE load_option => 'MERGE', update_option => 'ACCUMULATE'); END;
The following example is a simple load that only inserts new statements from the workload repository, skipping existing ones (in the SQL tuning set). Note that 'INSERT'
is the default value for the load_option
argument of the LOAD_SQLSET
procedure.
DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,2)) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload', populate_cursor => cur); END; /
The next example demonstrates a load with UPDATE
option. This updates statements that already exist in the SQL tuning set but does not add new ones. By default, old statistics are replaced by their new values.
DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload', populate_cursor => cur, load_option => 'UPDATE'); END; /
This procedure copies profile data from the SYS
. schema into the staging table.
See Also:
SQL Profile Subprograms for other subprograms in this groupDBMS_SQLTUNE.PACK_STGTAB_SQLPROF ( profile_name IN VARCHAR2 := '%', profile_category IN VARCHAR2 := 'DEFAULT', staging_table_name IN VARCHAR2, staging_schema_owner IN VARCHAR2 := NULL);
Table 101-22 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-sensitive). Required. |
|
The schema where the table resides, or |
This procedures requires SELECT
privilege on dba_sql_profiles
and INSERT
privilege on staging table.
Note that this function issues a COMMIT
after packing each SQL profile, so if an error is raised mid-execution, clear the staging table by deleting its rows.
Put only those profiles in the DEFAULT
category into the staging table. This corresponds to all profiles that will be used by default on this system.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROFILE_STGTAB');
This is another example where you put all profiles into the staging table. Note this will even move profiles that are not currently being used by default but are in other categories, such as for testing purposes.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_category => '%', - staging_table_name => 'PROFILE_STGTAB');
This procedure copies one or more SQL tuning sets from their location in the SYS
schema to a staging table created by the CREATE_STGTAB_SQLSET Procedure.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_SQLTUNE.PACK_STGTAB_SQLSET ( sqlset_name IN VARCHAR2, sqlset_owner IN VARCHAR2 := NULL, staging_table_name IN VARCHAR2, staging_schema_owner IN VARCHAR2 := NULL);
Table 101-23 PACK_STGTAB_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The name of the SQL Tuning Set to pack (% wildcards acceptable, case-sensitive) |
|
The category from which to pack SQL Tuning Sets (% wildcards acceptable, case-sensitive) |
|
The name of the table to use (case-sensitive) |
|
The schema where the table resides, or |
This procedure can be called several times to move more than one SQL tuning set. Users can then move the populated staging table to another system using any method, such as database link or datapump. Users can then call the UNPACK_STGTAB_SQLSET Procedure create the SQL tuning set on the other system.
Note that this function issues a COMMIT
after packing each SQL tuning set, so if an error is raised mid-execution, clear the staging table by deleting its rows.
Put all SQL tuning sets on the system in the staging table (to create a staging table, see the CREATE_STGTAB_SQLSET Procedure
).
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => '%', - sqlset_owner => '%', - staging_table_name => 'STGTAB_SQLSET');
Put only those SQL tuning sets owned by the current user in the staging table.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => '%', - staging_table_name => 'STGTAB_SQLSET');
Pack a specific SQL tuning set.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'my_workload', - staging_table_name => 'STGTAB_SQLSET');
Pack a second SQL tuning set.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'workload_subset', - staging_table_name => 'STGTAB_SQLSET');
This procedure allows DBAs to change the profile data values kept in the staging table prior to performing an unpack operation. The procedure can be used to change the category of a profile.It can be used to change the name of a profile if one already exists on the system with the same name.
See Also:
SQL Profile Subprograms for other subprograms in this groupDBMS_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);
Table 101-24 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 |
Using this procedure requires the UPDATE
privilege on the staging table.
Change the name of a profile before we unpack, to avoid conflicts
EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF(old_profile_name => :pname, - new_profile_name => 'IMP' || :pname, - staging_table_name => 'PROFILE_STGTAB');
Change the SQL profile in the staging table to be 'TEST'
category before we import it. This way users can test the profile on the new system before it is active.
EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF(old_profile_name => :pname, - new_profile_category => 'TEST', - staging_table_name => 'PROFILE_STGTAB');
This procedure changes the tuning set names and owners in the staging table so that they can be unpacked with different values than they had on the host system.
See Also:
SQL Profile Subprograms for other subprograms in this groupDBMS_SQLTUNE.REMAP_STGTAB_SQLSET ( old_sqlset_name IN VARCHAR2, old_sqlset_owner IN VARCHAR2 := NULL, new_sqlset_name IN VARCHAR2 := NULL, new_sqlset_owner IN VARCHAR2 := NULL, staging_table_name IN VARCHAR2, taging_schema_owner IN VARCHAR2 := NULL);
Table 101-25 REMAP_STGTAB_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The name of the tuning set to target for a remap operation. Wildcards are not supported. |
|
The new name of the tuning set owner to target for a remap operation. |
|
The new name for the tuning set, or |
|
The new owner for the tuning set, or |
|
The name of the table on which to perform the remap operation (case-sensitive) |
|
The name of staging table owner, or |
You can call this procedure multiple times to remap more than one tuning set name or owner. Note that this procedure only handles one tuning set per call.
-- Change the name of an STS in the staging table before we unpack it. EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLSET(old_sqlset_name => 'my_workload', - old_sqlset_owner => 'SH', - new_sqlset_name => 'imp_workload', - staging_table_name => 'STGTAB_SQLSET'); -- Change the owner of an STS in the staging table before we unpack it. EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLSET(old_sqlset_name => 'imp_workload', - old_sqlset_owner => 'SH', - new_sqlset_owner => 'SYS', - staging_table_name => 'STGTAB_SQLSET');
This procedure deactivates a SQL tuning set to indicate it is no longer used by the client.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE ( sqlset_name IN VARCHAR2, reference_id IN NUMBER);
Table 101-26 REMOVE_SQLSET_REFERENCE Procedure Parameters
Parameter | Description |
---|---|
|
The SQL tuning set name |
|
The identifier of the reference to remove |
You can remove references on a given SQL tuning set when you finish using it and want to make it writable again.
EXEC DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE( - sqlset_name => 'my_workload', - reference_id => :rid);
Use views USER/DBA_SQLSET_REFERENCES
to find all references on a given SQL tuning set.
This procedure displays the results of a tuning task.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupDBMS_SQLTUNE.REPORT_TUNING_TASK( task_name IN VARCHAR2, type IN VARCHAR2 := TYPE_TEXT, level IN VARCHAR2 := LEVEL_TYPICAL, section IN VARCHAR2 := SECTION_ALL, object_id IN NUMBER := NULL, result_limit IN NUMBER := NULL) RETURN CLOB;
Table 101-27 REPORT_TUNING_TASK Function Parameters
Parameter | Description |
---|---|
|
The name of the tuning task to report |
|
The type of the report to produce. Possible values are |
|
The format of the recommendations. Possible values are |
|
The particular section in the report. Possible values are |
|
The identifier of the advisor framework object that represents a given statement in the SQL tuning set |
|
The number of statements in a SQL tuning set for which a report is generated |
A CLOB
containing the desired report. This means that you have to set the any SQL*Plus 'LONG
' and 'LONGCHUNKSIZE
' variables so that the report will print in entirety.
-- Get the whole report for the single statement case. SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:stmt_task) from dual; -- Show me the summary for the sts case. SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY') FROM DUAL; -- Show me the findings for the statement I'm interested in. SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;
This procedure is called on a tuning task that is not currently executing to prepare it for re-execution.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupDBMS_SQLTUNE.RESET_TUNING_TASK( task_name IN VARCHAR2);
Table 101-28 RESET_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
|
The name of the tuning task to reset |
-- 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);
This procedure resumes a previously interrupted task that was created to tune a SQL tuning set.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupDBMS_SQLTUNE.RESUME_TUNING_TASK( task_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL);
Table 101-29 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 will be applied in conjunction with the basic filter (i.e., parameter |
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.
-- 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);
This function creates a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupDBMS_SQLTUNE.SCRIPT_TUNING_TASK( task_name IN VARCHAR2, rec_type IN VARCHAR2, object_id IN NUMBER, result_limit IN NUMNBER, owner_name IN VARCHAR2) RETURN CLOB;
Table 101-30 SCRIPT_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
|
The name of the tuning task for which to apply a script |
|
Filter the script by types of recommendations to include. Any subset of the following separated by commas: or ' |
|
Optionally filters by a single object ID |
|
Optionally shows commands for only top N SQL (ordered by object_id and ignored if an object_id is also specified) |
|
Owner of the relevant tuning task. Defaults to the current schema owner |
This function returns a CLOB
containing the PL/SQL calls to be executed to implement the subset of recommendations dictated by the arguments.
Once the script is returned, it should then by checked by the DBA and executed.
Wrap with a call to DBMS_ADVISOR.CREATE_FILE
to put it into a file.
SET LINESIZE 140 -- Get a script for all actions recommended by the task. SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task) FROM DUAL; -- Get a script of just the sql profiles we should create. SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'PROFILES') FROM DUAL; -- get a script of just stale / missing stats SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'STATISTICS') FROM DUAL; -- Get a script with recommendations about just one SQL statement when we have -- tuned an entire STS. SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:sts_task, 'ALL', 5) FROM DUAL;
This function collects SQL statements from the SQL Cursor Cache.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_SQLTUNE.SELECT_CURSOR_CACHE ( basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, ranking_measure1 IN VARCHAR2 := NULL, ranking_measure2 IN VARCHAR2 := NULL, ranking_measure3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := 1, result_limit IN NUMBER := NULL, attribute_list IN VARCHAR2 := NULL) RETURN sys.sqlset PIPELINED;
Table 101-31 SELECT_CURSOR_CACHE Procedure Parameters
Parameter | Description |
---|---|
|
The SQL tuning set name |
|
The SQL predicate to filter the SQL from the cursor cache defined on attributes of the |
|
Specifies the objects that should exist in the object list of selected SQL from the cursor cache |
|
An order-by clause on the selected SQL |
|
A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given. |
|
The top L(imit) SQL from the (filtered) source ranked by the ranking measure |
|
List of SQL statement attributes to return in the result. The possible values are:
|
This function returns a one SQLSET_ROW
per SQL_ID
or PLAN_HASH_VALUE
pair found in each data source.
Users need privileges on the cursor cache views.
-- Get sql ids and sql text for statements with 500 buffer gets. SELECT sql_id, sql_text FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500')) ORDER BY sql_id; -- Get all the information we have about a particular statement. SELECT * FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j''')); -- Notice that some statements can have multiple plans. The output of the -- SELECT_XXX table functions is unique by (sql_id, plan_hash_value). This is -- because a data source can store multiple plans per sql statement. SELECT sql_id, plan_hash_value FROM table(dbms_sqltune.select_cursor_cache('sql_id = ''ay1m3ssvtrh24''')) ORDER BY sql_id, plan_hash_value; -- PL/SQL examples: load_sqlset will be called after opening a cursor, along the -- lines given below -- Select all statements in the cursor cache. DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT value(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur; END;/ -- Look for statements not parsed by SYS. DECLARE cur sys_refcursor; BEGIN OPEN cur for SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''')) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur; end;/ -- All statements from a particular module/action. DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; END;/ -- all statements that ran for at least five seconds DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; end;/ -- select all statements that pass a simple buffer_gets threshold and -- are coming from an APPS user DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'buffer_gets > 100 and parsing_schema_name = ''APPS'''))P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; end;/ -- select all statements exceeding 5 seconds in elapsed time, but also -- select the plans (by default we only select execution stats and binds -- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row -- is NULL) DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(dbms_sqltune.select_cursor_cache( 'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL, 'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN')) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; END;/ -- Select the top 100 statements in the cursor cache ordering by elapsed_time. DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL, NULL, 'ELAPSED_TIME', NULL, NULL, 1, 100)) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; end;/ -- Select the set of statements which cumulatively account for 90% of the -- buffer gets in the cursor cache. This means that the buffer gets of all -- of these statements added up is approximately 90% of the sum of all -- statements currently in the cache. DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL, NULL, 'BUFFER_GETS', NULL, NULL, .9)) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur; END; /
This function reads SQLSET contents.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_SQLTUNE.SELECT_SQLSET ( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, ranking_measure1 IN VARCHAR2 := NULL, ranking_measure2 IN VARCHAR2 := NULL, ranking_measure3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := 1, result_limit IN NUMBER := NULL) attribute_list IN VARCHAR2 := NULL, plan_filter IN VARCHAR2 := NULL, sqlset_owner IN VARCHAR2 := NULL) RETURN sys.sqlset PIPELINED;
Table 101-32 SELECT_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The SQL tuning set name |
|
The SQL predicate to filter the SQL from the SQL Tuning Set defined on attributes of the |
|
Specifies the objects that should exist in the object list of selected SQL from the cursor cache |
|
An order-by clause on the selected SQL |
|
A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given. |
|
The top L(imit) SQL from the (filtered) source ranked by the ranking measure |
|
List of SQL statement attributes to return in the result. The possible values are:
|
|
The plan filter |
|
The owner of the SQL tuning set, or |
This function returns a one SQLSET_ROW
per SQL_ID
or PLAN_HASH_VALUE
pair found in each data source.
-- 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; /
This function collects SQL statements from the workload repository. The overloaded forms let you:
Collect SQL statements from all snapshots between begin_snap
and end_snap
.
Collect SQL statements from a workload repository baseline.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_SQLTUNE.SELECT_WORKLAOD_REPOSITORY ( begin_snap IN NUMBER, end_snap IN NUMBER, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, ranking_measure1 IN VARCHAR2 := NULL, ranking_measure2 IN VARCHAR2 := NULL, ranking_measure3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := 1, result_limit IN NUMBER := NULL attribute_list IN VARCHAR2 := NULL) RETURN sys.sqlset PIPELINED; DBMS_SQLTUNE.SELECT_WORKLAOD REPOSITORY ( baseline_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, ranking_measure1 IN VARCHAR2 := NULL, ranking_measure2 IN VARCHAR2 := NULL, ranking_measure3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := 1, result_limit IN NUMBER := NULL) attribute_list IN VARCHAR2 := NULL) RETURN sys.sqlset PIPELINED;
Table 101-33 SELECT_WORKLOAD_REPOSITORY Function Parameters
Parameter | Description |
---|---|
|
Begin snapshot |
|
End snapshot |
|
The name of the baseline period |
|
The SQL predicate to filter the SQL from the workload repository defined on attributes of the |
|
Specifies the objects that should exist in the object list of selected SQL from the SWRF |
|
An order-by clause on the selected SQL |
|
A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given. |
|
The top L(imit) SQL from the (filtered) source ranked by the ranking measure |
|
List of SQL statement attributes to return in the result. The possible values are:
|
This function returns a one SQLSET_ROW
per SQL_ID
or PLAN_HASH_VALUE
pair found in each data source.
-- 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; /
This function returns a SQL text's signature. The signature can be used to identify SQL text in dba_sql_profiles
.
See Also:
SQL Profile Subprograms for other subprograms in this groupDBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE ( sql_text IN CLOB, force_match IN BOOLEAN := FALSE) RETURN NUMBER;
Table 101-34 SQLTEXT_TO_SIGNATURE Function Parameters
Parameter | Description |
---|---|
|
SQL text whose signature is required. Required. |
|
If |
This function returns the signature of the specified SQL text.
This procedure copies profile data stored in the staging table to create profiles on the system.
See Also:
SQL Profile Subprograms for other subprograms in this groupDBMS_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);
Table 101-35 UNPACK_STGTAB_SQLPROF Procedure Parameters
Parameter | Description |
---|---|
|
The name of the profile to unpack (% wildcards acceptable, case-sensitive) |
|
The category from which to unpack profiles (% wildcards acceptable, case-sensitive) |
|
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-sensitive). Required. |
|
The schema where the table resides, or |
Using this procedure requires the CREATE
ANY
SQL
PROFILE
privilege and the SELECT
privilege on staging table.
-- Unpack all profiles stored in a staging table EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => FALSE, - staging_table_name => 'PROFILE_STGTAB'); -- If there is a failure during the unpack operation, users can find the profile -- we failed on and perform a remap_stgtab_sqlprof operation targeting it. Then -- they can resume the unpack operation by setting replace to TRUE so that -- the profiles that were already created will just be replaced EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE, - staging_table_name => 'PROFILE_STGTAB');
This procedure copies one or more SQL tuning sets from their location in the staging table into the SQL tuning sets schema, making them proper SQL tuning sets.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_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);
Table 101-36 UNPACK_STGTAB_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
The name of the tuning set to unpack (not NULL). Wildcard characters ('%') are supported to unpack multiple tuning sets in a single call. For example, just specify '%' to unpack all tuning sets from the staging table. |
|
The name of tuning set owner, or |
|
Replaces tuning set if they already exist.If |
|
The name of the staging table, moved after a call to the PACK_STGTAB_SQLSET Procedure (case-sensitive) |
|
The name of staging table owner, or |
Users can drop the staging table after this procedure completes successfully.
The unpack procedure commits after successfully loading each SQL tuning set. If it fails with one tuning set, no part of that tuning set will have been unpacked, but those which the subprogram had already apprehended will continue to exist.
When failures occur due to SQL tuning set name or owner conflicts, users should use the REMAP_STGTAB_SQLSET Procedure to patch the staging table, and then call this procedure again to unpack those tuning sets that remain.
-- 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');
This procedure updates selected fields for SQL statement in a SQL tuning set.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupDBMS_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);