使用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/，如需轉載，請註明出處，否則將追究法律責任。