Oracle DBMS_SQLTUNE
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 The interface for tuning SQL on demand. The related package DBMS_AUTO_SQLTUNE provides the interface for SQL Tuning Advisor run as an automated task.

Provides the APIs to tune SQL statements using three main modules:
1- sqlTune
2- sqlset
3- sqlProfile
AUTHID CURRENT_USER
Constants
Name Data Type Value
SQLTune Advisor Name
ADV_SQLTUNE_NAME VARCHAR2(18) 'SQL Tuning Advisor'
SQLTune Advisor Task Scope Parameters
SCOPE_COMPREHENSIVE VARCHAR2(13) 'COMPREHENSIVE'
SCOPE_LIMITED VARCHAR2(7) 'LIMITED'
SQLTune Advisor Time_Limit Constants
TIME_LIMIT_DEFAULT NUMBER 1800
Report Type Constants
TYPE_HTML VARCHAR2(4) 'HTML'
TYPE_TEXT VARCHAR2(4) 'TEXT'
TYPE_XML VARCHAR2(3) 'XML'
Report Level Constants
LEVEL_ALL VARCHAR2(3) 'ALL'
LEVEL_BASIC VARCHAR2(5) 'BASIC'
LEVEL_TYPICAL VARCHAR2(7) 'TYPICAL'
Report Section Constants
SECTION_ALL VARCHAR2(3) 'ALL'
SECTION_ERRORS VARCHAR2(6) 'ERRORS'
SECTION_FINDINGS VARCHAR2(8) 'FINDINGS'
SECTION_INFORMATION VARCHAR2(11) 'INFORMATION'
SECTION_PLANS VARCHAR2(5) 'PLANS'
SECTION_SUMMARY VARCHAR2(7) 'SUMMARY'
Common Date Format Constant
DATE_FMT VARCHAR2(21) 'mm/dd/yyyy hh24:mi:ss'
Script Section Constants
REC_TYPE_ALL VARCHAR2(3) 'ALL'
REC_TYPE_ALL_SQL VARCHAR2(17) 'ALL_SQL_REC_TYPES'
REC_TYPE_ALTER_PLAN VARCHAR2(17) 'ALTERNATIVE_PLAN'
REC_TYPE_ALTER_PLANS VARCHAR2(17) 'ALTERNATIVE_PLANS'
REC_TYPE_INDEX VARCHAR2(7) 'INDEX'
REC_TYPE_INDEXES VARCHAR2(7) 'INDEXES'
REC_TYPE_PX VARCHAR2(18) 'PARALLEL_EXECUTION'
REC_TYPE_SQL_PROFILE VARCHAR2(8) 'PROFILES'
REC_TYPE_SQL_PROFILES VARCHAR2(8) 'PROFILE'
REC_TYPE_STATS VARCHAR2(10) 'STATISTICS'
REC_TYPE_SYSTEM_STATS VARCHAR2(12) 'SYSTEM_STATS'
Capture Section Constants
MODE_REPLACE_OLD_STATS NUMBER 1
MODE_ACCUMULATE_STATS NUMBER 2
SQL Tuning Set Constants
ALL_COMMAND_TYPE BINARY_INTEGER 2
ALL_EXECUTIONS POSITIVE 2
LIMITED_COMMAND_TYPE BINARY_INTEGER 1
SINGLE_EXECUTION POSITIVE 1
SQL Profile Type
PX_PROFILE VARCHAR2(10) 'PX PROFILE'
REGULAR_PROFILE VARCHAR2(11) 'SQL PROFILE'
SQLSet Staging Table
STS_STGTAB_10_2_VERSION NUMBER 1
STS_STGTAB_11_1_VERSION NUMBER 2
STS_STGTAB_11_2_VERSION NUMBER 3
STS_STGTAB_11_202_VERSION NUMBER 4
STS_STGTAB_12_1_VERSION NUMBER 5
STS_STGTAB_12_2_VERSION NUMBER 6
Recursive SQL Filter
HAS_RECURSIVE_SQL VARCHAR2(30) 'Y'
NO_RECURSIVE_SQL VARCHAR2(30) 'N'
SQL Monitoring Types
MONITOR_TYPE_SQL NUMBER 1
MONITOR_TYPE_DBOP NUMBER 2
MONITOR_TYPE_ALL NUMBER 3
Miscellaneous
FLAG_PREPAWR_WRAPCTOR NUMBER POWER(2, 0);
FLAG_PREPAWR_NOCKBINDS NUMBER POWER(2, 1);
FLAG_PREPAWR_INCLBID NUMBER POWER(2, 2);
Data Types TYPE sqlset_cursor IS REF CURSOR;

TYPE arglist IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767);
Dependencies
ALL_PROCEDURES DBMS_SQL PRVT_AWR_VIEWER
ALL_SCHEDULER_JOBS DBMS_SQLDIAG PRVT_EMX
ALL_SQLSET DBMS_SQLDIAG_INTERNAL PRVT_REPORT_TAGS
ALL_SQLSET_BINDS DBMS_SQLPA PRVT_SMGUTIL
AWR_CDB_SQLBIND DBMS_SQLSET PRVT_SQLADV_INFRA
AWR_PDB_SQLBIND DBMS_SQLTCB_INTERNAL PRVT_SQLPA
AWR_ROOT_SNAPSHOT DBMS_SQLTUNE_INTERNAL PRVT_SQLPROF_INFRA
AWR_ROOT_SQLBIND DBMS_SQLTUNE_LIB PRVT_SQLSET_INFRA
CDB_SQL_PATCHES DBMS_SQLTUNE_UTIL0 PRVT_WORKLOAD
CDB_SQL_PLAN_BASELINES DBMS_SQLTUNE_UTIL1 SQLPROF_ATTR
CDB_SQL_PROFILES DBMS_SQLTUNE_UTIL2 SQLSET
DBA_ADVISOR_PARAMETERS DBMS_SQL_MONITOR SQLSET_ROW
DBA_ADVISOR_TASKS DBMS_STANDARD SQL_BIND
DBA_HIST_SQLBIND DBMS_STATS SQL_BINDS
DBA_SQLSET DBMS_SWAT_ARM_INTERNAL SQL_BIND_SET
DBA_SQLSET_BINDS DBMS_SWAT_VER_INTERNAL SYS_IXMLAGG
DBA_SQLSET_PLANS DBMS_SYS_ERROR USER_SQLSET_BINDS
DBA_SQLSET_REFERENCES DBMS_UTILITY V$ACTIVE_SESSION_HISTORY
DBA_SQLSET_STATEMENTS DBMS_WORKLOAD_REPLAY_I V$ALL_ACTIVE_SESSION_HISTORY
DBA_SQL_PROFILES DBMS_WRR_INTERNAL V$ALL_SQL_MONITOR
DBMS_ADVISOR DBMS_XPLAN V$ALL_SQL_PLAN_MONITOR
DBMS_ASSERT DBMS_XPLAN_INTERNAL V$DATABASE
DBMS_AUTO_INDEX_INTERNAL DUAL V$DUAL
DBMS_AUTO_REPORT GV$ALL_SQL_MONITOR V$SESSION
DBMS_AUTO_SQLTUNE GV$SQL V$SESSION_LONGOPS
DBMS_LOB GV$SQLAREA_PLAN_HASH V$SQL_MONITOR_STATNAME
DBMS_LOCK GV$SQL_OPTIMIZER_ENV V$SYS_OPTIMIZER_ENV
DBMS_MANAGEMENT_PACKS GV_$ASH_INFO WRI$_REPT_SQLDETAIL
DBMS_PERF GV_$SQL_SHARED_CURSOR WRI$_REPT_SQLMONITOR
DBMS_PRIV_CAPTURE PLITBLM WRI$_REPT_SQLT
DBMS_REPORT PRVTEMX_ADMIN XMLAGG
DBMS_SCHEDULER PRVTEMX_PERF XMLSEQUENCE
DBMS_SMB PRVTEMX_SQL XMLTYPE
DBMS_SMB_INTERNAL PRVT_ADVISOR XQSEQUENCE
DBMS_SPM PRVT_AWRV_MAPTAB _ALL_SQLSET_STS_TOPACK
DBMS_SPM_INTERNAL PRVT_AWRV_METADATA  
Documented Yes
Exceptions
Error Code Reason
ORA-13605 The specified task or object <task_name> does not exist for the current user.
ORA-13752 User <schema_name> must be SYS or must have the "ADMINISTER ANY SQL TUNING SET" privilege.
ORA-13754 SQL Tuning Set <set_name> does not exist for user <schema_name>
ORA-13971 Unknown component
ORA-20000 check_priv: invalid priv specified
ORA-20000 invalid advisor task name
First Available 10.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC

To tune objects in all schemas requires the system privs ADMINISTER SQL TUNING SET or ADMINISTER ANY SQL TUNING SET

