Oracle DBMS_SQLTUNE_UTIL1
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
Purpose As opposed to dbms_sqltune_util0, is for sqltune and sqlpi internal utility procedures and functions that might access dictionary objects. It should be used for all general utility functions that can/need to be DEFINER's rights. If a function only needs to be accessible from the dbms_sqltune/sqldiag/etc feature layer, do not put it here, but rather in the infrastructure layer (prvssqlf). This layer is for code that should be globally accessible, even from the internal package.
AUTHID DEFINER
Constants
Name Data Type Value
Target object IDs which are defined in OBJ_XXX_NUM
OBJ_SQL# NUMBER 7
OBJ_SQLSET# NUMBER 8
OBJ_AUTO_SQLWKLD# NUMBER 22
OBJ_SPA_EXEC_PROP# NUMBER 23
OBJ_SPA_TASK# NUMBER 24
OBJ_SPM_EVOLVE_TASK# NUMBER 25
Execution Type Names
SQLTUNE VARCHAR2(10) 'TUNE SQL'
TEST_EXECUTE VARCHAR2(12) 'TEST EXECUTE'
EXPLAIN_PLAN VARCHAR2(12) 'EXPLAIN PLAN'
COMPARE VARCHAR2(19) 'COMPARE PERFORMANCE'
STS2TRIAL VARCHAR2(19) 'CONVERT SQLSET'
SQLDIAG VARCHAR2(19) 'SQL DIAGNOSIS'
SPMEVOLVE VARCHAR2(14) 'SPM EVOLVE'
Execution Type IDs
SQLTUNE# INTEGER 1
EXECUTE# INTEGER 2
EXPLAIN# INTEGER 3
SQLDIAG# INTEGER 4
COMPARE# INTEGER 5
EVOLVE# INTEGER 6
STS Properties
PROP_SQLSET_NAME VARCHAR2(30) 'SQLSET_NAME'
PROP_SQLSET_OWNER VARCHAR2(30) 'SQLSET_OWNER'
PROP_SQLSET_ID VARCHAR2(30) 'SQLSET_ID'
PROP_SQLSET_DESC VARCHAR2(30) 'SQLSET_DESC'
Shared properties for multi-statement targets
PROP_NB_SQL VARCHAR2(30) 'NB_STMTS'
PROP_CON_DBID VARCHAR2(30) 'CON_DBID'
Properties for STS2 (compare STS)
PROP_SQLSET_NAME2 VARCHAR2(30) 'SQLSET_NAME2'
PROP_SQLSET_OWNER2 VARCHAR2(30) 'SQLSET_OWNER2'
PROP_SQLSET_ID2 VARCHAR2(30) 'SQLSET_ID2'
PROP_SQLSET_DESC2 VARCHAR2(30) 'SQLSET_DESC2'
PROP_NB_SQL2 VARCHAR2(30) 'NB_STMTS2'
PROP_CON_DBID2 VARCHAR2(30) 'CON_DBID2'
Automatic Workload Properties
PROP_SUM_ELAPSED VARCHAR2(30) 'SUM_ELAPSED'
Single statement Properties
PROP_SQL_ID VARCHAR2(30) 'SQL_ID'
PROP_PARSING_SCHEMA VARCHAR2(30) 'PARSING_SCHEMA'
PROP_SQL_TEXT VARCHAR2(30) 'SQL_TEXT'
PROP_TUNE_STATS VARCHAR2(30) 'TUNE_STATS'
Parse modes for query
PARSE_MOD_SQLSET VARCHAR2(6) 'SQLSET'
PARSE_MOD_AWR VARCHAR2(4) 'AWR'
PARSE_MOD_CURSOR VARCHAR2(5) 'V$SQL'
PARSE_MOD_CAPCC VARCHAR2(8) 'V$SQLCAP'
PARSE_MOD_PROFILE VARCHAR2(10) 'SQLPROFILE'
Miscellaneous
PNUM_SYSPLS_OBEY_FORCE NUMBER 1
Data Types TYPE property_map IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767);

TYPE task_sqlobj IS RECORD(
obj_id              NUMBER,
sql_id              VARCHAR2(13),
plan_hash_value     NUMBER,
parsing_schema_name VARCHAR2(30),
sql_text            CLOB,
other_xml           CLOB,
exec_frequency      NUMBER,
flags               BINARY_INTEGER,
con_name            VARCHAR2(30),
con_dbid            NUMBER);

TYPE task_wkldobj IS RECORD(
adv_id    NUMBER,       -- advisor id#
task_name VARCHAR2(30), -- name of the current task
type      NUMBER,       -- one of OBJ_XXX_NUM keat constants
obj_id    NUMBER,       -- object id of target object
props     property_map, -- (name, value) pairs describing the target
cursql    task_sqlobj,  -- SQL object for the current statement
is_cdb    BOOLEAN);     -- checks if this ia cdb env

