Oracle DBMS_SQLTUNE
Version 11.2.0.3
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmssqlt.sql
First Available 10.1
Constants
SQLTune Advisor Name
Name Data Type Value
ADV_SQLTUNE_NAME VARCHAR2(18) 'SQL Tuning Advisor'

SQLTune Advisor Task Scope Parameters
Name Data Type Value
SCOPE_LIMITED VARCHAR2(7) 'LIMITED'
SCOPE_COMPREHENSIVE VARCHAR2(13) 'COMPREHENSIVE'

SQLTune Advisor Time_Limit Constants
Name Data Type Value
TIME_LIMIT_DEFAULT NUMBER 1800

Report Type Constants
Name Data Type Value
TYPE_TEXT VARCHAR2(4) 'TEXT'
TYPE_XML VARCHAR2(3) 'XML'
TYPE_HTML VARCHAR2(4) 'HTML'

Report Level Constants
Name Data Type Value
LEVEL_TYPICAL VARCHAR2(7) 'TYPICAL'
LEVEL_BASIC VARCHAR2(5) 'BASIC'
LEVEL_ALL VARCHAR2(3) 'ALL'

Report Section Constants
Name Data Type Value
SECTION_FINDINGS VARCHAR2(8) 'FINDINGS'
SECTION_PLANS VARCHAR2(5) 'PLANS'
SECTION_INFORMATION VARCHAR2(11) 'INFORMATION'
SECTION_ERRORS VARCHAR2(6) 'ERRORS'
SECTION_ALL VARCHAR2(3) 'ALL'
SECTION_SUMMARY VARCHAR2(7) 'SUMMARY'

Common Date Format Constant
Name Data Type Value
DATE_FMT VARCHAR2(21) 'mm/dd/yyyy hh24:mi:ss'

Script Section Constants
Name Data Type Value
REC_TYPE_ALL VARCHAR2(3) 'ALL'
REC_TYPE_SQL_PROFILES VARCHAR2(8) 'PROFILES'
REC_TYPE_STATS VARCHAR2(10) 'STATISTICS'
REC_TYPE_INDEXES VARCHAR2(7) 'INDEXES'

Capture Section Constants
Name Data Type Value
MODE_REPLACE_OLD_STATS NUMBER 1
MODE_ACCUMULATE_STATS NUMBER 2

SQL Tuning Set Constants
Name Data Type Value
SINGLE_EXECUTION POSITIVE 1
ALL_EXECUTIONS POSITIVE 2
LIMITED_COMMAND_TYPE BINARY_INTEGER 1
ALL_COMMAND_TYPE BINARY_INTEGER 2

Internal Usage Constants
Name Data Type Value
FLAG_PREPAWR_WRAPCTOR NUMBER POWER(2, 0)
FLAG_PREPAWR_NOCKBINDS NUMBER POWER(2, 1)
FLAG_PREPAWR_INCLBID NUMBER POWER(2, 1)
Data Types TYPE sqlset_cursor IS REF CURSOR;
Dependencies
dba_hist_baseline sqlset_row user_sqlset
dba_hist_sqlbind user_advisor_actions user_sqlset_plans
dbms_advisor user_advisor_exec_parameters user_sqlset_references
dbms_advisor_log user_advisor_executions user_sqlset_statements
dbms_sqltune_internal user_advisor_findings user_tune_mview
dbms_sqltune_lib user_advisor_log user_sqltune_binds
dbms_sys_error user_advisor_objects user_sqltune_statistics
gv_$sql_bind_capture user_advisor_parameters user_sqltune_plans
prvt_advisor user_advisor_rationale user_sqltune_rationale_plan
session_privs user_advisor_recommendations v_$database
sql_binds user_advisor_sqlplans wri$_adv_executions
sqlprof_attr user_advisor_tasks wri$_adv_tasks
sqlset user_advisor_templates  
Security Model Execute is granted to PUBLIC but to tune objects in all schemas requires the system privs administer sql tuning set and administer any sql tuning set
GRANT administer any sql tuning set TO UWCLASS;
 
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;
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);
TBD
 
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);
BEGIN
  dbms_sqltune.alter_sql_profile('emp_dept_profile', 'STATUS', 'DISABLED');
END;
/
 
CANCEL_TUNING_TASK
Cancel Currently Executing Task dbms_sqltune.cancel_tuning_task(task_name IN VARCHAR2);
See Tuning Task Demo at Page Bottom
 
CAP_STS_CBK
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
 
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);
 
CHECK_SQL_PROFILE_PRIV
For internal usage only dbms_sqltune.check_sql_profile_priv(priv IN VARCHAR2);
TBD
 
CHECK_SQLSET_PRIVS
Undocumented dbms_sqltune.check_sqlset_privs(
sqlset_name   IN VARCHAR2,
sqlset_owner  IN VARCHAR2,
sqlset_create IN BOOLEAN := FALSE,
read_only     IN BOOLEAN := FALSE);
BEGIN
  dbms_sqltune.check_sqlset_privs('UWSet3','UWCLASS', TRUE, FALSE);
END;
/
 
CREATE_SQLPLAN_BASELINE (new 11.2.0.1)
Creates a plan baseline for an existing plan dbms_sqltune.check_sqlplan_baseline(
task_name       IN VARCHAR2,
object_id       IN NUMBER   := NULL,
plan_hash_value IN NUMBER,
owner_name      IN VARCHAR2 := NULL);
TBD
 
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; -- seems that a delete bug exists
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; -- seems that a delete bug exists
 
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
 ret_val 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
  ret_val := dbms_sqltune.create_tuning_task(SqlStr);
  dbms_output.put_line(ret_val);
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

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 / as sysdba

