sqlturningadvise的使用.docx

sql turning advise（sta）SQL优化器SQL Tuning Advisor （STA)，是oracle的sql优化补助工具。其实优化sql主要有两个方案：其一是改写sql本身，改写sql需要对sql语法、数据库的执行方式都要有较好地理解。其二就是这个STA，它属于DBMS_SQLTUNE包，它的主要作用是对于sql使用到的表创建正确的索引。使用STA前提：要保证优化器是CBO模式下。show parameter OPTIMIZER_MODEall_rows /*CBO，sql所有返回行都采用基于成本的方式运行*/first_rows /*CBO，使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行*/first_rows_n /*CBO，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录*/choose /*如果有统计信息，采用CBO，否则采用RBO*/rule /*RBO*/执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：grant advisor to scott;下面通过案例详细介绍该工具的具体使用：1：创建案例用户并授权SQL create user lanniao identified by lanniao;用户已创建。SQL grant connect,resource to lanniao;授权成功。SQL grant advisor to lanniao;授权成功。2：创建测试表SQL create table bigtab as select rownum as id,a.* from sys.all_objects a;表已创建。SQL create table smalltab as select rownum as id,a.* from sys.all_tables a;表已创建。然后多运行几次下面的脚本，增加表里的数据：SQL insert into bigtab select rownum as id,a.* from sys.all_objects a;已创建55637行。SQL insert into bigtab select rownum as id,a.* from sys.all_objects a;已创建55637行。SQL insert into bigtab select rownum as id,a.* from sys.all_objects a;已创建55637行。SQL commit;提交完成。 这里创建一张大表和一张小表，并且都没有索引，下面执行一个查询：SQL conn / as sysdba已连接。SQL grant dba to lanniao;授权成功。SQL conn lanniao/lanniao已连接。SQL set timing onSQL set autot onSQL select count(*) from bigtab a, smalltab b where a.object_name=b.table_name; COUNT(*)- 752已用时间: 00: 00: 00.32执行计划-Plan hash value: 3089226980-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 34 | 966 (1)| 00:00:12 | 1 | SORT AGGREGATE | | 1 | 34 | | |* 2 | HASH JOIN | | 155K| 5152K| 966 (1)| 00:00:12 | 3 | TABLE ACCESS FULL| SMALLTAB | 2542 | 43214 | 32 (0)| 00:00:01 | 4 | TABLE ACCESS FULL| BIGTAB | 227K| 3778K| 932 (1)| 00:00:12 |-Predicate Information (identified by operation id):- 2 - access(A.OBJECT_NAME=B.TABLE_NAME)Note- - dynamic sampling used for this statement (level=2)统计信息- 73 recursive calls 1 db block gets 3683 consistent gets 942 physical reads 132 redo size 535 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed以上可以看到，在执行以上两个查询的时候，两张表走的全表扫和hash join。3: 使用STA对sql进行分析3.1：创建优化任务通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程EXECUTE_TUNING_TASK执行该任务：SQL set autot offSQL set timing offSQL declare 2 my_task_name varchar2(30); 3 my_sqltext clob; 4 begin 5 my_sqltext:=select count(*) from bigtab a,smalltab b where 6 a.object_name=b.table_name; 7 my_task_name:=dbms_sqltune.create_tuning_task( 8 sql_text =my_sqltext, 9 user_name =LANNIAO, 10 scope =COMPREHENSIVE, 11 time_limit =60, 12 task_name =tuning_sql_test, 13 description =Task to tune a query on specified table); 14 dbms_sqltune.execute_tuning_task(task_name =tuning_sql_test); 15 end; 16 /PL/SQL 过程已成功完成。函数CREATE_TUNING_TASK，sql_text是需要优化的语句，user_name是该语句通过哪个用户执行，用户名大写，scope是优化范围（limited或comprehensive），time_limit优化过程的时间限制，task_name优化任务名称，description优化任务描述。3.2: 执行优化任务通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。SQL exec dbms_sqltune.execute_tuning_task(tuning_sql_test);PL/SQL 过程已成功完成。3.3：检查优化任务的状态通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。SQL set linesize 1000SQL SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =tuning_sql_test;TASK_NAME STATUS- -tuning_sql_test COMPLETED3.4: 查看优化结果通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。SQL set long 999999SQL set serveroutput on size 999999SQL set line 120SQL 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_testTuning Task Owner : LANNIAOWorkload Type : Single SQL StatementExecution Count : 2Current Execution : EXEC_1056Execution Type : TUNE SQLScope : COMPREHENSIVETime Limit(seconds): 60Completion Status : COMPLETEDDBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)-Started at : 04/14/2014 16:42:18Completed at : 04/14/2014 16:42:19-Schema Name: LANNIAOSQL ID : 9n5grk4kh8ndqSQL Text : select count(*) from bigtab a,smalltab b where a.object_name=b.table_name-FINDINGS SECTION (3 findings)DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)-1- Statistics Finding- 尚未分析表 LANNIAO.SMALLTAB。 Recommendation - - 考虑收集此表的优化程序统计信息。 execute dbms_stats.gather_table_stats(ownname = LANNIAO, tabname = SMALLTAB, estimate_percent = DBMS_STATS.AUTO_SAMPLE_SIZE,DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)- method_opt = FOR ALL COLUMNS SIZE AUTO); Rationale - 为了选择好的执行计划, 优化程序需要此表的最新统计信息。2- Statistics Finding- 尚未分析表 LANNIAO.BIGTAB。 RecommendationDBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)- - - 考虑收集此表的优化程序统计信息。 execute dbms_stats.gather_table_stats(ownname = LANNIAO, tabname = BIGTAB, estimate_percent = DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt = FOR ALL COLUMNS SIZE AUTO); Rationale - 为了选择好的执行计划, 优化程序需要此表的最新统计信息。3- Index Finding (see explain plans section below)DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)- 通过创建一个或多个索引可以改进此语句的执行计划。 Recommendation (estimated benefit: 90.48%) - - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。 create index LANNIAO.IDX$_04050001 on LANNIAO.SMALLTAB(TABLE_NAME); - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。 create index LANNIAO.IDX$_04050002 on LANNIAO.BIGTAB(OBJECT_NAME);DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)- Rationale - 创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 访问指导 可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。-EXPLAIN PLANS SECTION-DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)-1- Original-Plan hash value: 3089226980-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 34 | 966 (1)| 00:00:12 | 1 | SORT AGGREGATE | | 1 | 34 | | |* 2 | HASH JOIN | | 155K| 5152K| 966 (1)| 00:00:12 | 3 | TABLE ACCESS FULL| SMALLTAB | 2542 | 43214 | 32 (0)| 00:00:01 |DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)-| 4 | TABLE ACCESS FULL| BIGTAB | 227K| 3778K| 932 (1)| 00:00:12 |-Predicate Information (identified by operation id):- 2 - access(A.OBJECT_NAME=B.TABLE_NAME)2- Using New Indices-Plan hash value: 2901183249DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 34 | 92 (4)| 00:00:02 | 1 | SORT AGGREGATE | | 1 | 34 | | |* 2 | HASH JOIN | | 155K| 5152K| 92 (4)| 00:00:02 | 3 | INDEX FAST FULL SCAN| IDX$_04050001 | 2542 | 43214 | 12 (0)| 00:00:01 | 4 | INDEX FAST FULL SCAN| IDX$_04050002 | 227K| 3778K| 78 (2)| 00:00:01 |DBMS_SQLTUNE.REPORT_TUNING_TASK(TUNING_SQL_TEST)-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值大大下降。3.5：删除优化任务通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务SQLexec dbms_sqltune.drop_tuning_task(tuning_sql_test);3.6：按照优化建议进行优化首先要说明一点的是，最好不要直接按照优化器给出的建议直接优化。因为像建索引这种操作影响可不是这一条语句，我们这里只是验证一下优化建议的效果。按照建议，创建两个索引：SQL create index smalltab_idx1 on smalltab(table_name);索引已创建。SQL create index bigtab_idx1 on bigtab(object_name);索引已创建。SQL analyze table smalltab compute statistics;表已分析。SQL analyze table bigtab compute statistics;表已分析。SQL set timing onSQL set autot onSQL select count(*) from bigtab a, smalltab b where a.object_name=b.table_name; COUNT