SQL Tuning Advisor 使用11G的自动调优建议

来源：互联网发布：北京市十一学校知乎编辑：程序博客网时间：2024/04/29 18:50

先给监控用户授权

grant advisor to DBA_MONITER;

可以在PL/SQL DEVELOPER 命令窗口执行

SQL_ID方式

DECLARE

my_task_name VARCHAR2(30);

BEGIN

my_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id => '8gb2jup02tzt3',

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => 'test_sql_tuning_task2',

description => 'Task to tune a query');

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task2');

END;

/ PL/SQL proceduresuccessfully completed

SQL> select task_name,ADVISOR_NAME,STATUS fromuser_advisor_tasks;

TASK_NAME ADVISOR_NAME STATUS

------------------------------------------------------------ -----------

TASK_8352 ADDM COMPLETED

TASK_8836 ADDM COMPLETED

TASK_8671 ADDM COMPLETED

TASK_8560 ADDM COMPLETED

TASK_8429 ADDM COMPLETED

TASK_8481 ADDM COMPLETED

TASK_8507 ADDM COMPLETED

TASK_8645 ADDM COMPLETED

TASK_8810 ADDM COMPLETED

TASK_8455 ADDM COMPLETED

TASK_8370 ADDM COMPLETED

TASK_8723 ADDM COMPLETED

TASK_8351 ADDM COMPLETED

TASK_8400 ADDM COMPLETED

TASK_8780 ADDM COMPLETED

TASK_8619 ADDM COMPLETED

TASK_8914 ADDM COMPLETED

TASK_8590 ADDM COMPLETED

TASK_8697 ADDM COMPLETED

TASK_8862 ADDM COMPLETED

TASK_NAME ADVISOR_NAME STATUS

------------------------------------------------------------ -----------

TASK_8750 ADDM COMPLETED

test_sql_tuning_task1 SQL Tuning Advisor COMPLETED

test_sql_tuning_task2 SQL Tuning Advisor COMPLETED

TASK_8888 ADDM COMPLETED

TASK_8533 ADDM COMPLETED

25rows selected

在PL/SQL DEVELOPER 查询窗口执行执行结果显示出 CLOB

select dbms_sqltune.report_tuning_task('test_sql_tuning_task2') from dual;

执行结果 CSDN格式没对齐实际上是很好的

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test_sql_tuning_task2
Tuning Task Owner : DBA_MONITER
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 06/03/2014 11:04:43
Completed at : 06/03/2014 11:04:58

-------------------------------------------------------------------------------
Schema Name: CCPS
SQL ID : 8gb2jup02tzt3
SQL Text : select trd.tr_no,trd.tr_reference,trd.tr_status,trd.tr_paystartti
me,trd.tr_datetime,trd.tr_bankcurrency,trd.tr_bankamout,trd.tr_ch
a_code,ch.cha_merno,ch.cha_vpc_accesscode,ch.cha_secure_secret
from ccps_traderecord trd left join ccps_channel ch on
trd.tr_cha_code = ch.cha_code where trd.TR_MER_NO != :1 and
trd.tr_checked = 0 and trd.tr_status != -2 and
(sysdate-:2/(24*60)) >= trd.tr_datetime and
upper(trd.tr_bank_code) in (:3,:4) and rownum <= :5 order by
trd.tr_id asc

-------------------------------------------------------------------------------
FINDINGS SECTION (4 findings) /*找到了4个优化建议*/
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划 2。选择以下 SQL 概要文件之一进行实施。

Recommendation (estimated benefit: 50%)
---------------------------------------
- 考虑接受推荐的 SQL 概要文件。
execute dbms_sqltune.accept_sql_profile(task_name =>
'test_sql_tuning_task2', task_owner => 'DBA_MONITER', replace =>
TRUE);

Validation results
------------------
已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成,
则另一计划可能只执行了一部分。

Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time(us): 1046935 479043 54.24 %
CPU Time(us): 1046842 479427 54.2 %
User I/O Time(us): 19275 207 98.92 %
Buffer Gets: 167373 83679 50 %
Physical Read Requests: 972 15 98.45 %
Physical Write Requests: 0 0
Physical Read Bytes: 53682176 122880 99.77 %
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1