The fact that EXECUTE on this package has been granted to PUBLIC is nothing but inappropriate. There is no justification for anyone with CREATE SESSION privilege even knowing that this package exists much less being able to execute any of its functionality. The only person that should be able to export, extract, alter, cancel, or drop anything is a DBA.
Source {ORACLE_HOME}/rdbms/admin/dbmssqlt.sql
Subprograms
ACCEPT_ALL_SQL_PROFILES EXECUTE_TUNING_TASK REPORT_SQL_MONITOR_LIST
ACCEPT_SQL_PROFILE EXPORT_PLAN_OBJECT_XML REPORT_SQL_MONITOR_LIST_XML
ADD_SQLSET_REFERENCE EXTRACT_BIND REPORT_SQL_MONITOR_XML
ALTER_PLAN_OBJECT_XML EXTRACT_BINDS REPORT_TUNING_TASK
ALTER_SQL_PROFILE IMPLEMENT_TUNING_TASK REPORT_TUNING_TASK_LIST_XML
ALTER_TUNING_TASK_XML IMPLEMENT_TUNING_TASK_XML REPORT_TUNING_TASK_XML
BUILD_STASH_XML IMPORT_PLAN_OBJECT_XML RESET_TUNING_TASK
CANCEL_TUNING_TASK IMPORT_SQL_PROFILE RESUME_TUNING_TASK
CAPTURE_CURSOR_CACHE_SQLSET INTERRUPT_TUNING_TASK SCHEDULE_TUNING_TASK
CAP_STS_CBK LIST_ALL_DIRECTORIES_XML SCRIPT_TUNING_TASK
CHECK_SQLSET_PRIVS LOAD_SQLSET SELECT_CURSOR_CACHE
CHECK_SQL_PROFILE_PRIV LOAD_SQL_PLAN_BASELINE_XML SELECT_SQLPA_TASK
CHECK_TUNING_TASK_STATUS PACK_STGTAB_SQLPROF SELECT_SQLSET
CONFIGURE_TUNING_TASK_XML PACK_STGTAB_SQLSET SELECT_SQL_TRACE
CREATE_SQLSET PREPARE_AUTOSTS_STATEMENT SELECT_WORKLOAD_REPOSITORY
CREATE_SQL_PLAN_BASELINE PREPARE_AWR_STATEMENT SET_AUTO_TUNING_TASK_PARAMETER
CREATE_STGTAB_SQLPROF PREPARE_SQLSET_STATEMENT SET_TUNING_TASK_PARAMETER
CREATE_STGTAB_SQLSET REMAP_STGTAB_SQLPROF SQLSET_PROGRESS_STATS
CREATE_TUNING_TASK REMAP_STGTAB_SQLSET SQLTEXT_TO_SIGNATURE
DELETE_SQLSET REMOVE_SQLSET_REFERENCE TRANSFORM_SQLSET_CURSOR
DROP_PLAN_OBJECT_XML REPORT_AUTO_TUNING_TASK UNPACK_STGTAB_SQLPROF
DROP_SQLSET REPORT_SQL_DETAIL UNPACK_STGTAB_SQLSET
DROP_SQL_PROFILE REPORT_SQL_DETAIL_XML UPDATE_SQLSET
DROP_TUNING_TASK REPORT_SQL_MONITOR Demos
EXAMINE_STGTAB    
 