TYPE task_spaobj IS RECORD(
exec1_name      VARCHAR2(32767), -- the execution name of trial one
exec1_type_num  NUMBER,          -- the execution type of trial one
comp_exec_name  VARCHAR2(32767), -- compare exec name, max length ?
ce_obj_id       NUMBER,          -- obj id of comp env
target_obj_type NUMBER,          -- could be SQLSET or SQL
target_obj_id   NUMBER,          -- id of the target object of SPA task
wkld            task_wkldobj);   -- has the target obj id
DBMS_SQLDIAG
Dependencies
DBA_ADVISOR_EXECUTIONS DBMS_SQLPA PRVT_SQLADV_INFRA
DBA_ADVISOR_EXEC_PARAMETERS DBMS_SQLTUNE PRVT_SQLPA
DBA_ADVISOR_OBJECTS DBMS_SQLTUNE_INTERNAL PRVT_SQLSET_INFRA
DBA_ADVISOR_PARAMETERS DBMS_SQLTUNE_UTIL2 WRI$_ADV_SQLTUNE
DBA_ADVISOR_TASKS DBMS_SQL_MONITOR WRI$_REPT_SPMEVOLVE
DBMS_ADVISOR DBMS_XPLAN WRI$_REPT_SQLPI
DBMS_ASH_INTERNAL PRVTEMX_ADMIN WRI$_REPT_SQLT
DBMS_AUTO_REPORT_INTERNAL PRVT_ADVISOR X$KSPPCV
DBMS_SPM PRVT_EMX X$KSPPI
DBMS_SPM_INTERNAL    
Documented No
Exceptions
Error Code Reason
ORA-13645 The specified execution <execution_task_name> does not exist for this task
ORA-13755 invalid "SQL Tuning Set" name
First Available 10gR1
Security Model Owned by SYS with no privileges granted.
Source {ORACLE_HOME}/rdbms/admin/dbmssqlu.sql
Subprograms
 
ALTER_SESSION_PARAMETER
Sets the indicated parameter to a hard-coded value if it is currently different, and returns a Boolean value indicating whether or not the value had to be changed dbms_sqltune_util1.alter_session_parameter(pnum IN NUMBER) RETURN BOOLEAN;
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm like '_parallel_sys%';

BEGIN
  IF dbms_sqltune_util1.alter_session_parameter(dbms_sqltune_util1.pnum_syspls_obey_force) THEN
    dbms_output.put_line('Change Required');
  ELSE
    dbms_output.put_line('No Change Required');
 END IF;
END;
/

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm like '_parallel_sys%';

exec  dbms_sqltune_util1.restore_session_parameter(1);

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm like '_parallel_sys%';
 
CHECK_STBY_OPER (new 12.2)
Raises an error if it is called from a standby database dbms_sqltune_util1.check_stby_oper;
SQL> exec dbms_sqltune_util1.check_stby_oper;

PL/SQL procedure successfully completed.
 
COPY_CLOB (new 12.2)
Returns copying values from remote/local CLOB to local/remote CLOB. dbms_sqltune_util1.copy_clob(
inCLOB  IN            CLOB,
outCLOB IN OUT NOCOPY CLOB);
SQL> DECLARE
  2   iClob CLOB := 'Welcome to Morgan''s Library Where Database 18c Is Coming Soon';
  3   oClob CLOB;
  4  BEGIN
  5    dbms_sqltune_util1.copy_clob(iClob, oClob);
  6    dbms_output.put_line(oClob);
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 1163
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1617
ORA-06512: at line 5
 
GET_AWR_QUERY_TEXT (new 12.2)
Builds the text of the SQL statement that captures plans
from AWR based on the flags passed as parameters
dbms_sqltune_util1.get_awr_query_text(
con_dbid_bind   IN BOOLEAN  := FALSE,
stmt_bind       IN BOOLEAN  := FALSE,
begin_snap_op   IN NUMBER   := 0,
stats_only      IN BOOLEAN  := FALSE,
cmd_type_filter IN BOOLEAN  := FALSE,
awr_view        IN VARCHAR2 := AWR_VIEW_ROOT)
RETURN VARCHAR2;
TBD
 
GET_AWR_VIEW_LOCATION (new 12.2)
Determines the location/prefix of the AWR view to use for the database that corresponds to the dbid parameter dbms_sqltune_util1.get_awr_view_location(dbid IN NUMBER)
RETURN VARCHAR2;
SQL> SELECT dbms_sqltune_util1.get_awr_view_location(549318987)
  2  FROM dual;

DBMS_SQLTUNE_UTIL1.GET_AWR_VIEW_LOCATION(549318987)
----------------------------------------------------
AWR_ROOT
 