Notes
-----
1. SQL profile plan 已首先执行以预热缓冲区高速缓存。
2. the SQL profile plan 的统计信息来自第二次执行。

Recommendation (estimated benefit: 87.66%)
------------------------------------------
- 考虑接受建议的 SQL 概要文件, 以便对此语句使用并行执行。
execute dbms_sqltune.accept_sql_profile(task_name =>
'test_sql_tuning_task2', task_owner => 'DBA_MONITER', replace =>
TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

与 DOP 9 并行执行此查询会使 SQL 概要文件计划上的响应时间缩短 87.66%。但是, 启用并行执行时要付出一些代价。它将增加语句的资源消耗
(预计为 11.04%), 这会导致系统吞吐量降低。此外, 由于在非常短的持续时间内消耗了这些资源, 因此如果没有足够可用的硬件容量,
并发语句的响应时间将受到负面影响。

The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.

Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0

Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
谓词 "TRD"."TR_MER_NO"<>TO_NUMBER(:B1) (在执行计划的行 ID 5 处使用) 是索引列 "TR_MER_NO"
的不等式条件。此不等式条件使优化程序无法选择表 "OSSC"."CCPS_TRADERECORD" 的索引。

Recommendation
--------------
- 将谓词重写为等价型以便利用索引。

3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
谓词 "TRD"."TR_MER_NO"<>TO_NUMBER(:B1) (在执行计划的行 ID 5 处使用) 是索引列 "TR_MER_NO"
的不等式条件。此不等式条件使优化程序无法选择表 "OSSC"."CCPS_TRADERECORD" 的索引。

Recommendation
--------------
- 将谓词重写为等价型以便利用索引。

4- Alternative Plan Finding
---------------------------
通过搜索系统的实时和历史性能数据找到了此语句的某些替代执行计划。

The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.

id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 415509470 2014-05-17/10:50:22 2.081 Cursor Cache
2 280106883 2014-05-24/23:56:25 2.187 Cursor Cache
3 1210540799 2014-06-02/00:48:34 2.437 Cursor Cache

Information
-----------
- 因为找不到原始计划的任何执行历史记录, SQL 优化指导无法确定这些执行计划中是否有一些执行计划优于原始计划。但是,
如果您知道某个替代计划优于原始计划, 可以为该替代计划创建 SQL 计划基线。这将指示 Oracle 优化程序在将来优先于任何其他选择来选取它。
execute dbms_sqltune.create_sql_plan_baseline(task_name =>
'test_sql_tuning_task2', owner_name => 'DBA_MONITER',
plan_hash_value => xxxxxxxx);

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION /*原始的执行计划*/
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 410215884

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 242 | 45432 (1)| 00:09:06 |
| 1 | SORT ORDER BY | | 1 | 242 | 45432 (1)| 00:09:06 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 1 | 242 | 45431 (1)| 00:09:06 |
| 4 | TABLE ACCESS FULL | CCPS_CHANNEL | 193 | 14282 | 5 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | CCPS_TRADERECORD | 1 | 168 | 45426 (1)| 00:09:06 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM<=:5)
3 - access("TRD"."TR_CHA_CODE"="CH"."CHA_CODE"(+))
5 - filter("TRD"."TR_CHECKED"=0 AND "TRD"."TR_MER_NO"<>TO_NUMBER(:1) AND
(UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:3) OR
UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:4)) AND "TRD"."TR_STATUS"<>(-2) AND
"TRD"."TR_DATETIME"<=SYSDATE@!-:2/1440)

