Oracle DBMS_SQLTUNE
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 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_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'
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
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_SCHEDULER_JOBS DBMS_SQLDIAG PRVT_REPORT_TAGS
ALL_SQLSET DBMS_SQLDIAG_INTERNAL PRVT_SMGUTIL
ALL_SQLSET_BINDS DBMS_SQLPA PRVT_SQLADV_INFRA
DBA_ADVISOR_OBJECTS DBMS_SQLTCB_INTERNAL PRVT_SQLPA
DBA_ADVISOR_PARAMETERS DBMS_SQLTUNE_INTERNAL PRVT_SQLPROF_INFRA
DBA_ADVISOR_TASKS DBMS_SQLTUNE_LIB PRVT_SQLSET_INFRA
DBA_HIST_BASELINE DBMS_SQLTUNE_UTIL0 PRVT_WORKLOAD
DBA_HIST_PARAMETER DBMS_SQLTUNE_UTIL1 SQLPROF_ATTR
DBA_HIST_SNAPSHOT DBMS_SQLTUNE_UTIL2 SQLSET
DBA_HIST_SQLBIND DBMS_SQL_MONITOR SQLSET_ROW
DBA_HIST_SQLSTAT DBMS_STANDARD SQL_BIND
DBA_HIST_SQLTEXT DBMS_STATS SQL_BINDS
DBA_HIST_WR_CONTROL DBMS_SYS_ERROR SQL_BIND_SET
DBA_PROCEDURES DBMS_UTILITY SQL_PLAN_TABLE_TYPE
DBA_SQLSET DBMS_WORKLOAD_REPLAY SYS_IXMLAGG
DBA_SQLSET_BINDS DBMS_WRR_INTERNAL USER_SQLSET_BINDS
DBA_SQLSET_PLANS DBMS_XPLAN V$ACTIVE_SESSION_HISTORY
DBA_SQLSET_REFERENCES DUAL V$CONTAINERS
DBA_SQLSET_STATEMENTS GV$ASH_INFO V$DATABASE
DBA_SQL_PROFILES GV$PARAMETER V$INSTANCE
DBMS_ADVISOR GV$SESSION_LONGOPS V$SESSION
DBMS_ASSERT GV$SQL V$SQL_MONITOR_STATNAME
DBMS_AUTO_REPORT GV$SQLAREA_PLAN_HASH V$SQL_PLAN_MONITOR
DBMS_AUTO_SQLTUNE GV$SQLSTATS_PLAN_HASH V$SYS_OPTIMIZER_ENV
DBMS_LOB GV$SQL_MONITOR V$TIMER
DBMS_LOCK GV$SQL_OPTIMIZER_ENV WRI$_ADV_SQLTUNE
DBMS_MANAGEMENT_PACKS GV$SQL_PLAN_MONITOR WRI$_REPT_SQLDETAIL
DBMS_PERF GV$SQL_SHARED_CURSOR WRI$_REPT_SQLMONITOR
DBMS_REPORT PLITBLM WRI$_REPT_SQLT
DBMS_SCHEDULER PRVTEMX_PERF XMLAGG
DBMS_SMB PRVT_ACCESS_ADVISOR XMLSEQUENCE
DBMS_SMB_INTERNAL PRVT_ADVISOR XMLSEQUENCETYPE
DBMS_SPM PRVT_AWRV_MAPTAB XMLTYPE
DBMS_SPM_INTERNAL PRVT_AWRV_METADATA XQSEQUENCE
DBMS_SQL PRVT_AWR_VIEWER _ALL_SQLSET_STS_TOPACK
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-20000 check_priv: invalid priv specified
ORA-20000 invalid advisor task name
First Available 10gR1
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
Source {ORACLE_HOME}/rdbms/admin/dbmssqlt.sql
Subprograms
 
ACCEPT_ALL_SQL_PROFILES (new 12.1)
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);
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)
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);
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_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 (new 12.1)
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 (new 12.1)
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
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@pdborcl

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@pdborcl

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 (new 12.1)
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 (new 12.1)
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);
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_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);
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)
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)
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)
RETURN VARCHAR2;
conn sh/sh@pdborcl

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@pdborcl 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 2014 21:00      1
                               2572 17 JAN 2014 21:20      1
                               2573 17 JAN 2014 21:40      1
                               2574 17 JAN 2014 22:00      1
                               2575 17 JAN 2014 22:20      1
                               2576 18 JAN 2014 05:26      1
                               2577 18 JAN 2014 05:40      1
                               2578 18 JAN 2014 06:00      1
                               2579 18 JAN 2014 06:20      1
                               2580 18 JAN 2014 07:44      1
                               2581 18 JAN 2014 08:00      1
                               2582 18 JAN 2014 08:20      1
                               2583 18 JAN 2014 08:40      1
                               2584 18 JAN 2014 09:00      1
                               2585 18 JAN 2014 09:20      1
                               2586 18 JAN 2014 09:40      1
                               2587 18 JAN 2014 10:00      1
                               2588 18 JAN 2014 10:11      1

grant dba to sh;
grant advisor to sh;

conn sh/sh@pdborcl

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@pdborcl 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)
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_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 (new 12.1)
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)
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);
exec dbms_sqltune.execute_tuning_task('TASK_188');
 
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
TBD
 
IMPLEMENT_TUNING_TASK_XML (new 12.1)
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)
RETURN XMLTYPE;
exec dbms_sqltune.implement_tuning_task_xml('TASK_3730');
 
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');
 
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;
/
 
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);
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);
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);
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

SQL> 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 : 25
Current Execution : EXEC_8397
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 06/29/2014 06:05:19
Completed at : 06/29/2014 06:05:58
Number of Candidate SQLs : 18
Cumulative Elapsed Time of SQL (s) : 145
-------------------------------------------------------------------------------
There are no recommendations to improve the statements in the workload.
-------------------------------------------------------------------------------
 
REPORT_SQL_DETAIL (new 12.1)
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 (new 12.1)
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 (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(
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')
RETURN CLOB;
TBD
SELCT dbms... ((sql_id => ???, session_id =? ???,

must get this working
 
REPORT_SQL_MONITOR_LIST (new 12.1)
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)
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)
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)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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. 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('TASK_188');
  dbms_output.put_line(retVal);
END;
/
 
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 NUMNBER  := NULL,
owner_name     IN VARCHAR2 := NULL,
execution_name 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')
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 (new 12.1)
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)
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

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')
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')
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
Similar to set_tuning_task_parameter, but used for the reserved auto tuning task
Overload 1
dbms_sqltune.set_auto_tuning_task_parameter(parameter IN VARCHAR2, value IN VARCHAR2);
TBD
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);
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);
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;
SELECT dbms_sqltune.sqltext_to_signature('SELECT * FROM dual')
FROM dual;
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
DBMS_ADVISOR
DBMS_AUTO_SQLTUNE
DBMS_SQLDIAG
DBMS_SQLPA
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL1
DBMS_SQLTUNE_UTIL2
DBMS_XPLAN
Packages
Tim Hall's Demos
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