CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SQLTUNE
Version 11.1
 
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 user_sqlset
dba_hist_sqlbind user_sqlset_plans
dbms_advisor user_sqlset_references
dbms_sqltune_internal user_sqlset_statements
dbms_sqltune_lib user_tune_mview
dbms_sys_error user_sqltune_binds
gv_$sql_bind_capture user_sqltune_statistics
prvt_advisor user_sqltune_plans
session_privs user_sqltune_rationale_plan
sql_binds v_$database
sqlprof_attr wri$_adv_executions
sqlset wri$_adv_tasks
sqlset_row  
Security Model administer sql tuning set
administer any sql tuning set
GRANT administer any sql tuning set TO UWCLASS;
USER_ADVISOR_OBJECTS;
USER_ADVISOR_EXEC_PARAMETERS; -- 34
USER_ADVISOR_RATIONALE;
USER_ADVISOR_FINDINGS;
USER_ADVISOR_PARAMETERS; -- 34
USER_ADVISOR_LOG;
USER_ADVISOR_SQLPLANS; -- 8
USER_ADVISOR_RECOMMENDATIONS;
USER_ADVISOR_EXECUTIONS
USER_ADVISOR_ACTIONS
USER_ADVISOR_TASKS
USER_ADVISOR_TEMPLATES
ACCEPT_SQL_PROFILE

Create a SQL Profile for the specified tuning task

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 (new 11g parameter)

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 (new in 11g)

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, -- what is the min/max?
repeat_interval IN POSITIVE := 300,
capture_option  IN VARCHAR2 := 'MERGE',
capture_mode    IN NUMBER   := MODE_REPLACE_OLD_STATS,
basic_filter    IN VARCHAR2 := NULL,
sqlset_owner    IN VARCHAR2 := NULL);
TBD

exec dbms_sqltune.capture_cursor_cache_sqlset('UWSet', 1000);
 
CHECK_SQL_PROFILE_PRIV (new in 11g)
For internal usage only dbms_sqltune.check_sql_profile_priv(priv IN VARCHAR2);
TBD
 
CHECK_SQLSET_PRIVS (new in 11g)

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_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

Overload 2
dbms_sqltune.create_sqlset(
sqlset_name  IN VARCHAR2 := NULL,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2; -- name of SQLSET created
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);
TBD
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,
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');

Overload 3

Prepare the tuning of a single statement given its text based on a SQL_ID
dbms_sqltune.create_tuning_task(
begin_snap      IN NUMBER,
end_snap        IN NUMBER,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER,
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;

Overload 4

Prepare the tuning of a single statement given its text based on a SQL_ID
dbms_sqltune.create_tuning_task(
sqlset_name       IN VARCHAR2
basic_filter      IN VARCHAR2,
object_filter     IN VARCHAR2,
rank1             IN VARCHAR2,
rank2             IN VARCHAR2,
rank3             IN VARCHAR2,
result_percentage IN NUMBER,
result_limit      IN NUMBER,
scope             IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit        IN NUMBER := TIME_LIMIT_DEFAULT,
task_name         IN VARCHAR2 := NULL,
description       IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD
 
DELETE_SQLSET

Deletes a set of SQL statements from a SQL tuning set
dbms_sqltune.delete_sqlset(
sqlset_name  IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL);
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 (new parameters and overload in 11g)

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. := 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 (new in 11g)
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 (new in 11g)
Given the value of a bind_data column captured in v$sql this function returns the collection (list) of bind values associated to the corresponding SQL statement dbms_sqltune.extract_binds(
bind_data IN RAW)
RETURN SQL_BIND_SET PIPELINED;
TBD
 
IMPLEMENT_TUNING_TASK (new in 11g)
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 (new in 11g)

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 (new in 11g)

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 (new 11g parameter)

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 (new in 11g)

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 in 11g)

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 -1,
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,
event_detail        IN VARCHAR2 DEFAULT 'yes',
bucket_min_interval IN NUMBER   DEFAULT 60,
bucket_max_count    IN NUMBER   DEFAULT 10,
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 in 11g)

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 -1,
last_nsec      IN NUMBER   DEFAULT NULL,
auto_refresh   IN NUMBER   DEFAULT NULL)
RETURN XMLTYPE;
TBD
 
REPORT_SQL_MONITOR_XML (new in 11g)

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 -1,
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,
event_detail        IN VARCHAR2 DEFAULT 'yes',
bucket_min_interval IN NUMBER   DEFAULT 60,
bucket_max_count    IN NUMBER   DEFAULT 10,
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 (new 11g parameters)

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: emp uning.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 (new 11g parameter)

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 (new in 11.1.0.7)

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,        -- location of the trace file
file_name           IN VARCHAR2,        -- trace file name
mapping_table_name  IN VARCHAR2,
mapping_table_owner IN VARCHAR2,
select_mode         IN BINARY_INTEGER,  -- SQL Tuning Set Constants
options             IN BINARY_INTEGER,  -- SQL Tuning Set Constants
pattern_start       IN VARCHAR2,        -- not used for now
pattern_end         IN VARCHAR2,        -- not used for now
result_limit        IN BINARY_INTEGER); -- not used for now
RETURN sqlset.sqlset_row
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
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 form 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_TUNING_TASK_PARAMETER (new 11g)

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 (new in 11g)

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 (new 11g overload)
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 (new in 11g)
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 (new 11g overloads and parameters)

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_SQLDIAG
DBMS_XPLAN
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [90 users online]    © 2010 psoug.org