2- Using SQL Profile (使用概要的执行计划)
--------------------
Plan hash value: 3313146672

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 242 | 45428 (1)| 00:09:06 |
| 1 | SORT ORDER BY | | 1 | 242 | 45428 (1)| 00:09:06 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 242 | 45427 (1)| 00:09:06 |
|* 4 | TABLE ACCESS FULL | CCPS_TRADERECORD | 1 | 168 | 45426 (1)| 00:09:06 |
| 5 | TABLE ACCESS BY INDEX ROWID| CCPS_CHANNEL | 1 | 74 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_CCPS_CHANNEL | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM<=:5)
4 - filter("TRD"."TR_CHECKED"=0 AND (UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:3) OR
UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:4)) AND "TRD"."TR_MER_NO"<>TO_NUMBER(:1) AND
"TRD"."TR_STATUS"<>(-2) AND "TRD"."TR_DATETIME"<=SYSDATE@!-:2/1440)
6 - access("TRD"."TR_CHA_CODE"="CH"."CHA_CODE"(+))

3- Using Parallel Execution (使用并行的执行计划)
---------------------------
Plan hash value: 2972040833

-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 251 | 5605 (1)| 00:01:08 | | | |
| 1 | SORT ORDER BY | | 1 | 251 | 5605 (1)| 00:01:08 | | | |
|* 2 | COUNT STOPKEY | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 251 | 5604 (1)| 00:01:08 | Q1,00 | P->S | QC (RAND) |
|* 5 | COUNT STOPKEY | | | | | | Q1,00 | PCWC | |
| 6 | NESTED LOOPS OUTER | | 1 | 251 | 5604 (1)| 00:01:08 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | CCPS_TRADERECORD | 1 | 177 | 5604 (1)| 00:01:08 | Q1,00 | PCWP | |
| 9 | TABLE ACCESS BY INDEX ROWID| CCPS_CHANNEL | 1 | 74 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 10 | INDEX UNIQUE SCAN | PK_CCPS_CHANNEL | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM<=:5)
5 - filter(ROWNUM<=:5)
8 - filter("TRD"."TR_CHECKED"=0 AND "TRD"."TR_MER_NO"<>TO_NUMBER(:1) AND (UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:3) OR
UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:4)) AND "TRD"."TR_STATUS"<>(-2) AND "TRD"."TR_DATETIME"<=SYSDATE@!-:2/1440)
10 - access("TRD"."TR_CHA_CODE"="CH"."CHA_CODE"(+))

-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------

Plan 3
------

Plan Origin :Cursor Cache
Plan Hash Value :1210540799
Executions :6829
Elapsed Time :2.437 sec
CPU Time :2.436 sec
Buffer Gets :152007
Disk Reads :360
Disk Writes :0

Notes:
1. Statistics shown are averaged over multiple executions.

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 236K| 1396K (1)| 04:39:23 |
| 1 | SORT ORDER BY | | 1000 | 236K| 1396K (1)| 04:39:23 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | HASH JOIN RIGHT OUTER | | 2103 | 496K| 1396K (1)| 04:39:23 |
| 4 | TABLE ACCESS FULL | CCPS_CHANNEL | 193 | 14282 | 5 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| CCPS_TRADERECORD | 2103 | 345K| 1396K (1)| 04:39:23 |
|* 6 | INDEX RANGE SCAN | IX_TR_DATETIME | 1531K| | 13143 (1)| 00:02:38 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM<=:5)
3 - access("TRD"."TR_CHA_CODE"="CH"."CHA_CODE"(+))
5 - filter("TRD"."TR_CHECKED"=0 AND "TRD"."TR_MER_NO"<>TO_NUMBER(:1) AND
(UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:3) OR UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:4))
AND "TRD"."TR_STATUS"<>(-2))
6 - access("TRD"."TR_DATETIME"<=SYSDATE@!-:2/1440)

Plan 2 (更换了下驱动表啦)
------

Plan Origin :Cursor Cache
Plan Hash Value :280106883
Executions :6441
Elapsed Time :2.187 sec
CPU Time :2.186 sec
Buffer Gets :602021
Disk Reads :329
Disk Writes :0