-- force an AWR snapshot
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 gv$sql
WHERE sql_text LIKE '%SQLTUNE%';

SQL_ID SQL_TEXT
------------- --------------------------------------------------
4kt7vr7vss27b SELECT /* SQLTUNE */ s.prod_id, s.cust_id, COUNT(*
cdv6nwwr7w66m select task_id from USER_SQLTUNE_PLANS
663c873t8s88c SELECT /*+ opt_param('parallel_execution_enabled',
c69zh6farhdvk select task_id, object_id, position, value from US
439fv9rryjph1 SELECT COUNT(*) FROM USER_SQLTUNE_PLANS
1pr5672cjf6gu SELECT COUNT(*) FROM USER_SQLTUNE_RATIONALE_PLAN
gzw2aumcbqg5d SELECT COUNT(*) FROM USER_SQLTUNE_STATISTICS
72jp8uj95bry7 SELECT COUNT(*) FROM USER_SQLTUNE_BINDS

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
------------ ------------ --------- ------------------ -----
orabase      ORABASE      3215      20 DEC 2008 18:00  1
                          3216      20 DEC 2008 19:00  1
                          3217      20 DEC 2008 22:31  1
                          3218      21 DEC 2008 00:00  1
                          3219      21 DEC 2008 10:29  1
                          3220      21 DEC 2008 11:00  1
                          3221      21 DEC 2008 13:20  1
                          3222      21 DEC 2008 14:00  1
                          3223      21 DEC 2008 16:23  1
                          3224      21 DEC 2008 17:00  1
                          3225      21 DEC 2008 18:00  1
                          3226      21 DEC 2008 19:00  1
                          3227      21 DEC 2008 20:00  1

grant dba to sh;
grant advisor to sh;

conn sh/sh

set serveroutput on

DECLARE
 ttask VARCHAR2(100);
BEGIN
  ttask := dbms_sqltune.create_tuning_task(
    begin_snap => 3221,
    end_snap => 3227,
    sql_id => '4kt7vr7vss27b',
    scope => dbms_sqltune.scope_comprehensive,
    time_limit => 60,
    task_name => '4kt7vr7vss27b_AWR_tuning_task',
    description => 'Tuning task for statement 4kt7vr7vss27b 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 => '4kt7vr7vss27b',
    scope => DBMS_SQLTUNE.scope_comprehensive,
    time_limit => 60,
    task_name => '4kt7vr7vss27b_tuning_task',
    description => 'Tuning task for statement 4kt7vr7vss27b');

  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('4kt7vr7vss27b_AWR_tuning_task');

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

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

exec dbms_sqltune.resume_tuning_task('kt7vr7vss27b_AWR_tuning_task');

set long 100000

SELECT dbms_sqltune.report_tuning_task('4kt7vr7vss27b_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 / 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);
TBD
 
DROP_SQL_PROFILE
Drops the named SQL Profile dbms_sqltune.drop_sql_profile(
name   IN VARCHAR2,
ignore IN BOOLEAN := FALSE);
BEGIN
  dbms_sqltune.drop_sql_profile('emp_dept_profile', TRUE);
END;
/
 
DROP_SQLSET
Drops a SQL tuning set if it is not active dbms_sqltune.drop_sqlset(
sqlset_name  IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL);
exec dbms_sqltune.drop_sqlset('UWSet', 'UWCLASS');
 
DROP_TUNING_TASK
Drop a tuning task dbms_sqltune.drop_tuning_task(task_name IN VARCHAR2);
exec dbms_sqltune.drop_tuning_task('TASK_214');
 
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;
exec dbms_sqltune.execute_tuning_task('TASK_188');

Example:
dbms_advisor.arglist('time_limit', 12, 'username', 'foo')
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;
TBD
 
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;
TBD
 
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
 
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);
BEGIN
  dbms_sqltune.create_sqlset(
  dbms_sqltune.load_sqlset('UW Set',
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;
TBD
 
REPORT_SQL_MONITOR (new 11.2 parameters and defaults)
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. 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;
Note: This report puts together performance data exposed by the following fixed views:
-- GV$SQL_MONITOR
-- GV$SQL_PLAN_MONITOR
-- GV$SQL_PLAN
-- GV$ACTIVE_SESSION_HISTORY
-- GV$SESSION_LONGOPS
-- GV$SQL
 
REPORT_SQL_MONITOR_LIST_XML (new 11.2 parameters)
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 (new 11.2 parameters)
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. 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;
Note: This report puts together performance data exposed by the following fixed views:
-- GV$SQL_MONITOR
-- GV$SQL_PLAN_MONITOR
-- GV$SQL_PLAN
-- GV$ACTIVE_SESSION_HISTORY
-- GV$SESSION_LONGOPS
-- GV$SQL
 
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
 
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(....?
  dbms_sqltune.resume_tuning_task('TASK_188');
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_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 / 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_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 *
FROM TABLE(dbms_sqltune.select_sqlset('UWSet2'))
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,
--optmizer_env,
command_type, plan_hash_value,
FROM TABLE(dbms_sqltune.select_sqlset('UWSet2'))
where rownum = 1;
 
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 (new 11.2.0.1: deprecated 11.2.0.2)
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);
-- need 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 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 on a specified 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_XPLAN
Tim Hall's Demos
 
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-2013 Daniel A. Morgan All Rights Reserved