使用dbms_sqltune进行SQL优化

随着Oracle版本的进化，越来越多的自动化工具简化了数据库运维，优化时的工作，如SQL Access Advisor ,SQL Tuning Advisor等。
这里简略记录一下使用DBMS_SQLTUNE package调优一个简单的SQL的几个步骤。
准备工作：
create table test_sqltune as select rownum id, trunc(dbms_random.value*1000000) amt from dual
connect by rownum <= 100000;

有如下SQL速度较慢：
select * from test_sqltune where amt = 4;

1. 首先创建一个SQL Tuning Task

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext := 'select * from test_sqltune where amt = 4';


  my_task_name := dbms_sqltune.create_tuning_task(sql_text    => my_sqltext,
                                                  user_name   => 'SCOTT',
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 60,
                                                  task_name   => 'TEST_sql_tuning_task',
                                                  description => 'Test SQL Tuning Task');
END;

2.  执行SQL Tuning Task
   begin
      dbms_sqltune.execute_tuning_task(task_name => 'TEST_sql_tuning_task');
    end;

3. 获取SQL Tuning Advisor对于这个SQL给出的建议
   select dbms_sqltune.report_tuning_task(task_name => 'TEST_sql_tuning_task') from dual
   
   结果如下：
    GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TEST_sql_tuning_task1
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/24/2016 10:36:09
Completed at       : 05/24/2016 10:36:10


-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 4j2paxndm1dqd
SQL Text   : select * from test_sqltune where amt = 4


-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------


1- Statistics Finding
---------------------
  尚未分析表 "SCOTT"."TEST_SQLTUNE"。


  Recommendation
  --------------
  - 考虑收集此表的优化程序统计信息。
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'TEST_SQLTUNE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');


  Rationale
  ---------
    为了选择好的执行计划, 优化程序需要此表的最新统计信息。


2- Index Finding (see explain plans section below)
--------------------------------------------------
  通过创建一个或多个索引可以改进此语句的执行计划。


  Recommendation (estimated benefit: 96.75%)
  ------------------------------------------
  - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
    create index SCOTT.IDX$$_01750001 on SCOTT.TEST_SQLTUNE("AMT","ID");


  Rationale
  ---------
    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
    可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。


-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


1- Original
-----------
Plan hash value: 2201427922


----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     2 |    52 |    62   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SQLTUNE |     2 |    52 |    62   (2)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("AMT"=4)


2- Using New Indices
--------------------
Plan hash value: 3991186737


-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |    26 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX$$_01750001 |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("AMT"=4)


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

给出了两个建议，收集表的统计信息，创建amt的索引，并给出了预期的改进幅度

   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-2105843/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8520577/viewspace-2105843/