Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
View PDF |
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:
SQL Tuning Advisor
The SQL Tuning Advisor is one of a suite of Advisors, a set of expert systems that identifies and helps resolve database performance problems. Specifically, the SQL Tuning Advisor automates the tuning process of problematic SQL statements. That is, it takes one or more SQL statements as input and gives precise advice on how to tune the statements. The advice is provided is in the form of precise SQL actions for tuning the SQL along with their expected performance benefit.
The group of SQL Tuning Advisor Subprograms provide a task-oriented interface that lets you access the Advisor. You can call the following subprograms in the order given to use some of the SQL Tuning Advisor's features:
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
SQL Profile Subprograms
The SQL Tuning Advisor may recommend the creation of a SQL Profile to improve the performance of a statement. SQL Profiles consist of auxiliary statistics specific to the statement. The query optimizer makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount, resulting in poor execution plans. The SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to adjust these estimates.
The group of SQL Profile Subprograms provides a mechanism for delivering statistics to the optimizer that targets one particular SQL statement, and helps the optimizer make good decisions for that statement by giving it the most accurate statistical information possible. For example:
You can use the ACCEPT_SQL_PROFILE Procedure and Function to accept a SQL Profile recommended by the SQL Tuning Advisor.
You can alter the STATUS
, NAME
, DESCRIPTION
, and CATEGORY
attributes of an existing SQL Profile with the ALTER_SQL_PROFILE Procedure.
You can drop a SQL Profile with the DROP_SQL_PROFILE Procedure.
SQL Tuning Sets
The SQL Tuning Advisor input can be a single SQL statement or a set of statements. When tuning multiple statements in one advisor task, you give the input in the form of a SQL Tuning Set (STS). A SQL Tuning Set is a database object that stores SQL statements along with their execution context in a system-provided schema. SQL Tuning Sets provide an infrastructure for dealing with SQL workloads and simplify tuning of a large number of SQL statements.
SQL Tuning Sets store SQL statements along with
The execution context, such as the parsing schema name and bind values
Execution statistics such as average elapsed time and execution count
Execution plans - which are the sequence of operations Oracle performs to run SQL statements
Row source statistics such as the number of rows processed for each operation executed within the plan
SQL Tuning Sets can be created by filtering or ranking SQL statements from several sources:
The cursor cache using the SELECT_CURSOR_CACHE Function
Top SQL statements from the Automatic Workload Repository using the SELECT_WORKLOAD_REPOSITORY Functions
Other SQL Tuning Sets using the SELECT_SQLSET Function
A user-defined workload
The complete group of SQL Tuning Set Subprograms facilitates this functionality. As examples:
You use the CREATE_SQLSET Procedure and Function to creates a SQL tuning set object in the database
The LOAD_SQLSET Procedure populates the SQL tuning set with a set of selected SQL
The CAPTURE_CURSOR_CACHE_SQLSET Procedure collects SQL statements from the cursor cache over a specified time interval, attempting to build a realistic picture of system workload.
Import/Export SQL Tuning Sets and SQL Profiles
You use DBMS_SQLTUNE
subprograms to move SQL Profiles and SQL Tuning Sets from one system to another using a common programmatic model. In both cases, you create a staging table on the source system and populate that staging table with the relevant data. You then move that staging table to the destination system following the method of your choice (such as datapump, import/export, or database link), where it is used to reconstitute the objects in their original form. These steps are implemented by means of subprograms included in this package:
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
Object Types
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.
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(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)
Attributes
Table 101-1 SQLSET_ROW Attributes
Attribute | Description |
---|---|
sql_id |
Unique SQL ID |
forcing_matching_signature |
Signature with literals, case, and whitespace removed |
sql_text |
Full text for the statement |
object_list |
Currently not implemented |
bind_data |
Bind data as captured for this SQL. Note that you cannot stipulate an argument for this parameter and also for bind_list - they are mutually exclusive. |
parsing_schema |
Schema where the SQL is parsed |
module |
Last application module for the SQL |
action |
Last application action for the SQL |
elapsed_time |
Sum total elapsed time for this SQL statement |
cpu_time |
Sum total CPU time for this SQL statement |
buffer_gets |
Sum total number of buffer gets |
disk_reads |
Sum total number of disk reads |
direct_writes |
Sum total number of direct writes |
rows_processed |
Sum total number of rows processed by this SQL |
fetches |
Sum total number of fetches |
executions |
Total executions of this SQL |
end_of_fetch_count |
Number of times the statement was fully executed with all of its rows fetched |
optimizer_cost |
Optimizer cost for this SQL |
optimizer_env |
Optimizer environment for this SQL statement |
priority |
User-defined priority (1,2,3) |
command_type |
Statement type, such as INSERT or SELECT . |
first_load_time |
Load time of parent cursor |
stat_period |
Period of time (seconds) when the statistics of this SQL statement were collected |
active_stat_period |
Effective period of time (in seconds) during which the SQL statement was active |
other |
Other column for user defined attributes |
plan_hash_value |
Plan hash value of the plan |
sql_plan |
Explain plan |
bind_list |
List of user specified binds for SQL This is used for user-specified workloads. Note that you cannot stipulate an argument for this parameter and also for bind_data - they are mutually exclusive. |
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 |
---|---|
CANCEL_TUNING_TASK Procedure |
Cancels the currently executing tuning task |
CREATE_TUNING_TASK Functions |
Creates a tuning of a single statement or SQL tuning set |
DROP_TUNING_TASK Procedure |
Drops a SQL tuning task |
EXECUTE_TUNING_TASK Procedure |
Executes a previously created tuning task |
INTERRUPT_TUNING_TASK Procedure |
Interrupts the currently executing tuning task |
REPORT_TUNING_TASK Function |
Displays the results of a tuning task |
RESET_TUNING_TASK Procedure |
Resets the currently executing tuning task to its initial state |
RESUME_TUNING_TASK Procedure |
Resumes a previously interrupted task that was created to tune a SQL tuning set. |
SCRIPT_TUNING_TASK Function |
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 |
---|---|
ACCEPT_SQL_PROFILE Procedure and Function |
Creates a SQL Profile for the specified tuning task |
ALTER_SQL_PROFILE Procedure |
Alters specific attributes of an existing SQL Profile object |
CREATE_STGTAB_SQLPROF Procedure |
Creates the staging table used for copying SQL profiles from one system to another. |
DROP_SQL_PROFILE Procedure |
Drops the named SQL Profile from the database |
PACK_STGTAB_SQLPROF Procedure |
Moves profile data out of the SYS schema into the staging table |
REMAP_STGTAB_SQLPROF Procedure |
Changes the profile data values kept in the staging table prior to performing an unpack operation |
SQLTEXT_TO_SIGNATURE Function |
Returns a SQL text's signature |
UNPACK_STGTAB_SQLPROF Procedure |
Uses the profile data stored in the staging table to create profiles on this system |
The Summary of DBMS_SQLTUNE Subprograms contains a complete listing of all subprograms in the package.
This subprogram group provides an interface to manage SQL tuning sets.
Table 101-4 SQL Tuning Set Subprograms
Subprogram | Description |
---|---|
ADD_SQLSET_REFERENCE Function |
Adds a new reference to an existing SQL tuning set to indicate its use by a client |
CAPTURE_CURSOR_CACHE_SQLSET Procedure |
Over a specified time interval incrementally captures a workload from the cursor cache into a SQL tuning set |
CREATE_SQLSET Procedure and Function |
Creates a SQL tuning set object in the database |
CREATE_STGTAB_SQLSET Procedure |
Creates a staging table through which SQL Tuning Sets are imported and exported |
DELETE_SQLSET Procedure |
Deletes a set of SQL statements from a SQL tuning set |
DROP_SQLSET Procedure |
Drops a SQL tuning set if it is not active |
LOAD_SQLSET Procedure |
Populates the SQL tuning set with a set of selected SQL |
PACK_STGTAB_SQLSET Procedure |
Copies tuning sets out of the SYS schema into the staging table |
REMOVE_SQLSET_REFERENCE Procedure |
Deactivates a SQL tuning set to indicate it is no longer used by the client |
SELECT_CURSOR_CACHE Function |
Collects SQL statements from the cursor cache |
SELECT_SQLSET Function |
Collects SQL statements from an existing SQL tuning set |
SELECT_WORKLOAD_REPOSITORY Functions |
Collects SQL statements from the workload repository |
UNPACK_STGTAB_SQLSET Procedure |
Copies one or more SQL tuning sets from the staging table |
UPDATE_SQLSET Procedures |
Updates whether selected string fields for a SQL statement in a SQL tuning set or the set numerical attributes of a SQL in a SQL tuning set |
The Summary of DBMS_SQLTUNE Subprograms contains a complete listing of all subprograms in the package.
Table 101-5 DBMS_SQLTUNE Package Subprograms
Subprogram | Description | Group |
---|---|---|
ACCEPT_SQL_PROFILE Procedure and Function |
Create a SQL Profile for the specified tuning task | SQL Profile Subprograms |
ADD_SQLSET_REFERENCE Function |
Adds a new reference to an existing SQL tuning set to indicate its use by a client | SQL Tuning Set Subprograms |
ALTER_SQL_PROFILE Procedure |
Alters specific attributes of an existing SQL Profile object | SQL Profile Subprograms |
CANCEL_TUNING_TASK Procedure |
Cancels the currently executing tuning task | SQL Tuning Advisor Subprograms |
CAPTURE_CURSOR_CACHE_SQLSET Procedure |
Over a specified time interval incrementally captures a workload from the cursor cache into a SQL tuning set | SQL Tuning Set Subprograms |
CREATE_SQLSET Procedure and Function |
Creates a SQL tuning set object in the database | SQL Tuning Set Subprograms |
CREATE_STGTAB_SQLPROF Procedure |
Creates the staging table used for copying SQL profiles from one system to another. | SQL Profile Subprograms |
CREATE_STGTAB_SQLSET Procedure |
Creates a staging table through which SQL Tuning Sets are imported and exported | SQL Tuning Set Subprograms |
CREATE_TUNING_TASK Functions |
Creates a tuning of a single statement or SQL tuning set | SQL Tuning Advisor Subprograms |
DELETE_SQLSET Procedure |
Deletes a set of SQL statements from a SQL tuning set | SQL Tuning Set Subprograms |
DROP_SQL_PROFILE Procedure |
Drops the named SQL Profile from the database | SQL Profile Subprograms |
DROP_SQLSET Procedure |
Drops a SQL tuning set if it is not active | SQL Tuning Set Subprograms |
DROP_TUNING_TASK Procedure |
Drops a SQL tuning task | SQL Tuning Advisor Subprograms |
EXECUTE_TUNING_TASK Procedure |
Executes a previously created tuning task | SQL Tuning Advisor Subprograms |
INTERRUPT_TUNING_TASK Procedure |
Interrupts the currently executing tuning task | SQL Tuning Advisor Subprograms |
LOAD_SQLSET Procedure |
Populates the SQL tuning set with a set of selected SQL | SQL Tuning Set Subprograms |
PACK_STGTAB_SQLPROF Procedure |
Moves profile data out of the SYS schema into the staging table |
SQL Profile Subprograms |
PACK_STGTAB_SQLSET Procedure |
Moves tuning sets out of the SYS schema into the staging table |
SQL Tuning Set Subprograms |
REMAP_STGTAB_SQLPROF Procedure |
Changes the profile data values kept in the staging table prior to performing an unpack operation | SQL Profile Subprograms |
REMAP_STGTAB_SQLSET Procedure |
Changes the tuning set names and owners in the staging table so that they can be unpacked with different values than they had on the host system | SQL Tuning Set Subprograms |
REMOVE_SQLSET_REFERENCE Procedure |
Deactivates a SQL tuning set to indicate it is no longer used by the client | SQL Tuning Set Subprograms |
REPORT_TUNING_TASK Function |
Displays the results of a tuning task | SQL Tuning Set Subprograms |
RESET_TUNING_TASK Procedure |
Resets the currently executing tuning task to its initial state | SQL Tuning Advisor Subprograms |
RESUME_TUNING_TASK Procedure |
Resumes a previously interrupted task that was created to tune a SQL tuning set. | SQL Tuning Advisor Subprograms |
SCRIPT_TUNING_TASK Function |
Creates a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations | SQL Tuning Advisor Subprograms |
SELECT_CURSOR_CACHE Function |
Collects SQL statements from the cursor cache | SQL Tuning Set Subprograms |
SELECT_SQLSET Function |
Collects SQL statements from an existing SQL tuning set | SQL Tuning Set Subprograms |
SELECT_WORKLOAD_REPOSITORY Functions |
Collects SQL statements from the workload repository | SQL Tuning Set Subprograms |
SQLTEXT_TO_SIGNATURE Function |
Returns a SQL text's signature | SQL Profile Subprograms |
UNPACK_STGTAB_SQLPROF Procedure |
Uses the profile data stored in the staging table to create profiles on this system | SQL Profile Subprograms |
UNPACK_STGTAB_SQLSET Procedure |
Moves one or more SQL tuning sets from the staging table | SQL Tuning Set Subprograms |
UPDATE_SQLSET Procedures |
Updates selected fields for a SQL statement in a SQL tuning set | SQL Tuning Set Subprograms |
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 groupSyntax
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name IN VARCHAR2, object_id IN NUMBER := NULL, name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL); task_owner IN VARCHAR2 := NULL, replace IN BOOLEAN := FALSE, force_match IN BOOLEAN := FALSE);
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name IN VARCHAR2, object_id IN NUMBER := NULL, name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL; task_owner IN VARCHAR2 := NULL, replace IN BOOLEAN := FALSE, force_match IN BOOLEAN := FALSE) RETURN VARCHAR2;
Parameters
Table 101-6 ACCEPT_SQL_PROFILE Procedure and Function Parameters
Parameter | Description |
---|---|
task_name |
The (mandatory) name of the SQL tuning task |
object_id |
The identifier of the advisor framework object representing the SQL statement associated with the tuning task |
name |
The name of the SQL Profile. It cannot contain double quotation marks. The name is case sensitive. If not specified, the system will generate a unique name for the SQL Profile. |
description |
A user specified string describing the purpose of the SQL Profile. The description is truncated if longer than 256 characters. The maximum size is 500 characters. |
category |
This is the category name which must match the value of the SQLTUNE_CATEGORY parameter in a session for the session to use this SQL Profile. It defaults to the value "DEFAULT ". This is also the default of the SQLTUNE_CATEGORY parameter. The category must be a valid Oracle identifier. The category name specified is always converted to upper case. The combination of the normalized SQL text and category name creates a unique key for a SQL Profile. An ACCEPT_SQL_PROFILE will fail if this combination is duplicated. |
task_owner |
Owner of the tuning task. This is an optional parameter that has to be specified to accept a SQL Profile associated to a tuning task owned by another user. The current user is the default value. |
replace |
If the profile already exists, it will be replaced if this argument is TRUE . It is an error to pass a name that is already being used for another signature/category pair, even with replace set to TRUE . |
force_match |
If TRUE this causes SQL Profiles to target all SQL statements which have the same text after normalizing all literal values into bind variables. (Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.) This is analogous to the matching algorithm used by the FORCE option of the cursor_sharing parameter.
If |
Return Values
The name of the SQL profile.
Usage Notes
The CREATE
ANY
SQL PROFILE
privilege is required.
Examples
You use both the procedure and the function versions of the subprogram in the same way except you must specify a return value to invoke the function. Here we give examples of the procedure only.
In this example, you tune a single SQL statement form the workload repository and you create the SQL profile recommended by SQL tuning advisor.
variable stmt_task VARCHAR2(64); variable sts_task VARCHAR2(64); -- create a tuning task tune the statement EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( begin_snap => 1, - end_snap => 2, - sql_id => 'ay1m3ssvtrh24'); -- execute the resulting task EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task); EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(:stmt_task);
Note that you do not have to specify the ID (that is, object_id
) for the advisor framework object created by SQL tuning advisor to represent the tuned SQL statement.
You might also want to accept the recommended SQL profile in a different category, (for example, TEST
), so that it will not be used by default.
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => :stmt_task, - category => 'TEST');
You can use command ALTER
SESSION
SET
SQLTUNE_CATEGORY
= 'TEST'
to see how this profile behaves.
The following call creates a SQL profile that targets any SQL statement with the same force_matching_signature
as the tuned statement.
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name => :stmt_task, - force_match => TRUE);
In the following example, you tune a SQL tuning set, and you create a SQL profile for only one of the SQL statements in the SQL tuning set. The SQL statement is represented by an advisor framework object with ID equal to '5'. Please notice that you must pass an object id to the ACCEPT_SQL_PROFILE
procedure because there are potentially many SQL profiles for the tuning task. This object id is given along with the report.
EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK ( - sqlset_name => 'my_workload', - rank1 => 'ELAPSED_TIME', - time_limit => 3600, - description => 'my workload ordered by elapsed time'); -- execute the resulting task EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:sts_task); -- create the profile for the sql statement corresponding to object_id = 5. EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => :sts_task, - object_id => 5);
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 groupSyntax
DBMS_SQLTUNE.ADD_SQLSET_REFERENCE ( sqlset_name IN VARCHAR2, description IN VARCHAR2 := NULL) RETURN NUMBER;
Parameters
Table 101-7 ADD_SQLSET_REFERENCE Function Parameters
Parameter | Description |
---|---|
sqlset_name |
The SQL tuning set name |
description |
The description of the usage of SQL tuning set. The description is truncated if longer than 256 characters. |
Return Values
The identifier of the added reference.
Examples
You can add reference to a SQL tuning set. This prevents the tuning set from being modified while it is being used. References are automatically added when you invoke SQL tuning advisor on the SQL tuning set, so you should use this function for custom purposes only.The function returns a reference ID that is used to remove it later. You use the REMOVE_SQLSET_REFERENCE Procedure
to delete references to a SQL tuning set.
variable rid number; EXEC :rid := DBMS_SQLTUNE.ADD_SQLSET_REFERENCE( - sqlset_name => 'my_workload', - description => 'my sts reference');
You can use the views USER/DBA_SQLSET_REFERENCES
to find all references on a given SQL tuning set.
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 groupSyntax
DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name IN VARCHAR2, attribute_name IN VARCHAR2, value IN VARCHAR2);
Parameters
Table 101-8 ALTER_SQL_PROFILE Procedure Parameters
Parameter | Description |
---|---|
name |
The (mandatory) name of the existing SQL Profile to alter |
attribute_name |
The (mandatory) attribute name to alter (case insensitive) using valid attribute names |
value |
The (mandatory) new value of the attribute using valid attribute values |
Usage Notes
Requires the "ALTER
ANY
SQL PROFILE
" privilege.
Examples
-- Disable a profile, so it will be not be used by any sessions. EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => :pname, - attribute_name => 'STATUS', - value => 'DISABLED'); -- Enable it back: EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name => :pname, - attribute_name => 'STATUS', - value => 'ENABLED'); -- Change the category of the profile so it will be used only by sessions -- with category set to TEST. -- Use ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST' to see how this profile -- behaves. EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name => :pname, - attribute_name => 'CATEGORY', - value => 'TEST'); -- Change it back: EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name => :pname, - attribute_name => 'CATEGORY', - value => 'DEFAULT');
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 groupSyntax
DBMS_SQLTUNE.CANCEL_TUNING_TASK( task_name IN VARCHAR2);
Parameters
Table 101-9 CANCEL_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
task_name |
The name of the task to cancel |
Examples
You cancel a task when you need to stop it executing and do not require to view any already-completed results.
EXEC DBMS_SQLTUNE.CANCEL_TUNING_TASK(:my_task);
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 groupSyntax
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET ( sqlset_name IN VARCHAR2, time_limit IN POSITIVE := 1800, repeat_interval IN POSITIVE := 300, capture_option IN VARCHAR2 := 'MERGE', capture_mode IN NUMBER := MODE_REPLACE_OLD_STATS, basic_filter IN VARCHAR2 := NULL, sqlset_owner IN VARCHAR2 := NULL);
Parameters
Table 101-10 CAPTURE_CURSOR_CACHE_SQLSET Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The SQL tuning set name |
time_limit |
The total amount of time, in seconds, to execute |
repeat_interval |
The amount of time, in seconds, to pause between sampling |
capture_option |
During capture, either insert new statements, update existing ones, or both. 'INSERT ', 'UPDATE ', or 'MERGE ' just like load_option in load_sqlset |
capture_mode |
capture mode (UPDATE and MERGE capture options).Possible values:
|
basic_filter |
Filter to apply to cursor cache on each sampling (see select_xxx subprograms) |
sqlset_owner |
The owner of the SQL tuning set or NULL for current schema owner |
Examples
In this example capture takes place over a 30-second period, polling the cache once every five seconds. This will capture all statements run during that period but not before or after. If the same statement appears a second time, the process replaces the stored statement with the new 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 groupSyntax
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 101-11 CREATE_SQLSET Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The SQL tuning set name |
description |
The description of the SQL tuning set |
sqlset_owner |
The owner of the SQL tuning set, or NULL for the current schema owner |
Examples
EXEC DBMS_SQLTUNE.CREATE_SQLSET(- sqlset_name => 'my_workload', - description => 'complete application workload');
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 groupSyntax
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( table_name IN VARCHAR2, schema_name IN VARCHAR2 := NULL, tablespace_name IN VARCHAR2 := NULL);
Parameters
Table 101-12 CREATE_STGTAB_SQLPROF Procedure Parameters
Parameter | Description |
---|---|
table_name |
The name of the table to create (case-sensitive). Required. |
schema_name |
The schema to create the table in, or NULL for current schema (case-sensitive) |
tablespace_name |
The tablespace to store the staging table within, or NULL for current user's default tablespace (case-sensitive) |
Usage Notes
Call this procedure once before issuing a call to the PACK_STGTAB_SQLPROF Procedure.
This procedure can be called multiple times if you would like to have different SQL profiles in different staging tables.
Note that this is a DDL operation, so it does not occur within a transaction.
Examples
Create a staging table to store profile data that can be moved to another system.
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'PROFILE_STGTAB');
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 groupSyntax
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ( table_name IN VARCHAR2, schema_name IN VARCHAR2 := NULL, tablespace_name IN VARCHAR2 := NULL);
Parameters
Table 101-13 CREATE_STGTAB_SQLSET Procedure Parameters
Parameter | Description |
---|---|
table_name |
The name of the table to create (case-sensitive) |
schema_name |
The schema in which to create the table in, or NULL for current schema (case-sensitive) |
tablespace_name |
The tablespace in which to store the staging table, or NULL for current user's default tablespace (case-sensitive) |
Usage Notes
Call this procedure once before issuing a call to the PACK_STGTAB_SQLSET Procedure.
This procedure can be called multiple times if you would like to have different tuning sets in different staging tables.
Note that this is a DDL operation, so it does not occur within a transaction.
Users issuing the call must have permission to CREATE
TABLE
in the schema provided and the relevant tablespace.
Please note that the staging table contains nested table columns and indexes, so it should not be renamed.
Examples
Create a staging table for packing and eventually exporting a SQL tuning sets
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name => 'STGTAB_SQLSET');
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 groupSyntax
DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text IN CLOB, bind_list IN sql_binds := NULL, user_name IN VARCHAR2 := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2; DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2; DBMS_SQLTUNE.CREATE_TUNING_TASK( begin_snap IN NUMBER, end_snap IN NUMBER, sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2; DBMS_SQLTUNE.CREATE_TUNING_TASK( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, rank1 IN VARCHAR2 := NULL, rank2 IN VARCHAR2 := NULL, rank3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := NULL, result_limit IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME', sqlset_owner IN VARCHAR2 := NULL) RETURN VARCHAR2;
Parameters
Table 101-14 CREATE_TUNING_TASK Function Parameters
Parameter | Description |
---|---|
sql_text |
The text of a SQL statement |
begin_snap |
Begin snapshot identifier |
end_snap |
End snapshot identifier |
sql_id |
The identifier of a SQL statement |
bind_list |
An ordered list of bind values in ANYDATA type |
plan_hash_value |
The hash value of the SQL execution plan |
sqlset_name |
The SQL tuning set name |
basic_filter |
The SQL predicate to filter the SQL from the SQL tuning set |
object_filter |
The object filter |
rank(i) |
An order-by clause on the selected SQL |
result_percentage |
A percentage on the sum of a ranking measure |
result_limit |
The top L(imit) SQL from the (filtered/ranked) SQL |
user_name |
The username for whom the statement is to be tuned |
scope |
Tuning scope (limited/comprehensive) |
time_limit |
The maximum duration in seconds for the tuning session |
task_name |
An optional tuning task name |
description |
A task of the SQL tuning session to a maximum of 256 characters |
plan_filter |
Plan filter. It is applicable in case there are multiple plans (plan_hash_value ) associated with the same statement. This filter allows for selecting one plan (plan_hash_value ) only. Possible values are:
|
sqlset_owner |
The owner of the SQL tuning set, or NULL for the current schema owner |
Return Values
A SQL tuning task name.
Examples
variable stmt_task VARCHAR2(64); variable sts_task VARCHAR2(64); -- Sql text format EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( - sql_text => 'select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-00'')'); -- Sql id format (cursor cache) EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24'); -- tune in limited scope EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', - scope => 'LIMITED'); -- only give 10 minutes for tuning statement EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', - time_limit => 600); -- Workload repository format exec :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 1, - end_snap => 2, sql_id => 'ay1m3ssvtrh24'); -- Sql tuning set format (first we need to load an STS, then tune it) -- Tune our statements in order by buffer gets, time limit of one hour -- the default ranking measure is elapsed time. EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( - sqlset_name => 'my_workload', - rank1 => 'BUFFER_GETS', - time_limit => 3600, - description => 'tune my workload ordered by buffer gets');
This procedure deletes a set of SQL statements from a SQL tuning set.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupSyntax
DBMS_SQLTUNE.DELETE_SQLSET ( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, sqlset_owner IN VARCHAR2 := NULL);
Parameters
Table 101-15 DELETE_SQLSET Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The SQL tuning set name |
basic_filter |
SQL predicate to filter the SQL from the SQL tuning set. This basic filter is used as a where clause on the SQL tuning set content to select a desired subset of SQL from the Tuning Set. |
sqlset_owner |
The owner of the SQL tuning set, or NULL for current schema owner |
Examples
-- Delete all statements in a sql tuning set. EXEC DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'my_workload'); -- Delete all statements in a sql tuning set which ran for less than a second EXEC DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'my_workload', - basic_filter => 'elapsed_time < 1000000');
This procedure drops the named SQL Profile from the database.
See Also:
SQL Profile Subprograms for other subprograms in this groupSyntax
DBMS_SQLTUNE.DROP_SQL_PROFILE ( name IN VARCHAR2, ignore IN BOOLEAN := FALSE);
Parameters
Table 101-16 DROP_SQL_PROFILE Procedure Parameters
Parameter | Description |
---|---|
name |
The (mandatory) name of SQL Profile to be dropped. The name is case sensitive. |
ignore |
Ignores errors due to object not existing |
Usage Notes
Requires the "DROP
ANY
SQL PROFILE
" privilege.
Examples
-- Drop the profile: EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(:pname);
This procedure drops a SQL tuning set if it is not active.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupSyntax
DBMS_SQLTUNE.DROP_SQLSET ( sqlset_name IN VARCHAR2, sqlset_owner IN VARCHAR2 := NULL);
Parameters
Table 101-17 DROP_SQLSET Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The SQL tuning set name |
sqlset_owner |
The owner of the SQL tuning set, or NULL for current schema owner |
Usage Notes
You cannot drop a SQL tuning set when it is referenced by one or more clients (for example, SQL tuning advisor).
Examples
-- 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 groupSyntax
DBMS_SQLTUNE.DROP_TUNING_TASK( task_name IN VARCHAR2);
Parameters
Table 101-18 DROP_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
task_name |
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 groupSyntax
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name IN VARCHAR2);
Parameters
Table 101-19 EXECUTE_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
task_name |
The name of the tuning task to execute |
Examples
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 groupSyntax
DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name IN VARCHAR2);
Parameters
Table 101-20 INTERRUPT_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
task_name |
The name of the tuning task to interrupt |
Examples
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 groupSyntax
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 101-21 LOAD_SQLSET Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The SQL tuning set name to populate |
populate_cursor |
The cursor reference from which to populate |
load_option |
Specifies how the statements will be loaded into the SQL tuning set. The possible values are:
|
update_option |
Specifies how the existing statements will be updated. This parameter is considered only if load_option is specified with 'UPDATE '/'MERGE ' as an option. The possible values are:
|
update_condition |
Specifies a where clause to execute the update operation. The update is performed only if the specified condition is true. The condition can refer to either the data source or destination. The condition must use the following prefixes to refer to attributes from the source or the destination:
|
update_attributes |
Specifies the list of a SQL statement attributes to update during a merge or update operation.The possible values are:
|
ignore_null |
If TRUE do not update an attribute if the new value is NULL . That is, do not override with NULL values unless intentional. |
commit_rows |
If a value is provided, the load will commit after each set of that many statements is inserted. If NULL is provided, the load will commit only once, at the end of the operation. |
sqlset_owner |
The owner of the SQL tuning set, or the current schema owner or NULL for current owner |
Exceptions
This procedure returns an error when sqlset_name
is invalid, or a corresponding SQL tuning set does not exist, or the populate_cursor
is incorrect and cannot be executed.
Exceptions are also raised when invalid filters are provided. Filters can be invalid either because they don't parse (for example, they refer to attributes not in sqlset_row), or because they violate the user's privileges.
Usage Notes
Rows in the input populate_cursor
must be of type SQLSET_ROW
.
Examples
In this example, you create and populate a SQL tuning set with all cursor cache statements with an elapsed time of 5 seconds or more excluding statements that belong to SYS
schema (to simulate an application user workload). You select all attributes of the SQL statements and load them in the tuning set using the default mode, which will only load new statements, since the SQL tuning set is empty.
-- create the tuning set EXEC DBMS_SQLTUNE.CREATE_SQLSET('my_workload'); -- populate the tuning set from the cursor cache DECLARE cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'parsing_schema_name <> ''SYS'' AND elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload', populate_cursor => cur); END; /
Suppose now you wish to augment this information with what is stored in the workload repository (AWR). You populate the tuning set with 'ACCUMULATE'
as your update_option
because it is assumed the cursors currently in the cache had aged out since the snapshot was taken.
You omit the elapsed_time
filter because it is assumed that any statement captured in AWR is important, but still you throw away the SYS
-parsed cursors to avoid recursive SQL.
DECLARE cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,2, 'parsing_schema_name <> ''SYS''', NULL, NULL,NULL,NULL, 1, NULL, 'ALL')) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload', populate_cursor => cur, Using DBMS_SQLTUNE load_option => 'MERGE', update_option => 'ACCUMULATE'); END;
The following example is a simple load that only inserts new statements from the workload repository, skipping existing ones (in the SQL tuning set). Note that 'INSERT'
is the default value for the load_option
argument of the LOAD_SQLSET
procedure.
DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,2)) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload', populate_cursor => cur); END; /
The next example demonstrates a load with UPDATE
option. This updates statements that already exist in the SQL tuning set but does not add new ones. By default, old statistics are replaced by their new values.
DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload', populate_cursor => cur, load_option => 'UPDATE'); END; /
This procedure copies profile data from the SYS
. schema into the staging table.
See Also:
SQL Profile Subprograms for other subprograms in this groupSyntax
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 101-22 PACK_STGTAB_SQLPROF Procedure Parameters
Parameter | Description |
---|---|
profile_name |
The name of the profile to pack (% wildcards acceptable, case-sensitive) |
profile_category |
The category to pack profiles from (% wildcards acceptable, case-sensitive) |
staging_table_name |
The name of the table to use (case-sensitive). Required. |
staging_schema_owner |
The schema where the table resides, or NULL for current schema (case-sensitive) |
Usage Notes
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.
Examples
Put only those profiles in the DEFAULT
category into the staging table. This corresponds to all profiles that will be used by default on this system.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROFILE_STGTAB');
This is another example where you put all profiles into the staging table. Note this will even move profiles that are not currently being used by default but are in other categories, such as for testing purposes.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_category => '%', - staging_table_name => 'PROFILE_STGTAB');
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 groupSyntax
DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( sqlset_name IN VARCHAR2, sqlset_owner IN VARCHAR2 := NULL, staging_table_name IN VARCHAR2, staging_schema_owner IN VARCHAR2 := NULL);
Parameters
Table 101-23 PACK_STGTAB_SQLSET Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The name of the SQL Tuning Set to pack (% wildcards acceptable, case-sensitive) |
sqlset_owner |
The category from which to pack SQL Tuning Sets (% wildcards acceptable, case-sensitive) |
staging_table_name |
The name of the table to use (case-sensitive) |
staging_schema_owner |
The schema where the table resides, or NULL for current schema (case-sensitive) |
Usage Notes
This procedure can be called several times to move more than one SQL tuning set. Users can then move the populated staging table to another system using any method, such as database link or datapump. Users can then call the UNPACK_STGTAB_SQLSET Procedure create the SQL tuning set on the other system.
Note that this function issues a COMMIT
after packing each SQL tuning set, so if an error is raised mid-execution, clear the staging table by deleting its rows.
Examples
Put all SQL tuning sets on the system in the staging table (to create a staging table, see the CREATE_STGTAB_SQLSET Procedure
).
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => '%', - sqlset_owner => '%', - staging_table_name => 'STGTAB_SQLSET');
Put only those SQL tuning sets owned by the current user in the staging table.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => '%', - staging_table_name => 'STGTAB_SQLSET');
Pack a specific SQL tuning set.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'my_workload', - staging_table_name => 'STGTAB_SQLSET');
Pack a second SQL tuning set.
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'workload_subset', - staging_table_name => 'STGTAB_SQLSET');
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 groupSyntax
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 101-24 REMAP_STGTAB_SQLPROF Procedure Parameters
Parameter | Description |
---|---|
old_profile_name |
The name of the profile to target for a remap operation (case-sensitive) |
new_profile_name |
The new name of the profile, or NULL to remain the same (case-sensitive) |
new_profile_category |
The new category for the profile, or NULL to remain the same (case-sensitive) |
staging_table_name |
The name of the table on which to perform the remap operation (case-sensitive). Required. |
staging_schema_owner |
The schema where the table resides, or NULL for current schema (case-sensitive) |
Usage Notes
Using this procedure requires the UPDATE
privilege on the staging table.
Examples
Change the name of a profile before we unpack, to avoid conflicts
EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF(old_profile_name => :pname, - new_profile_name => 'IMP' || :pname, - staging_table_name => 'PROFILE_STGTAB');
Change the SQL profile in the staging table to be 'TEST'
category before we import it. This way users can test the profile on the new system before it is active.
EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF(old_profile_name => :pname, - new_profile_category => 'TEST', - staging_table_name => 'PROFILE_STGTAB');
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 groupSyntax
DBMS_SQLTUNE.REMAP_STGTAB_SQLSET ( old_sqlset_name IN VARCHAR2, old_sqlset_owner IN VARCHAR2 := NULL, new_sqlset_name IN VARCHAR2 := NULL, new_sqlset_owner IN VARCHAR2 := NULL, staging_table_name IN VARCHAR2, taging_schema_owner IN VARCHAR2 := NULL);
Parameters
Table 101-25 REMAP_STGTAB_SQLSET Procedure Parameters
Parameter | Description |
---|---|
old_sqlset_name |
The name of the tuning set to target for a remap operation. Wildcards are not supported. |
old_sqlset_owner |
The new name of the tuning set owner to target for a remap operation. NULL for current schema owner |
new_sqlset_name |
The new name for the tuning set, or NULL to keep the same tuning set name. |
new_sqlset_owner |
The new owner for the tuning set, or NULL to remain the same owner name. |
staging_table_name |
The name of the table on which to perform the remap operation (case-sensitive) |
staging_schema_owner |
The name of staging table owner, or NULL for current schema owner (case-sensitive) |
Usage Notes
You can call this procedure multiple times to remap more than one tuning set name or owner. Note that this procedure only handles one tuning set per call.
Examples
-- Change the name of an STS in the staging table before we unpack it. EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLSET(old_sqlset_name => 'my_workload', - old_sqlset_owner => 'SH', - new_sqlset_name => 'imp_workload', - staging_table_name => 'STGTAB_SQLSET'); -- Change the owner of an STS in the staging table before we unpack it. EXEC DBMS_SQLTUNE.REMAP_STGTAB_SQLSET(old_sqlset_name => 'imp_workload', - old_sqlset_owner => 'SH', - new_sqlset_owner => 'SYS', - staging_table_name => 'STGTAB_SQLSET');
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 groupSyntax
DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE ( sqlset_name IN VARCHAR2, reference_id IN NUMBER);
Parameters
Table 101-26 REMOVE_SQLSET_REFERENCE Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The SQL tuning set name |
reference_id |
The identifier of the reference to remove |
Examples
You can remove references on a given SQL tuning set when you finish using it and want to make it writable again.
EXEC DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE( - sqlset_name => 'my_workload', - reference_id => :rid);
Use views USER/DBA_SQLSET_REFERENCES
to find all references on a given SQL tuning set.
This procedure displays the results of a tuning task.
See Also:
SQL Tuning Advisor Subprograms for other subprograms in this groupSyntax
DBMS_SQLTUNE.REPORT_TUNING_TASK( task_name IN VARCHAR2, type IN VARCHAR2 := TYPE_TEXT, level IN VARCHAR2 := LEVEL_TYPICAL, section IN VARCHAR2 := SECTION_ALL, object_id IN NUMBER := NULL, result_limit IN NUMBER := NULL) RETURN CLOB;
Parameters
Table 101-27 REPORT_TUNING_TASK Function Parameters
Parameter | Description |
---|---|
task_name |
The name of the tuning task to report |
type |
The type of the report to produce. Possible values are TEXT , HTML and XML . |
level |
The format of the recommendations. Possible values are TYPICAL , BASIC and ALL . |
section |
The particular section in the report. Possible values are FINDING , PLAN , INFORMATION , ERROR and ALL . |
object_id |
The identifier of the advisor framework object that represents a given statement in the SQL tuning set |
result_limit |
The number of statements in a SQL tuning set for which a report is generated |
Return Values
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.
Examples
-- Get the whole report for the single statement case. SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:stmt_task) from dual; -- Show me the summary for the sts case. SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY') FROM DUAL; -- Show me the findings for the statement I'm interested in. SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;
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 groupSyntax
DBMS_SQLTUNE.RESET_TUNING_TASK( task_name IN VARCHAR2);
Parameters
Table 101-28 RESET_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
task_name |
The name of the tuning task to reset |
Examples
-- reset and re-execute a task EXEC DBMS_SQLTUNE.RESET_TUNING_TASK(:sts_task); -- re-execute the task EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:sts_task);
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 groupSyntax
DBMS_SQLTUNE.RESUME_TUNING_TASK( task_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL);
Parameters
Table 101-29 RESUME_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
task_name |
The name of the tuning task to resume |
basic_filter |
A SQL predicate to filter the SQL from the sql tuning set. Note that this filter will be applied in conjunction with the basic filter (i.e., parameter basic_filter ) when calling CREATE_TUNING_TASK Functions. |
Usage Notes
Resuming a single SQL tuning task (a task that was created to tune a single SQL statement as compared to a SQL Tuning Set) is not supported.
Examples
-- Interrupt the task EXEC DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(:conc_task); -- Once a task is interrupted, we can elect to reset it, resume it, or check -- out its results and then decide. For this example we will just resume. EXEC DBMS_SQLTUNE.RESUME_TUNING_TASK(:conc_task);
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 groupSyntax
DBMS_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;
Parameters
Table 101-30 SCRIPT_TUNING_TASK Procedure Parameters
Parameter | Description |
---|---|
task_name |
The name of the tuning task for which to apply a script |
rec_type |
Filter the script by types of recommendations to include. Any subset of the following separated by commas: or 'ALL : ''PROFILES' ''STATISTICS' ''INDEXES' . For example, a script with profiles and statistics: 'PROFILES,STATISTICS' |
object_id |
Optionally filters by a single object ID |
result_limit |
Optionally shows commands for only top N SQL (ordered by object_id and ignored if an object_id is also specified) |
owner_name |
Owner of the relevant tuning task. Defaults to the current schema owner |
Return Values
This function returns a CLOB
containing the PL/SQL calls to be executed to implement the subset of recommendations dictated by the arguments.
Usage Notes
Once the script is returned, it should then by checked by the DBA and executed.
Wrap with a call to DBMS_ADVISOR.CREATE_FILE
to put it into a file.
Examples
SET LINESIZE 140 -- Get a script for all actions recommended by the task. SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task) FROM DUAL; -- Get a script of just the sql profiles we should create. SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'PROFILES') FROM DUAL; -- get a script of just stale / missing stats SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'STATISTICS') FROM DUAL; -- Get a script with recommendations about just one SQL statement when we have -- tuned an entire STS. SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:sts_task, 'ALL', 5) FROM DUAL;
This function collects SQL statements from the SQL Cursor Cache.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupSyntax
DBMS_SQLTUNE.SELECT_CURSOR_CACHE ( basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, ranking_measure1 IN VARCHAR2 := NULL, ranking_measure2 IN VARCHAR2 := NULL, ranking_measure3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := 1, result_limit IN NUMBER := NULL, attribute_list IN VARCHAR2 := NULL) RETURN sys.sqlset PIPELINED;
Parameters
Table 101-31 SELECT_CURSOR_CACHE Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The SQL tuning set name |
basic_filter |
The SQL predicate to filter the SQL from the cursor cache defined on attributes of the SQLSET_ROW |
object_filter |
Specifies the objects that should exist in the object list of selected SQL from the cursor cache |
ranking_measure(n) |
An order-by clause on the selected SQL |
result_percentage |
A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given. |
result_limit |
The top L(imit) SQL from the (filtered) source ranked by the ranking measure |
attribute_list |
List of SQL statement attributes to return in the result. The possible values are:
|
Return Values
This function returns a one SQLSET_ROW
per SQL_ID
or PLAN_HASH_VALUE
pair found in each data source.
Usage Notes
Users need privileges on the cursor cache views.
Examples
-- Get sql ids and sql text for statements with 500 buffer gets. SELECT sql_id, sql_text FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500')) ORDER BY sql_id; -- Get all the information we have about a particular statement. SELECT * FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j''')); -- Notice that some statements can have multiple plans. The output of the -- SELECT_XXX table functions is unique by (sql_id, plan_hash_value). This is -- because a data source can store multiple plans per sql statement. SELECT sql_id, plan_hash_value FROM table(dbms_sqltune.select_cursor_cache('sql_id = ''ay1m3ssvtrh24''')) ORDER BY sql_id, plan_hash_value; -- PL/SQL examples: load_sqlset will be called after opening a cursor, along the -- lines given below -- Select all statements in the cursor cache. DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT value(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur; END;/ -- Look for statements not parsed by SYS. DECLARE cur sys_refcursor; BEGIN OPEN cur for SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''')) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur; end;/ -- All statements from a particular module/action. DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; END;/ -- all statements that ran for at least five seconds DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; end;/ -- select all statements that pass a simple buffer_gets threshold and -- are coming from an APPS user DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'buffer_gets > 100 and parsing_schema_name = ''APPS'''))P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; end;/ -- select all statements exceeding 5 seconds in elapsed time, but also -- select the plans (by default we only select execution stats and binds -- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row -- is NULL) DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(dbms_sqltune.select_cursor_cache( 'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL, 'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN')) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; END;/ -- Select the top 100 statements in the cursor cache ordering by elapsed_time. DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL, NULL, 'ELAPSED_TIME', NULL, NULL, 1, 100)) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; end;/ -- Select the set of statements which cumulatively account for 90% of the -- buffer gets in the cursor cache. This means that the buffer gets of all -- of these statements added up is approximately 90% of the sum of all -- statements currently in the cache. DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL, NULL, 'BUFFER_GETS', NULL, NULL, .9)) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur; END; /
This function reads SQLSET contents.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupSyntax
DBMS_SQLTUNE.SELECT_SQLSET ( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, ranking_measure1 IN VARCHAR2 := NULL, ranking_measure2 IN VARCHAR2 := NULL, ranking_measure3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := 1, result_limit IN NUMBER := NULL) attribute_list IN VARCHAR2 := NULL, plan_filter IN VARCHAR2 := NULL, sqlset_owner IN VARCHAR2 := NULL) RETURN sys.sqlset PIPELINED;
Parameters
Table 101-32 SELECT_SQLSET Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The SQL tuning set name |
basic_filter |
The SQL predicate to filter the SQL from the SQL Tuning Set defined on attributes of the SQLSET_ROW |
object_filter |
Specifies the objects that should exist in the object list of selected SQL from the cursor cache |
ranking_measure(n) |
An order-by clause on the selected SQL |
result_percentage |
A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given. |
result_limit |
The top L(imit) SQL from the (filtered) source ranked by the ranking measure |
attribute_list |
List of SQL statement attributes to return in the result. The possible values are:
|
plan_filter |
The plan filter |
sqlset_owner |
The owner of the SQL tuning set, or NULL for the current schema owner |
Return Values
This function returns a one SQLSET_ROW
per SQL_ID
or PLAN_HASH_VALUE
pair found in each data source.
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; /
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 groupSyntax
DBMS_SQLTUNE.SELECT_WORKLAOD_REPOSITORY ( begin_snap IN NUMBER, end_snap IN NUMBER, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, ranking_measure1 IN VARCHAR2 := NULL, ranking_measure2 IN VARCHAR2 := NULL, ranking_measure3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := 1, result_limit IN NUMBER := NULL attribute_list IN VARCHAR2 := NULL) RETURN sys.sqlset PIPELINED; DBMS_SQLTUNE.SELECT_WORKLAOD REPOSITORY ( baseline_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, ranking_measure1 IN VARCHAR2 := NULL, ranking_measure2 IN VARCHAR2 := NULL, ranking_measure3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := 1, result_limit IN NUMBER := NULL) attribute_list IN VARCHAR2 := NULL) RETURN sys.sqlset PIPELINED;
Parameters
Table 101-33 SELECT_WORKLOAD_REPOSITORY Function Parameters
Parameter | Description |
---|---|
begin_snap |
Begin snapshot |
end_snap |
End snapshot |
baseline_name |
The name of the baseline period |
basic_filter |
The SQL predicate to filter the SQL from the workload repository defined on attributes of the SQLSET_ROW |
object_filter |
Specifies the objects that should exist in the object list of selected SQL from the SWRF |
ranking_measure(n) |
An order-by clause on the selected SQL |
result_percentage |
A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given. |
result_limit |
The top L(imit) SQL from the (filtered) source ranked by the ranking measure |
attribute_list |
List of SQL statement attributes to return in the result. The possible values are:
|
Return Values
This function returns a one SQLSET_ROW
per SQL_ID
or PLAN_HASH_VALUE
pair found in each data source.
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; /
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 groupSyntax
DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE ( sql_text IN CLOB, force_match IN BOOLEAN := FALSE) RETURN NUMBER;
Parameters
Table 101-34 SQLTEXT_TO_SIGNATURE Function Parameters
Parameter | Description |
---|---|
sql_text |
SQL text whose signature is required. Required. |
force_match |
If TRUE , this returns a signature that supports SQL matching with literal values transformed into bind variables. If FALSE , returns the signature based on the text with literals not transformed |
Return Values
This function returns the signature of the specified SQL text.
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 groupSyntax
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 101-35 UNPACK_STGTAB_SQLPROF Procedure Parameters
Parameter | Description |
---|---|
profile_name |
The name of the profile to unpack (% wildcards acceptable, case-sensitive) |
profile_category |
The category from which to unpack profiles (% wildcards acceptable, case-sensitive) |
replace |
The option to replace profiles if they already exist. Note that profiles cannot be replaced if one in the staging table has the same name as an active profile in a different SQL statement.If FALSE , this function raises errors if you try to create a profile that already exists |
staging_table_name |
The name of the table on which to perform the remap operation (case-sensitive). Required. |
staging_schema_owner |
The schema where the table resides, or NULL for current schema (case-sensitive) |
Usage Notes
Using this procedure requires the CREATE
ANY
SQL
PROFILE
privilege and the SELECT
privilege on staging table.
Examples
-- Unpack all profiles stored in a staging table EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => FALSE, - staging_table_name => 'PROFILE_STGTAB'); -- If there is a failure during the unpack operation, users can find the profile -- we failed on and perform a remap_stgtab_sqlprof operation targeting it. Then -- they can resume the unpack operation by setting replace to TRUE so that -- the profiles that were already created will just be replaced EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE, - staging_table_name => 'PROFILE_STGTAB');
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 groupSyntax
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET ( sqlset_name IN VARCHAR2 := '%', sqlset_owner IN VARCHAR2 := NULL, replace IN BOOLEAN, staging_table_name IN VARCHAR2, staging_schema_owner IN VARCHAR2 := NULL);
Parameters
Table 101-36 UNPACK_STGTAB_SQLSET Procedure Parameters
Parameter | Description |
---|---|
sqlset_name |
The name of the tuning set to unpack (not NULL). Wildcard characters ('%') are supported to unpack multiple tuning sets in a single call. For example, just specify '%' to unpack all tuning sets from the staging table. |
sqlset_owner |
The name of tuning set owner, or NULL for current schema owner. Wildcards supported. |
replace |
Replaces tuning set if they already exist.If FALSE , raises errors if you try to create a tuning set that already exists |
staging_table_name |
The name of the staging table, moved after a call to the PACK_STGTAB_SQLSET Procedure (case-sensitive) |
staging_schema_owner |
The name of staging table owner, or NULL for current schema owner (case-sensitive) |
Usage Notes
Users can drop the staging table after this procedure completes successfully.
The unpack procedure commits after successfully loading each SQL tuning set. If it fails with one tuning set, no part of that tuning set will have been unpacked, but those which the subprogram had already apprehended will continue to exist.
When failures occur due to SQL tuning set name or owner conflicts, users should use the REMAP_STGTAB_SQLSET Procedure to patch the staging table, and then call this procedure again to unpack those tuning sets that remain.
Examples
-- unpack all STS in the staging table EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name => '%', - sqlset_owner => '%', - replace => FALSE, - staging_table_name => 'STGTAB_SQLSET'); -- errors can arise during STS unpack when a STS in the staging table has the -- same name/owner as STS on the system. In this case, users should call -- remap_stgtab_sqlset to patch the staging table and with which to call unpack -- Replace set to TRUE. EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name => '%', - sqlset_owner => '%', - replace => TRUE, - staging_table_name => 'STGTAB_SQLSET');
This procedure updates selected fields for SQL statement in a SQL tuning set.
See Also:
SQL Tuning Set Subprograms for other subprograms in this groupSyntax
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