ACCEPT_ALL_SQL_PROFILES
Accepts all SQL profiles as recommended by the specified SQL tuning task dbms_sqltune.accept_all_sql_profiles(
task_name        IN VARCHAR2,
category         IN VARCHAR2 := NULL,
replace          IN BOOLEAN  := FALSE,
force_match      IN BOOLEAN  := FALSE,
profile_type     IN VARCHAR2 := REGULAR_PROFILE,
autotune_period  IN NUMBER   := NULL,
execution_name   IN VARCHAR2 := NULL,
task_owner       IN VARCHAR2 := NULL,
description      IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL);
exec dbms_sqltune.accept_all_sql_profiles('TASK_3730', force_match=>TRUE, description=>''query for server by id');
 
ACCEPT_SQL_PROFILE
Create a SQL Profile for the specified tuning task recommended by SQLTune

Overload 1		 dbms_sqltune.accept_sql_profile(
task_name        IN VARCHAR2,
object_id        IN NUMBER   := NULL,
name             IN VARCHAR2 := NULL,
description      IN VARCHAR2 := NULL,
category         IN VARCHAR2 := NULL;
task_owner       IN VARCHAR2 := NULL,
replace          IN BOOLEAN  := FALSE,
force_match      IN BOOLEAN  := FALSE,
profile_type     IN VARCHAR2 := REGULAR_PROFILE,
database_link_to IN VARCHAR2 := NULL)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba
set serveroutput on

DECLARE
 tune_task_id VARCHAR2(20);
BEGIN
  -- create a task
  tune_task_id := dbms_sqltune.accept_sql_profile('UW Tune', 1, 'UW Profile');
  dbms_output.put_line('Task ID: ' || tune_task_id);
END;
/
Overload 2 dbms_sqltune.accept_sql_profile(
task_name        \IN VARCHAR2,
object_id        IN NUMBER   := NULL,
name             IN VARCHAR2 := NULL,
description      IN VARCHAR2 := NULL,
category         IN VARCHAR2 := NULL,
task_owner       IN VARCHAR2 := NULL,
replace          IN BOOLEAN  := FALSE,
force_match      IN BOOLEAN  := FALSE,
profile_type     IN VARCHAR2 := REGULAR_PROFILE,
database_link_to IN VARCHAR2 := NULL);
exec dbms_sqltune.accept_sql_profile('TASK_3730', force_match=>TRUE);
 
ADD_SQLSET_REFERENCE
Adds a new reference to an existing SQLSet to indicate its use by a client dbms_sqltune.add_sqlset_reference(
sqlset_name  IN VARCHAR2,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN NUMBER;
SELECT sqlset_name, sqlset_owner, description
FROM all_sqlset_references;

set serveroutput on

DECLARE
 n NUMBER;
BEGIN
  n := dbms_sqltune.add_sqlset_reference('UWSet', 'New Desc');
  dbms_output.put_line(TO_CHAR(n));
END;
/

SELECT sqlset_name, sqlset_owner, description
FROM all_sqlset_references;
 
ALTER_PLAN_OBJECT_XML
Alters an existing SQL plan object dbms_sqltune.alter_plan_object_xml(
obj_name   IN VARCHAR2,
obj_type   IN VARCHAR2,
attr_name  IN VARCHAR2,
attr_value IN VARCHAR2,
sql_handle IN VARCHAR2 DEFAULT NULL,
show_sql   IN NUMBER   DEFAULT 0)
RETURN XMLTYPE;
TBD
 
ALTER_SQL_PROFILE
Alters specific attributes of an existing SQL Profile object dbms_sqltune.alter_sql_profile(
name           IN VARCHAR2,
attribute_name IN VARCHAR2,
value          IN VARCHAR2);
exec dbms_sqltune.alter_sql_profile('emp_dept_profile', 'STATUS', 'DISABLED');
 
ALTER_TUNING_TASK_XML
Called to alter an existing SQL tuning advisor task dbms_sqltune.alter_tuning_task_xml(
task_name     IN VARCHAR2,
action_type   IN VARCHAR2,  -- drop, interrupt, resume, cancel, reset
show_sql_only IN NUMBER := 0)
RETURN XMLTYPE;
DECLARE
 x XMLType;
BEGIN
  x := dbms_sqltune.alter_tuning_task_xml('TASK_3730', 'RESUME');
END;
/
 
BUILD_STASH_XML
Undocumented dbms_sqltune.build_stash_xml(
session_id           IN NUMBER         DEFAULT NULL,
session_serial       IN NUMBER         DEFAULT NULL,
session_inst_id      IN NUMBER         DEFAULT NULL,
px_mode              IN VARCHAR2       DEFAULT 'yes',
start_time           IN DATE           DEFAULT NULL,
end_time             IN DATE           DEFAULT NULL,
missing_seconds      IN NUMBER         DEFAULT NULL,
instance_low_filter  IN NUMBER         DEFAULT 0,
instance_high_filter IN NUMBER         DEFAULT 10000,
bucket_max_count     IN NUMBER         DEFAULT 128,
bucket_interval      IN NUMBER         DEFAULT NULL,
report_level         IN VARCHAR2       DEFAULT 'TYPICAL',
cpu_cores            IN BINARY_INTEGER DEFAULT NULL,
is_hyper             IN VARCHAR2       DEFAULT NULL)
RETURN XMLType;
DECLARE
 xVal XMLType;
BEGIN
  xVal := dbms_sqltune.build_stash_xml(sys_context('USERENV', 'SESSION_USERID'));
END;
/
 
CANCEL_TUNING_TASK
Cancel the named executing task dbms_sqltune.cancel_tuning_task(task_name IN VARCHAR2);
See Tuning Task Demo at Page Bottom
 
CAPTURE_CURSOR_CACHE_SQLSET
Over a specified time interval this procedure incrementally captures a workload from the cursor cache into a SQL tuning set dbms_sqltune.capture_cursor_cache_sqlset(
sqlset_name     IN VARCHAR2,
time_limit      IN POSITIVE := 1800,    -- time in seconds to execute
repeat_interval IN POSITIVE := 300,     -- pause time between samples
capture_option  IN VARCHAR2 := 'MERGE', -- options INSERT, UPDATE, MERGE
capture_mode    IN NUMBER   := MODE_REPLACE_OLD_STATS,
basic_filter    IN VARCHAR2 := NULL,    -- cursor cache filter
sqlset_owner    IN VARCHAR2 := NULL,    -- schema owner
recursive_sql   IN VARCHAR2 := HAS_RECURSIVE_SQL);
exec dbms_sqltune.capture_cursor_cache_sqlset('UWSet', 1000);
 
CAP_STS_CBK
Undocumented: For internal usage only dbms_sqltune.
sqlset_name   IN VARCHAR2,
iterations    IN POSITIVE,
cap_option    IN VARCHAR2,
cap_mode      IN NUMBER,
cbk_proc_name IN VARCHAR2,
basic_filter  IN VARCHAR2 := NULL,
sqlset_owner  IN VARCHAR2 := NULL);
TBD
 
CHECK_SQLSET_PRIVS
Returns ORA-13752 if the user does not have the appropriate SQLSET privileges dbms_sqltune.check_sqlset_privs(
sqlset_name   IN VARCHAR2,
sqlset_owner  IN VARCHAR2,
sqlset_create IN BOOLEAN := FALSE,
read_only     IN BOOLEAN := FALSE);
conn / as sysdba

exec dbms_sqltune.check_sqlset_privs('UWSet3','UWCLASS', TRUE, FALSE);

conn scott/tiger@pdbdev

exec dbms_sqltune.check_sqlset_privs('UWSet3','UWCLASS', TRUE, FALSE);
*
ERROR at line 1:
ORA-13752: User "SCOTT" must be SYS or must have the "ADMINISTER ANY SQL TUNING SET" privilege.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SQLTUNE", line 524
ORA-06512: at line 1
 
CHECK_SQL_PROFILE_PRIV
Returns ORA-2000 if the current schema does not have the named system privilege dbms_sqltune.check_sql_profile_priv(priv IN VARCHAR2);
conn / as sysdba

exec dbms_sqltune.check_sql_profile_priv('DBA');

conn scott/tiger@pdbdev

exec dbms_sqltune.check_sql_profile_priv('DBA');
*
ERROR at line 1:
ORA-20000: check_priv: invalid priv specified
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL2", line 249
ORA-06512: at "SYS.DBMS_SQLTUNE", line 8824
ORA-06512: at line 1
 
CHECK_TUNING_TASK_STATUS
Called to check the status of a sqltune tuning task as recorded in the advisor framework dbms_sqltune.check_tuning_task_status(
task_name  IN VARCHAR2,
task_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba

DECLARE
 retVal VARCHAR2(12);
BEGIN
  retVal := dbms_sqltune.check_tuning_task_status('TASK_3730', 'UWCLASS');
  dbms_output.put_line(retVal);
END;
/

Valid Return Values
CANCELED COMPLETED EXECUTING FATAL ERROR, INITIAL INTERRUPTED
 
CONFIGURE_TUNING_TASK_XML
Undocumented: Called to configure an existing SQL tuning advisor task.

It appears from testing that the task must be actively running at the time this command is issued or it will fail, as shown at right, saying the task does not exist while clearly it does as proven by the drop statement.		 dbms_sqltune.configure_tuning_task_xml(
task_name       IN VARCHAR2,
task_parameters IN arglist,
task_owner      IN VARCHAR2 := NULL,
show_sql_only   IN NUMBER   := 0)
RETURN XMLTYPE;

Valid ArgList Parameters
AUTOIMPL_STATUS AUTO_MAX_PROFILES EXEC_MAX_PROFILES PERSQL_TIME_LIMIT STATUS
set serveroutput on

DECLARE
 retVal VARCHAR2(4000);
 SqlStr  CLOB := 'SELECT SUBSTR(version,1+INSTR(version,''.'',1,2), INSTR(version,''.'',1,3) - INSTR(version,''.'',1,2)-1) FROM v$instance';
BEGIN
  retVal := dbms_sqltune.create_tuning_task(SqlStr);
  dbms_output.put_line(retVal);
END;
/

col task_name format a25
col advisor_name format a20

SELECT owner, task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
ORDER BY 3;

DECLARE
 xVal     XMLType;
 argArray dbms_sqltune.arglist;
BEGIN
  argArray(1) := 'persql_time_limit=10';
  xVal := dbms_sqltune.configure_tuning_task_xml('TASK_1242', argArray);
END;
/
*
ERROR at line 1:
ORA-20000: invalid advisor task name
ORA-06512: at "SYS.DBMS_SQLTUNE", line 2028
ORA-06512: at line 6

exec dbms_sqltune.drop_tuning_task('TASK_1242');
 
CREATE_SQLSET
Creates a SQLSET object

Overload 1		 dbms_sqltune.create_sqlset(
sqlset_name  IN VARCHAR2,
description  IN VARCHAR2 := NULL
sqlset_owner IN VARCHAR2 := NULL);
SELECT * FROM user_sqlset;

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

set linesize 121
col name format a10
col description format a30

SELECT * FROM user_sqlset;

exec dbms_sqltune.drop_sqlset('UWSet', 'UWCLASS');

SELECT * FROM user_sqlset;
Returns the name of the SQLSET created

Overload 2		 dbms_sqltune.create_sqlset(
sqlset_name  IN VARCHAR2 := NULL,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SELECT * FROM user_sqlset;

set serveroutput on

DECLARE
 retval VARCHAR2(100);
BEGIN
  retval := dbms_sqltune.create_sqlset('UWSet', 'Test Tuning Set', 'UWCLASS');
  dbms_output.put_line(retval);
END;
/

set linesize 121
col name format a10
col description format a30

SELECT * FROM user_sqlset;

exec dbms_sqltune.drop_sqlset('UWSet', 'UWCLASS');

SELECT * FROM user_sqlset;
 
CREATE_SQL_PLAN_BASELINE
Creates a plan baseline for an existing plan dbms_sqltune.check_sql_plan_baseline(
task_name        IN VARCHAR2,
object_id        IN NUMBER   := NULL,
plan_hash_value  IN NUMBER,
owner_name       IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL);
TBD
 
CREATE_STGTAB_SQLPROF
Creates the staging table used for copying SQL profiles from one system to another dbms_sqltune.create_stgtab_sqlprof(
table_name      IN VARCHAR2,
schema_name     IN VARCHAR2 := NULL,
tablespace_name IN VARCHAR2 := NULL);
exec dbms_sqltune.create_stgtab_sqlprof('STGTAB', 'UWCLASS', 'UWDATA');

SELECT table_name
FROM user_all_tables;

desc stgtab
 
CREATE_STGTAB_SQLSET
Creates a staging table through which SQL Tuning Sets are imported and exported dbms_sqltune.create_stgtab_sqlset(
table_name      IN VARCHAR2,
schema_name     IN VARCHAR2 := NULL,
tablespace_name IN VARCHAR2 := NULL,
db_version      IN NUMBER   := NULL);
exec dbms_sqltune.create_stgtab_sqlset('STGTAB', 'UWCLASS', 'UWDATA');

SELECT table_name, table_type
FROM user_all_tables;

desc stgtab
 
CREATE_TUNING_TASK
Prepare the tuning of a single statement given its text based on a Task Name

Overload 1		 dbms_sqltune.create_tuning_task(
sql_text         IN CLOB,
bind_list        IN sql_binds := NULL,
user_name        IN VARCHAR2  := NULL,
scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
task_name        IN VARCHAR2  := NULL,
description      IN VARCHAR2  := NULL,
con_name         IN VARCHAR2  := NULL,
database_link_to IN VARCHAR2  := NULL)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 retVal VARCHAR2(4000);
 SqlStr CLOB := 'SELECT SUBSTR(version,1+INSTR(version,''.'',1,2),
               INSTR(version,''.'',1,3)- INSTR(version,''.'',1,2)-1) FROM gv$instance';
BEGIN
  retVal := dbms_sqltune.create_tuning_task(SqlStr);
  dbms_output.put_line(retVal);
END;
/

set linesize 121
col advisor_name format a30

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
ORDER BY 3;

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
WHERE task_name LIKE 'TASK%';

exec dbms_sqltune.execute_tuning_task('TASK_3730');

set long 100000

SELECT dbms_sqltune.report_tuning_task('TASK_3730')
FROM dual;

col execution_name format a15
col operation format a20
col options format a10

SELECT task_id, execution_name, object_id, operation, options, cpu_cost, io_cost
FROM user_sqltune_plans
WHERE task_id = 3730;

exec dbms_sqltune.drop_tuning_task('TASK_3730');
Prepare the tuning of a single statement given its text based on a SQL_ID

Overload 2		 dbms_sqltune.create_tuning_task(
sql_id           IN VARCHAR2,
plan_hash_value  IN NUMBER   := NULL,
scope            IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit       IN NUMBER   := TIME_LIMIT_DEFAULT,
task_name        IN VARCHAR2 := NULL,
description      IN VARCHAR2 := NULL,
con_name         IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SELECT SUBSTR(version, 1+INSTR(version,'.',1,2), INSTR(version,'.',1,3)-INSTR(version,'.',1,2)-1)
FROM v$instance;

SELECT sql_id, sql_text
FROM gv$open_cursor
WHERE rownum < 11;

DECLARE
 ret_val VARCHAR2(4000);
BEGIN
  ret_val := dbms_sqltune.create_tuning_task(
  task_name=>'OPEN CUR',
  sql_id=>'asvzxj61dc5vs');

  dbms_sqltune.execute_tuning_task('OPEN CUR');
END;
/

set long 100000

SELECT dbms_sqltune.report_tuning_task('OPEN CUR')
FROM dual;

exec dbms_sqltune.drop_tuning_task('OPEN CUR');
Prepare the tuning of a single statement given its text based on a SQL_ID

Overload 3		 dbms_sqltune.create_tuning_task(
begin_snap       IN NUMBER,
end_snap         IN NUMBER,
sql_id           IN VARCHAR2,
plan_hash_value  IN NUMBER   := NULL,
scope            IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit       IN NUMBER   := TIME_LIMIT_DEFAULT,
task_name        IN VARCHAR2 := NULL,
description      IN VARCHAR2 := NULL,
con_name         IN VARCHAR2 := NULL,
dbid             IN NUMBER   := NULL,
database_link_to IN VARCHAR2 := NULL)
RETURN VARCHAR2;
conn sh/sh@pdbdev

SELECT /* SQLTUNE */ s.prod_id, s.cust_id, COUNT(*)
FROM sales s, customers c, products p
WHERE s.prod_id = p.prod_id
AND s.cust_id = c.cust_id
AND s.cust_id LIKE '2%'
GROUP BY s.prod_id, s.cust_id
HAVING COUNT(*) > 20;

conn sys@pdbdev as sysdba

-- force AWR snapshot creation
set serveroutput on

DECLARE
 i dba_hist_snapshot.snap_id%TYPE;
BEGIN
  i := dbms_workload_repository.create_snapshot;
  dbms_output.put_line(TO_CHAR(i));
END;
/

col sql_text format a50

SELECT sql_id, substr(sql_text, 1, 50) SQL_TEXT
FROM v$sql
WHERE sql_text LIKE '%SQLTUNE%';

SQL_ID        SQL_TEXT
------------- --------------------------------------------------
53wam8t6512hz SELECT sql_id, substr(sql_text, 1, 50) SQL_TEXT FR
4djqpjbrmf9vf SELECT /* SQLTUNE */ s.prod_id, s.cust_id, COUNT(*

col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_NUMBER = s.instance_NUMBER
AND di.startup_time = s.startup_time
ORDER BY snap_id;
                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
...
orabase2     ORABASE2          2571 17 JAN 2018 21:00      1
                               2572 17 JAN 2018 21:20      1
                               2573 17 JAN 2018 21:40      1
                               2574 17 JAN 2018 22:00      1
                               2575 17 JAN 2018 22:20      1
                               2576 18 JAN 2018 05:26      1
                               2577 18 JAN 2018 05:40      1
                               2578 18 JAN 2018 06:00      1
                               2579 18 JAN 2018 06:20      1
                               2580 18 JAN 2018 07:44      1
                               2581 18 JAN 2018 08:00      1
                               2582 18 JAN 2018 08:20      1
                               2583 18 JAN 2018 08:40      1
                               2584 18 JAN 2018 09:00      1
                               2585 18 JAN 2018 09:20      1
                               2586 18 JAN 2018 09:40      1
                               2587 18 JAN 2018 10:00      1
                               2588 18 JAN 2018 10:11      1

grant dba to sh;
grant advisor to sh;

conn sh/sh@pdbdev

set serveroutput on

DECLARE
 ttask VARCHAR2(100);
BEGIN
  ttask := dbms_sqltune.create_tuning_task(
    begin_snap => 2580,
    end_snap => 2588,
    sql_id => '4djqpjbrmf9vf',
    scope => dbms_sqltune.scope_comprehensive,
    time_limit => 60,
    task_name => '4djqpjbrmf9vf_AWR_tuning_task',
    description => 'Tuning task for statement 4djqpjbrmf9vf in AWR.');

  dbms_output.put_line('Tuning Task: ' || ttask);
END;
/

-- or create for a specific a statement from the cursor cache
DECLARE
 ttask VARCHAR2(100);
BEGIN
  ttask := dbms_sqltune.create_tuning_task(
    sql_id => '4djqpjbrmf9vf',
    scope => dbms_sqltune.scope_comprehensive,
    time_limit => 60,
    task_name => '4djqpjbrmf9vf_tuning_task',
    description => 'Tuning task for statement 4djqpjbrmf9vf');

  dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- or create from an SQL tuning set
DECLARE
 l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := dbms_sqltune.create_tuning_task(
    sqlset_name => 'test_sql_tuning_set',
    scope => dbms_sqltune.scope_comprehensive,
    time_limit => 60,
    task_name => 'sqlset_tuning_task',
    description => 'Tuning task for an SQL tuning set.');

 dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- or create for a manually specified statement
DECLARE
 l_sql VARCHAR2(500);
 l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql := 'SELECT e.*, d.* ' ||
  'FROM emp e JOIN dept d ON e.deptno = d.deptno ' ||
  'WHERE NVL(empno, ''0'') = :empno';

  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
    sql_text => l_sql,
    bind_list => sql_binds(anydata.ConvertNumber(100)),
    user_name => 'scott',
    scope => dbms_sqltune.scope_comprehensive,
    time_limit => 60,
    task_name => 'emp_dept_tuning_task',
    description => 'Tuning task for an EMP to DEPT join query.');

  dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

SELECT task_id, task_name, created, advisor_name, status
FROM user_advisor_tasks;

exec dbms_sqltune.execute_tuning_task('4djqpjbrmf9vf_AWR_tuning_task');

-- interrupt and resume the tuning task
exec dbms_sqltune.interrupt_tuning_task('4djqpjbrmf9vf_AWR_tuning_task');

SELECT task_id, task_name, execution_start, execution_end, status
FROM user_advisor_log;

exec dbms_sqltune.resume_tuning_task('4djqpjbrmf9vf_AWR_tuning_task');

set long 100000

SELECT dbms_sqltune.report_tuning_task('4djqpjbrmf9vf_AWR_tuning_task')
FROM dual;

col execution_name format a15
col operation format a20
col options format a10

SELECT task_id, execution_name, object_id, operation, options, cpu_cost, io_cost
FROM user_sqltune_plans
WHERE task_id = 3734;

conn sys@pdbdev as sysdba
revoke dba from sh;
revoke advisor from sh;
Prepare the tuning of a single statement given its text based on a SQL_ID

Overload 4		 dbms_sqltune.create_tuning_task(
sqlset_name       IN VARCHAR2
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
rank1             IN VARCHAR2 := NULL,
rank2             IN VARCHAR2 := NULL,
rank3             IN VARCHAR2 := NULL,
result_percentage IN NUMBER   := NULL,
result_limit      IN NUMBER   := NULL,
scope             IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit        IN NUMBER   := TIME_LIMIT_DEFAULT,
task_name         IN VARCHAR2 := NULL,
description       IN VARCHAR2 := NULL,
plan_filter       IN VARCHAR2 := 'MAX_ELAPSED_TIME',
sqlset_owner      IN VARCHAR2 := NULL,
database_link_to  IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD
Prepare the tuning of a single statement given its text based on a SQL Performance Analyzer

Overload 5		 dbms_sqltune.create_tuning_task(
spa_task_name    IN VARCHAR2,
spa_task_owner   IN VARCHAR2 := NULL,
spa_compare_exec IN VARCHAR2 := NULL,
basic_filter     IN VARCHAR2 := NULL,
time_limit       IN NUMBER   := TIME_LIMIT_DEFAULT,
task_name        IN VARCHAR2 := NULL,
description      IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD
 
DELETE_SQLSET
Deletes a set of SQL statements from a SQL tuning set dbms_sqltune.delete_sqlset(
sqlset_name  IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL);
conn uwclass/uwclass@pdbdev

col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

DECLARE
 l_cursor dbms_sqltune.sqlset_cursor;
BEGIN
  dbms_sqltune.create_sqlset('UWSet5', 'Test Tuning Set', 'UWCLASS');

  OPEN l_cursor FOR
  SELECT VALUE(p)
  FROM TABLE (dbms_sqltune.select_workload_repository (
  2576,   -- begin_snap
  2592,   -- 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(sqlset_name => 'UWSet5', populate_cursor => l_cursor);
END;
/

SELECT * FROM user_sqlset_statements;

exec dbms_sqltune.delete_sqlset('UWSet5', sqlset_owner=>'UWCLASS');

SELECT * FROM user_sqlset_statements;
 
DROP_PLAN_OBJECT_XML
Drops an existing SQL plan object dbms_sqltune.drop_plan_object_xml(
obj_name   IN VARCHAR2,
obj_type   IN VARCHAR2,
sql_handle IN VARCHAR2 DEFAULT NULL,
show_sql   IN NUMBER   DEFAULT 0)
RETURN XMLTYPE;
desc v$sql_plan_monitor

col plan_object_name format a30

SELECT plan_object_name, plan_object_type
FROM v$sql_plan_monitor
WHERE plan_object_type IS NOT NULL
ORDER BY 1;

SELECT dbms_sqltune.drop_plan_object_xml('TEST', 'PLAN_BASELINE', NULL, 1)
FROM dual;
 
DROP_SQLSET
If not active drops a SQL Tuning Set dbms_sqltune.drop_sqlset(
sqlset_name  IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL);
SELECT * FROM user_sqlset;

exec dbms_sqltune.drop_sqlset('UWSet5', 'UWCLASS');

-- show metadata SQL
 
DROP_SQL_PROFILE
Drops the named SQL Profile dbms_sqltune.drop_sql_profile(
name   IN VARCHAR2,
ignore IN BOOLEAN := FALSE);
SELECT con_id, name, created, last_modified
FROM cdb_sql_profiles;

exec dbms_sqltune.drop_sql_profile('emp_dept_profile', TRUE);

SELECT con_id, name, created, last_modified
FROM cdb_sql_profiles;
 
DROP_TUNING_TASK
Drop a tuning task dbms_sqltune.drop_tuning_task(task_name IN VARCHAR2);
SELECT owner, task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
ORDER BY 3;

exec dbms_sqltune.drop_tuning_task('TASK_214');

SELECT owner, task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
ORDER BY 3;
 
EXAMINE_STGTAB
Undocumented: For internal use only dbms_sqltune.examine_stgtab(
stgtab_owner IN  VARCHAR2,
stgtab       IN  VARCHAR2,
sts_name     OUT VARCHAR2,
sts_owner    OUT VARCHAR2);
TBD
 
EXECUTE_TUNING_TASK
Run a tuning task function that returns the name of the new execution

Overload 1		 dbms_sqltune.execute_tuning_task(
task_name        IN VARCHAR2,
execution_name   IN VARCHAR2             := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc   IN VARCHAR2             := NULL,
database_link_to IN VARCHAR2             := NULL)
RETURN VARCHAR2;
BEGIN
  dbms_advisor.arglist('time_limit', 12, 'username', 'foo')
  dbms_sqltune.execute_tuning_task('TASK_188');
END;
/
Run a tuning task procedure

Overload 2		 dbms_sqltune.execute_tuning_task(
task_name        IN VARCHAR2,
execution_name   IN VARCHAR2             := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc   IN VARCHAR2             := NULL,
database_link_to IN VARCHAR2             := NULL)
exec dbms_sqltune.execute_tuning_task('TASK_188');
 
EXPORT_PLAN_OBJECT_XML
Exports an existing SQL plan object using the DataPump API dbms_sqltune.export_plan_object_xml(
obj_name     IN VARCHAR2,
obj_type     IN VARCHAR2,
dir_name     IN VARCHAR2,
obj_category IN VARCHAR2 DEFAULT NULL,
sql_handle   IN VARCHAR2 DEFAULT NULL,
show_sql     IN NUMBER   DEFAULT 0)
RETURN XMLTYPE;
TBD
 
EXTRACT_BIND
Given the value of a bind_data column captured in v$sql and a bind position, this function returns the value of the bind variable at that position in the SQL statement dbms_sqltune.extract_bind(
bind_data IN RAW,
bind_pos  IN PLS_INTEGER)
RETURN SQL_BIND;
desc sys.sql_bind

See GET_BINDS_COUNT Demo Below
 
EXTRACT_BINDS
Given the value of a bind_data column captured in v$sql this function returns the collection (list) of bind values associated with the corresponding SQL statement dbms_sqltune.extract_binds(bind_data IN RAW)
RETURN SQL_BIND_SET PIPELINED;
desc sys.sql_bind_set

DECLARE
 bdata  RAW(2000);
 x      VARCHAR2(30);
 y      VARCHAR2(40);
 z      DATE;
 sb     sys.sql_bind;
BEGIN
  SELECT bind_data
  INTO bdata
  FROM v$sql
  WHERE is_bind_sensitive = 'Y'
  AND rownum = 1;
  dbms_output.put_line(bdata);

  SELECT datatype_string, value_string, last_captured
  INTO x, y, z
  FROM TABLE(dbms_sqltune.extract_binds(bdata));
  dbms_output.put_line('Data Type:  ' || x);
  dbms_output.put_line('Val String: ' || y);
  dbms_output.put_line('Last Capt:  ' || TO_CHAR(z));

  sb := dbms_sqltune.extract_bind(bdata, 1);
  dbms_output.put_line('Val String: ' || sb.value_string);
END;
/
 
IMPLEMENT_TUNING_TASK
Implement a set of SQL Profile recommendations made by the SQL Tuning Advisor. Calling it is analogous to calling script_tuning_task and then running the script. dbms_sqltune.implement_tuning_task(
task_name        IN VARCHAR2,
rec_type         IN VARCHAR2 := REC_TYPE_SQL_PROFILES,
owner_name       IN VARCHAR2 := NULL,
execution_name   IN VARCHAR2 := NULL,  -- if null use most recent
database_link_to IN VARCHAR2 := NULL);
TBD
 
IMPLEMENT_TUNING_TASK_XML
Implement one or a set of recommendations made by the SQL Tuning Advisor. Calling it is analogous to calling script_tuning_task and then running the script. This function is mainly called/used by EM express. dbms_sqltune.implement_tuning_task_xml(
task_name       IN VARCHAR2,
rec_type        IN VARCHAR2 := REC_TYPE_SQL_PROFILES,
object_id       IN NUMBER   := NULL,
owner_name      IN VARCHAR2 := NULL,
execution_name  IN VARCHAR2 := NULL,
plan_hash       IN NUMBER   := NULL,
category        IN VARCHAR2 := NULL,
force_match     IN NUMBER   := 0,
autotune_period IN NUMBER   := NULL,
show_sql_only   IN NUMBER   := 0,
rec_id          IN NUMBER   := NULL)
RETURN XMLTYPE;
exec dbms_sqltune.implement_tuning_task_xml('TASK_3730');
 
IMPORT_PLAN_OBJECT_XML
Imports an existing SQL plan object using the DataPump API dbms_sqltune.import_plan_object_xml(
obj_type  IN VARCHAR2,
dir_name  IN VARCHAR2,
file_name IN VARCHAR2,
show_sql  IN NUMBER DEFAULT 0)
RETURN XMLTYPE;
SELECT dbms_sqltune.import_plan_object_xml('PLAN_BASELINE', 'CTEMP', 'planobjs.xml')
FROM dual;
 
IMPORT_SQL_PROFILE
This procedure is only used by import

Overload 1		 dbms_sqltune.import_sql_profile(
sql_text    IN CLOB,
profile     IN sqlprof_attr,
name        IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category    IN VARCHAR2 := NULL,
validate    IN BOOLEAN  := TRUE,
replace     IN BOOLEAN  := FALSE,
force_match IN BOOLEAN  := FALSE);
TBD
Overload 2 dbms_sqltune.import_sql_profile(
sql_text    IN CLOB,
profile_xml IN CLOB,
name        IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category    IN VARCHAR2 := NULL,
validate    IN BOOLEAN  := TRUE,
replace     IN BOOLEAN  := FALSE,
force_match IN BOOLEAN  := FALSE);
TBD
 
INTERRUPT_TUNING_TASK
Interrupts the currently executing tuning task to allow access intermediate result data dbms_sqltune.interrupt_tuning_task(task_name IN VARCHAR2);
exec dbms_sqltune.interrupt_tuning_task('TASK_188');
 
LIST_ALL_DIRECTORIES_XML
List all the dirctory object names from the all_directories view. One of the directory objects listed is then chosen during export/import of SQL plan management objects. dbms_sqltune.list_all_directories_xml(show_sql IN NUMBER DEFAULT NULL)
RETURN XMLTYPE;
SELECT dbms_sqltune.list_all_directories_xml(1)
FROM dual;

DBMS_SQLTUNE.LIST_ALL_DIRECTORIES_XML(1)
-----------------------------------------------------------------------------
<report db_version="18.0.0.0.0" cpu_cores="4" hyperthread="N" con_id="1"
con_name="CDB$ROOT" timezone_offset="0" packs="2">
  <report_id><![CDATA[/orarep/sql_detail/list_all_directories]]></report_id>
  <script><![CDATA[
    select
      xmlelement(
        "dir_objects",
          xmlagg(xmlelement(
            "dir_object",
              xmlelement(
                "name", a.directory_name))))
    from sys.all_directories a
    ;]]></script>
</report>
 
LOAD_SQLSET
Populates the SQLSet with a set of selected SQL dbms_sqltune.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
  dbms_sqltune.create_sqlset('SH_TSet1', 'Test Tuning Set', 'SH');

  OPEN l_cursor FOR
  SELECT VALUE(p)
  FROM TABLE (dbms_sqltune.select_workload_repository (
  4006,   -- begin_snap
  4014,   -- 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(sqlset_name => 'SH_TSet1', populate_cursor => l_cursor);
END;
/
 
LOAD_SQL_PLAN_BASELINE_XML
Loads and creates a new plan baseline from the cursor cache given the sql's sql_id and plan_hash_value. The function is a simple wrapper on top of function dbms_spm.load_plans_from_cursor_cache. dbms_sqltune.load_sql_plan_baseline_xml(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
show_sql        IN NUMBER DEFAULT 0)
RETURN XMLTYPE;
SELECT dbms_sqltune.load_sql_plan_baseline_xml('3y6pgnl2ubw7g', NULL, 1)
FROM dual;
 
PACK_STGTAB_SQLPROF
Copies profile data from the SYS  schema into the staging table dbms_sqltune.pack_stgtab_sqlprof(
profile_name         IN VARCHAR2 := '%',
profile_category     IN VARCHAR2 := 'DEFAULT',
staging_table_name   IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);
TBD
 
PACK_STGTAB_SQLSET
Copies one or more SQL tuning sets from their location in the SYS schema to a staging table created by CREATE_STGTAB_SQLSET dbms_sqltune.pack_stgtab_sqlset(
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);
TBD
 
PREPARE_AUTOSTS_STATEMENT (new 21c)
Undocumented dbms_sqltune.prepare_autosts_statement(
basic_filter       IN            VARCHAR2 := NULL,
plan_filter        IN            VARCHAR2 := NULL,
rank1              IN            VARCHAR2 := NULL,
result_limit       IN            NUMBER   := NULL,
attribute_list     IN            VARCHAR2 := NULL,
attribute_selected IN OUT NOCOPY BINARY_INTEGER)
RETURN VARCHAR2;
TBD
 
PREPARE_AWR_STATEMENT
For internal usage only dbms_sqltune.prepare_awr_statement(
begin_snap         IN     NUMBER,
end_snap           IN     NUMBER,
basic_filter       IN     VARCHAR2 := NULL,
stmt_filter        IN     BOOLEAN  := FALSE,
object_filter      IN     VARCHAR2 := NULL,
rank1              IN     VARCHAR2 := NULL,
rank2              IN     VARCHAR2 := NULL,
rank3              IN     VARCHAR2 := NULL,
result_percentage  IN     NUMBER   := 1,
result_limit       IN     NUMBER   := NULL,
attribute_list     IN     VARCHAR2 := NULL,
attribute_selected IN OUT NOCOPY BINARY_INTEGER,
flags              IN     NUMBER   := 0)
RETURN VARCHAR2;
TBD
 
PREPARE_SQLSET_STATEMENT
For internal usage only dbms_sqltune.prepare_sqlset_statement(
sqlset_name        IN     VARCHAR2,
sqlset_owner       IN     VARCHAR2,
basic_filter       IN     VARCHAR2 := NULL,
stmt_filter        IN     BOOLEAN  := FALSE,
object_filter      IN     VARCHAR2 := NULL,
plan_filter        IN     VARCHAR2 := NULL,
rank1              IN     VARCHAR2 := NULL,
rank2              IN     VARCHAR2 := NULL,
rank3              IN     VARCHAR2 := NULL,
result_percentage  IN     NUMBER   := 1,
result_limit       IN     NUMBER   := NULL,
attribute_list     IN     VARCHAR2 := NULL,
attribute_selected IN OUT NOCOPY BINARY_INTEGER,
wrap_obj_ctor      IN     BOOLEAN  := FALSE,
check_binds        IN     BOOLEAN  := TRUE,
sts_id                OUT NUMBER,
first_rows_hint    IN     BOOLEAN  := TRUE)
RETURN VARCHAR2;
TBD
 
REMAP_STGTAB_SQLPROF
Change the profile data values kept in the staging table prior to performing an unpack operation. The procedure can be used to change the category of a profile. It can be used to change the name of a profile if one already exists on the system with the same name. dbms_sqltune.remap_stgtab_sqlprof(
old_profile_name     IN VARCHAR2,
new_profile_name     IN VARCHAR2 := NULL,
new_profile_category IN VARCHAR2 := NULL,
staging_table_name   IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);
TBD
 
REMAP_STGTAB_SQLSET
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_sqltune.remap_stgtab_sqlset(
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);
TBD
 
REMOVE_SQLSET_REFERENCE
Deactivates a SQL tuning set dbms_sqltune.remove_sqlset_reference(
sqlset_name  IN VARCHAR2,
reference_id IN NUMBER,
sqlset_owner IN VARCHAR2 := NULL,
force_remove IN NUMBER := 0);
col sqlset_name format a20
col description format a30

SELECT sqlset_name, id, owner, description, created
FROM user_sqlset_references;

BEGIN
  dbms_sqltune.remove_sqlset_reference('UW Set', 1);
END;
/

SELECT sqlset_name, id, owner, description, created
FROM user_sqlset_references;
 
REPORT_AUTO_TUNING_TASK
Get a report from the automatic tuning task. This differs from the  report_tuning_task API in that it takes a range of subtasks to report on dbms_sqltune.report_auto_tuning_task(
begin_exec   IN VARCHAR2 := NULL,
end_exec     IN VARCHAR2 := NULL,
type         IN VARCHAR2 := TYPE_TEXT,
level        IN VARCHAR2 := LEVEL_TYPICAL,
section      IN VARCHAR2 := SECTION_ALL,
object_id    IN NUMBER   := NULL,
result_limit IN NUMBER   := NULL)
RETURN CLOB;
conn / as sysdba

SELECT dbms_sqltune.report_auto_tuning_task FROM dual;

REPORT_AUTO_TUNING_TASK
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                   : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                  : SYS
Workload Type                      : Automatic High-Load SQL Workload
Execution Count                    : 30
Current Execution                  : EXEC_3551
Execution Type                     : TUNE SQL
Scope                              : COMPREHENSIVE
Global Time Limit(seconds)         : 3600
Per-SQL Time Limit(seconds)        : 1200
Completion Status                  : COMPLETED
Started at                         : 04/26/2021 22:00:02
Completed at                       : 04/26/2021 22:00:23
Number of Candidate SQLs           : 6
Cumulative Elapsed Time of SQL (s) : 16
-------------------------------------------------------------------------------
There are no recommendations to improve the statements in the workload.
-------------------------------------------------------------------------------
 
REPORT_SQL_DETAIL
Builds a report for a specific sql_id. For each sql_id it gives various statistics and details as obtained from the v$ views dbms_sqltune.report_sql_detail(
sql_id              IN VARCHAR2 DEFAULT NULL,
sql_plan_hash_value IN NUMBER   DEFAULT NULL,
start_time          IN DATE     DEFAULT NULL,
duration            IN NUMBER   DEFAULT NULL,
inst_id             IN NUMBER   DEFAULT NULL,
dbid                IN NUMBER   DEFAULT NULL,
event_detail        IN VARCHAR2 DEFAULT 'yes',
bucket_max_count    IN NUMBER   DEFAULT 128,
bucket_interval     IN NUMBER   DEFAULT NULL,
top_n               IN NUMBER   DEFAULT 10,
report_level        IN VARCHAR2 DEFAULT NULL,
type                IN VARCHAR2 DEFAULT 'ACTIVE',
data_source         IN VARCHAR2 DEFAULT 'auto',
end_time            IN DATE     DEFAULT NULL,
duration_stats      IN NUMBER   DEFAULT NULL,
con_name            IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
TBD
 
REPORT_SQL_DETAIL_XML
Builds an XML report on behalf of report_sql_detail() dbms_sqltune.report_sql_detail_xml(
sql_id              IN VARCHAR2 DEFAULT NULL,
sql_plan_hash_value IN NUMBER   DEFAULT NULL,
start_time          IN DATE     DEFAULT NULL,
duration            IN NUMBER   DEFAULT NULL,
inst_id             IN NUMBER   DEFAULT NULL,
dbid                IN NUMBER   DEFAULT NULL,
event_detail        IN VARCHAR2 DEFAULT 'yes',
bucket_max_count    IN NUMBER   DEFAULT 128,
bucket_interval     IN NUMBER   DEFAULT NULL,
top_n               IN NUMBER   DEFAULT 10,
report_level        IN VARCHAR2 DEFAULT NULL,
data_source         IN VARCHAR2 DEFAULT 'auto',
end_time            IN DATE     DEFAULT NULL,
duration_stats      IN NUMBER   DEFAULT NULL,
con_name            IN VARCHAR2 DEFAULT NULL)
RETURN XMLType;
TBD
 
REPORT_SQL_MONITOR
Builds a report summarizing performance data from gv$active_session_history, gv$sql, gv$sql_monitor, gv$sql_plan, gv$sql_plan_monitor, and gv$session_longops to present monitoring information dbms_sqltune.report_sql_monitor(
sql_id              IN VARCHAR2 DEFAULT NULL,
session_id          IN NUMBER   DEFAULT NULL,
session_serial      IN NUMBER   DEFAULT NULL,
sql_exec_start      IN DATE     DEFAULT NULL,
sql_exec_id         IN NUMBER   DEFAULT NULL,
inst_id             IN NUMBER   DEFAULT NULL,
start_time_filter   IN DATE     DEFAULT NULL,
end_time_filter     IN DATE     DEFAULT NULL,
instance_id_filter  IN NUMBER   DEFAULT NULL,
parallel_filter     IN VARCHAR2 DEFAULT NULL,
plan_line_filter    IN NUMBER   DEFAULT NULL,
event_detail        IN VARCHAR2 DEFAULT 'yes',
bucket_max_count    IN NUMBER   DEFAULT 128,
bucket_interval     IN NUMBER   DEFAULT NULL,
base_path           IN VARCHAR2 DEFAULT NULL,
last_refresh_time   IN DATE     DEFAULT NULL,
report_level        IN VARCHAR2 DEFAULT 'TYPICAL',
type                IN VARCHAR2 DEFAULT 'TEXT',
sql_plan_hash_value IN NUMBER   DEFAULT NULL,
con_name            IN VARCHAR2 DEFAULT NULL,
report_id           IN NUMBER   DEFAULT NULL,
dbop_name           IN VARCHAR2 DEFAULT NULL,
dbop_exec_id        IN NUMBER   DEFAULT NULL)
RETURN CLOB;
SELECT dbms_sqltune.report_sql_monitor(type => 'HTML')
FROM dual;
 
REPORT_SQL_MONITOR_LIST
Builds a report for all or a sub-set of statements that have been monitored. For each statement, it gives key information and associated global statistics. dbms_sqltune.report_sql_monitor_list(
sql_id             IN VARCHAR2 DEFAULT NULL,
session_id         IN NUMBER   DEFAULT NULL,
session_serial     IN NUMBER   DEFAULT NULL,
inst_id            IN NUMBER   DEFAULT NULL,
active_since_date  IN DATE     DEFAULT NULL,
active_since_sec   IN NUMBER   DEFAULT NULL,
active_before_date IN DATE     DEFAULT NULL,
last_refresh_time  IN DATE     DEFAULT NULL,
dbop_name          IN VARCHAR2 DEFAULT NULL,
monitor_type       IN NUMBER   DEFAULT MONITOR_TYPE_ALL,
max_sqltext_length IN NUMBER   DEFAULT NULL,
top_n_count        IN NUMBER   DEFAULT NULL,
top_n_rankby       IN VARCHAR2 DEFAULT 'last_active_time',
report_level       IN VARCHAR2 DEFAULT 'TYPICAL',
auto_refresh       IN NUMBER   DEFAULT NULL,
base_path          IN VARCHAR2 DEFAULT NULL,
type               IN VARCHAR2 DEFAULT 'TEXT',
con_name           IN VARCHAR2 DEFAULT NULL,
top_n_detail_count IN NUMBER   DEFAULT NULL)
RETURN CLOB;
SELECT dbms_sqltune.report_sql_monitor_list('9y2w9gsfvzh4x')
FROM dual;
 
REPORT_SQL_MONITOR_LIST_XML
For internal usage only dbms_sqltune.report_sql_monitor_list_xml(
sql_id             IN VARCHAR2       DEFAULT NULL,
session_id         IN NUMBER         DEFAULT NULL,
session_serial     IN NUMBER         DEFAULT NULL,
inst_id            IN NUMBER         DEFAULT NULL,
active_since_date  IN DATE           DEFAULT NULL,
active_since_sec   IN NUMBER         DEFAULT NULL,
last_refresh_time  IN DATE           DEFAULT NULL,
report_level       IN VARCHAR2       DEFAULT 'TYPICAL',
auto_refresh       IN NUMBER         DEFAULT NULL,
base_path          IN VARCHAR2       DEFAULT NULL,
dbop_name          IN VARCHAR2       DEFAULT NULL,
monitor_type       IN number         DEFAULT MONITOR_TYPE_ALL,
max_sqltext_length IN NUMBER         DEFAULT NULL,
top_n_count        IN NUMBER         DEFAULT NULL,
top_n_rankby       IN VARCHAR2       DEFAULT 'last_active_time',
top_n_detail_count IN NUMBER         DEFAULT NULL,
con_name           IN VARCHAR2       DEFAULT NULL,
compress_xml       IN BINARY_INTEGER := 0,
search_key         IN VARCHAR2       DEFAULT NULL,
search_value       IN VARCHAR2       DEFAULT NULL)
RETURN XMLTYPE;
TBD
 
REPORT_SQL_MONITOR_XML
Builds a report (text, html, xml) to present the monitoring information collected on behalf of the execution of a SQL statement. This function is provided for convenience only and Oracle might change the structure or content of this report in future releases. The report output is hopefully self-descriptive.

This report summarizes performance data from gv$active_session_history, gv$sql, gv$sql_monitor, gv$sql_plan, gv$sql_plan_monitor, and gv$session_longops.		 dbms_sqltune.report_sql_monitor_xml(
sql_id              IN VARCHAR2 DEFAULT NULL,
session_id          IN NUMBER   DEFAULT NULL,
session_serial      IN NUMBER   DEFAULT NULL,
sql_exec_start      IN DATE     DEFAULT NULL,
sql_exec_id         IN NUMBER   DEFAULT NULL,
inst_id             IN NUMBER   DEFAULT NULL,
start_time_filter   IN DATE     DEFAULT NULL,
end_time_filter     IN DATE     DEFAULT NULL,
instance_id_filter  IN NUMBER   DEFAULT NULL,
parallel_filter     IN VARCHAR2 DEFAULT NULL,
plan_line_filter    IN NUMBER   DEFAULT NULL,
event_detail        IN VARCHAR2 DEFAULT 'yes',
bucket_max_count    IN NUMBER   DEFAULT 128,
bucket_interval     IN NUMBER   DEFAULT NULL,
last_refresh_time   IN DATE     DEFAULT NULL,
report_level        IN VARCHAR2 DEFAULT 'TYPICAL',
auto_refresh        IN NUMBER   DEFAULT NULL,
sql_plan_hash_value IN NUMBER   DEFAULT NULL,
dbop_name           IN VARCHAR2 DEFAULT NULL,
dbop_exec_id        IN NUMBER   DEFAULT NULL,
con_name            IN VARCHAR2 DEFAULT NULL,
report_id           IN NUMBER   DEFAULT NULL)
RETURN XMLType;
TBD
 
REPORT_TUNING_TASK
Displays tuning task results dbms_sqltune.report_tuning_task(
task_name        IN VARCHAR2,
type             IN VARCHAR2 := TYPE_TEXT,
level            IN VARCHAR2 := LEVEL_TYPICAL,
section          IN VARCHAR2 := SECTION_ALL,
object_id        IN NUMBER   := NULL,
result_limit     IN NUMBER   := NULL,
owner_name       IN VARCHAR2 := NULL,
execution_name   IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL)
RETURN CLOB;
set serveroutput on

spool c:\temp\tuning.log

DECLARE
 RetVal  CLOB;

 PROCEDURE show_msg(msg CLOB) IS
   BEGIN
     IF dbms_lob.getlength(msg) > 255 THEN
       dbms_output.put_line(SUBSTR(msg,1,255));
       show_msg(SUBSTR(msg, 256));
     ELSE
       dbms_output.put_line(msg);
     END IF;
  END;
BEGIN
  dbms_output.enable(1000000);
  RetVal := dbms_sqltune.report_tuning_task('TASK_188');
  show_msg(RetVal);
END;
/

spool off
 
REPORT_TUNING_TASK_LIST_XML
Called to display the list of SQL tuning tasks in XML format dbms_sqltune.report_tuning_task_list_xml(
result_limit    IN NUMBER := 100,
autotune_period IN NUMBER := NULL)
RETURN XMLType;
DECLARE
 xVal XMLType;
BEGIN
  xVal := dbms_sqltune.report_tuning_task_list_xml(20);
END;
/
 
REPORT_TUNING_TASK_XML
Called to display the results of a tuning task in XML format dbms_sqltune.report_tuning_task_xml(
task_name       IN VARCHAR2 := NULL,
level           IN VARCHAR2 := LEVEL_TYPICAL,
section         IN VARCHAR2 := SECTION_ALL,
object_id       IN NUMBER   := NULL,
result_limit    IN NUMBER   := 160,
owner_name      IN VARCHAR2 := NULL,
execution_name  IN VARCHAR2 := NULL,
autotune_period IN NUMBER   := NULL,
report_tag      IN VARCHAR2 := NULL)
RETURN XMLType;
DECLARE
 xVal XMLType;
BEGIN
  xVal := dbms_sqltune.report_tuning_task_xml('TASK_1242', level=>dbms_sqltune.level_all, section=>dbms_sqltune.section_findings);
END;
/
 
RESET_TUNING_TASK
Reset the currently executing tuning task to its initial state dbms_sqltune.reset_tuning_task(task_name IN VARCHAR2);
exec dbms_sqltune.reset_tuning_task('TASK_188');
 
RESUME_TUNING_TASK
Resumes a previously interrupted tuning task dbms_sqltune.resume_tuning_task(
task_name    IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL);
/* resuming a single SQL tuning task (a task that was created to tune a single SQL statement -- as compared to a SQL Tuning Set) is not supported */

BEGIN
  dbms_sqltune.interrupt_tuning_task('TASK_188');
  dbms_sqltune.resume_tuning_task('TASK_188');
END;
/
 
SCHEDULE_TUNING_TASK
Schedule the tuning of a single statement from the cursor cache given its SQL identifier. Creates a SQL tuning advisor task and then a dbms_scheduler job that executes the created tuning task at the specified start time/date.

Overload 1		 dbms_sqltune.schedule_tuning_task(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER                   := NULL,
start_date      IN TIMESTAMP WITH TIME ZONE := NULL,
scope           IN VARCHAR2                 := SCOPE_COMPREHENSIVE,
time_limit      IN NUMBER                   := TIME_LIMIT_DEFAULT,
task_name       IN VARCHAR2                 := NULL,
description     IN VARCHAR2                 := NULL,
con_name        IN VARCHAR2                 := NULL)
RETURN VARCHAR2;
DECLARE
 retVal VARCHAR2(128);
BEGIN
  retVal := dbms_sqltune.schedule_tuning_task('062savj8zgzut');
  dbms_output.put_line(retVal);
END;
/
Workload Repository format

Overload 2		 dbms_sqltune.schedule_tuning_task(
begin_snap      IN NUMBER,
end_snap        IN NUMBER,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER                   := NULL,
start_date      IN TIMESTAMP WITH TIME ZONE := NULL,
scope           IN VARCHAR2                 := SCOPE_COMPREHENSIVE,
time_limit      IN NUMBER                   := TIME_LIMIT_DEFAULT,
task_name       IN VARCHAR2                 := NULL,
description     IN VARCHAR2                 := NULL,
con_name        IN VARCHAR2                 := NULL,
dbid            IN NUMBER                   := NULL)
RETURN VARCHAR2;
SELECT dbms_sqltune.schedule_tuning_task(46503, 46515, 'aca4xvmz0rzup')
FROM dual;
 
SCRIPT_TUNING_TASK
Creates a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations dbms_sqltune.script_tuning_task(
task_name        IN VARCHAR2,
rec_type         IN VARCHAR2 := 'REC_TYPE_ALL',
object_id        IN NUMBER   := NULL,
result_limit     IN NUMBER   := NULL,
owner_name       IN VARCHAR2 := NULL,
execution_name   IN VARCHAR2 := NULL,
database_link_to IN VARCHAR2 := NULL)
RETURN CLOB;
TBD
 
SELECT_CURSOR_CACHE
Collects SQL statements from the SQL Cursor Cache dbms_sqltune.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 := 'BASIC',
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;
DECLARE
 l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR SELECT VALUE(p) FROM TABLE
  (dbms_sqltune.select_cursor_cache(
  NULL, -- basic_filter
  NULL, -- object_filter
  NULL, -- ranking_measure1
  NULL, -- ranking_measure2
  NULL, -- ranking_measure3
  NULL, -- result_percentage
  1)) p; -- result_limit

  dbms_sqltune.load_sqlset(sqlset_name => 'test_sql_tuning_set', populate_cursor => l_cursor);
END;
/
 
SELECT_SQLPA_TASK
Collect SQL statements from a SQL performance analyzer task dbms_sqltune.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;
SELECT * FROM TABLE(dbms_sqltune.select_sqlpa_task('TASK_188'));
 
SELECT_SQLSET
Collects SQL statements from the cursor cache dbms_sqltune.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 := 'BASIC',
plan_filter       IN VARCHAR2 := NULL,
sqlset_owner      IN VARCHAR2 := NULL,
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;
SELECT sqlset_name
FROM cdb_sqlset_plans
ORDER BY 1;

desc sqlset

SELECT *
FROM TABLE(dbms_sqltune.select_sqlset(''UWSet''))
WHERE rownum = 1;

SELECT sql_id, force_matching_signature, sql_text, parsing_schema_name, elapsed_time,
cpu_time, buffer_gets, disk_reads, direct_writes, rows_processed, fetches, executions,
end_of_fetch_count, optimizer_env, command_type, plan_hash_value
FROM TABLE(dbms_sqltune.select_sqlset('UWSet'))
WHERE rownum = 1;
 
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_sqltune.select_sql_trace(
directory           IN VARCHAR2,                                -- trace file location
file_name           IN VARCHAR2 := NULL,                        -- trace file name
mapping_table_name  IN VARCHAR2 := NULL,
mapping_table_owner IN VARCHAR2 := NULL,
select_mode         IN POSITIVE := SINGLE_EXECUTION,            -- SQL Tuning Set Constants
options             IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,  -- SQL Tuning Set Constants
pattern_start       IN VARCHAR2,                                -- not used for now
pattern_end         IN VARCHAR2,                                -- not used for now
result_limit        IN POSITIVE);                               -- not used for now
RETURN sys.sqlset PIPELINED
conn sys@pdbdev as sysdba

GRANT administer sql tuning set TO uwclass;

CREATE OR REPLACE DIRECTORY sql_trace_dir AS '/stage';

GRANT read, write ON DIRECTORY sql_trace_dir TO uwclass;

conn uwclass/uwclass@pdbdev

CREATE TABLE mapping AS
SELECT object_id id, owner, substr(object_name, 1, 30) name
FROM dba_objects_ae
WHERE object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT',
'FUNCTION', 'INDEXTYPE', 'JAVA CLASS', 'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY', 'LOB', 'OPERATOR', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'QUEUE', 'RESOURCE PLAN', 'TRIGGER', 'TYPE', 'TYPE BODY')
UNION ALL
SELECT user_id id, username owner, NULL name
FROM dba_users;

exec dbms_sqltune.create_sqlset('uw_sts', 'test purpose');

-- load the SQL statements from the trace file into UW_STS
DECLARE
 cur sys_refcursor;
BEGIN
  OPEN cur FOR
  SELECT value(p)
  FROM TABLE(dbms_sqltune.select_sql_trace(directory=>'SQL_TRACE_DIR',
  file_name=>'%trc', mapping_table_name=>'mapping')) p;

  dbms_sqltune.load_sqlset('uw_sts', cur);
END;
/

SELECT name, id, statement_count
FROM user_sqlset;

-- create a trial from the UW_STS set
DECLARE
 x VARCHAR2(30);
BEGIN
  x := dbms_sqlpa.create_analysis_task(sqlset_name=>'uw_sts');
 dbms_sqlpa.execute_analysis_task(task_name =>x, execution_type => 'convert sqlset');
END;
/
 
SELECT_WORKLOAD_REPOSITORY
Collects SQL statements from workload repository

Overload 1		 dbms_sqltune.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 := 'BASIC',
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL,
dbid              IN NUMBER   := NULL)
RETURN sys.sqlset PIPELINED;
TBD
Overload 2 dbms_sqltune.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 := 'BASIC',
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL,
dbid              IN NUMBER   := NULL)
RETURN sys.sqlset PIPELINED;
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(sqlset_name => 'test_sql_tuning_set', populate_cursor => l_cursor);
END;
/
 
SET_AUTO_TUNING_TASK_PARAMETER
Deprecated see DBMS_AUTO_SQLTUNE
Overload 1		 dbms_sqltune.set_auto_tuning_task_parameter(parameter IN VARCHAR2, value IN VARCHAR2);
TBD
Deprecated see DBMS_AUTO_SQLTUNE
Overload 2		 dbms_sqltune.set_auto_tuning_task_parameter(parameter IN VARCHAR2, value IN NUMBER);
TBD
 
SET_TUNING_TASK_PARAMETER
Update the value of a sql tuning parameter of type VARCHAR2

Overload 1		 dbms_sqltune.set_tuning_task_parameter(
task_name        IN VARCHAR2,
parameter        IN VARCHAR2,
value            IN VARCHAR2,
database_link_to IN VARCHAR2 := NULL);
TBD
Update the value of a sql tuning parameter of type NUMBER

Overload 2		 dbms_sqltune.set_tuning_task_parameter(
task_name        IN VARCHAR2,
parameter        IN VARCHAR2,
value            IN NUMBER,
database_link_to IN VARCHAR2 := NULL);
TBD
Update the default value of a sql tuning parameter of type VARCHAR2

Overload 3		 dbms_sqltune.set_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN VARCHAR2);
TBD
Update the default value of a sql tuning parameter of type NUMBER

Overload 4		 dbms_sqltune.set_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN NUMBER);
TBD
 
SQLSET_PROGRESS_STATS
For internal usage only dbms_sqltune.sqlset_progress_stats(
sqlset_name       IN  VARCHAR2,
sqlset_owner      IN  VARCHAR2,
basic_filter      IN  VARCHAR2 := NULL,
plan_filter       IN  VARCHAR2 := NULL,
rank1             IN  VARCHAR2 := NULL,
rank2             IN  VARCHAR2 := NULL,
rank3             IN  VARCHAR2 := NULL,
result_percentage IN  NUMBER   := 1,
result_limit      IN  NUMBER   := NULL,
sql_count         OUT NUMBER,
workload_time     OUT NUMBER,
exec_type#        IN  PLS_INTEGER);
-- further research required to understand params, especially the last one.

DECLARE
 scount NUMBER;
 stime  NUMBER;
BEGIN
  dbms_sqltune.sqlset_progress_stats('UWSet', 'UWCLASS', sql_count => scount, workload_time => stime, exec_type# => 20);
  dbms_output.put_line(scount);
  dbms_output.put_line(stime);
END;
/
 
SQLTEXT_TO_SIGNATURE
Returns a SQL text's signature. The signature can be used to identify SQL text in dba_sql_profiles

Overload 1		 dbms_sqltune.sqltext_to_signature(
sql_text    IN CLOB,
force_match IN BOOLEAN := FALSE)
RETURN NUMBER;
BEGIN
  IF dbms_sqltune.sqltext_to_signature('SELECT * FROM dual', TRUE) THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
Returns a SQL text's signature. The signature can be used to identify SQL text in dba_sql_profiles.

Overload 2		 dbms_sqltune.sqltext_to_signature(
sql_text    IN CLOB,
force_match IN BINARY_INTEGER) -- 0 = FALSE, not zero = TRUE
RETURN NUMBER;
SELECT dbms_sqltune.sqltext_to_signature('SELECT * FROM dual', 1)
FROM dual;
 
TRANSFORM_SQLSET_CURSOR
Transforms a user specified sql tuning set cursor to a table (function) so that the cursor can be queried in SQL query dbms_sqltune.transform_sqlset_cursor(populate_cursor IN sqlset_cursor)
RETURN sys.sqlset PIPELINED;
TBD
 
UNPACK_STGTAB_SQLPROF
Copies profile data stored in the staging table to create profiles on the system dbms_sqltune.unpack_stgtab_sqlprof(
profile_name         IN VARCHAR2 := '%',
profile_category     IN VARCHAR2 := '%',
replace              IN BOOLEAN,
staging_table_name   IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);
TBD
 
UNPACK_STGTAB_SQLSET
Copies one or more SQL tuning sets from their location in the staging table into the SQL tuning sets schema, making them proper SQL tuning sets dbms_sqltune.unpack_stgtab_sqlset(
sqlset_name          IN VARCHAR2 := '%',
sqlset_owner         IN VARCHAR2 := NULL,
replace              IN BOOLEAN,
staging_table_name   IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);
TBD
 
UPDATE_SQLSET
Updates whether selected string fields for a SQL statement in a SqlSet or the set numerical attributes of a SQL in a SqlSet

Overload 1		 dbms_sqltune.update_sqlset (
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2 := NULL,
sqlset_owner    IN VARCHAR2 := NULL);
TBD
Overload 2 dbms_sqltune.update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2 := NULL,
sqlset_owner    IN VARCHAR2 := NULL);
TBD
Overload 3 dbms_sqltune.update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN NUMBER   := NULL,
sqlset_owner    IN VARCHAR2 := NULL);
TBD
Overload 4 dbms_sqltune.update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER,
attribute_name  IN VARCHAR2,
attribute_value IN NUMBER   := NULL,
sqlset_owner    IN VARCHAR2 := NULL);
TBD
 
SQLTune Demos
Tuning Demo conn sys@pdbdev as sysdba

SELECT * FROM user_sqltune_binds;

set serveroutput on

DECLARE
 ret_val VARCHAR2(4000);
 SqlStr  CLOB := 'SELECT * FROM servers WHERE srvr_id = :bnd';
BEGIN
  ret_val := dbms_sqltune.create_tuning_task(
  sql_text => SqlStr,
  bind_list => sql_binds(anydata.ConvertNumber(100)),
  user_name => 'UWCLASS',
  scope => 'comprehensive',
  time_limit => 60,
  task_name => 'UW Tune',
  description => 'query for server by id');

  dbms_output.put_line(ret_val);
END;
/

SELECT * FROM user_sqltune_binds;

exec dbms_sqltune.execute_tuning_task('UW Tune');

col execution_name format a15
col operation format a20
col options format a20

SELECT task_id, execution_name, operation, options, cpu_cost, io_cost
FROM user_sqltune_plans;

set long 100000

SELECT dbms_sqltune.report_tuning_task('UW Tune')
FROM dual;

exec dbms_sqltune.cancel_tuning_task('UW Tune');

exec dbms_sqltune.drop_tuning_task('UW Tune');

SELECT task_id, execution_name
FROM user_sqltune_plans;

Related Topics
ADDM Demo
Built-in Functions
Built-in Packages
DBMS_ADVISOR
DBMS_AUTO_SQLTUNE
DBMS_SQLDIAG
DBMS_SQLPA
DBMS_SQLSET
DBMS_SQLTUNE_INTERNAL
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL1
DBMS_SQLTUNE_UTIL2
DBMS_XPLAN
PRVT_SQLPROF_INFRA
PRVT_SQLSET_INFRA
Tim Hall's Demos
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