Notes:
1. Statistics shown are averaged over multiple executions.

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 236K| 1396K (1)| 04:39:23 |
| 1 | SORT ORDER BY | | 1000 | 236K| 1396K (1)| 04:39:23 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | HASH JOIN OUTER | | 2103 | 496K| 1396K (1)| 04:39:23 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CCPS_TRADERECORD | 2103 | 345K| 1396K (1)| 04:39:23 |
|* 5 | INDEX RANGE SCAN | IX_TR_DATETIME | 1531K| | 13143 (1)| 00:02:38 |
| 6 | TABLE ACCESS FULL | CCPS_CHANNEL | 193 | 14282 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM<=:5)
3 - access("TRD"."TR_CHA_CODE"="CH"."CHA_CODE"(+))
4 - filter("TRD"."TR_CHECKED"=0 AND "TRD"."TR_MER_NO"<>TO_NUMBER(:1) AND
(UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:3) OR UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:4))
AND "TRD"."TR_STATUS"<>(-2))
5 - access("TRD"."TR_DATETIME"<=SYSDATE@!-:2/1440)

Plan 1
------

Plan Origin :Cursor Cache
Plan Hash Value :415509470
Executions :1387
Elapsed Time :2.081 sec
CPU Time :2.082 sec
Buffer Gets :1217334
Disk Reads :29
Disk Writes :0

Notes:
1. Statistics shown are averaged over multiple executions.

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 236K| 1398K (1)| 04:39:48 |
| 1 | SORT ORDER BY | | 1000 | 236K| 1398K (1)| 04:39:48 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS OUTER | | 2103 | 496K| 1398K (1)| 04:39:48 |
|* 4 | TABLE ACCESS BY INDEX ROWID| CCPS_TRADERECORD | 2103 | 345K| 1396K (1)| 04:39:23 |
|* 5 | INDEX RANGE SCAN | IX_TR_DATETIME | 1531K| | 13143 (1)| 00:02:38 |
| 6 | TABLE ACCESS BY INDEX ROWID| CCPS_CHANNEL | 1 | 74 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_CCPS_CHANNEL | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM<=:5)
4 - filter("TRD"."TR_CHECKED"=0 AND "TRD"."TR_MER_NO"<>TO_NUMBER(:1) AND
(UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:3) OR UPPER("TRD"."TR_BANK_CODE")=SYS_OP_C2C(:4))
AND "TRD"."TR_STATUS"<>(-2))
5 - access("TRD"."TR_DATETIME"<=SYSDATE@!-:2/1440)
7 - access("TRD"."TR_CHA_CODE"="CH"."CHA_CODE"(+))

SQL_TEXT

DECLARE

my_task_name VARCHAR2(30);

my_sqltext CLOB;

BEGIN

my_sqltext := 'select a.id,a.name,b.id,b.namefrom bigtable a,smalltable b where a.id=b.id and a.id=40000';

my_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK(
                           sql_text => my_sqltext,
                           user_name => 'SCOTT',
                            scope => 'COMPREHENSIVE',
                            time_limit => 60,
                            task_name => 'test_sql_tuning_task1',
                           description => 'Task to tune a query');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=> 'test_sql_tuning_task1');
END;
/

官方文档

SQL textformat:

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;

SQL IDformat:

DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

