如何用 SQL Tuning Advisor (STA) 优化SQL语句

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

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

SQL>createuserdaveidentifiedbydave;

用户已创建。

SQL>grantconnect,resourcetodave;

授权成功。

SQL>grantadvisortodave;

授权成功。

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

createtablebigtabasselectrownumas"id",a.*fromsys.all_objectsa;

createtablesmalltabasselectrownumas"id",a.*FROMsys.all_tablesa;

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

insertintobigtabselectrownumas"id",a.*fromsys.all_objectsa;

insertintosmalltabselectrownumas"id",a.*FROMsys.all_tablesa;

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

SQL>settimingon

SQL>setautoton

SQL>selectcount(*)frombigtaba,smalltabbwherea.object_name=b.table_name;

COUNT(*)

----------

2141537

已用时间:00:00:20.05

执行计划

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

Planhashvalue:3089226980

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

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

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

|0|SELECTSTATEMENT||1|45|3146(1)|00:00:38|

|1|SORTAGGREGATE||1|45|||

|*2|HASHJOIN||447K|19M|3146(1)|00:00:38|

|3|TABLEACCESSFULL|SMALLTAB|27327|533K|264(1)|00:00:04|

|4|TABLEACCESSFULL|BIGTAB|712K|16M|2878(1)|00:00:35|

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

PredicateInformation(identifiedbyoperationid):

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

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

统计信息

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

0recursivecalls

0dbblockgets

31149consistentgets

21058physicalreads

0redosize

426bytessentviaSQL*Nettoclient

416bytesreceivedviaSQL*Netfromclient

2SQL*Netroundtripsto/fromclient

0sorts(memory)

0sorts(disk)

1rowsprocessed

第一步：创建优化任务

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

SQL>setautotoff

SQL>settimingoff

SQL>DECLARE

2my_task_nameVARCHAR2(30);

3my_sqltextCLOB;

4BEGIN

5my_sqltext:='selectcount(*)frombigtaba,smalltabbwherea.object_name=b.table_name';

6my_task_name:=DBMS_SQLTUNE.CREATE_TUNING_TASK(

7sql_text=>my_sqltext,

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

9scope=>'COMPREHENSIVE',

10time_limit=>60,

11task_name=>'tuning_sql_test',

12description=>'Tasktotuneaqueryonaspecifiedtable');

13

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

15END;

16/

PL/SQLproceduresuccessfullycompleted.

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

第二步：执行优化任务

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

SQL>execdbms_sqltune.execute_tuning_task('tuning_sql_test');

PL/SQL过程已成功完成。

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

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

SQL>SELECTtask_name,statusFROMUSER_ADVISOR_TASKSWHEREtask_name='tuning_sql_test';

TASK_NAMESTATUS

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

tuning_sql_testCOMPLETED

第四步：查看优化结果

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

SQL>SETLONG999999

SQL>setserveroutputonsize999999

SQL>SETLINESIZE100

SQL>SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK('tuning_sql_test')fromDUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

GENERALINFORMATIONSECTION

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

TuningTaskName:tuning_sql_test

TuningTaskOwner:DEMO

Scope:COMPREHENSIVE

TimeLimit(seconds):60

CompletionStatus:COMPLETED

Startedat:5/28/201013:16:43

Completedat:5/28/201013:16:44

NumberofIndexFindings:1

SchemaName:DEMO

SQLID:6p64dnnsqf9pm

SQLText:selectcount(*)frombigtaba,smalltabbwhere

a.object_name=b.table_name

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

FINDINGSSECTION(1finding)

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

1-IndexFinding(seeexplainplanssectionbelow)

Theexecutionplanofthisstatementcanbeimprovedbycreatingoneormore

indices.

Recommendation(estimatedbenefit:100%)

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

-ConsiderrunningtheAccessAdvisortoimprovethephysicalschemadesign

orcreatingtherecommendedindex.

createindexDEMO.IDX$$_06C50001onSYS.SMALLTAB('TABLE_NAME');

-ConsiderrunningtheAccessAdvisortoimprovethephysicalschemadesign

orcreatingtherecommendedindex.

createindexDEMO.IDX$$_06C50002onSYS.BIGTAB('OBJECT_NAME');

Rationale

---------

Creatingtherecommendedindicessignificantlyimprovestheexecutionplan

ofthisstatement.However,itmightbepreferabletorun"AccessAdvisor"

usingarepresentativeSQLworkloadasopposedtoasinglestatement.This

willallowtogetcomprehensiveindexrecommendationswhichtakesinto

accountindexmaintenanceoverheadandadditionalspaceconsumption.

EXPLAINPLANSSECTION

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

1-Original

-----------

Planhashvalue:3089226980

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

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

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

|0|SELECTSTATEMENT||1|36|3550(2)|00:00:43|

|1|SORTAGGREGATE||1|36|||

|*2|HASHJOIN||155K|5462K|3550(2)|00:00:43|

|3|TABLEACCESSFULL|SMALLTAB|1223|22014|11(0)|00:00:01|

|4|TABLEACCESSFULL|BIGTAB|1205K|20M|3526(1)|00:00:43|

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

PredicateInformation(identifiedbyoperationid):

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

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

2-UsingNewIndices

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

Planhashvalue:494801882

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

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

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

|0|SELECTSTATEMENT||1|36|1108(3)|00:00:14|

|1|SORTAGGREGATE||1|36|||

|*2|HASHJOIN||155K|5462K|1108(3)|00:00:14|

|3|INDEXFASTFULLSCAN|IDX$$_06C50001|1223|22014|3(0)|00:00:01|

|4|INDEXFASTFULLSCAN|IDX$$_06C50002|1205K|20M|1093(2)|00:00:14|

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

PredicateInformation(identifiedbyoperationid):

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

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

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

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

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

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

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

五、删除优化任务

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

SQL>execdbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQLproceduresuccessfullycompleted.

注：整理自网络