Oracle DBMS_SQLTUNE_UTIL1
Version 21c

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 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
OBJ_COMPARE_PLANS_TASK# NUMBER 30
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'
COMPAREPLANS VARCHAR2(19) 'COMPARE PLANS'
Execution Type IDs
SQLTUNE# INTEGER 1
EXECUTE# INTEGER 2
EXPLAIN# INTEGER 3
SQLDIAG# INTEGER 4
COMPARE# INTEGER 5
EVOLVE# INTEGER 6
COMPAREPLANS# INTEGER 7
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'
Task DBLINK
TASK_DBLINK_OWNER VARCHAR2(3) 'SYS'
TASK_DBLINK_USER VARCHAR2(7) 'SYS$UMF'
PARAM_DBLINK_TO VARCHAR2(16) 'DATABASE_LINK_TO'
Validate Name
TYPE_STS BINARY_INTEGER 0
TYPE_DBOP BINARY_INTEGER 1
Validate Name
DB_TYPE_ROOT VARCHAR2(4) 'ROOT'
DB_TYPE_PDB VARCHAR2(3) 'PDB'
DB_TYPE_IMP VARCHAR2(8) 'IMPORTED'
AWR View Prefixes
AWR_VIEW_ROOT VARCHAR2(8) 'AWR_ROOT'
AWR_VIEW_PDB VARCHAR2(7) 'AWR_PDB'
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 idDBMS_SQLDIAG
Dependencies
DBA_ADVISOR_EXECUTIONS DBMS_SQLTUNE_LIB V$PARAMETER
DBA_ADVISOR_EXEC_PARAMETERS DBMS_SQLTUNE_UTIL0 V$SESSION_FIX_CONTROL
DBA_ADVISOR_OBJECTS DBMS_SQLTUNE_UTIL2 V$SQL_MONITOR
DBA_ADVISOR_PARAMETERS DBMS_SQL_MONITOR WRI$_ADV_DEFINITIONS
DBA_ADVISOR_RECOMMENDATIONS DBMS_STANDARD WRI$_ADV_DEF_PARAMETERS
DBA_AUTOSQLSET_SQLSTAT DBMS_SYS_ERROR WRI$_ADV_EXECUTIONS
DBMS_ADVISOR DBMS_XPLAN WRI$_ADV_OBJECTS
DBMS_ASH_INTERNAL DBMS_XPLAN_INTERNAL WRI$_ADV_SQLTUNE
DBMS_ASSERT GV_$SQL WRI$_ADV_TASKS
DBMS_AUTO_REPORT_INTERNAL PRVTEMX_ADMIN WRI$_REPT_SPMEVOLVE
DBMS_LOB PRVT_ADVISOR WRI$_REPT_SQLPI
DBMS_SPM PRVT_AWRV_METADATA WRI$_REPT_SQLT
DBMS_SPM_INTERNAL PRVT_AWR_VIEWER WRI$_SQLSET_DEFINITIONS
DBMS_SQLDIAG PRVT_EMX WRI$_SQLSET_STATEMENTS
DBMS_SQLPA PRVT_SQLADV_INFRA X$KSPPCV
DBMS_SQLTUNE PRVT_SQLPA X$KSPPI
DBMS_SQLTUNE_INTERNAL PRVT_SQLSET_INFRA  
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 10.1
Security Model Owned by SYS with no privileges granted.
Source {ORACLE_HOME}/rdbms/admin/dbmssqlu.sql
Subprograms
ALTER_SESSION_PARAMETER GET_SEQ_REMOTE IS_FIX_CONTROL_ON
CHECK_STBY_OPER GET_SQLSET_CON_DBID IS_RUNNING_FAKE_CC_TEST
COPY_CLOB GET_SQLSET_IDENTIFIER IS_SESSION_MONITORED
DS_SETTING GET_SQLSET_NB_STMTS IS_STANDBY
GET_ASH_SAMPLING_INFO GET_TASK_NAME REPLACE_AWR_VIEW_PREFIX
GET_AUTOSTS_NB_STMTS GET_VIEW_TEXT RESOLVE_DB_TYPE
GET_AWR_QUERY_TEXT GET_WKLDTYPE_NAME RESOLV_EXEC_NAME
GET_AWR_VIEW_LOCATION INIT_REMOTE_CONTEXT RESTORE_SESSION_PARAMETER
GET_CURRENT_TIME INIT_TASK_SPAOBJ RUNNING_LRG_MODE
GET_DBID_FROM_CONID INIT_TASK_WKLDOBJ VALIDATE_NAME
GET_EXECUTION_TYPE IS_ADAPTIVE_PLAN VALIDATE_TASK_STATUS
GET_FULL_SQLTEXT IS_EXADAT_PROFILE  
 
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
Raises an error if it is called from a standby database dbms_sqltune_util1.check_stby_oper;
exec dbms_sqltune_util1.check_stby_oper;