WorkloadRepository format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(

begin_snap IN NUMBER,

end_snap IN NUMBER,

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

SQLSETformat:

DBMS_SQLTUNE.CREATE_TUNING_TASK(

sqlset_name IN VARCHAR2,

basic_filter IN VARCHAR2 := NULL,

object_filter IN VARCHAR2:= NULL,

rank1 IN VARCHAR2 := NULL,

rank2 IN VARCHAR2 := NULL,

rank3 IN VARCHAR2 := NULL,

result_percentageIN NUMBER := NULL,

result_limit IN NUMBER := NULL,

scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL

plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME',

sqlset_owner IN VARCHAR2 := NULL)

RETURN VARCHAR2;

SQLPerformance Analyzer format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(

spa_task_name IN VARCHAR2,

spa_task_owner IN VARCHAR2:= NULL,

spa_compare_exec IN VARCHAR2:= NULL,

basic_filter IN VARCHAR2:= NULL,

time_limit IN NUMBER := TIME_LIMIT_DEFAULT,

task_name IN VARCHAR2 := NULL,

description IN VARCHAR2 := NULL)

RETURN VARCHAR2;

Parameters

Table 140-17 CREATE_TUNING_TASK Function Parameters

Parameter

Description

sql_text

Text of a SQL statement

begin_snap

Begin snapshot identifier

end_snap

End snapshot identifier

sql_id

Identifier of a SQL statement

bind_list

An ordered list of bind values in ANYDATA type

plan_hash_value

Hash value of the SQL execution plan

sqlset_name

SQL tuning set name

basic_filter

SQL predicate to filter the SQL from the SQL tuning set

object_filter

Object filter

rank(i)

Order-by clause on the selected SQL

result_percentage

Percentage on the sum of a ranking measure

result_limit

Top L(imit) SQL from the (filtered/ranked) SQL

user_name

Username for whom the statement is to be tuned

scope

Tuning scope (limited/comprehensive)

time_limit

The maximum duration in seconds for the tuning session

task_name

Optional tuning task name

description

Description of the SQL tuning session to a maximum of 256 characters

plan_filter

Plan filter. It is applicable in case there are multiple plans (plan_hash_value) associated with the same statement. This filter allows for selecting one plan (plan_hash_value) only. Possible values are:

LAST_GENERATED: plan with the most recent timestamp
FIRST_GENERATED: plan with the earliest timestamp, the opposite to LAST_GENERATED
LAST_LOADED: plan with the most recent first_load_time statistics information
FIRST_LOADED: plan with the earliest first_load_time statistics information, the opposite to LAST_LOADED
MAX_ELAPSED_TIME: plan with the maximum elapsed time
MAX_BUFFER_GETS: plan with the maximum buffer gets
MAX_DISK_READS: plan with the maximum disk reads
MAX_DIRECT_WRITES: plan with the maximum direct writes
MAX_OPTIMIZER_COST: plan with the maximum optimizer cost

sqlset_owner

Owner of the SQL tuning set, or NULL for the current schema owner

spa_task_name

Name of the SQL Performance Analyzer task whose regressions are to be tuned

spa_task_owner

Owner of specified SQL Performance Analyzer task or NULL for current user

spa_compare_exec

Execution name of Compare Performance trial of SQL Performance Analyzer task. If NULL, we use the most recent execution of the given SQL Performance Analyzer task, of type COMPAREPERFORMANCE

Return Values

A SQL tuningtask name that is unique by user (two different users can give the same name totheir advisor tasks).

Usage Notes

With regardto the form of this subprogram that takes a SQL tuning set, filters provided tothis function are evaluated as part of a SQL run by the current user. As such,they are executed with that user's security privileges and can contain anyconstructs and subqueries that user can access, but no more.

Examples

variable stmt_task VARCHAR2(64);

variable sts_task VARCHAR2(64);

variable spa_tune_task VARCHAR2(64);

Create TuningTask with SQL Text format

EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -

sql_text =>'select quantity_sold from sales s, times t where s.time_id = t.time_id ands.time_id = TO_DATE(''24-NOV-00'')');

Create TuningTask with SQL ID format

EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id=> 'ay1m3ssvtrh24');

-- tune in limited scope

EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id=> 'ay1m3ssvtrh24', -

scope =>'LIMITED');

-- only give 10 minutes for tuning statement

EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id=> 'ay1m3ssvtrh24', -

time_limit =>600);

Create TuningTask with AWR Snapshot format

EXEC :stmt_task :=DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 1, -

end_snap =>2, sql_id => 'ay1m3ssvtrh24');

Create TuningTask with SQL Tuning Set format

-- First we need to load an STS, then tune it

-- Tune our statements in order by buffer gets, timelimit of one hour

-- the default ranking measure is elapsed time.

EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -

sqlset_name => 'my_workload', -

rank1 => 'BUFFER_GETS', -

time_limit => 3600, -

description => 'tune my workload ordered by buffergets');

Create Tuning Task with SPA Task format

-- Tune the SQLs that were reported as having regressedfrom the compare

-- performance execution of the SPA task named task_123

EXEC :spa_tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(

spa_task_name =>'task_123',

spa_task_owner => 'SCOTT',

spa_compare_exec => 'exec1');

0 0