Oracle DBMS_SQLTUNE_UTIL1
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.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.
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
Dependencies
DBA_ADVISOR_EXECUTIONS DBMS_SQLTUNE PRVT_SQLSET_INFRA
DBA_ADVISOR_EXEC_PARAMETERS DBMS_SQLTUNE_INTERNAL V$PARAMETER
DBA_ADVISOR_OBJECTS DBMS_SQLTUNE_LIB WRI$_ADV_OBJECTS
DBA_ADVISOR_PARAMETERS DBMS_SQLTUNE_UTIL0 WRI$_ADV_SQLTUNE
DBA_ADVISOR_TASKS DBMS_SQLTUNE_UTIL2 WRI$_REPT_SPMEVOLVE
DBMS_ADVISOR DBMS_SQL_MONITOR WRI$_REPT_SQLPI
DBMS_AUTO_REPORT_INTERNAL DBMS_STANDARD WRI$_REPT_SQLT
DBMS_SPM DBMS_SYS_ERROR WRI$_SQLSET_DEFINITIONS
DBMS_SPM_INTERNAL PRVT_ADVISOR WRI$_SQLSET_STATEMENTS
DBMS_SQLDIAG PRVT_SQLADV_INFRA X$KSPPCV
DBMS_SQLPA PRVT_SQLPA X$KSPPI
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 hardcoded 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%';
 
GET_CURRENT_TIME
Returns the current date-time. Just 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 (new 12.1)
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_SQLSET_CON_DBID (new 12.1)
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_VIEW_TEXT
Returns the text of the SQL 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_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_RUNNING_FAKE_CC_TEST
Determine 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;
/
 
RESTORE_SESSION_PARAMETER
Follows up on a call to set_session_parameter by clearing it back to its initial value. It 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
DBMS_ADVISOR
DBMS_SPM
DBMS_SQLDIAG
DBMS_SQLPA
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL2
PRVTEMX_PERF
Packages
Tuning

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