PL/SQL procedure successfully completed.
 
COPY_CLOB
Returns copying values from remote/local CLOB to local/remote CLOB. dbms_sqltune_util1.copy_clob(
inCLOB  IN            CLOB,
outCLOB IN OUT NOCOPY CLOB);
DECLARE
 iClob CLOB := 'Welcome to Morgan''s Library Where Database 21c Has Arrived';
 oClob CLOB;
BEGIN
  dbms_sqltune_util1.copy_clob(iClob, oClob);
  dbms_output.put_line(oClob);
END;
/
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 1786
ORA-06512: at line 5
 
DS_SETTING (new 21c)
Sets dynamic_sampling to 2 from 11 to avoid ORA-01002 during old style ds when statement contains a  TABLE function

The Oracle doc mistakenly says that this sets to 11 but clearly that is not true		 dbms_sqltune_util1.ds_setting(reset_str OUT NOCOPY VARCHAR2);
DECLARE
 outVal VARCHAR2(32767);
BEGIN
  dbms_sqltune_util1.ds_setting(outVal);
  dbms_output.put_line(outVal);
END;
/
begin
  execute immediate 'alter session set optimizer_dynamic_sampling=2';
  execute immediate 'alter session set "_optimizer_ads_max_table_count"=0';
  end;

PL/SQL procedure successfully completed.
 
GET_ASH_SAMPLING_INFO
Retrieve the oldest sample time as well as the sampling interval from Active Session History dbms_sqltune_util1.get_ash_sampling_info(
inst_id_low         IN  NUMBER,
inst_id_high        IN  NUMBER,
ash_oldest_sample   OUT DATE,
ash_sample_interval OUT NUMBER);
TBD
 
GET_AUTOSTS_NB_STMTS (new 21c)
Returns the number of SQL statements in a Auto SQL tuning set dbms_sqltune_util1.get_autosts_nb_stmts(
begin_time IN DATE,
end_time   IN DATE)
RETURN NUMBER;
SELECT dbms_sqltune_util1.get_autosts_nb_stmts(SYSDATE-1, SYSDATE-12/24)
FROM dual;

DBMS_SQLTUNE_UTIL1.GET_AUTOSTS_NB_STMTS(SYSDATE-1,SYSDATE-12/24)
----------------------------------------------------------------
                                                               0
 
GET_AWR_QUERY_TEXT
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
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;
SELECT dbms_sqltune_util1.get_awr_view_location(549318987)
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_CURRENT_TIME
--------------------
27-APR-2021 14:41:14
 
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;

 CON_ID DBID       CON_UID
------- ---------- ----------
      2 3298821576 3298821576
      3 1823093744 1823093744

SELECT dbms_sqltune_util1.get_dbid_from_conid(2)
FROM dual;

DBMS_SQLTUNE_UTIL1.GET_DBID_FROM_CONID(2)
-----------------------------------------
                               3298821576
 
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 UNIQUE task_id, execution_name
FROM cdb_sqltune_plans
ORDER BY 1;

 TASK_ID EXECUTION_NAME
-------- ---------------
       1 EXEC_2849
       1 EXEC_2963
       1 EXEC_3005
       1 EXEC_3119
       1 EXEC_3145
       1 EXEC_3421
       1 EXEC_3473
       1 EXEC_3551

SELECT dbms_sqltune_util1.get_execution_type(1, 'EXEC_2963')
FROM dual;

DBMS_SQLTUNE_UTIL1.GET_EXECUTION_TYPE(1,'EXEC_2963')
----------------------------------------------------
TUNE SQL
 
GET_FULL_SQLTEXT
Retrieves the full text of a given SQL statement from the cursor cache dbms_sqltune_util1.get_full_sqltext(
sql_id       IN            VARCHAR2,
inst_id_low  IN            NUMBER,
inst_id_high IN            NUMBER,
full_sqltext IN OUT NOCOPY CLOB);
TBD
 
