sql turning advise的使用

             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 identified by lanniao;

用户已创建。

SQL> grantconnect,resource to lanniao;

授权成功。

SQL> grantadvisor to lanniao;

授权成功。

2：创建测试表

SQL> createtable bigtab as select rownum as id,a.* from sys.all_objects a;

表已创建。

SQL> createtable smalltab as select rownum as id,a.* from sys.all_tables a;

表已创建。

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

SQL> insertinto bigtab  select rownum as id,a.* fromsys.all_objects a;

已创建55637行。

SQL> insertinto bigtab  select rownum as id,a.* fromsys.all_objects a;

已创建55637行。

SQL> insertinto bigtab  select rownum as id,a.* fromsys.all_objects a;

已创建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

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

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

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

PredicateInformation (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> 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 size 999999

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 ID     : 9n5grk4kh8ndq

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

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

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

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

PredicateInformation (identified by operation id):

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

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

2- Using NewIndices

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

Plan hash value:2901183249

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

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

---------

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

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| IDX$$_04050001 | 2542 | 43214 |    12   (0)| 0

0:00:01 |

|   4 |   INDEX FAST FULL SCAN| IDX$$_04050002 |  227K|  3778K|    78  (2)| 0

0:00:0

1 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

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

---------

PredicateInformation (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可以删除已经存在的优化任务

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

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

--------

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

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 id):

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

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

统计信息

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

          1 recursive calls

          0 db block gets

       1176 consistent gets

          3 physical reads

          0 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

可以看出，consistentgets比优化前大大下降了，优化建议确实提高了性能。Oracle10g让优化变得如此简单。