sql turning advise的使用

petrel919

　　sql turning advise（sta）
　　SQL优化器SQL Tuning Advisor （STA)，是oracle的sql优化补助工具。
　　其实优化sql主要有两个方案：
　　其一是改写sql本身，改写sql需要对sql语法、数据库的执行方式都要有较好地理解。
　　其二就是这个STA，它属于DBMS_SQLTUNE包，它的主要作用是对于sql使用到的表创建正确的索引。
　　使用STA前提：
　　要保证优化器是CBO模式下。
　　show parameterOPTIMIZER_MODE
　　all_rows /*CBO，sql所有返回行都采用基于成本的方式运行*/
　　first_rows /*CBO，使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行*/
　　first_rows_n /*CBO，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录*/
　　choose  /*如果有统计信息，采用CBO，否则采用RBO*/
　　rule /*RBO*/
　　执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：
　　grant advisor toscott;
　　下面通过案例详细介绍该工具的具体使用：
　　1：创建案例用户并授权

　　SQL> createuser lanniao>　　用户已创建。
　　SQL> grantconnect,resource to lanniao;
　　授权成功。
　　SQL> grantadvisor to lanniao;
　　授权成功。
　　2：创建测试表

　　SQL> createtable bigtab as select rownum as>　　表已创建。

　　SQL> createtable smalltab as select rownum as>　　表已创建。
　　然后多运行几次下面的脚本，增加表里的数据：

　　SQL> insertinto bigtab  select rownum as>　　已创建55637行。

　　SQL> insertinto bigtab  select rownum as>　　已创建55637行。

　　SQL> insertinto bigtab  select rownum as>　　已创建55637行。
　　SQL> commit;
　　提交完成。
　　这里创建一张大表和一张小表，并且都没有索引，下面执行一个查询：
　　SQL> conn /as sysdba
　　已连接。
　　SQL> grantdba to lanniao;
　　授权成功。
　　SQL>conn  lanniao/lanniao
　　已连接。
　　SQL> settiming on
　　SQL> setautot on
　　SQL>  select count(*) from bigtab a, smalltab bwhere a.object_name=b.table_name;
　　COUNT(*)
　　----------
　　752
　　已用时间:  00: 00: 00.32
　　执行计划
　　----------------------------------------------------------
　　Plan hashvalue: 3089226980
　　--------------------------------------------------------------------------------

　　|>　　--------------------------------------------------------------------------------
　　|   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 |
　　--------------------------------------------------------------------------------

