SQL Tuning Advisor使用实例

kinght2008

　　在oracle10g之前，想要优化一个sql语句是比较麻烦，但是在oracle10g这个版本推出的SQL Tuning Advisor这个工具，能大大减少sql调优的工作量，不过要想使用SQL Tuning Advisor，一定要保证你的优化器是CBO模式。
　　1.首先需要创建一个用于调优的用户bamboo,并授予advisor给创建的用户

　　SQL> create user bamboo>　　User created.
　　SQL> grant connect,resource to bamboo;
　　Grant succeeded.
　　SQL> grant advisor to bamboo;
　　Grant succeeded.
　　2.创建用户做测试的2张表,大表里面插入500万条数据，小表里面插入10万条数据，其创建方法如下
　　SQL> create table bigtable (id number(10),name varchar2(100));
　　Table created.
　　SQL> begin
　　2 for i in 1..5000000 loop
　　3 insert into bigtable values(i,'test'||i);
　　4 end 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..100000 loop
　　3 insert into smalltable values(i,'test'||i);
　　4 end loop;
　　5 end;
　　6 /
　　PL/SQL procedure successfully completed.
　　SQL> commti;
　　3.然后对bigtable和smalltable做一个等连接查询，然后跟踪其执行计划
　　SQL> select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000;

　　>　　---------- ---------------------------------------- ---------- ----------------------------------------
　　40000 test40000                   40000 test40000
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 1703851322
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　|  0 | SELECT STATEMENT  |      |  839 |  106K| 3656  (5)| 00:00:44 |
　　|* 1 | HASH JOIN     |      |  839 |  106K| 3656  (5)| 00:00:44 |
　　|* 2 |  TABLE ACCESS FULL| SMALLTABLE |   5 |  325 |  71  (3)| 00:00:01 |
　　|* 3 |  TABLE ACCESS FULL| BIGTABLE  |  173 | 11245 | 3584  (5)| 00:00:44 |
　　---------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - access("A"."ID"="B"."ID")
　　2 - filter("B"."ID"=40000)
　　3 - filter("A"."ID"=40000)
　　Note
　　-----
　　- dynamic sampling used for this statement
　　Statistics
　　----------------------------------------------------------
　　9 recursive calls
　　0 db block gets
　　16151 consistent gets
　　11469 physical reads

　　0 redo>　　588 bytes sent via SQL*Net to client
　　385 bytes received via SQL*Net from client
　　2 SQL*Net roundtrips to/from client
　　2 sorts (memory)
　　0 sorts (disk)
　　1 rows processed
　　熟悉执行计划的就可以看出，这个sql执行是很慢的，2个表都做的是全表扫描，并且其物理读是11469，按照优化的经验，给2个表的id创建索引，减少查询时候的物理读,下面我们就看看通过优化器，oracle能我们什么样的建议呢？
　　4.下面就通过DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务，然后通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行调优任务，生成调优建议
　　SQL> DECLARE
　　2  my_task_name VARCHAR2(30);
　　3  my_sqltext CLOB;
　　4 BEGIN
　　5  my_sqltext := 'select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000';
　　6
　　7  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
　　8              sql_text => my_sqltext,
　　9              user_name => 'SCOTT',
　　10               scope => 'COMPREHENSIVE',
　　11               time_limit => 60,
　　12               task_name => 'test_sql_tuning_task1',
　　13               description => 'Task to tune a query');
　　14   DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task1');
　　15 END;
　　16 /
　　5.执行的过程中，也可以通过user_advisor_tasks或者dba_advisor_tasks来查看调优任务执行的状况
　　SQL> select task_name,ADVISOR_NAME,STATUS from user_advisor_tasks;
　　TASK_NAME           ADVISOR_NAME               STATUS
　　------------------------------ ---------------------------------------- ---------------------------------
　　test_sql_tuning_task1     SQL Tuning Advisor            COMPLETED
　　如果status是EXECUTING，则表示任务正在执行，如果为COMPLETED，则任务已经执行完毕
　　6.通过调用dbms_sqltune.report_tuning_task可以查询调优的结果，不过在查询结果之前，得设置sqlplus的环境，如果不设置，则查询的结果出不来
　　SQL> set long 999999
　　SQL> set LONGCHUNKSIZE 999999

　　SQL> set serveroutput on>　　SQL> set linesize 200
　　SQL> select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;
　　SQL> select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　---------------------------------------------------------------------------------------------------------------------------------
　　GENERAL INFORMATION SECTION
　　-------------------------------------------------------------------------------
　　Tuning Task Name         : test_sql_tuning_task1
　　Tuning Task Owner         : BAMBOO
　　Scope               : COMPREHENSIVE
　　Time Limit(seconds)        : 60
　　Completion Status         : COMPLETED
　　Started at            : 10/13/2011 05:07:53
　　Completed at           : 10/13/2011 05:08:18
　　Number of Statistic Findings   : 2
　　Number of Index Findings     : 1
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　----------------------------------------------------------------------------------------------------------------------------------
　　Schema Name: SCOTT

　　SQL>　　SQL Text  : select a.id,a.name,b.id,b.name from bigtable a,smalltable b
　　where a.id=b.id and a.id=40000
　　-------------------------------------------------------------------------------
　　FINDINGS SECTION (3 findings)
　　-------------------------------------------------------------------------------
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　----------------------------------------------------------------------------------------------------------------------------------
　　1- Statistics Finding
　　---------------------
　　Table "SCOTT"."SMALLTABLE" was not analyzed.
　　Recommendation
　　--------------
　　- Consider collecting optimizer statistics for this table.
　　execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
　　'SMALLTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

　　method_opt => 'FOR ALL COLUMNS>　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　----------------------------------------------------------------------------------------------------------------------------------
　　Rationale
　　---------
　　The optimizer requires up-to-date statistics for the table in order to
　　select a good execution plan.
　　2- Statistics Finding
　　---------------------
　　Table "SCOTT"."BIGTABLE" was not analyzed.
　　Recommendation
　　--------------
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　----------------------------------------------------------------------------------------------------------------------------------
　　- Consider collecting optimizer statistics for this table.
　　execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
　　'BIGTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

　　method_opt => 'FOR ALL COLUMNS>　　Rationale
　　---------
　　The optimizer requires up-to-date statistics for the table in order to
　　select a good execution plan.
　　3- Index Finding (see explain plans section below)
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　---------------------------------------------------------------------------------------------------------------------------------
　　The execution plan of this statement can be improved by creating one or more
　　indices.
　　Recommendation (estimated benefit: 100%)
　　----------------------------------------
　　- Consider running the Access Advisor to improve the physical schema design
　　or creating the recommended index.
　　create index SCOTT.IDX$$_00790001 on SCOTT.SMALLTABLE('ID');
　　- Consider running the Access Advisor to improve the physical schema design
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　----------------------------------------------------------------------------------------------------------------------------------
　　or creating the recommended index.
　　create index SCOTT.IDX$$_00790002 on SCOTT.BIGTABLE('ID');
　　Rationale
　　---------
　　Creating the recommended indices significantly improves the execution plan
　　of this statement. However, it might be preferable to run "Access Advisor"
　　using a representative SQL workload as opposed to a single statement. This
　　will allow to get comprehensive index recommendations which takes into
　　account index maintenance overhead and additional space consumption.
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　----------------------------------------------------------------------------------------------------------------------------------
　　-------------------------------------------------------------------------------
　　EXPLAIN PLANS SECTION
　　-------------------------------------------------------------------------------
　　1- Original
　　-----------
　　Plan hash value: 1703851322
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　----------------------------------------------------------------------------------------------------------------------------------
　　|  0 | SELECT STATEMENT  |      |  839 |  106K| 3656  (5)| 00:00:44 |
　　|* 1 | HASH JOIN     |      |  839 |  106K| 3656  (5)| 00:00:44 |
　　|* 2 |  TABLE ACCESS FULL| SMALLTABLE |   5 |  325 |  71  (3)| 00:00:01 |
　　|* 3 |  TABLE ACCESS FULL| BIGTABLE  |  173 | 11245 | 3584  (5)| 00:00:44 |
　　---------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - access("A"."ID"="B"."ID")
　　2 - filter("B"."ID"=40000)
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　---------------------------------------------------------------------------------------------------------------------------------
　　3 - filter("A"."ID"=40000)
　　2- Using New Indices
　　--------------------
　　Plan hash value: 3720188830
　　------------------------------------------------------------------------------------------------

　　|>　　------------------------------------------------------------------------------------------------
　　|  0 | SELECT STATEMENT       |        |   1 |  130 |   5  (0)| 00:00:01 |
　　|  1 | TABLE ACCESS BY INDEX ROWID | BIGTABLE    |   1 |  65 |   3  (0)| 00:00:01 |
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
　　---------------------------------------------------------------------------------------------------------------------------------
　　|  2 |  NESTED LOOPS        |        |   1 |  130 |   5  (0)| 00:00:01 |
　　|  3 |  TABLE ACCESS BY INDEX ROWID| SMALLTABLE   |   1 |  65 |   2  (0)| 00:00:01 |

　　|* 4 |   INDEX RANGE SCAN     |>
　　|* 5 |  INDEX RANGE SCAN      |>　　------------------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　4 - access("B"."ID"=40000)
　　5 - access("A"."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>　　（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>　　（3）oracle建议我们在表SCOTT.SMALLTABLE，SCOTT.BIGTABLE的id列创建一个bitree索引，给的建议如下
　　create index SCOTT.IDX$$_00790002 on SCOTT.BIGTABLE('ID');
　　create index SCOTT.IDX$$_00790001 on SCOTT.SMALLTABLE('ID');
　　当然创建索引的名字可以改成别的名字
　　通过以上查看oracle的调优顾问给的建议，基本和我们在前面给出的调优方案是一致，因此当我们给一个大的ＳＱＬ做优化的时候，可以先使用oracle调优顾问，得到一些调优方案，然后根据实际情况做一些调整就可以。
　　以下就是执行oracle调优顾问的建议，重新执行select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000这天语句得到的执行计划，可以看出查询时间和物理读大大减少
　　SQL> select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000;

　　>　　---------- ---------------------------------------- ---------- ----------------------------------------
　　40000 test40000                   40000 test40000
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 777647921
　　-------------------------------------------------------------------------------------------------

　　|>　　-------------------------------------------------------------------------------------------------
　　|  0 | SELECT STATEMENT       |         |   1 |  31 |   5  (0)| 00:00:01 |
　　|  1 | TABLE ACCESS BY INDEX ROWID | BIGTABLE    |   1 |  17 |   3  (0)| 00:00:01 |
　　|  2 |  NESTED LOOPS        |         |   1 |  31 |   5  (0)| 00:00:01 |
　　|  3 |  TABLE ACCESS BY INDEX ROWID| SMALLTABLE   |   1 |  14 |   2  (0)| 00:00:01 |
　　|* 4 |   INDEX RANGE SCAN     | I_ID_SAMLLTABLE |   1 |    |   1  (0)| 00:00:01 |
　　|* 5 |  INDEX RANGE SCAN      | I_ID_BIGTABLE  |   1 |    |   2  (0)| 00:00:01 |
　　-------------------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　4 - access("B"."ID"=40000)
　　5 - access("A"."ID"=40000)
　　Statistics
　　----------------------------------------------------------
　　0 recursive calls
　　0 db block gets
　　9 consistent gets
　　0 physical reads

　　0 redo>　　588 bytes sent via SQL*Net to client
　　385 bytes received via SQL*Net from client
　　2 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　1 rows processed
　　oracle视频教程请关注:http://down.51cto.com/4202939/up