ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
The DBMS_SQLTUNE 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.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
Capture Section Constants
MODE_REPLACE_OLD_STATS
NUMBER
dbms_sqltune.MODE_REPLACE_OLD_STATS
MODE_ACCUMULATE_STATS
NUMBER
dbms_sqltune.MODE_ACCUMULATE_STATS
SQL Tuning Set Constants
ALL_COMMAND_TYPE
BINARY_INTEGER
dbms_sqltune.ALL_COMMAND_TYPE
ALL_EXECUTIONS
POSITIVE
dbms_sqltune.ALL_EXECUTIONS
LIMITED_COMMAND_TYPE
BINARY_INTEGER
dbms_sqltune.LIMITED_COMMAND_TYPE
SINGLE_EXECUTION
POSITIVE
dbms_sqltune.SINGLE_EXECUTION
SQLSET Staging Table Constants
STS_STGTAB_10_2_VERSION
NUMBER
dbms_sqltune.STS_STGTAB_10_2_VERSION
STS_STGTAB_11_1_VERSION
NUMBER
dbms_sqltune.STS_STGTAB_11_1_VERSION
STS_STGTAB_11_2_VERSION
NUMBER
dbms_sqltune.STS_STGTAB_11_2_VERSION
STS_STGTAB_11_202_VERSION
NUMBER
dbms_sqltune.STS_STGTAB_11_202_VERSION
STS_STGTAB_12_1_VERSION
NUMBER
dbms_sqltune.STS_STGTAB_12_1_VERSION
STS_STGTAB_12_2_VERSION
NUMBER
dbms_sqltune.STS_STGTAB_12_2_VERSION
Recursive SQL Filters
NO_RECURSIVE_SQL
VARCHAR2(30)
dbms_sqltune.NO_RECURSIVE_SQL
HAS_RECURSIVE_SQL
VARCHAR2(30)
dbms_sqltune.HAS_RECURSIVE_SQL
Data Types
TYPE sqlset_cursor IS REF CURSOR;
Dependencies
DBMS_SQLTUNE
SQLSET
SQLSET_ROW
Documented
Yes
First Available
18.1
Pragma
PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE)
Security Model
Owned by SYS with EXECUTE granted to PUBLIC.
The library recommends revoking the grant to PUBLIC. Every connected user does not need access to this functionality.
GRANT EXECUTE to specific roles or users based on a need to perform diagnostic work and tuning work.
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.
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);
Creates a staging table through which SQL tuning sets are imported and exported
dbms_sqlset.create_stgtab(
table_name IN VARCHAR2,
schema_name IN VARCHAR2 := NULL,
tablespace_name IN VARCHAR2 := NULL,
db_version IN NUMBER := NULL);
Drops one or multiple STS with given prefix, not active, and older than two weeks
dbms_sqlset.drop_sqlset_by_prefix(
sqlset_prefix IN VARCHAR2,
expiration_days IN NUMBER := 14,
time_limit IN NUMBER := sys.dbms_sqltune.time_limit_sec_drop_sts);
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);
DECLARE
l_cursor dbms_sqltune.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (dbms_sqltune.select_workload_repository(
765, -- begin_snap
766, -- end_snap
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
10)) p; -- result_limit
Moves one or more STS from their location in the SYS schema to a staging table created by the create_stgtab function
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);
Changes the sqlset names and owners in the staging table so that they can be unpacked with different values than they had on the host system
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);
Provided to be able to collect SQL statements from the Cursor Cache
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;
Collects SQL statements from a Performance Analyzer task for creating a SQL Tuning Set containing the subset of SQL statements that regressed during a SQL Performance Analyzer (SPA) run
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;
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;
Collects SQL statements from the workload repository to collect SQL statements from all snapshots between begin_snap and and end_snap
Overload 1
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;
TBD
Collects SQL statements from the workload repository to collect SQL statements from a specified baseline
Overload 2
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;
Moves one or more STS from the staging table, as populated by a call to pack_stgtab and moved by the user, into the STS schema, making them proper STS. Users can drop the staging table after this procedure completes successfully.
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);
updates selected string fields for a SQL statement in a sqlset
Overload 1
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);
TBD
Overload 2
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);
TBD
Overload 3
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 CLOB := NULL,
sqlset_owner IN VARCHAR2 := NULL);