sqlturningadvise的使用

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

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

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

| 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

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

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

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

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

---------

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

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

--------

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让优化变得如此简单。

当前题目：sqlturningadvise的使用
文章分享：http://ybzwz.com/article/pgjceh.html

sqlturningadvise的使用

其他资讯