GET_CURRENT_TIME
Returns the current date-time: a wrapper around ksugctm() dbms_sqltune_util1.get_current_time RETURN DATE;
SELECT dbms_sqltune_util1.get_current_time
FROM dual;
 
GET_DBID_FROM_CONID
Returns the container dbid for a container id. If not in in a cdb environment, it simply returns the dbid from v$database dbms_sqltune_util1.get_dbid_from_conid(con_id IN PLS_INTEGER) RETURN NUMBER;
SELECT con_id, dbid, con_uid
FROM v$pdbs;

SELECT dbms_sqltune_util1.get_dbid_from_conid(2)
FROM dual;
 
GET_EXECUTION_TYPE
Returns the type of a given task execution dbms_sqltune_util1.get_execution_type(
tid   IN VARCHAR2,  -- task identifier
ename IN VARCHAR2)  -- execution name
RETURN VARCHAR2;
SELECT task_id, execution_name
FROM cdb_sqltune_plans
ORDER BY 1;

SELECT dbms_sqltune_util1.get_execution_type(1, 'EXEC_2516')
FROM dual;
 
GET_SEQ_REMOTE (new 12.2)
Returns the sequence from the primary database when it is required at the standby.

Clearly some coder(s) at Oracle never considered this being called when there isn't an active Data Guard Standby.
dbms_sqltune_util1.get_seq_remote(
seq_name IN  VARCHAR2,
ref_id   OUT NUMBER);
SQL> DECLARE
  2   retVal VARCHAR2(30);
  3  BEGIN
  4    dbms_sqltune_util1.get_seq_remote('TSDP_SOURCE$SEQUENCE', retVal);
  5    dbms_output.put_line(retVal);
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kesutlGetSeqExt:dbLinkTo], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1321
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1367
ORA-06512: at line 4
 
GET_SQLSET_CON_DBID
Returns the container dbid given a SQL Tuning Set ID dbms_sqltune_util1.get_sqlset_con_dbid(sts_id IN NUMBER) RETURN NUMBER;
SELECT sqlset_id
FROM cdb_sqlset_plans;

SELECT dbms_sqltune_util1.get_sqlset_con_dbid(23)
FROM dual;
 
GET_SQLSET_IDENTIFIER
Returns the SqlSet ID from its name dbms_sqltune_util1.get_sqlset_identifier(
sts_name  IN VARCHAR2,
sts_owner IN VARCHAR2)
RETURN NUMBER;
conn sys@pdbdev as sysdba

exec dbms_sqltune.create_sqlset('UWSet', 'Test Tuning Set', 'UWCLASS');

SELECT id, owner, name
FROM cdb_sqlset;

SELECT dbms_sqltune_util1.get_sqlset_identifier('UWSet', 'UWCLASS')
FROM dual;
 
GET_SQLSET_NB_STMTS
Returns the number of SQL statements in a SQL tuning set dbms_sqltune_util1.get_sqlset_nb_stmts(sts_id IN NUMBER) RETURN NUMBER;
conn sys@pdbdev as sysdba

exec dbms_sqltune.create_sqlset('UWSet', 'Test Tuning Set', 'UWCLASS');

SELECT dbms_sqltune_util1.get_sqlset_nb_stmts(1)
FROM dual;
 
GET_TASK_NAME (new 12.2)
Returns the task_name of a given task_id dbms_sqltune_util1.get_task_name(task_id IN NUMBER)
RETURN VARCHAR2;
SQL> SELECT dbms_sqltune_util1.get_task_name(1845)
  2  FROM dual;

DBMS_SQLTUNE_UTIL1.GET_TASK_NAME(1845)
---------------------------------------
ADDM:549318987_1_1652
 
