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