| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmssqlt.sql |
| First Available |
10.1 |
Constants |
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' |
|
| Dependencies |
| dba_hist_baseline |
sql_binds |
| dba_hist_sqlbind |
sqlprof_attr |
| dbms_advisor |
sqlset |
| dbms_sqltune_internal |
sqlset_row |
| dbms_sqltune_lib |
user_sqlset |
| dbms_sys_error |
user_tune_mview |
| gv_$database |
user_sqltune_binds |
| gv_$sql_bind_capture |
user_sqltune_statistics |
| prvt_advisor |
user_sqltune_plans |
| session_privs |
user_sqltune_rationale_plan |
|
| System Privileges |
administer sql tuning set
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
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);
|
set serveroutput on
DECLARE
tune_task_id VARCHAR2(20);
BEGIN
tune_task_id := dbms_sqltune.accept_sql_profile(
task_name => 'emp_dept_tuning_task', name =>
'emp_dept_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)
RETURN VARCHAR2;
|
| TBD |
| |
| ADD_SQLSET_REFERENCE |
| Adds a new reference to an existing SqlSet to indicate its use |
dbms_sqltune.add_sqlset_reference(
sqlset_name IN VARCHAR2,
description IN VARCHAR2 := NULL)
RETURN NUMBER; |
set serveroutput on
DECLARE
n NUMBER;
BEGIN
n := dbms_sqltune.add_sqlset_reference('UW
Set', 'New Desc');
dbms_output.put_line(TO_CHAR(n));
END;
/ |
| |
| 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); |
|
exec dbms_sqltune.cancel_tuning_task('TASK_188'); |
| |
| 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,
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 |
| |
| 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);
|
BEGIN
dbms_sqltune.create_sqlset('UW Set', 'A test SQL tuning set',
'UWCLASS');
END;
/
set linesize 141
col description format a30
SELECT *
FROM user_sqlset; |
Overload 2 |
dbms_sqltune.create_sqlset(
sqlset_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
|
| TBD |
| |
| CREATE_STGTAB_SQLPROF |
|
Creates the staging table used for copying SQL profiles from one system to another. |
dbms_sqltune.create_stgtab_sqlprof(
table_name IN VARCHAR2,
schema_name IN VARCHAR2 := NULL,
tablespace_name IN VARCHAR2 := NULL); |
| TBD |
| |
| 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); |
| TBD |
| |
| 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 130
col advisor_name format a30
SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks;
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_890');
set long 10000
SELECT dbms_sqltune.report_tuning_task('TASK_890')
FROM dual; |
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 gv_$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=>'ffwhd4jkg425q');
dbms_sqltune.execute_tuning_task('OPEN CUR');
END;
/
set long 10000
SELECT dbms_sqltune.report_tuning_task('OPEN CUR')
FROM dual; |
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
SELECT sql_id
FROM gv$sql
WHERE sql_text LIKE '%SQLTUNE%';
SQL_ID
-------------
bxvvwnb5uctky
4djqpjbrmf9vf
2vbt24cg9rsa4
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
876 27 JUL 2007 13:00 1
877 27 JUL 2007 14:00 1
878 27 JUL 2007 15:00 1
879 27 JUL 2007 16:00 1
880 27 JUL 2007 17:00 1
881 27 JUL 2007 18:00 1
882 27 JUL 2007 19:00 1
883 27 JUL 2007 20:00 1
884 27 JUL 2007 21:00 1
grant dba to sh;
grant advisor to sh;
conn sh/sh
set serveroutput on
DECLARE
sql_ttask VARCHAR2(100);
BEGIN
sql_ttask := dbms_sqltune.create_tuning_task(
begin_snap => 863,
end_snap => 883,
sql_id => '19v5guvsgcd1v',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_AWR_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
dbms_output.put_line('Tuning Task: ' || sql_ttask);
END;
/
-- Tuning task created for specific a statement from the cursor
cache
DECLARE
sql_ttask VARCHAR2(100);
BEGIN
sql_ttask := dbms_sqltune.create_tuning_task (
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v.');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
-- Tuning task created 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;
/
-- Tuning task created 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;
/
If the TASK_NAME parameter is specified, its value is returned as the SQL tune task identifier. If omitted, a system generated name such as "TASK_1478" is returned. If the SCOPE parameter is set to scope_limited the SQL profiling analysis is omitted. The TIME_LIMIT parameter simply restricts the time the optimizer can spend compiling the recommendations.
The following examples will reference the last tuning set, as it has no external dependencies other than the SCOTT schema. The NVL in the SQL statement was put in to provoke a reaction from the optimizer. In addition, we can delete the statistics from one of the tables to provoke it even more:
EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP');
With the tuning task defined, the next step is to execute it using the EXECUTE_TUNING_TASK procedure:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');
During the execution phase, you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:
-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');
-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');
-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');
The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';
TASK_NAME STATUS
------------------------------ -----------
emp_dept_tuning_task COMPLETED
1 row selected.
Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function:
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24
In this case, the output looks like this:
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : emp_dept_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/06/2004 09:29:13
Completed at : 05/06/2004 09:29:15
-------------------------------------------------------------------------------
SQL ID : 0wrmfv2yvswx1
SQL Text: SELECT e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE NVL(empno, '0') = :empno
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."EMP" and its indices were not analyzed.
Recommendation
--------------
Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan
contains an expression on indexed column "EMPNO". This expression prevents
the optimizer from selecting indices on table "SCOTT"."EMP".
Recommendation
--------------
Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.
Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.
/* Once the tuning session is over the tuning task can be dropped using the
-- DROP_TUNING_TASK procedure */
BEGIN
dbms_sqltune.drop_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');
dbms_sqltune.drop_tuning_task (task_name => '19v5guvsgcd1v_tuning_task');
dbms_sqltune.drop_tuning_task (task_name => 'sqlset_tuning_task');
dbms_sqltune.drop_tuning_task (task_name => 'emp_dept_tuning_task');
END;
/ |
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); |
| exec dbms_sqltune.delete_sqlset('UW
Set'); |
| |
| 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('UW
Set', '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 |
dbms_sqltune.execute_tuning_task(task_name
IN VARCHAR2); |
| exec dbms_sqltune.execute_tuning_task('TASK_188'); |
| |
| IMPORT_SQL_PROFILE |
| Undocumented |
? |
| 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 |
| |
| 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 |
| Undocumented |
dbms_sqltune.remap_stgtab_sqlset(?); |
| TBD |
| |
| REMOVE_SQLSET_REFERENCE |
| Deactivates a SQL tuning
set |
dbms_sqltune.remove_sqlset_reference(
sqlset_name IN VARCHAR2,
reference_id IN NUMBER); |
-- need query for how to
get reference_id
BEGIN
dbms_sqltune.remove_sqlset_reference('UW
Set', 1);
END;
/ |
| |
| 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)
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,
object_id IN NUMBER,
result_limit IN NUMNBER,
owner_name IN VARCHAR2)
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 := NULL)
RETURN sys.sqlset PIPELINED; |
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR SELECT VALUE(p) FROM TABLE
(dbms_sqltune.select_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_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 := NULL,
plan_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN sys.sqlset PIPELINED; |
SELECT *
FROM TABLE(dbms_sqltune.select_sqlset('UW Set'))
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 ('UW Set'))
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 := NULL)
RETURN sys.sqlset PIPELINED;
|
| TBD |
Overload 2 |
dbms_sqltune.select_workload_repository(
baseline_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
ranking_measure1 IN VARCHAR2 := NULL,
ranking_measure2 IN VARCHAR2 := NULL,
ranking_measure3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := 1,
result_limit IN NUMBER := NULL)
attribute_list IN VARCHAR2 := NULL)
RETURN sys.sqlset PIPELINED; |
DECLARE
l_cursor dbms_sqltune.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (dbms_sqltune.select_workload_repository (
765, -- begin_snap
766, -- end_snap
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
10)) p; -- result_limit
dbms_sqltune.load_sqlset (sqlset_name =>
'test_sql_tuning_set', populate_cursor => l_cursor);
END;
/ |
| |
| SQLTEXT_TO_SIGNATURE |
| returns a SQL text's signature. The signature can be used to identify SQL text in dba_sql_profiles |
dbms_sqltune.sqltext_to_signature(
sql_text IN CLOB,
force_match IN BOOLEAN := FALSE)
RETURN NUMBER; |
| 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 := 'DEFAULT',
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); |
| TBD |
| Overload 2 |
dbms_sqltune.update_sqlset(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
attribute_name IN VARCHAR2,
attribute_value IN NUMBER := NULL); |
| TBD |
| |
| SQLTune Demos |
| Tuning Demo |
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;
/
exec dbms_sqltune.execute_tuning_task('UW Tune');
set long 100000
SELECT dbms_sqltune.report_tuning_task('UW Tune')
FROM dual; |