170 DBMS_SQLSET
The DBMS_SQLSET
package provides an interface to manage SQL tuning sets.
This package provides the same subprograms, although in some cases with slightly different names, as the SQL tuning set subprograms in DBMS_SQLTUNE
. The difference is that DBMS_SQLSET
does not require the Oracle Tuning Pack.
This chapter contains the following topics:
170.1 DBMS_SQLSET Overview
Use this package to manage SQL tuning sets.
Note:
All DBMS_SQLSET
subprograms have equivalents in the DBMS_SQLTUNE
package.
SQL tuning sets store SQL statements along with the following information:
-
The execution context, such as the parsing schema name and bind values
-
Execution statistics such as average elapsed time and execution count
-
Execution plans, which are the sequence of operations that the database performs to run SQL statements
-
Row source statistics such as the number of rows processed for each operation executed within the plan
You can create SQL tuning sets by filtering or ranking SQL statements from several sources:
-
The shared SQL area using the SELECT_CURSOR_CACHE Function
-
Top SQL statements from the Automatic Workload Repository using the SELECT_WORKLOAD_REPOSITORY Function
-
Other SQL tuning sets using the SELECT_SQLSET Function
-
SQL Performance Analyzer task comparison results using the SELECT_SQLPA_TASK Function
-
SQL Trace files using the SELECT_SQL_TRACE Function
-
A user-defined workload
The complete group of subprograms listed in Summary of DBMS_SQLSET Subprograms facilitates this functionality. As examples:
-
The CREATE_SQLSET Procedure and Function creates a SQL tuning set object in the database.
-
The LOAD_SQLSET Procedure populates the SQL tuning set with a set of selected SQL.
-
The CAPTURE_CURSOR_CACHE Procedure collects SQL statements from the shared SQL area over a specified time interval, attempting to build a realistic picture of database workload.
170.2 DBMS_SQLSET Security Model
This package is available to PUBLIC
and performs its own security checking.
SQL tuning set subprograms 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 that they own. The ADMINISTER ANY SQL TUNING SET
privilege allows users to manipulate all SQL tuning sets, even those owned by other users. For example, you can use CREATE_SQLSET Procedure and Function to create a SQL tuning set to be owned by a different user. In this case, the different user need not have the ADMINISTER SQL TUNING SET
privilege to manipulate the SQL tuning set.
170.3 DBMS_SQLSET Data Structures
The SELECT_*
subprograms in the DBMS_SQLTUNE
package return objects of the SQLSET_ROW
type.
Object Types
170.3.1 SQLSET_ROW Object Type
The SQLSET_ROW
object models the content of a SQL tuning set for the user.
Logically, a SQL tuning set is a collection of SQLSET_ROW
objects. Each SQLSET_ROW
contains a single SQL statement along with its execution context, statistics, binds, and plan. The SELECT_*
subprograms each model a data source as a collection of SQLSET_ROW
objects, with each object uniquely identified by (sql_id
, plan_hash_value
). Similarly, the LOAD_SQLSET
procedure takes as input a cursor whose row type is SQLSET_ROW
, treating each SQLSET_ROW
in isolation according to the policies requested by the user.
Several subprograms package accept basic filters on the content of a SQL tuning set or data source. These filters are expressed in terms of the attributes within the SQLSET_ROW
as defined.
Syntax
CREATE TYPE sqlset_row AS object (
sql_id VARCHAR(13),
force_matching_signature NUMBER,
sql_text CLOB,
object_list sql_objects,
bind_data RAW(2000),
parsing_schema_name VARCHAR2(30),
module VARCHAR2(48),
action VARCHAR2(32),
elapsed_time NUMBER,
cpu_time NUMBER,
buffer_gets NUMBER,
disk_reads NUMBER,
direct_writes NUMBER,
rows_processed NUMBER,
fetches NUMBER,
executions NUMBER,
end_of_fetch_count NUMBER,
optimizer_cost NUMBER,
optimizer_env RAW(2000),
priority NUMBER,
command_type NUMBER,
first_load_time VARCHAR2(19),
stat_period NUMBER,
active_stat_period NUMBER,
other CLOB,
plan_hash_value NUMBER,
sql_plan sql_plan_table_type,
bind_list sql_binds,
con_dbid NUMBER,
last_exec_start_time VARCHAR2(19))
Attributes
Table 170-1 SQLSET_ROW Attributes
Attribute | Description |
---|---|
|
Unique SQL ID. |
|
Signature with literals, case, and whitespace removed. |
|
Full text for the SQL statement. |
|
Currently not implemented. |
|
Bind data as captured for this SQL. Note that you cannot stipulate an argument for this parameter and also for |
|
Schema where the SQL is parsed. |
|
Last application module for the SQL. |
|
Last application action for the SQL. |
|
Sum total elapsed time for this SQL statement. |
|
Sum total CPU time for this SQL statement. |
|
Sum total number of buffer gets. |
|
Sum total number of disk reads. |
|
Sum total number of direct path writes. |
|
Sum total number of rows processed by this SQL. |
|
Sum total number of fetches. |
|
Total executions of this SQL statement. |
|
Number of times the SQL statement was fully executed with all of its rows fetched. |
|
Optimizer cost for this SQL. |
|
Optimizer environment for this SQL statement. |
|
User-defined priority (1,2,3). |
|
Statement type, such as |
|
Load time of the parent cursor. |
|
Period of time (seconds) when the statistics of this SQL statement were collected. |
|
Effective period of time (in seconds) during which the SQL statement was active. |
|
Other column for user-defined attributes. |
|
Plan hash value of the plan. |
|
Execution plan for the SQL statement. |
|
List of user-specified binds for the SQL statement. This is used for user-specified workloads. Note that you cannot stipulate an argument for this parameter and also for |
|
DBID of the PDB or CDB root. |
|
Most recent execution start time of this SQL statement. |
170.4 Summary of DBMS_SQLSET Subprograms
This table lists the DBMS_SQLSET
subprograms and briefly describes them.
Table 170-2 DBMS_SQLSET Package Subprograms
Subprogram | Description |
---|---|
This procedure adds a new reference to an existing SQL tuning set to indicate its use by a client. |
|
This procedure captures a workload from the shared SQL area into a SQL tuning set. |
|
This procedure or function creates a SQL tuning set object in the database. |
|
This procedure creates a staging table through which SQL tuning sets are imported and exported. |
|
This procedure deletes a set of SQL statements from a SQL tuning set. |
|
This procedure drops a SQL tuning set if it is not active. |
|
This procedure populates the SQL tuning set with a set of selected SQL statements. You can call the procedure multiple times to add new SQL statements or replace attributes of existing statements. |
|
This procedure copies one or more SQL tuning sets from their location in the |
|
This procedure changes the tuning set names and owners in the staging table so that they can be unpacked with different values. |
|
This procedure deactivates a SQL tuning set to indicate that it is no longer used by the client. |
|
This function collects SQL statements from the workload repository. |
|
This table function reads the content of one or more trace files and returns the SQL statements it finds in the format of |
|
This function collects SQL statements from a SQL Performance Analyzer comparison task. |
|
This is a table function that reads the contents of a SQL tuning set. |
|
This function collects SQL statements from the workload repository. |
|
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. |
|
This overloaded procedure updates selected fields for SQL statements in a SQL tuning set. |
170.4.1 ADD_REFERENCE Function
This procedure adds a new reference to an existing SQL tuning set to indicate its use by a client.
Syntax
DBMS_SQLSET.ADD_REFERENCE (
sqlset_name IN VARCHAR2,
description IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 :=NULL)
RETURN NUMBER;
Parameters
The parameters are identical for
DBMS_SQLTUNE.ADD_SQLSET_REFERENCE
and
DBMS_SQLSET.ADD_REFERENCE
.
Table 170-3 ADD_SQLSET_REFERENCE and ADD_REFERENCE Function Parameters
Parameter | Description |
---|---|
|
Specifies the name of the SQL tuning set. |
|
Provides an optional description of the usage of SQL tuning set. The description is truncated if longer than 256 characters. |
|
Specifies the owner of the SQL tuning set, or |
Return Values
The identifier of the added reference.
Usage Notes
Adding a reference to a SQL tuning set prevents the tuning set from being modified while it is being used. Invoking SQL Tuning Advisor on the SQL tuning set adds a reference automatically, so use ADD_REFERENCE
only when the automatically generated reference is not sufficient. The ADD_REFERENCE
function returns a reference ID that you can later supply to the REMOVE_SQLSET_REFERENCE
procedure. Query the DBA_SQLSET_REFERENCES
view to find all references to a specified SQL tuning set.
Examples
This example generates a reference to the SQL tuning set named my_workload
and stores it in the b_rid
variable.
VARIABLE b_rid NUMBER;
EXEC :b_rid := DBMS_SQLSET.ADD_REFERENCE(sqlset_name => 'my_workload', description => 'my sts ref');
170.4.2 CAPTURE_CURSOR_CACHE Procedure
This procedure captures a workload from the shared SQL area into a SQL tuning set.
The procedure polls the cache multiple times over a time period, and updates the workload data stored there. It can execute over as long a period as required to capture an entire system workload.
Syntax
DBMS_SQLSET.CAPTURE_CURSOR_CACHE (
sqlset_name IN VARCHAR2,
time_limit IN POSITIVE := 1800,
repeat_interval IN POSITIVE := 300,
capture_option IN VARCHAR2 := 'MERGE',
capture_mode IN NUMBER := MODE_REPLACE_OLD_STATS,
basic_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL,
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL);
Parameters
The parameters are the same for both
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET
and
DBMS_SQLSET.CAPTURE_CURSOR_CACHE
.
Table 170-4 CAPTURE_CURSOR_CACHE_SQLSET and CAPTURE_CURSOR_CACHE Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the SQL tuning set name |
|
Defines the total amount of time, in seconds, to execute. |
|
Defines the amount of time, in seconds, to pause between sampling. |
|
Specifies whether to insert new statements, update existing statements, or both. Values are |
|
Specifies the capture mode (
|
|
Defines a filter to apply to the shared SQL area for each sample. If |
|
Specifies the owner of the SQL tuning set or |
|
Defines a filter that includes recursive SQL in the SQL tuning set
( |
Examples
In this example capture takes place over a 30-second period, polling the cache once every five seconds. This captures all statements run during that period but not before or after. If the same statement appears a second time, the process replaces the stored statement with the new occurrence.
Note that in production systems the time limit and repeat interval would be set much higher. You should tune the time_limit
and repeat_interval
parameters based on the workload time and shared SQL area turnover properties of your system.
EXEC DBMS_SQLSET.CAPTURE_CURSOR_CACHE( -
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_SQLSET.CAPTURE_CURSOR_CACHE( -
sqlset_name => 'my_workload', -
time_limit => 30, -
repeat_interval => 5, -
capture_mode => DBMS_SQLSET.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_SQLSET.CAPTURE_CURSOR_CACHE( -
sqlset_name => 'my_workload', -
time_limit => 30, -
repeat_interval => 5, -
capture_option => 'INSERT');
170.4.3 CREATE_SQLSET Procedure and Function
This procedure or function creates a SQL tuning set object in the database.
Syntax
DBMS_SQLSET.CREATE_SQLSET (
sqlset_name IN VARCHAR2,
description IN VARCHAR2 := NULL
sqlset_owner IN VARCHAR2 := NULL);
DBMS_SQLSET.CREATE_SQLSET (
sqlset_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
Parameters
Table 170-5 CREATE_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the created SQL tuning set. The name is the name passed to the function. If no name is passed to the function, then the function generates an automatic name. |
|
Provides an optional description of the SQL tuning set. |
|
Specifies the owner of the SQL tuning set, or |
Return Values
Specifies the name of the created SQL tuning set. The name is the name passed to the function. If no name is passed to the function, then the function generates an automatic name.
Examples
EXEC DBMS_SQLSET.CREATE_SQLSET(-
sqlset_name => 'my_workload', -
description => 'complete application workload');
170.4.4 CREATE_STGTAB Procedure
This procedure creates a staging table through which SQL tuning sets are imported and exported.
Syntax
DBMS_SQLSET.CREATE_STGTAB (
table_name IN VARCHAR2,
schema_name IN VARCHAR2 := NULL,
tablespace_name IN VARCHAR2 := NULL,
db_version IN NUMBER := NULL);
Parameters
Table 170-6 CREATE_STGTAB_SQLSET and CREATE_STGTAB Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the of the table to create. The name is case sensitive. |
|
Defines the schema in which to create the table, or
|
|
Specifies the tablespace in which to store the staging table, or
|
|
Specifies the database version that determines the format of the staging table. You can also create an older database version staging table to export an STS to an older database version. Use one of the following values:
|
Examples
Create a staging table for packing and eventually exporting a SQL tuning sets:
EXEC DBMS_SQLSET.CREATE_STGTAB(table_name => 'STGTAB_SQLSET');
Create a staging table to pack a SQL tuning set in Oracle Database 12c Release 1 (12.1.0.2) format:
BEGIN
DBMS_SQLSET.CREATE_STGTAB(
table_name => 'STGTAB_SQLSET_121'
, db_version => DBMS_SQLSET.STS_STGTAB_12_1_VERSION );
END;
170.4.5 DELETE_SQLSET Procedure
This procedure deletes a set of SQL statements from a SQL tuning set.
Syntax
DBMS_SQLSET.DELETE_SQLSET (
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL);
Parameters
Table 170-7 DELETE_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the SQL tuning set. |
|
Specifies the SQL predicate to filter the SQL from the SQL tuning set. This basic filter is used as a where clause on the SQL tuning set content to select a desired subset of SQL from the SQL tuning set. |
|
Specifies the owner of the SQL tuning set, or |
Examples
-- Delete all statements in a sql tuning set.
EXEC DBMS_SQLSET.DELETE_SQLSET(sqlset_name => 'my_workload');
-- Delete all statements in a sql tuning set which ran for less than a second
EXEC DBMS_SQLSET.DELETE_SQLSET(sqlset_name => 'my_workload', -
basic_filter => 'elapsed_time < 1000000');
170.4.6 DROP_SQLSET Procedure
This procedure drops a SQL tuning set if it is not active.
Syntax
DBMS_SQLSET.DROP_SQLSET (
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL);
Parameters
Table 170-8 DROP_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the SQL tuning set. |
|
Specifies the owner of the SQL tuning set, or |
Usage Notes
You cannot drop a SQL tuning set when it is referenced by one or more clients.
Examples
-- Drop the sqlset.
EXEC DBMS_SQLSET.DROP_SQLSET ('my_workload');
170.4.7 LOAD_SQLSET Procedure
This procedure populates the SQL tuning set with a set of selected SQL statements. You can call the procedure multiple times to add new SQL statements or replace attributes of existing statements.
Syntax
DBMS_SQLSET.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 170-9 LOAD_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of SQL tuning set to be loaded. |
|
Specifies the cursor reference to the SQL tuning set to be loaded. |
|
Specifies which statements are loaded into the SQL tuning set. The possible values are:
|
|
Specifies how existing SQL statements are updated. This parameter is considered only if
|
|
Specifies when to perform the update. The procedure only performs the update when the specified condition is satisfied. The condition can refer to either the data source or destination. The condition must use the following prefixes to refer to attributes from the source or the destination:
|
|
Specifies the list of SQL statement attributes to update during a merge or update. The possible values are:
|
|
Specifies whether to update attributes when the new value is
If |
|
Specifies whether to commit statements after DML. If a value is provided, then the load commits after each specified
number of statements is inserted. If Providing a value for this argument enables you to monitor the
progress of a SQL tuning set load operation in the |
|
Defines the owner of the SQL tuning set, or the current schema owner
(or |
Exceptions
-
This procedure returns an error when
sqlset_name
is invalid, or a corresponding SQL tuning set does not exist, or thepopulate_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 shared SQL area statements with an elapsed time of 5 seconds or more, excluding statements that belong to SYS
schema. You select all attributes of the SQL statements and load them in the tuning set using the default mode. The default mode loads only new statements because the SQL tuning set is empty.
-- create the tuning set
EXEC DBMS_SQLSET.CREATE_SQLSET('my_workload');
-- populate the tuning set from the shared SQL area
DECLARE
cur DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLSET.SELECT_CURSOR_CACHE(
'parsing_schema_name <> ''SYS'' AND elapsed_time > 5000000',
NULL, NULL, NULL, NULL, 1, NULL,
'ALL')) P;
DBMS_SQLSET.LOAD_SQLSET(sqlset_name => 'my_workload',
populate_cursor => cur);
END;
/
Now you want 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 have 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_SQLSET.SQLSET_CURSOR;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(1,2,
'parsing_schema_name <> ''SYS''',
NULL, NULL,NULL,NULL,
1,
NULL,
'ALL')) P;
DBMS_SQLSET.LOAD_SQLSET(sqlset_name => 'my_workload',
populate_cursor => cur,
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_SQLSET.SELECT_WORKLOAD_REPOSITORY(1,2)) P;
DBMS_SQLSET.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_SQLSET.SELECT_CURSOR_CACHE) P;
DBMS_SQLSET.LOAD_SQLSET(sqlset_name => 'my_workload',
populate_cursor => cur,
load_option => 'UPDATE');
END;
/
170.4.8 PACK_STGTAB Procedure
This procedure copies one or more SQL tuning sets from their location in the SYS
schema to a staging table created by the CREATE_STGTAB
procedure.
Syntax
DBMS_SQLSET.PACK_STGTAB (
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL,
staging_table_name IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL,
db_version IN NUMBER := NULL);
Parameters
The parameters are identical for the
DBMS_SQLTUNE.PACK_STGTAB_SQLSET
and
DBMS_SQLSET.PACK_STGTAB
procedures.
Table 170-10 PACK_STGTAB_SQLSET and PACK_STGTAB Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the SQL tuning set to pack. The name is case
sensitive. Wildcard characters ( |
|
Specifies the category from which to pack SQL tuning sets. The name
is case sensitive. Wildcard characters ( |
|
Specifies the name of the table to use. The value is case sensitive. |
|
Specifies the schema where the table resides, or
|
|
Specifies the database version that determines the format of the staging table. You can also create an older database version staging table to export an STS to an older database version. Use any of the following values:
|
Usage Notes
-
To move more than one SQL tuning set, call this procedure multiple times. You can then move the populated staging table to a destination database using any method, such as a database link or Oracle Data Pump, and then unpack the SQL tuning set in the destination database.
-
This function issues a
COMMIT
after packing each SQL tuning set. If an error is raised mid-execution, then clear the staging table by deleting its rows.
Examples
Put all SQL tuning sets on the database in the staging table:
BEGIN
DBMS_SQLSET.PACK_STGTAB(
sqlset_name => '%'
, sqlset_owner => '%'
, staging_table_name => 'STGTAB_SQLSET');
END;
Put only those SQL tuning sets owned by the current user in the staging table:
BEGIN
DBMS_SQLSET.PACK_STGTAB(
sqlset_name => '%'
, staging_table_name => 'STGTAB_SQLSET');
END;
Pack a specific SQL tuning set:
BEGIN
DBMS_SQLSET.PACK_STGTAB(
sqlset_name => 'my_workload'
, staging_table_name => 'STGTAB_SQLSET');
END;
Pack a second SQL tuning set:
BEGIN
DBMS_SQLSET.PACK_STGTAB(
sqlset_name => 'workload_subset'
, staging_table_name => 'STGTAB_SQLSET');
END;
Pack the STS my_workload_subset
into a staging table stgtab_sqlset
created for Oracle Database 11g Release 2 (11.2):
BEGIN
DBMS_SQLSET.PACK_STGTAB(
sqlset_name => 'workload_subset'
, staging_table_name => 'STGTAB_SQLSET'
, db_version => DBMS_SQLSET.STS_STGTAB_11_2_VERSION);
END;
170.4.9 REMAP_STGTAB Procedure
This procedure changes the tuning set names and owners in the staging table so that they can be unpacked with different values.
Syntax
DBMS_SQLSET.REMAP_STGTAB (
old_sqlset_name IN VARCHAR2,
old_sqlset_owner IN VARCHAR2 := NULL,
new_sqlset_name IN VARCHAR2 := NULL,
new_sqlset_owner IN VARCHAR2 := NULL,
staging_table_name IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL
old_con_dbid IN NUMBER := NULL,
new_con_dbid IN NUMBER := NULL);
);
Parameters
The parameters are identical for the DBMS_SQLTUNE.REMAP_STGTAB_SQLSET and DBMS_SQLSET.REMAP_SQLSET procedures.
Table 170-11 REMAP_STGTAB_SQLSET and REMAP_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the tuning set to target for a remap operation.
Wildcard characters ( |
|
Specifies the new name of the tuning set owner to target for a remap
operation. |
|
Specifies the new name for the tuning set, or |
|
Specifies the new owner for the tuning set, or |
|
Specifies the name of the table on which to perform the remap operation. The value is case sensitive. |
|
Specifies the name of staging table owner, or |
|
Specifies the old container DBID to be remapped to a new container DBID. Specify |
|
Specifies the new container DBID to replace with the old container DBID. Specify |
Usage Notes
Call this procedure multiple times to remap more than one tuning set name or owner. This procedure only handles one tuning set per call.
Examples
-- Change the name of an STS in the staging table before unpacking it.
BEGIN
DBMS_SQLSET.REMAP_STGTAB(
old_sqlset_name => 'my_workload'
, old_sqlset_owner => 'SH'
, new_sqlset_name => 'imp_workload'
, staging_table_name => 'STGTAB_SQLSET');
-- Change the owner of an STS in the staging table before unpacking it.
DBMS_SQLSET.REMAP_STGTAB(
old_sqlset_name => 'imp_workload'
, old_sqlset_owner => 'SH'
, new_sqlset_owner => 'SYS'
, staging_table_name => 'STGTAB_SQLSET');
END;
170.4.10 REMOVE_REFERENCE Procedure
This procedure deactivates a SQL tuning set to indicate that it is no longer used by the client.
Syntax
DBMS_SQLSET.REMOVE_REFERENCE (
sqlset_name IN VARCHAR2,
reference_id IN NUMBER,
sqlset_owner IN VARCHAR2 := NULL,
force_remove IN NUMBER := 0);
Parameters
The parameters are identical for the
DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE
and
DBMS_SQLSET.REMOVE_REFERENCE
procedures.
Table 170-12 REMOVE_SQLSET_REFERENCE and REMOVE_REFERENCE Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the SQL tuning set. |
|
Specifies the identifier of the reference to remove. |
|
Specifies the owner of the SQL tuning set (or |
|
Specifies whether references can be removed for other users
( Setting this parameter to |
Examples
You can remove references on a given SQL tuning set when you finish using it and want to make it writable again. The following example removes the reference to my_workload
:
EXEC DBMS_SQLSET.REMOVE_REFERENCE(sqlset_name => 'my_workload', -
reference_id => :rid);
Use the DBA_SQLSET_REFERENCES
view to find all references to a given SQL tuning set.
170.4.11 SELECT_CURSOR_CACHE Function
This function collects SQL statements from the shared SQL area.
Syntax
DBMS_SQLSET.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 := 'TYPICAL',
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;
Parameters
Table 170-13 SELECT_CURSOR_CACHE Function Parameters
Parameter | Description |
---|---|
|
Specifies the SQL predicate that filters the SQL from the shared SQL
area defined on attributes of the If |
|
Currently not supported. |
|
Defines an |
|
Specifies a filter that picks the top n% according to the supplied ranking measure. The value applies only if one ranking measure is supplied. |
|
Defines the top limit SQL from the filtered source ranked by the ranking measure. |
|
Specifies the list of SQL statement attributes to return in the result. Possible values are:
|
|
Specifies that the filter must include recursive SQL in the SQL
tuning set ( |
Return Values
This function returns a one SQLSET_ROW
per
SQL_ID
or PLAN_HASH_VALUE
pair found in each data
source.
Usage Notes
-
Filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.
-
Users need privileges on the shared SQL area views.
Example 170-1 Statements with 500 or More Buffer Gets
This query obtains the SQL IDs and SQL text for statements with 500 buffer gets:
SELECT SQL_ID, SQL_TEXT
FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY sql_id;
Example 170-2 All Information About a Statement
The following query obtains all information about the SQL statement with the SQL ID 4rm4183czbs7j
:
SELECT * FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j'''));
Example 170-3 Multiple Plans for a SQL Statement
A data source may store multiple plans for each SQL statement. The output of the SELECT_CURSOR_CACHE
function is a SQL row set object that is uniquely identified by SQL ID and plan hash value. This example queries the plan hash values for the statement with the SQL ID ay1m3ssvtrh24
:
SELECT sql_id, plan_hash_value
FROM table(DBMS_SQLSET.select_cursor_cache('sql_id = ''ay1m3ssvtrh24'''))
ORDER BY sql_id, plan_hash_value;
Example 170-4 Processing All Statements in the Shared SQL Area
This example processes all statements in the shared SQL area:
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(p)
FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE) p;
-- Process each statement in cursor (or pass cursor to load_sqlset).
CLOSE cur;
END;
/
Example 170-5 Process Statements Not Parsed by SYS
This example processes all statements not parsed in the SYS
schema:
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur for
SELECT VALUE(p)
FROM TABLE(
DBMS_SQLSET.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''')) p;
-- Process each statement (or pass cursor to load_sqlset).
CLOSE cur;
end;
/
Example 170-6 All Statements from an Application Module and Action
This example processes all statements from a specified application module and action:
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(p)
FROM TABLE(
DBMS_SQLSET.SELECT_CURSOR_CACHE(
'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) p;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
END;/
Example 170-7 All Statements Whose Elapsed Time Is At Least Five Seconds
This example processes all statements that ran for at least five seconds:
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(DBMS_SQLSET.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
END;
/
Example 170-8 Statements Parsed in the APPS Schema
This example processes all SQL statements that were parsed in the APPS
schema and had more than 100 buffer gets:
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(p)
FROM TABLE(
DBMS_SQLSET.SELECT_CURSOR_CACHE(
'buffer_gets > 100 and parsing_schema_name = ''APPS'''))p;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
END;
/
Example 170-9 Plans and SQL Statements
This example processes all SQL statements exceeding 5 seconds. It also selects the plans for these statements. For performance reasons, the example selects execution statistics and SQL binds. The SQL_PLAN
attribute of sqlset_row
is NULL
.
-- 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_SQLSET.SELECT_CURSOR_CACHE(
basic_filter => 'elapsed_time > 5000000',
object_filter => NULL,
ranking_measure1 => NULL,
ranking_measure2 => NULL,
ranking_measure3 => NULL,
result_percentage => 1,
result_limit => NULL,
attribute_list => 'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN',
recursive_sql => HAS_RECURSIVE_SQL)) p;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
END;/
Example 170-10 Top 100 Statements Ordered by Elapsed Time
This example selects the top 100 statements in the shared SQL area, ordered by elapsed time:
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(p)
FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE(
basic_filter => NULL,
object_filter => NULL,
ranking_measure_1 => 'ELAPSED_TIME',
ranking_measure_2 => NULL,
ranking_measure_3 => NULL,
result_percentage => 1,
result_limit => 100,
attribute_list => 'TYPICAL',
recursive_sql => HAS_RECURSIVE_SQL))) p;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
END;
/
Example 170-11 Statements Responsible for Most Buffer Gets
This example processes statements that cumulatively account for 90% of the buffer gets in the shared SQL area. The buffer gets of all statements added together is approximately 90% of the sum of all statements currently in the shared SQL area.
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(DBMS_SQLSET.SELECT_CURSOR_CACHE(
basic_filter => NULL,
object_filter => NULL,
ranking_measure_1 => 'BUFFER_GETS',
ranking_measure_2 => NULL,
ranking_measure_3 => NULL,
result_percentage => .9,
result_limit => NULL,
attribute_list => 'TYPICAL',
recursive_sql => HAS_RECURSIVE_SQL))) p;
-- Process each statement (or pass cursor to load_sqlset).
CLOSE cur;
END;
/
170.4.12 SELECT_SQL_TRACE Function
This table function reads the content of one or more trace files and returns the SQL statements it finds in the format of sqlset_row
.
Syntax
DBMS_SQLSET.SELECT_SQL_TRACE (
directory IN VARCHAR2,
file_name IN VARCHAR2 := NULL,
mapping_table_name IN VARCHAR2 := NULL,
mapping_table_owner IN VARCHAR2 := NULL,,
select_mode IN POSITIVE := SINGLE_EXECUTION,
options IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
pattern_start IN VARCHAR2 := NULL,
pattern_end IN VARCHAR2 := NULL,
result_limit IN POSITIVE := NULL)
RETURN sys.sqlset PIPELINED;
Parameters
Table 170-14 SELECT_SQL_TRACE Function Parameters
Parameter | Description |
---|---|
|
Defines the directory object containing the trace files. This field is mandatory. |
|
Specifies all or part of the name of the trace files. If |
|
Specifies the mapping table name. Note that the mapping table name is case insensitive. If the mapping
table name is |
|
Specifies the mapping table owner. If it is |
|
Specifies the mode for selecting SQL from the trace. Possible values are:
|
|
Specifies which types of SQL statements are returned.
|
|
Specifies the delimiting pattern of the trace file sections to consider. CURRENTLY INOPERABLE. |
|
Specifies the closing delimiting pattern of the trace file sections to process. CURRENTLY INOPERABLE. |
|
Specifies the top SQL from the filtered source. Default to
|
Return Values
This function returns a SQLSET_ROW
object.
Usage Notes
The ability to create a
directory object for the system directory creates a potential security issue. For example,
in a CDB, all containers write trace files to the same directory. A local user with
SELECT
privileges on this directory can read the contents of trace files
belonging to any container.
To prevent this type of
unauthorized access, copy the files from the default SQL trace directory into a different
directory, and then create a directory object. Use the PATH_PREFIX
clause
of the CREATE PLUGGABLE DATABASE
statement to ensure that all directory
object paths associated with the PDB are restricted to the specified directory or its
subdirectories.
Examples
The following code shows how to enable SQL trace for a few SQL statements and load the results into a SQL tuning set:
-- turn on the SQL trace in the capture database
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'
-- run sql statements
SELECT 1 FROM DUAL;
SELECT COUNT(*) FROM dba_tables WHERE table_name = :mytab;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
-- create mapping table from the capture database
CREATE TABLE mapping AS
SELECT object_id id, owner, substr(object_name, 1, 30) name
FROM dba_objects
WHERE object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT',
'FUNCTION', 'INDEXTYPE', 'JAVA CLASS',
'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY',
'LOB', 'OPERATOR', 'PACKAGE',
'PACKAGE BODY', 'PROCEDURE', 'QUEUE',
'RESOURCE PLAN', 'TRIGGER', 'TYPE',
'TYPE BODY')
UNION ALL
SELECT user_id id, username owner, NULL name
FROM dba_users;
-- create the directory object where the SQL traces are stored
CREATE DIRECTORY SQL_TRACE_DIR as '/home/foo/trace';
-- create the STS
EXEC DBMS_SQLSET.CREATE_SQLSET('my_sts', 'test purpose');
-- load the SQL statements into STS from SQL TRACE
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT value(p)
FROM TABLE(
DBMS_SQLSET.SELECT_SQL_TRACE(
directory=>'SQL_TRACE_DIR',
file_name=>'%trc',
mapping_table_name=>'mapping')) p;
DBMS_SQLSET.LOAD_SQLSET('my_sts', cur);
CLOSE cur;
END;
/
See Also:
Oracle Database SQL Language Reference to
learn more about the PATH_PREFIX
clause
170.4.13 SELECT_SQLPA_TASK Function
This function collects SQL statements from a SQL Performance Analyzer comparison task.
See Also:
Oracle Database Testing Guide for a SELECT_SQLPA_TASK
example
Syntax
DBMS_SQLSET.SELECT_SQLPA_TASK(
task_name IN VARCHAR2,
task_owner IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL,
level_filter IN VARCHAR2 := 'REGRESSED',
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
attribute_list IN VARCHAR2 := 'TYPICAL')
RETURN sys.sqlset PIPELINED;
Parameters
Table 170-15 SELECT_SQLPA_TASK Function Parameters
Parameter | Description |
---|---|
|
Specifies the name of the SQL Performance Analyzer task. |
|
Specifies the owner of the SQL Performance Analyzer task. If
|
|
Specifies the name of the SQL Performance Analyzer task execution
(type |
|
Specifies which subset of SQL statements to include. Same format as
|
|
Specifies the SQL predicate to filter the SQL in addition to the level filters. |
|
Currently not supported. |
|
Defines the SQL statement attributes to return in the result. Possible values are:
|
Return Values
This function returns a SQL tuning set object.
Usage Notes
For example, you can use this function to create a SQL tuning set containing the subset of SQL statements that regressed during a SQL Performance Analyzer (SPA) experiment. You can also specify other arbitrary filters.
170.4.14 SELECT_SQLSET Function
This is a table function that reads the contents of a SQL tuning set.
Syntax
DBMS_SQLSET.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 := 'TYPICAL',
plan_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL,
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;
Parameters
Table 170-16 SELECT_SQLSET Function Parameters
Parameter | Description |
---|---|
|
Specifies the name of the SQL tuning set to query. |
|
Specifies the SQL predicate to filter the SQL from the SQL tuning
set defined on attributes of the |
|
Currently not supported. |
|
Specifies an |
|
Specifies a filter that picks the top n% according to the supplied ranking measure. Note that this parameter applies only if one ranking measure is supplied. |
|
The top limit SQL from the filtered source, ranked by the ranking measure. |
|
Defines the SQL statement attributes to return in the result. The possible values are:
|
|
Specifies the plan filter. This parameter enables you to select a single plan when a statement has multiple plans. Possible values are:
|
|
Specifies the owner of the SQL tuning set, or |
|
Specifies that the filter must include recursive SQL in the SQL
tuning set ( |
Return Values
This function returns one SQLSET_ROW
per
SQL_ID
or PLAN_HASH_VALUE
pair found in each data
source.
Usage Notes
Filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.
Examples
-- select from a sql tuning set
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE (P)
FROM table(DBMS_SQLSET.SELECT_SQLSET('my_workload')) P;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
END;
/
170.4.15 SELECT_WORKLOAD_REPOSITORY Function
This function collects SQL statements from the workload repository.
The overloaded forms enable you to collect SQL statements from the following sources:
-
Snapshots between
begin_snap
andend_snap
-
A workload repository baseline
Syntax
DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY (
begin_snap IN NUMBER,
end_snap IN NUMBER,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
ranking_measure1 IN VARCHAR2 := NULL,
ranking_measure2 IN VARCHAR2 := NULL,
ranking_measure3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := 1,
result_limit IN NUMBER := NULL,
attribute_list IN VARCHAR2 := 'TYPICAL',
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL,
dbid IN NUMBER := NULL)
RETURN sys.sqlset PIPELINED;
DBMS_SQLSET.SELECT_WORKLOAD REPOSITORY (
baseline_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
ranking_measure1 IN VARCHAR2 := NULL,
ranking_measure2 IN VARCHAR2 := NULL,
ranking_measure3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := 1,
result_limit IN NUMBER := NULL,
attribute_list IN VARCHAR2 := 'TYPICAL',
recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL,
dbid IN NUMBER := NULL)
RETURN sys.sqlset PIPELINED;
Parameters
Table 170-17 SELECT_WORKLOAD_REPOSITORY Function Parameters
Parameter | Description |
---|---|
|
Defines the beginning AWR snapshot (non-inclusive). |
|
Defines the ending AWR snapshot (inclusive). |
|
Specifies the name of the AWR baseline period. |
|
Specifies the SQL predicate to filter the SQL from the workload
repository. The filter is defined on attributes of the
If |
|
Currently not supported. |
|
Defines an |
|
Specifies a filter that picks the top n% according to the supplied ranking measure. Note that this percentage applies only if one ranking measure is given. |
|
Specifies the top limit SQL from the source according to the supplied ranking measure. |
|
Specifies the SQL statement attributes to return in the result. The possible values are:
|
|
Specifies the filter that includes recursive SQL in the SQL tuning
set ( |
|
Specifies the DBID for imported or PDB-level AWR data. If
|
Return Values
This function returns one SQLSET_ROW
per
SQL_ID
or PLAN_HASH_VALUE
pair found in each data
source.
Usage Notes
Filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.
Examples
-- select statements from snapshots 1-2
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE (P)
FROM table(DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(1,2)) P;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
END;
/
170.4.16 UNPACK_STGTAB Procedure
This procedure copies one or more SQL tuning sets from their location in the staging table into the SQL tuning sets schema, making them proper SQL tuning sets.
Syntax
DBMS_SQLSET.UNPACK_STGTAB (
sqlset_name IN VARCHAR2 := '%',
sqlset_owner IN VARCHAR2 := NULL,
replace IN BOOLEAN,
staging_table_name IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);
Parameters
The parameters are identical for
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET
and
DBMS_SQLSET.UNPACK_STGTAB
.
Table 170-18 UNPACK_STGTAB_SQLSET and UNPACK_STGTAB Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the tuning set to unpack (not null). Wildcard characters ( |
|
Specifies the name of tuning set owner, or |
|
Specifies whether to replace an existing SQL tuning set. If
|
|
Specifies the name of the staging table, moved after a call to the
|
|
Specifies the name of staging table owner, or |
Examples
-- unpack all STS in the staging table
EXEC DBMS_SQLSET.UNPACK_STGTAB(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_SQLSET.UNPACK_STGTAB(sqlset_name => '%', -
sqlset_owner => '%', -
replace => TRUE, -
staging_table_name => 'STGTAB_SQLSET');
170.4.17 UPDATE_SQLSET Procedures
This overloaded procedure updates selected fields for SQL statements in a SQL tuning set.
Syntax
DBMS_SQLSET.UPDATE_SQLSET (
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
attribute_name IN VARCHAR2,
attribute_value IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL);
DBMS_SQLSET.UPDATE_SQLSET (
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
attribute_name IN VARCHAR2,
attribute_value IN NUMBER := NULL,
sqlset_owner IN VARCHAR2 := NULL);
Parameters
Table 170-19 UPDATE_SQLSET Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the SQL tuning set. |
|
Specifies the identifier of the SQL statement to be updated. |
|
Specifies the hash value of the execution plan for a SQL statement. Use this parameter when you want to update the attribute for a specific plan for a statement, but not all plans for the statement. |
|
Specifies the name of the attribute to be modified. You can update the text field for If a statement has multiple plans, then the procedure changes the attribute value for all plans. |
|
Specifies the new value of the attribute. |