Oracle DBMS_SQLSET
Version 23c

General Information
Library Note Morgan's Library Page Header
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.
Source {ORACLE_HOME}/rdbms/admin/dbmssqls.sql
Subprograms
 
ADD_REFERENCE
Adds a new reference to an existing SQL tuning set to indicate its use by a client dbms_sqlset.add_reference(
sqlset_name  IN VARCHAR2,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN NUMBER;
DECLARE
 retVal NUMBER;
BEGIN
  retVal := dbms_sqlset.add_reference('UW_SQLSET', 'Test Add Ref');
  dbms_output.put_line(retVal);
END;
/
 
CAPTURE_CURSOR_CACHE
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);
exec dbms_sqlset.capture_cursor_cache('UW_SQLSET', 20, 3);

exec dbms_sqlset.capture_cursor_cache('UW_SQLSET', 9000, 10, capture_option => 'INSERT');

exec dbms_sqlset.capture_cursor_cache('UW_SQLSET', 15, 5, capture_mode => dbms_sqlset,mode_replace_old_stats);
 
CREATE_SEQLSET
Creates a SQL tuning set object in the database

Overload 1
dbms_sqlset.create_sqlset(
sqlset_name  IN VARCHAR2,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL);
exec dbms_sqlset.create_sqlset('UW_SQLSET', 'MLib workload');
Overload 2 dbms_sqlset.create_sqlset(
sqlset_name  IN VARCHAR2 := NULL,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
DECLARE
 retVal VARCHAR2(60);
BEGIN
 retVal := dbms_sqlset.create_sqlset('UW_SQLSET', 'MLib workload', 'UWCLASS');
 dbms_output.put_line(retVal);
END;
/
 
CREATE_STGTAB
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);
exec dbms_sqlset.create_stgtab('SQLSET_TAB', 'UWCLASS', 'UWDATA', dbms_sqlset.sts_stgtab_12_1_version);
 
DELETE_SQLSET
Deletes a set of SQL statements from a SQL tuning set dbms_sqlset.delete_sqlset(
sqlset_name  IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL);
exec dbms_sqlset.delete_sqlset('UW_SQLSET', 'elapsed_time < 250000', 'UWCLASS');
 
DROP_SQLSET
Drops a SQL tuning set if it is not active dbms_sqlset.drop_sqlset(
sqlset_name  IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL);
exec dbms_sqlset.drop_sqlset('UW_SQLSET', 'UWCLASS');
 
DROP_SQLSET_BY_PREFIX (new 23c)
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);
exec dbms_sqlset.drop_sqlset('UW_SQLSET', 30, 900);
 
LOAD_SQLSET
Populates the sqlset with a set of selected SQL 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

  dbms_sqltune.load_sqlset('UW_SQLSET', l_cursor);
  dbms_sqlset.load_sqlset('UW_SQLSET', l_cursor);
END;
/
 
PACK_STGTAB
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);
exec dbms_sqlset.pack_stgtab('UW_SQLSET', 'UW_SQSET_TAB');
 
REMAP_STGTAB
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);
exec dbms_sqlset.remap_stgtab('UW_SQLSET', 'UW_SQSET_TAB');
 
REMOVE_REFERENCE
Deactivates a sqlset to indicate it is no longer used by the client dbms_sqlset.remove_reference(
sqlset_name  IN VARCHAR2,
reference_id IN NUMBER,
sqlset_owner IN VARCHAR2 := NULL,
force_remove IN NUMBER   := 0);
TBD
 
SELECT_CURSOR_CACHE
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;
TBD
 
SELECT_SQLPA_TASK
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;
TBD
 
SELECT_SQLSET
Reads SQL tuning set contents 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;
exec dbms_sqlset.select_sqlset('UW_SQLSET');
 
SELECT_SQL_TRACE
Reads the content of one or more trace files and returns the SQL statements it finds in the format of sqlset_row 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;
TBD
 
SELECT_WORKLOAD_REPOSITORY
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;
TBD
 
UNPACK_STGTAB
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);
exec dbms_sqlset.unpack_stgtab(replace => TRUE, staging_table_name => 'UW_SQSET_TAB');
 
UPDATE_SQLSET (new 23c overload) 
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);
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_AUTO_SQLSET
DBMS_AUTO_SQLTUNE
DBMS_SQLDIAG
DBMS_SQLPA
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL1
DBMS_SQLTUNE_UTIL2
PRVTEMX_PERF
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx