爱华网在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。在Oracle10g中推出了自己的SQL优化辅助工具:SQL优化器(SQLTuningAdvisor：STA)，它是新的DBMS_SQLTUNE包。一定要保证你的优化器是CBO模式。
1.执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限
SQL> grant advisor tozy;
Grant succeeded.
下面简单介绍一下如何优化一条找到的问题语句
2.创建用户做测试的2张表，大表里面插入500万条数据，小表里面插入10万条数据，其创建方法如下：
SQL> create table bigtable(id number(10),name varchar2(100));
Table created.
SQL> begin
2for i in 1..5000000 loop
3insertinto bigtable values(i,'test'||i);
4end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commti;
SQL> create table smalltable(id number(10),name varchar2(100));
Table created.
SQL> begin
2 for i in 1..100000loop
3 insert into smalltablevalues(i,'test'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commti;
3.然后对bigtable和smalltable做一个等连接查询，然后跟踪其执行计划。
SQL> set autotracetrace
SQL>select a.id,a.name,b.id,b.name
2from bigtable a,smalltableb
3where a.id=b.id anda.id=40000;
执行计划
----------------------------------------------------------
Plan hash value:1703851322
---------------------------------------------------------------------------------
|Id | Operation| Name| Rows | Bytes | Cost (%CPU)|Time|
---------------------------------------------------------------------------------
|0 | SELECTSTATEMENT||582 | 75660| 3708(6)|00:00:45|
|* 1 | HASH JOIN||582 | 75660| 3708(6)|00:00:45|
|* 2 |TABLE ACCESS FULL|SMALLTABLE |3 |195 |73(6)|00:00:01|
|* 3 |TABLE ACCESS FULL|BIGTABLE|173 | 11245| 3635(6)|00:00:44|
---------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
1- access("A"."ID"="B"."ID")
2- filter("B"."ID"=40000)
3- filter("A"."ID"=40000)
Note
-----
-dynamic sampling used for this statement
统计信息
----------------------------------------------------------
134 recursivecalls
2 db block gets
32073 consistentgets
8368 physical reads
1135576 redo size
585 bytes sent via SQL*Net toclient
385 bytes received via SQL*Net fromclient
2 SQL*Net roundtrips to/fromclient
2 sorts (memory)
0 sorts (disk)
1 rows processed
熟悉执行计划的就可以看出，这个sql执行是很慢的，2个表都做的是全表扫描，并且其物理读是8368，按照优化的经验，给2个表的id创建索引，减少查询时候的物理读,下面我们就看看通过优化器，oracle能我们什么样的建议呢？
第一步：创建优化任务
4.通过DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务。
SQL> DECLARE
2my_task_namevarchar2(30);
3my_sqltextclob;
4BEGIN
5my_sqltext:='select a.id,a.name,b.id,b.name from bigtablea,smalltable b where a.id=b.id and a.id=40000'; --需要优化的语句
6my_task_name:=DBMS_SQLTUNE.create_tuning_task(
7sql_text =>my_sqltext,
8user_name => 'ZY', --该语句通过哪个用户执行。注意是大写，不然会报错，用户无效
9scope => 'COMPREHENSIVE', --优化范围（limited或comprehensive）
10time_limit => 60, --优化过程的时间限制
11task_name => 'tuning_sql_test', --优化任务名称，自己拟定
12description => 'Task to tune a query on a specified table');--优化任务描述，自己拟定
13 END;
14 /
PL/SQL procedure successfullycompleted
第二步：执行优化任务
5.通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行前面创建好的优化任务，生成调优建议。
SQL> execDB【】MS_SQLTUNE.execute_tuning_task('tuning_sql_test');
PL/SQLprocedure successfully completed
第三步：检查优化任务的状态
6.通过user_advisor_tasks或者dba_advisor_tasks来查看调优任务执行的状况
SQL> selecttask_name,advisor_name,status
2from user_advisor_tasks;
TASK_NAMEADVISOR_NAMESTATUS
----------------------------------------------------------------------------------
tuning_sql_testSQL Tuning AdvisorCOMPLETED
如果status是EXECUTING，则表示任务正在执行，如果为COMPLETED，则任务已经执行完毕。
第四步：查看优化结果
7.通过调用dbms_sqltune.report_tuning_task查询调优的结果，不过在查询结果之前，得设置sqlplus的环境，如果不设置，则查询的结果出不来。
SQL> set long 999999
SQL> set LONGCHUNKSIZE 999999
SQL> set serveroutput on size 999999
SQL> set linesize 200
SQL> selectdbms_sqltune.report_tuning_task('tuning_sql_test') fromdual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-----------------------------------------------------------------------------------------
GENERAL INFORMATIONSECTION
-------------------------------------------------------------------------------
Tuning TaskName:tuning_sql_test
Tuning Task Owner:ZY
Scope :COMPREHENSIVE
TimeLimit(seconds):60
CompletionStatus : COMPLETED
Started at :09/12/2012 11:06:15
Completed at : 09/12/201211:06:48
Numberof Statistic Findings :2
Number ofIndex Findings : 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
SchemaName:ZY
SQLID:7arau1k5a3mv1
SQL Text:select a.id,a.name,b.id,b.name from bigtable a,smalltableb
where a.id=b.id anda.id=40000
-------------------------------------------------------------------------------
FINDINGS SECTION (3findings)
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
1- StatisticsFinding
---------------------
尚未分析表"ZY"."SMALLTABLRecommendation
--------------
- 考虑收集此表的优
executedbms_stats.gather_table_stats(ownname => 'ZY', tabname=>
'SMALLTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZEAUTO');
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Rationale
---------
为了选择好的执行计划, 优化程序需
2- StatisticsFinding
---------------------
尚未分析表"ZY"."BIGTABL
Recommendation
--------------
- 考虑收集此表的优
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
executedbms_stats.gather_table_stats(ownname => 'ZY', tabname=>
'BIGTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZEAUTO');
Rationale
---------
为了选择好的执行计划, 优化程序需
3- Index Finding (see explain plans sectionbelow)
--------------------------------------------------
通过创建一个或多个索引可以
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Recommendation(estimated benefit: 100%)
----------------------------------------
-考虑运行可以改进物理方案设计的Access Advi
create index ZY.IDX$$_30CA0001 onZY.BIGTABLE('ID');
-考虑运行可以改进物理方案设计的Access Advi
create index ZY.IDX$$_30CA0002 onZY.SMALLTABLE('ID');
Rationale
---------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的
可能比单个语句更可取。通过这种方法可以获得全面的索引建
-------------------------------------------------------------------------------
EXPLAIN PLANSSECTION
-------------------------------------------------------------------------------
1-Original
-----------
Plan hash value:1703851322
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------------------
|Id | Operation| Name| Rows | Bytes | Cost (%CPU)|Time|
---------------------------------------------------------------------------------
|0 | SELECTSTATEMENT||582 | 75660| 3708 (6)| 00:00:45|
|* 1 | HASH JOIN||582 | 75660| 3708(6)|00:00:45|
|* 2 |TABLE ACCESS FULL|SMALLTABLE |3 |195 |73(6)|00:00:01|
|* 3 |TABLE ACCESS FULL|BIGTABLE|173 | 11245| 3635(6)|00:00:44|
--------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
----------------------------------------
1- access("A"."ID"="B"."ID")
2- filter("B"."ID"=40000)
3- filter("A"."ID"=40000)
2-Using New Indices
--------------------
Planhash value: 57768072
------------------------------------------------------------------------------------------------
|Id | Operation| Name|Rows | Bytes | Cost (%CPU)|Time|
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
|0 | SELECTSTATEMENT||1 |130 | 3(0)| 00:00:01 |
|1 | TABLE ACCESS BY INDEXROWID | SMALLTABLE | 1 | 65 | 1(0)| 00:00:01 |
|2 |NESTEDLOOPS|| 1 |130 | 3(0)| 00:00:01 |
|3 |TABLE ACCESS BY INDEXROWID| BIGTABLE |1 | 65 |2 (0)| 00:00:01|
|* 4 |INDEX RANGE SCAN|IDX$$_30CA0001| 1 || 1 (0)| 00:00:01|
|* 5 |INDEX RANGESCAN|IDX$$_30CA0002| 1 || 0 (0)| 00:00:01|
------------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
4- access("A"."ID"=40000)
5- access("B"."ID"=40000)
-------------------------------------------------------------------------------
从上面的结果可以看到oracle的调优顾问给我们3条建议：
（1）SCOTT.SMALLTABLE表没有做分析，需要做一下表结构的分析，并且给出一个分析的建议，如下所示：
execute dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname =>
'SMALLTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
（2）SCOTT.BIGTABLE表没有做分析，需要做一下表结构的分析，并且给出一个分析的建议，如下所示：
execute dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname =>
'BIGTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
（3）oracle建议我们在表SCOTT.SMALLTABLE，SCOTT.BIGTABLE的id列创建一个bitree索引，给的建议如下：
create index SCOTT.IDX$$_00790002 onSCOTT.BIGTABLE('ID');
create index SCOTT.IDX$$_00790001 onSCOTT.SMALLTABLE('ID');
当然创建索引的名字可以改成别的名字。
最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。
通过以上查看oracle的调优顾问给的建议，基本和我们在前面给出的调优方案是一致，因此当我们给一个大的SQL做优化的时候，可以先使用oracle调优顾问，得到一些调优方案，然后根据实际情况做一些调整就可以。
五、删除优化任务
8.通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务
SQL>execdbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQLproceduresuccessfullycompleted.
