Oracle SQL 优化之sql tuning advisor (STA)

2025-04-13 技术教程

前言：经常可以碰到优化sql的需求，开发人员直接扔过来一个SQL让DBA优化，然后怎么办？

当然，经验丰富的DBA可以从各种方向下手，有时通过建立正确索引即可获得很好的优化效果，但是那些复杂SQL错综复杂的表关联，却让DBA们满头大汗。

如下特别介绍一种oracle官方提供的科学优化方法STA，经过实践，不敢说此特性绝对有效，但是可以开阔思路，并且从中学到许多知识，不再用“猜”的方式去创建索引了。

SQL优化器SQLTuningAdvisor（STA)，是oracle的sql优化补助工具。
其实优化sql主要有两个方案，其一是改写sql本身，改写sql需要对sql语法、数据库的执行方式都要有较好地理解。
其二就是这个STA，它属于DBMS_SQLTUNE包，它的主要作用是对于sql使用到的表创建正确的索引。

使用STA前提：

要保证优化器是CBO模式下。showparameterOPTIMIZER_MODEall_rows/*CBO，sql所有返回行都采用基于成本的方式运行*/first_rows/*CBO，使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行*/first_rows_n/*CBO，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录*/choose/*如果有统计信息，采用CBO，否则采用RBO*/rule/*RBO*/执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：grantadvisortoscott;

如下是STA使用例子：

1.首先创建两个练习表obj与ind，仅创建表，无需创建索引：

SQL>createtableobjasselect*fromdba_objects;表已创建。SQL>createtableindasselect*fromdba_indexes;表已创建。SQL>insertintoobjselect*fromobj;已创建74603行。SQL>insertintoobjselect*fromobj;已创建149206行。SQL>insertintoobjselect*fromobj;已创建298412行。SQL>insertintoindselect*fromind;已创建5134行。SQL>insertintoindselect*fromind;已创建10268行。SQL>insertintoindselect*fromind;已创建20536行。

2.然后对这两个表，obj与ind进行联合查询，并通过autotrace查看其执行计划：

SQL>settimingonSQL>setautottraceSQL>selectcount(*)fromobjo,indiwhereo.object_name=i.index_name;已用时间:00:00:00.15执行计划----------------------------------------------------------Planhashvalue:380737209------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|83||5063(1)|00:01:01||1|SORTAGGREGATE||1|83|||||*2|HASHJOIN||5861K|463M|1272K|5063(1)|00:01:01||3|TABLEACCESSFULL|IND|44789|743K||379(1)|00:00:05||4|TABLEACCESSFULL|OBJ|577K|36M||2472(1)|00:00:30|------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("O"."OBJECT_NAME"="I"."INDEX_NAME")Note------dynamicsamplingusedforthisstatement(level=2)统计信息----------------------------------------------------------9recursivecalls4dbblockgets10406consistentgets0physicalreads0redosize425bytessentviaSQL*Nettoclient415bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed

通过执行计划，可以清晰的看到，在执行以上两个表的联合查询的时候，两张表走的全表扫和hash join。

正式使用STA进行优化：
第一步：创建优化任务
通过调用函数DBMS_SQLTUNE.CREATE_TUNING_TASK来创建优化任务，调用存储过程DBMS_SQLTUNE.EXECUTE_TUNING_TASK执行该任务：

SQL>setautotoffSQL>settimingoffDECLAREmy_task_nameVARCHAR2(30);my_sqltextCLOB;BEGINmy_sqltext:='selectcount(*)fromobjo,indiwhereo.object_name=i.index_name';my_task_name:=DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text=>my_sqltext,user_name=>'SCOTT',scope=>'COMPREHENSIVE',time_limit=>30,task_name=>'tuning_sql_test',description=>'tuning');DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'tuning_sql_test');END;/PL/SQL过程已成功完成。

如下是参数解释：

函数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>setlong999999SQL>setserveroutputonsize999999SQL>setline120SQL>selectDBMS_SQLTUNE.REPORT_TUNING_TASK('tuning_sql_test')fromdual;

如下是显示优化的结果：

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')--------------------------------------------------------------------------------GENERALINFORMATIONSECTION-------------------------------------------------------------------------------TuningTaskName:tuning_sql_testTuningTaskOwner:SCOTTWorkloadType:SingleSQLStatementExecutionCount:2CurrentExecution:EXEC_112ExecutionType:TUNESQLScope:COMPREHENSIVETimeLimit(seconds):30CompletionStatus:COMPLETEDStartedat:08/29/201311:10:10Completedat:08/29/201311:10:12-------------------------------------------------------------------------------SchemaName:SCOTTSQLID:6wruu2mxyu8g3SQLText:selectcount(*)fromobjo,indiwhereo.object_name=i.index_name-------------------------------------------------------------------------------FINDINGSSECTION(3findings)-------------------------------------------------------------------------------1-StatisticsFinding---------------------尚未分析表"SCOTT"."IND"。Recommendation---------------考虑收集此表的优化程序统计信息。executedbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'IND',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO');Rationale---------为了选择好的执行计划,优化程序需要此表的最新统计信息。2-StatisticsFinding---------------------尚未分析表"SCOTT"."OBJ"。Recommendation---------------考虑收集此表的优化程序统计信息。executedbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'OBJ',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO');Rationale---------为了选择好的执行计划,优化程序需要此表的最新统计信息。3-IndexFinding(seeexplainplanssectionbelow)--------------------------------------------------通过创建一个或多个索引可以改进此语句的执行计划。Recommendation(estimatedbenefit:75.74%)-------------------------------------------考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。createindexSCOTT.IDX$$_00790001onSCOTT.OBJ("OBJECT_NAME");-考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。createindexSCOTT.IDX$$_00790002onSCOTT.IND("INDEX_NAME");Rationale---------创建推荐的索引可以显著地改进此语句的执行计划。但是,使用典型的SQL工作量运行"访问指导"可能比单个语句更可取。通过这种方法可以获得全面的索引建议案,包括计算索引维护的开销和附加的空间消耗。

-------------------------------------------------------------------------------EXPLAINPLANSSECTION-------------------------------------------------------------------------------1-Original-----------Planhashvalue:380737209------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|83||5063(1)|00:01:01||1|SORTAGGREGATE||1|83|||||*2|HASHJOIN||5861K|463M|1272K|5063(1)|00:01:01||3|TABLEACCESSFULL|IND|44789|743K||379(1)|00:00:05||4|TABLEACCESSFULL|OBJ|577K|36M||2472(1)|00:00:30|------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("O"."OBJECT_NAME"="I"."INDEX_NAME")2-UsingNewIndices--------------------Planhashvalue:4048334321--------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|83||1228(2)|00:00:15||1|SORTAGGREGATE||1|83|||||2|MERGEJOIN||5861K|463M||1228(2)|00:00:15||3|INDEXFULLSCAN|IDX$$_00790001|577K|36M||944(1)|00:00:12||*4|SORTJOIN||44789|743K|2120K|268(1)|00:00:04||5|INDEXFASTFULLSCAN|IDX$$_00790002|44789|743K||18(0)|00:00:01|--------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------4-access("O"."OBJECT_NAME"="I"."INDEX_NAME")filter("O"."OBJECT_NAME"="I"."INDEX_NAME")

报告如上，

仔细阅读此报告，主要给出了两种建议：1.收集两张表obj与ind的统计信息；2.在这两张表上面创建相应索引。

并且特别贴心的把语句都给了出来，完全可以拿去直接运行，

更加贴心的是，报告中把优化前后的预计效果都展示了出来，完全一目了然，更加方便了DBA考虑是否进行建议优化方案。

五、删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务，可以释放资源。

SQL>execdbms_sqltune.drop_tuning_task('tuning_sql_test');

结语：如上就是SQL优化器SQLTuningAdvisor（STA）的使用方法，可能语法有些复杂，但是确实给优化的工作带来了很大帮助，经常使用多加练习即可熟练。