使用DBMS_SQLTUNE的Sql Tuning Advisor优化SQL

在10.2.0.1和之后的版本中Oracle支持用DBMS_SQLTUNE运行Sql Tuning Advisor 对SQL进行调优，下面就用一个实例介绍下这个功能：
   1.需要调优的SQL所在的相应的用户必须有Advisor权限(如果在OEM中使用SQL Tuning Advisor，需要要赋予select_catalog_role 角色)，本例使用的是Oracle自带的SH用户：
      SQL> grant advisor  to sh;
      Grant succeeded.
     
      SQL> grant SELECT_CATALOG_ROLE TO sh;
      Grant succeeded.
     
      SQL> grant execute  on dbms_sqltune to sh;
      Grant succeeded.
     
   2.创建调优任务:
      SQL> DECLARE
      2    my_task_name VARCHAR2(30);
      3    my_sqltext CLOB;
      4  BEGIN
      5    my_sqltext := 'SELECT * '   ||
      6                  'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';
      7 
      8    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
      9                             sql_text => my_sqltext,
      10                             user_name => 'SH',
      11                             scope => 'COMPREHENSIVE',---表示建议中有SQL Profiling 分析的内容
      12                             time_limit => 60,---任务运行的时间(s)
      13                             task_name => 'TEST_sql_tuning_task',
      14                             description => 'Task to tune a query on a specified PRODUCT');
      15  end;
      16  /

   3.查看SH用户下调优任务：
     SQL> select task_name from dba_advisor_log where owner='SH';
     TASK_NAME
     ------------------------------
     TEST_sql_tuning_task      
   
   4.执行调优任务：
     SQL> Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
     PL/SQL procedure successfully completed.
    
   5.查看调优任务的状态：
     SQL> select status from dba_advisor_log where task_name='TEST_sql_tuning_task';
     STATUS
     -----------
     COMPLETED
    
   6.通过调用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> select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;
    
     DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
     --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     GENERAL INFORMATION SECTION
     -------------------------------------------------------------------------------
     Tuning Task Name                  : TEST_sql_tuning_task
     Tuning Task Owner                 : SH
     Scope                             : COMPREHENSIVE
     Time Limit(seconds)               : 60
     Completion Status                 : COMPLETED
     Started at                        : 07/24/2013 10:41:00
     Completed at                      : 07/24/2013 10:41:01
     Number of Index Findings          : 1
    
    
     DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
     --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     -------------------------------------------------------------------------------
     Schema Name: SH
     SQL ID     : 9bxw71yp99fr6
     SQL Text   : SELECT * FROM sales WHERE prod_id = 10 AND cust_id = 100
    
     -------------------------------------------------------------------------------
     FINDINGS SECTION (1 finding)
     -------------------------------------------------------------------------------
    
     1- Index Finding (see explain plans section below)
     --------------------------------------------------
    
     DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
     --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       The execution plan of this statement can be improved by creating one or more
       indices.
    
       Recommendation (estimated benefit: 93.1%)
       -----------------------------------------
       - Consider running the Access Advisor to improve the physical schema design
         or creating the recommended index.
         create index SH.IDX$$_00490001 on SH.SALES("CUST_ID","PROD_ID");---建议对SALES表创建一个CUST_ID和PROD_ID的联合索引
    
       Rationale
       ---------
    
     DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
     --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         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.
    
     -------------------------------------------------------------------------------
     EXPLAIN PLANS SECTION
     -------------------------------------------------------------------------------
    
     1- Original
    
     DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
     --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     -----------
     Plan hash value: 811666046
    
     ---------------------------------------------------------------------------------------------------------------------
     | Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
     ---------------------------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT                   |                |     1 |    29 |    58   (0)| 00:00:01 |       |       |
     |   1 |  PARTITION RANGE ALL               |                |     1 |    29 |    58   (0)| 00:00:01 |     1 |    28 |
     |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |     1 |    29 |    58   (0)| 00:00:01 |     1 |    28 |
     |   3 |    BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
     |   4 |     BITMAP AND                     |                |       |       |            |          |       |       |
    
     DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
     --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     |*  5 |      BITMAP INDEX SINGLE VALUE     | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
     |*  6 |      BITMAP INDEX SINGLE VALUE     | SALES_PROD_BIX |       |       |            |          |     1 |    28 |
     ---------------------------------------------------------------------------------------------------------------------
    
     Predicate Information (identified by operation id):
     ---------------------------------------------------
    
        5 - access("CUST_ID"=100)
        6 - access("PROD_ID"=10)
    
     2- Using New Indices
    
     DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
     --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     --------------------
     Plan hash value: 2738539909
    
     ---------------------------------------------------------------------------------------------------------------------
     | Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
     ---------------------------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT                   |                |     1 |    29 |     4   (0)| 00:00:01 |       |       |
     |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES          |     1 |    29 |     4   (0)| 00:00:01 | ROWID | ROWID |
     |*  2 |   INDEX RANGE SCAN                 | IDX$$_00490001 |     1 |       |     3   (0)| 00:00:01 |       |       |
     ---------------------------------------------------------------------------------------------------------------------
    
    
     DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
     --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Predicate Information (identified by operation id):
     ---------------------------------------------------
    
        2 - access("CUST_ID"=100 AND "PROD_ID"=10)
    
     -------------------------------------------------------------------------------
    
    7.查看SALES表中的索引并建立联合索引：
      SQL> select INDEX_NAME,index_type from user_indexes where table_name='SALES';
      INDEX_NAME                     INDEX_TYPE
      ------------------------------ ---------------------------
      SALES_PROD_BIX                 BITMAP
      SALES_CUST_BIX                  BITMAP
      SALES_TIME_BIX                  BITMAP
      SALES_CHANNEL_BIX         BITMAP
      SALES_PROMO_BIX              BITMAP
     
      发现是为图索引，下面建立联合索引
     
      SQL> create index idx_s on SH.SALES("CUST_ID","PROD_ID");
     
      SQL> SELECT * FROM sales WHERE prod_id = 10 AND cust_id = 100;

      no rows selected
     
     
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3410910736
     
      ------------------------------------------------------------------------------------------------------------
      | Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      ------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                   |       |     1 |    29 |     4   (0)| 00:00:01 |       |       |
      |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES |     1 |    29 |     4   (0)| 00:00:01 | ROWID | ROWID |
      |*  2 |   INDEX RANGE SCAN                 | IDX_S |     1 |       |     3   (0)| 00:00:01 |       |       |
      ------------------------------------------------------------------------------------------------------------
     
      Predicate Information (identified by operation id):
      ---------------------------------------------------
     
         2 - access("CUST_ID"=100 AND "PROD_ID"=10)
        
         这里可以看到走的是联合索引也就是ADVISOR建议的。