怎么用 SQL Tuning Advisor (STA) 优化SQL语句

在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。在10g中，Oracle推出了自己的SQL优化辅助工具:?SQL优化器(SQL?Tuning?Advisor?：STA)，它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。

?

执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：

?

SQL>?create?user?dave?identified?by?dave;

用户已创建。

SQL>?grant?connect,resource?to?dave;

授权成功。

SQL>?grant?advisor?to?dave;

授权成功。

?

?

下面简单介绍一下如何优化一条找到的问题语句。

?

?

create?table?bigtab?as?select?rownum?as?"id",a.*?from?sys.all_objects?a;

create?table?smalltab?as?select?rownum?as?"id",?a.*?FROM?sys.all_tables?a;

?

然后多运行几次下面的脚本，增加表里的数据：

insert?into?bigtab?select?rownum?as?"id",a.*?from?sys.all_objects?a;

insert?into?smalltab??select?rownum?as?"id",?a.*?FROM?sys.all_tables?a;

?

这里创建一张大表和一张小表，并且都没有索引，下面执行一个查询：

SQL>?set?timing?on

SQL>?set?autot?on

SQL>?select?count(*)?from?bigtab?a,?smalltab?b?where?a.object_name=b.table_name;

??COUNT(*)

----------

???2141537

已用时间:??00:?00:?20.05

执行计划

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

Plan?hash?value:?3089226980

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

|?Id??|?Operation???????????|?Name?????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|

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

|???0?|?SELECT?STATEMENT????|??????????|?????1?|????45?|??3146???(1)|?00:00:38?|

|???1?|??SORT?AGGREGATE?????|??????????|?????1?|????45?|????????????|??????????|

|*??2?|???HASH?JOIN?????????|??????????|???447K|????19M|??3146???(1)|?00:00:38?|

|???3?|????TABLE?ACCESS?FULL|?SMALLTAB?|?27327?|???533K|???264???(1)|?00:00:04?|

|???4?|????TABLE?ACCESS?FULL|?BIGTAB???|???712K|????16M|??2878???(1)|?00:00:35?|

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

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

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

???2?-?access("A"."OBJECT_NAME"="B"."TABLE_NAME")

?

统计信息

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

??????????0??recursive?calls

??????????0??db?block?gets

??????31149??consistent?gets

??????21058??physical?reads

??????????0??redo?size

????????426??bytes?sent?via?SQL*Net?to?client

????????416??bytes?received?via?SQL*Net?from?client

??????????2??SQL*Net?roundtrips?to/from?client

??????????0??sorts?(memory)

??????????0??sorts?(disk)

??????????1??rows?processed

?

?

第一步：创建优化任务

通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程EXECUTE_TUNING_TASK执行该任务：

SQL>?set?autot?off

SQL>?set?timing?off

SQL>?DECLARE

??2????my_task_name?VARCHAR2(30);

??3????my_sqltext???CLOB;

??4??BEGIN

??5????my_sqltext?:=?'select?count(*)?from?bigtab?a,?smalltab?b?where?a.object_name=b.table_name';

??6????my_task_name?:=?DBMS_SQLTUNE.CREATE_TUNING_TASK(

??7????????????sql_text????=>?my_sqltext,

??8????????????user_name???=>?'DAVE',???--?注意是大写，不然会报错，用户无效

??9????????????scope???????=>?'COMPREHENSIVE',

10????????????time_limit??=>?60,

11????????????task_name???=>?'tuning_sql_test',

12????????????description?=>?'Task?to?tune?a?query?on?a?specified?table');

13

14 ? ?--DBMS_SQLTUNE.EXECUTE_TUNING_TASK(?task_name?=>?'tuning_sql_test');

15??END;

16??/

?

PL/SQL?procedure?successfully?completed.

在函数CREATE_TUNING_TASK，sql_text是需要优化的语句，user_name是该语句通过哪个用户执行，scope是优化范围（limited或comprehensive），time_limit优化过程的时间限制，task_name优化任务名称，description优化任务描述。

?

第二步：?执行优化任务

通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。

SQL>?exec?dbms_sqltune.execute_tuning_task('tuning_sql_test');

PL/SQL?过程已成功完成。

?

第三步：检查优化任务的状态

?

通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。

?

SQL>?SELECT?task_name,status?FROM?USER_ADVISOR_TASKS?WHERE?task_name?='tuning_sql_test';

?

TASK_NAME?????????STATUS

------------------------------?-----------

tuning_sql_test?????????COMPLETED

?

第四步：查看优化结果

通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。

SQL>?SET?LONG?999999

SQL>?set?serveroutput?on?size?999999

