Oracle | DBMS

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	DROP_SQLSET_BY_PREFIX	SELECT_SQLPA_TASK
CAPTURE_CURSOR_CACHE	LOAD_SQLSET	SELECT_SQLSET
CREATE_SQLSET	PACK_STGTAB	SELECT_SQL_TRACE
CREATE_STGTAB	REMAP_STGTAB	SELECT_WORKLOAD_REPOSITORY
DELETE_SQLSET	REMOVE_REFERENCE	UNPACK_STGTAB
DROP_SQLSET	SELECT_CURSOR_CACHE	UPDATE_SQLSET

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