　　PredicateInformation (identified by operation>　　---------------------------------------------------
　　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>　　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> setautot off
　　SQL> settiming 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
　　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> execdbms_sqltune.execute_tuning_task('tuning_sql_test');
　　PL/SQL 过程已成功完成。
　　3.3：检查优化任务的状态
　　通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
　　SQL> setlinesize 1000
　　SQL> SELECTtask_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';
　　TASK_NAME                  STATUS
　　-----------------------------          -------------------
　　tuning_sql_test                  COMPLETED
　　3.4: 查看优化结果
　　通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。
　　SQL> set long999999

　　SQL> setserveroutput on>　　SQL> set line120
　　SQL> selectDBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from dual;
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
　　--------------------------------------------------------------------------------
　　GENERALINFORMATION SECTION
　　-------------------------------------------------------------------------------
　　Tuning TaskName   : tuning_sql_test
　　Tuning TaskOwner  : LANNIAO
　　WorkloadType      : Single SQL Statement
　　ExecutionCount    : 2
　　CurrentExecution  : EXEC_1056
　　Execution Type     : TUNE SQL
　　Scope              : COMPREHENSIVE
　　TimeLimit(seconds): 60
　　CompletionStatus  : COMPLETED
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
　　--------------------------------------------------------------------------------
　　Started at         : 04/14/2014 16:42:18
　　Completedat       : 04/14/2014 16:42:19
　　-------------------------------------------------------------------------------
　　Schema Name:LANNIAO

　　SQL>　　SQL Text   : select count(*) from bigtab a,smalltab bwhere
　　a.object_name=b.table_name
　　-------------------------------------------------------------------------------
　　FINDINGS SECTION(3 findings)
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
　　--------------------------------------------------------------------------------
　　-------------------------------------------------------------------------------
　　1- StatisticsFinding
　　---------------------
　　尚未分析表"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 COLUMNSSIZE AUTO');
　　Rationale
　　---------
　　为了选择好的执行计划, 优化程序需要此表的最新统计信息。
　　2- StatisticsFinding
　　---------------------
　　尚未分析表"LANNIAO"."BIGTAB"。
　　Recommendation
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
　　--------------------------------------------------------------------------------
　　--------------
　　- 考虑收集此表的优化程序统计信息。
　　executedbms_stats.gather_table_stats(ownname => 'LANNIAO', tabname =>
　　'BIGTAB', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
　　method_opt => 'FOR ALL COLUMNSSIZE 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 onLANNIAO.SMALLTAB("TABLE_NAME");
　　- 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
　　create index LANNIAO.IDX$$_04050002 onLANNIAO.BIGTAB("OBJECT_NAME");
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
　　--------------------------------------------------------------------------------
　　Rationale
　　---------
　　创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运
　　行 "访问指导"
　　可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护
　　的开销和附加的空间消耗。
　　-------------------------------------------------------------------------------
　　EXPLAIN PLANSSECTION
　　-------------------------------------------------------------------------------
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
　　--------------------------------------------------------------------------------
　　1- Original
　　-----------
　　Plan hash value:3089226980
　　--------------------------------------------------------------------------------

　　|>　　--------------------------------------------------------------------------------
　　|   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 |
　　--------------------------------------------------------------------------------

　　PredicateInformation (identified by operation>　　---------------------------------------------------
　　2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")
　　2- Using NewIndices
　　--------------------
　　Plan hash value:2901183249
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
　　--------------------------------------------------------------------------------
　　--------------------------------------------------------------------------------
　　---------

　　|>　　ime     |
　　--------------------------------------------------------------------------------
　　---------
　　|   0 | SELECT STATEMENT       |                |     1 |   34 |    92   (4)| 0
　　0:00:0
　　2 |
　　|   1 | SORT AGGREGATE        |                |     1 |   34 |            |
　　|
　　|*  2 |  HASH JOIN            |                |   155K| 5152K|    92   (4)| 0
　　0:00:02 |

　　|   3 |   INDEX FAST FULL SCAN|>　　0:00:01 |

　　|   4 |   INDEX FAST FULL SCAN|>　　0:00:0
　　1 |
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
　　--------------------------------------------------------------------------------
　　--------------------------------------------------------------------------------
　　---------

　　PredicateInformation (identified by operation>　　---------------------------------------------------
　　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可以删除已经存在的优化任务
　　SQL>execdbms_sqltune.drop_tuning_task('tuning_sql_test');
　　3.6：按照优化建议进行优化
　　首先要说明一点的是，最好不要直接按照优化器给出的建议直接优化。因为像建索引这种操作影响可不是这一条语句，我们这里只是验证一下优化建议的效果。
　　按照建议，创建两个索引：
　　SQL> createindex smalltab_idx1 on smalltab(table_name);
　　索引已创建。
　　SQL> createindex bigtab_idx1 on bigtab(object_name);
　　索引已创建。
　　SQL> analyzetable smalltab compute statistics;
　　表已分析。
　　SQL> analyzetable bigtab compute statistics;
　　表已分析。
　　SQL> settiming on
　　SQL> setautot on
　　SQL> selectcount(*) from bigtab a, smalltab b where a.object_name=b.table_name;
　　COUNT(*)
　　----------
　　752
　　已用时间:  00: 00: 00.05
　　执行计划
　　----------------------------------------------------------
　　Plan hash value:2594317117
　　--------------------------------------------------------------------------------
　　--------

　　|>　　me     |
　　--------------------------------------------------------------------------------
　　--------
　　|   0 | SELECT STATEMENT       |               |     1 |   44 |   321   (2)| 00
　　:00:04 |
　　|   1 | SORT AGGREGATE        |               |     1 |   44 |            |
　　|
　　|*  2 |  HASH JOIN            |               | 20689 |   888K|  321   (2)| 00
　　:00:04 |
　　|   3 |   INDEX FAST FULL SCAN| SMALLTAB_IDX1 | 2869 | 54511 |     5   (0)| 00
　　:00:01 |
　　|   4|    INDEX FAST FULL SCAN|BIGTAB_IDX1   |   222K| 5433K|   313   (1)| 00
　　:00:04 |
　　--------------------------------------------------------------------------------
　　--------

　　PredicateInformation (identified by operation>　　---------------------------------------------------
　　2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")
　　统计信息
　　----------------------------------------------------------
　　1 recursive calls
　　0 db block gets
　　1176 consistent gets
　　3 physical reads

　　0 redo>　　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
　　可以看出，consistentgets比优化前大大下降了，优化建议确实提高了性能。Oracle10g让优化变得如此简单。