SQL>?SET?LINESIZE?100

SQL>?SELECT?DBMS_SQLTUNE.REPORT_TUNING_TASK(?'tuning_sql_test')?from?DUAL;
?

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

GENERAL?INFORMATION?SECTION

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

Tuning?Task?Name??????????????????:?tuning_sql_test

Tuning?Task?Owner?????????????????:?DEMO

Scope?????????????????????????????:?COMPREHENSIVE

Time?Limit(seconds)???????????????:?60

Completion?Status?????????????????:?COMPLETED

Started?at????????????????????????:?5/28/2010?13:16:43

Completed?at??????????????????????:?5/28/2010?13:16:44

Number?of?Index?Findings??????????:?1

?

Schema?Name:?DEMO

SQL?ID?????:?6p64dnnsqf9pm

SQL?Text???:?select?count(*)?from?bigtab?a,?smalltab?b?where

?????????????a.object_name=b.table_name

?

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

FINDINGS?SECTION?(1?finding)

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

?

1-?Index?Finding?(see?explain?plans?section?below)

?

??The?execution?plan?of?this?statement?can?be?improved?by?creating?one?or?more

??indices.

?

??Recommendation?(estimated?benefit:?100%)

??----------------------------------------

??-?Consider?running?the?Access?Advisor?to?improve?the?physical?schema?design

????or?creating?the?recommended?index.

????create?index?DEMO.IDX$$_06C50001?on?SYS.SMALLTAB('TABLE_NAME');

?

??-?Consider?running?the?Access?Advisor?to?improve?the?physical?schema?design

????or?creating?the?recommended?index.

????create?index?DEMO.IDX$$_06C50002?on?SYS.BIGTAB('OBJECT_NAME');

?

??Rationale

??---------

????Creating?the?recommended?indices?significantly?improves?the?execution?plan

????of?this?statement.?However,?it?might?be?preferable?to?run?"Access?Advisor"

????using?a?representative?SQL?workload?as?opposed?to?a?single?statement.?This

????will?allow?to?get?comprehensive?index?recommendations?which?takes?into

????account?index?maintenance?overhead?and?additional?space?consumption.

?

?

EXPLAIN?PLANS?SECTION

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

1-?Original

-----------

Plan?hash?value:?3089226980

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

|?Id??|?Operation???????????|?Name?????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|

?

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

|???0?|?SELECT?STATEMENT????|??????????|?????1?|????36?|??3550???(2)|?00:00:43?|

|???1?|??SORT?AGGREGATE?????|??????????|?????1?|????36?|????????????|??????????|

|*??2?|???HASH?JOIN?????????|??????????|???155K|??5462K|??3550???(2)|?00:00:43?|

|???3?|????TABLE?ACCESS?FULL|?SMALLTAB?|??1223?|?22014?|????11???(0)|?00:00:01?|

|???4?|????TABLE?ACCESS?FULL|?BIGTAB???|??1205K|????20M|??3526???(1)|?00:00:43?|

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

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

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

???2?-?access("A"."OBJECT_NAME"="B"."TABLE_NAME")

2-?Using?New?Indices

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

Plan?hash?value:?494801882

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

|?Id??|?Operation??????????????|?Name???????????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|

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

|???0?|?SELECT?STATEMENT???????|????????????????|?????1?|????36?|??1108???(3)|?00:00:14?|

|???1?|??SORT?AGGREGATE????????|????????????????|?????1?|????36?|????????????|????????|

|*??2?|???HASH?JOIN????????????|????????????????|???155K|??5462K|??1108???(3)|?00:00:14?|

|???3?|????INDEX?FAST?FULL?SCAN|?IDX$$_06C50001?|??1223?|?22014?|?????3???(0)|?00:00:01?|

|???4?|????INDEX?FAST?FULL?SCAN|?IDX$$_06C50002?|??1205K|????20M|??1093???(2)|?00:00:14?|

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

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

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

???2?-?access("A"."OBJECT_NAME"="B"."TABLE_NAME")

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

?

看一下这个优化建议报告：

第一部分是关于这次优化任务的基本信息：如任务名称、执行时间、范围、涉及到的语句等等。

?

第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述：可以通过建立更多的所引来提高性能；然后是建议的具体内容：在表smalltab的字段table_name上创建索引，在表bigtab的字段object_name上创建索引；最后是相关注意事项：此次优化虽然给出了创建索引的建议，但是最好通过SQL访问建议器(SQL?Access?Advisor?SAA)结合整个数据库的工作量来深入分析，那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。

最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。

?

?

五、删除优化任务

通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务

SQL>exec?dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL?procedure?successfully?completed.

?

?