GET_VIEW_TEXT
Returns the DDL text to capture plans given a parse mode dbms_sqltune_util1.get_view_text(parse_mode IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_sqltune_util1.get_view_text('PARSE_MOD_SQLSET')
FROM dual;

DBMS_SQLTUNE_UTIL1.GET_VIEW_TEXT('PARSE_MOD_SQLSET')
-----------------------------------------------------------------------------
SELECT vs.*
       FROM sys.v_$sqlstats vst, (SELECT sql_id, force_matching_signature,
         NVL(plan_hash_value, 0) plan_hash_value,
         sql_fulltext as sql_text, parsing_schema_name,
         module, action, elapsed_time, cpu_time, buffer_gets,
         last_active_child_address,
         TO_CHAR(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') first_load_time,
         last_load_time,
         disk_reads, direct_writes, rows_processed, fetches, executions,
         end_of_fetch_count, optimizer_cost, optimizer_env,
         command_type, loaded_versions, bind_data, last_active_time, con_dbid
       FROM sys.v_$sqlarea_plan_hash s) vs
       WHERE vst.sql_id = vs.sql_id and
             vs.last_active_time >= :lat and
             vst.last_active_time >= :lat and
             vs.con_dbid = vst.con_dbid
 
GET_WKLDTYPE_NAME
Returns the string version of the workload type number dbms_sqltune_util1.get_wkldtype_name(type_num IN NUMBER) RETURN VARCHAR2;
DECLARE
 retVal VARCHAR2(30);
BEGIN
  retVal := dbms_sqltune_util1.get_wkldtype_name(dbms_sqltune_util1.obj_auto_sqlwkld#);
  dbms_output.put_line(retVal);
END;
/
 
INIT_REMOTE_CONTEXT (new 12.2)
Initialize db_links to/from a remote db to global variable sqlt_rmt_ctx dbms_sqltune_util1.init_remote_context(
task_name      IN VARCHAR2       := NULL,
db_link_to     IN VARCHAR2       := NULL,
is_create_task IN BOOLEAN        := FALSE,
user_id        IN BINARY_INTEGER := -1 );
TBD
 
INIT_TASK_SPAOBJ
Initializes the structure of that defines the object type of an SPA task whose regressions will be tuned by the tuning task dbms_sqltune_util1.init_task_spaobj(
tid            IN         NUMBER,
task_name      IN         VARCHAR2,
comp_exec_name IN         VARCHAR2,
spa_task       OUT NOCOPY task_spaobj);
TBD
 
INIT_TASK_WKLDOBJ
Initializes our structure that defines the object type of the workload as well as all of its properties dbms_sqltune_util1.init_task_wkldobj(
tid        IN         NUMBER,
begin_exec IN         VARCHAR2 := NULL,
end_exec   IN         VARCHAR2 := NULL,
wkld       OUT NOCOPY task_wkldobj);
TBD
 
IS_ADAPTIVE_PLAN (new 12.2)
Returns TRUE when the specified plan is an adaptive one dbms_sqltune_util1.is_adaptive_plan(
task_id   IN NUMBER,
exec_name IN VARCHAR2,
plan_id   IN NUMBER,
plan_hash IN NUMBER)
RETURN BOOLEAN;
TBD
 
IS_RUNNING_FAKE_CC_TEST
Determines from _sta_control, if a fake cursor cache tests is running. The capture sts queries are parsed differently for those tests. dbms_sqltune_util1.is_running_fake_cc_test RETURN BOOLEAN;
BEGIN
  IF dbms_sqltune_util1.is_running_fake_cc_test THEN
    dbms_output.put_line('Fake');
  ELSE
    dbms_output.put_line('Real');
  END IF;
END;
/
 
IS_STANDBY (new 12.2)
Checks to see if the current DB is a physical standby dbms_sqltune_util1.is_standby RETURN BOOLEAN;
BEGIN
  IF dbms_sqltune_util1.is_standby THEN
    dbms_output.put_line('This database is a standby');
  ELSE
    dbms_output.put_line('This database is not a standby');
  END IF;
END;
/
 
RESOLVE_DB_TYPE (new 12.2)
Resolves the type of database corresponding to the dbid. Used by get_awr_view_location to determine the location of AWR views. dbms_sqltune_util1.resolve_db_type(dbid IN NUMBER)
RETURN VARCHAR2;
SQL> SELECT dbms_sqltune_util1.resolve_db_type(549318987)
  2  FROM dual;

DBMS_SQLTUNE_UTIL1.RESOLVE_DB_TYPE(549318987)
----------------------------------------------
ROOT
 
RESTORE_SESSION_PARAMETER
Follows up on a call to set_session_parameter by setting it back to its initial value. Should only be called when the set function returns TRUE indicating the value was changed dbms_sqltune_util1.restore_session_parameter(pnum IN NUMBER);
See ALTER_SESSION_PARAMETER Demo Above
 
VALIDATE_NAME
Determines if a Tuning Set name is syntactically valid: not whether it actually exists dbms_sqltune_util1.validate_name(name IN VARCHAR2);
exec dbms_sqltune_util1.validate_name('UW Tuning Set');

exec dbms_sqltune_util1.validate_name('1234567890123456789012345678901234567890');
 
VALIDATE_TASK_STATUS
Check whether the task status is valid to be reported dbms_sqltune_util1.validate_task_status(tid IN NUMBER);
exec dbms_sqltune_util1.validate_task_status(1);

Related Topics
Built-in Functions
Built-in Packages
DBMS_AUTO_SQLTUNE
DBMS_ADVISOR
DBMS_SPM
DBMS_SQLDIAG
DBMS_SQLPA
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL2
PRVTEMX_PERF
Tuning
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved