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