GET_SEQ_REMOTE
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);
DECLARE
 retVal VARCHAR2(30);
BEGIN
  dbms_sqltune_util1.get_seq_remote('TSDP_SOURCE$SEQUENCE', retVal);
  dbms_output.put_line(retVal);
END;
/
 dbms_sqltune_util1.get_seq_remote('TSDP_SOURCE$SEQUENCE', retVal);
*
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00904: insufficient privilege to access object GET_SEQ_REMOTE
 
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 DISTINCT sqlset_id
FROM cdb_sqlset_plans
ORDER BY 1;

 SQLSET_ID
----------
         1
         5

SELECT dbms_sqltune_util1.get_sqlset_con_dbid(5)
FROM dual;

DBMS_SQLTUNE_UTIL1.GET_SQLSET_CON_DBID(5)
-----------------------------------------
                               2140826538
 
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
Returns the task_name of a given task_id dbms_sqltune_util1.get_task_name(task_id IN NUMBER)
RETURN VARCHAR2;
SELECT dbms_sqltune_util1.get_task_name(1845)
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;
/
Automatic SQL Workload

PL/SQL procedure successfully completed.
 
INIT_REMOTE_CONTEXT
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
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_EXADAT_PROFILE
Returns TRUE if an Exadata-aware profile exists for a task referenced by a given task_id dbms_sqltune_util1.is_exadata_profile(tid IN NUMBER)
RETURN BOOLEAN;
TBD
 
IS_FIX_CONTROL_ON (new 21c)
Verifies fix control is on in session for the respective bug number dbms_sqltune_util1.is_fix_control_on(bug_number IN NUMBER) RETURN BOOLEAN;
PRAGMA SUPPLEMENTAL_LOG_DATA(IS_FIX_CONTROL_ON, READ_ONLY);
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;
/
Real

PL/SQL procedure successfully completed.
 
IS_SESSION_MONITORED
Returns TRUE if there is an active database operation that is monitoring a given session dbms_sqltune_util1.is_session_monitored(session_id IN NUMBER)
RETURN BOOLEAN;
BEGIN
  IF dbms_sqltune_util1.is_session_monitored(388) THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

PL/SQL procedure successfully completed.
 
IS_STANDBY
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;
/
This database is not a standby

PL/SQL procedure successfully completed.
 
REPLACE_AWR_VIEW_PREFIX
Replaces awr view prefix in the sql text with the p_awr_view_prefix dbms_sqltune_util1.replace_awr_view_prefix(
p_qry             IN OUT VARCHAR2,
p_awr_view_prefix IN OUT VARCHAR2);
TBD
 
RESOLVE_DB_TYPE
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;
SELECT dbms_sqltune_util1.resolve_db_type(549318987)
FROM dual;

DBMS_SQLTUNE_UTIL1.RESOLVE_DB_TYPE(549318987)
----------------------------------------------
ROOT
 
RESOLVE_EXEC_NAME
Validates the execution name of an SPA task to ensure it was a Compare Performance (type id 5) while if NULL, returns the name of the most recent compare execution for the SPA task dbms_sqltune_util1.resolve_exec_name(
task_name  IN     VARCHAR2,
task_owner IN     VARCHAR2,
exec_name  IN OUT VARCHAR2)
RETURN NUMBER;
TBD
 
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
 
RUNNING_LRG_MODE
Checks whether the report is produced while running in lrg mode dbms_sqltune_util1.running_lrg_mode RETURN BOOLEAN;
BEGIN
  IF dbms_sqltune_util1.running_lrg_mode THEN
    dbms_output.put_line('Lrg Mode');
  ELSE
    dbms_output.put_line('Reg Mode');
  END IF;
END;
/
Reg Mode

PL/SQL procedure successfully completed.
 
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');

PL/SQL procedure successfully completed.

exec dbms_sqltune_util1.validate_name('1234567890123456789012345678901234567890');
BEGIN dbms_sqltune_util1.validate_name('1234567890123456789012345678901234567890'); END;

*
ERROR at line 1:
ORA-13755: invalid "SQL Tuning Set" name
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 457
ORA-06512: at line 1
 
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);

PL/SQL procedure successfully completed.

Related Topics
Built-in Functions
Built-in Packages
DBMS_AUTO_SQLTUNE
DBMS_ADVISOR
DBMS_SPM
DBMS_SQLDIAG
DBMS_SQLPA
DBMS_SQLSET
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL2
PRVTEMX_PERF
Tuning
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