Oracle Execute Plan原理分析與執行個體分享之二

一、說明

   在上一章中講了一些執行個體的分享，這一章我們将更加深入的了解執行計劃。

二、執行計劃的概念

   所謂執行計劃，顧名思義，就是對一個查詢任務，做出一份怎樣去完成任務的詳細方案。舉個生活中的例子，我從珠海要去英國，我可以選擇先去香港然後轉機，也可以先去北京轉機，或者去廣州也可以。但是到底怎樣去英國劃算，也就是我的費用最少，這是一件值得考究的事情。同樣對于查詢而言，我們送出的SQL僅僅是描述出了我們的目的地是英國，但至于怎麼去，通常我們的SQL中是沒有給出提示資訊的，是由資料庫來決定的。

-----先看一個執行計劃的對比，建立一張表jack-------------
 1 SQL> create table jack as select * from dba_objects;
 2 
 3 Table created.
 4 
 -----給表增加一個唯一限制-----------------------
 5 SQL> alter table jack add constraint jack_pk primary key(object_id);
 6 
 7 Table altered.
 8 
 -----分析一下表及索引--------------
 9 SQL> exec dbms_stats.gather_table_stats(user,'JACK',cascade=>true);
10 
11 PL/SQL procedure successfully completed.
12 
13 SQL> set autotrace traceonly;
14 SQL> select /*+ full(jack) */ count(*) from jack;
15 
16 
17 Execution Plan
18 ----------------------------------------------------------
19 Plan hash value: 1205023501
20 
21 -------------------------------------------------------------------
22 | Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |
23 -------------------------------------------------------------------
24 |   0 | SELECT STATEMENT   |      |    1 |   288   (1)| 00:00:04 |
25 |   1 |  SORT AGGREGATE    |      |    1 |           |      |
26 |   2 |   TABLE ACCESS FULL| JACK | 72229 |   288   (1)| 00:00:04 |
27 -------------------------------------------------------------------
28 
29 
30 Statistics
31 ----------------------------------------------------------
32       1   recursive calls
33       0   db block gets
34     1034  consistent gets
35       0   physical reads
36 
37 SQL> select /*+ index(jack,jack_pk) */ count(*) from jack;
38 
39 
40 Execution Plan
41 ----------------------------------------------------------
42 Plan hash value: 3245199814
43 
44 --------------------------------------------------------------------
45 | Id  | Operation     | Name    | Rows  | Cost (%CPU)| Time       |
46 --------------------------------------------------------------------
47 |   0 | SELECT STATEMENT |       |     1 |   151   (0)| 00:00:02 |
48 |   1 |  SORT AGGREGATE  |       |     1 |        |       |
49 |   2 |   INDEX FULL SCAN| JACK_PK | 72229 |   151   (0)| 00:00:02 |
50 --------------------------------------------------------------------
51 
52 
53 Statistics
54 ----------------------------------------------------------
55       1  recursive calls
56       0  db block gets
57     151  consistent gets
58       0  physical reads      

      這兩個執行計劃中，第一個表示求和是通過進行全表掃描來做的，把整個表中資料讀入記憶體來逐條累加；第二個表示根據表中索引，把整個索引讀進記憶體來逐條累加，而不用去讀表中的資料。但是這兩種方式到底哪種快呢？通常來說可能二比一塊，但也不是絕對的。 《全表掃描優于索引通路示例》

     上面代碼中的例子是一個很簡單的示範執行計劃差異的例子。對于複雜的SQL(表連接配接、嵌套子查詢等)，執行計劃可能幾十種甚至上百種，但是到底哪種最好呢？

我們事前并不知道，資料庫本身也不知道，但是資料庫會根據一定的規則或者統計資訊(statistics)去選擇一個執行計劃，通常來說選擇的是比較優的，但也有選擇失誤的時候，這就是這次讨論的價值所在。

 三、Oracle優化器模式

Oracle優化器有兩大類，基于規則的和基于代價的，在SQLPLUS中我們可以檢視pfile檔案或者spfile檔案中定義的預設的優化器模式。

1 SQL> show parameter optimizer_mode
2 
3 NAME                     TYPE     VALUE
4 ------------------------------------ ----------- ------------------------------
5 optimizer_mode                 string     ALL_ROWS      

      這是Oracle11g R2企業版，我們可以看出，預設安裝後資料庫優化模式為ALL_ROWS，我們還可以為FIRST_ROWS，而RBO已經在Oracle10g的時候棄用，但是可以用hints來使用。設定優化器模式可以在pfile檔案或spfile檔案中對整個instance的所有會話設定，也可以單獨對某個會話設定：

1 SQL> alter system set optimizer_mode=first_rows;
2 
3 System altered.
4 
5 SQL> alter session set optimizer_mode=first_rows;
6 
7 Session altered.      

      基于規則的查詢，資料庫根據表和索引等定義資訊，按照一定的規則來産生執行計劃；基于代價的查詢，資料庫根據搜集的表和索引的資料的統計資訊(通過analyze指令或者使用dbms_stats包來搜集，下面有一個分析的例子。)綜合來決定選取一個資料庫認為最優的執行計劃(實際上不一定最優)。

----analyze語句是隻分析jack表---------
1 SQL> analyze table jack compute statistics;
2 
3 Table analyzed.
4 
----下面的語句中加了cascade表示分析了表jack以及連帶其它對象(index)-----------
5 SQL> exec dbms_stats.gather_table_stats(user,'JACK',cascade=>true);
6 
7 PL/SQL procedure successfully completed.      

      RULE是基于規則的，CHOOSE表示如果查詢的表存在搜集的統計資訊則基于代價來執行(在CHOOSE模式下Oracle采用的是FIRST_ROWS),否則基于規則來執行。在基于代價的兩種方式中，FIRST_ROWS指執行計劃采用最少資源盡快的傳回部分結果給用戶端，對于排序分頁顯示這種查詢尤其适用；ALL_ROWS指以總體消耗資源最好的方式傳回結果給用戶端。

     基于規則的模式下，資料庫的執行計劃通常比較穩定。但在基于代價的模式下，我們才有更大的機會選擇最優的執行計劃。也由于Oracle的很多查詢方面的特性必須在基于代價的模式下才能展現出來，是以我們通常不選擇RULE(并且Oracle宣稱從Oracle10i版本資料庫開始将不再支援RULE)。既然是基于代價的模式，也就是說執行計劃的選擇是根據表、索引等定義和資料的統計資訊來決定的，這個統計資訊是根據analyze指令或者dbms_stats包來定期搜集的。首先存在着一種可能，就是由于搜集資訊是一個很消耗資源和時間的動作，尤其當表資料量很大的時候，因為搜集資訊是對整個表資料進行重新的完全統計，是以這是我們必須慎重考慮得問題。我們隻能在伺服器空閑的時候定期的進行資訊搜集。這說明我們在一段時期内，統計資訊可能和資料庫本身的資料并不吻合；另外就是Oracle的統計資料本身也存在着不精确部分(詳細參考Oracle DOCUMENT),更重要的一個問題就是及時統計資料相對比較準确，但是Oracle的優化器的選擇也并不是始終是最優的方案。這也倚賴于Oracle對不同執行計劃的代價的計算規則(我們通常是無法知道具體的計算規則的)。這好比我們決定從香港還是從北京去英國，車票、機票等實際價格到底是怎麼核算出來的我們并不知道，或者說我們現在了解的價格資訊，在我們乘車前往的時候，真實價格跟我們的預算已經發生了變化。所有的因素，都将影響我們的整個開銷。

     執行計劃穩定性帶給我們什麼呢？

     Oracle存在着執行計劃失誤的可能。這也是我們經常遇見的一些現象，比如總有人說我的程式在測試資料庫中跑的很好，但在産品資料庫上就跑的很差，甚至後者硬體條件比前者還好，這到底是為什麼？硬體資源、統計資訊、參數設定都可能對執行計劃産生影響。由于因素太多，我們總是對未來懷着一種莫名的恐懼，我的産品資料庫上線後到底跑的好不好？于是Oracle提供了一種穩定執行計劃的能力，也就是把在測試環境中的運作良好的執行計劃所産生的OUTLINES移植到産品資料庫，使得執行計劃不會随着其他因素的變化而變化。

     那麼OUTLINES是什麼呢？先要介紹一個内容，Oracle提供了在SQL中使用HINTS來引導優化器産生我們想要的執行計劃的能力。這在多表連接配接、複雜查詢中特别有效。HINTS的類型很多，可以設定優化器目标(RULE、CHOOSE、FIRST_ROWS、ALL_ROWS)，可以指定表連接配接的順序，可以指定使用哪個表的哪個索引等等，可以對SQL進行很多精細的控制。通過這種方式産生我們想要的執行計劃的這些HINTS，Oracle可以存儲這些HINTS，我們稱之為OUTLINES。通過STORE OUTLINES可以使得我們擁有以後産生相同執行計劃的能力，也就是使我們擁有了穩定執行計劃的能力。

     這裡想給出一個附加的說明就是，實際上，我們通過工具改寫SQL，比如使用SQL EXPERT改寫後的SQL，這些不僅僅是加了HINTS而且文本都已經發生了變化的SQL，也可以存儲OUTLINES，并可被應用到應用中。但這不是一定生效，我們必須測試檢查是否生效。但由于就算給了錯誤的OUTLINES，資料庫在執行的時候，也隻是忽略過去重新生成執行計劃而不會傳回錯誤，是以我們才敢放心的這麼使用。當然在Oracle文檔中并沒有指明可以這樣做，文檔中隻是說明，如果存在OUTLINES的同時又在SQL中加了HINTS，則會使用OUTLINES而忽略HINTS。這個功能在LECCO将釋出的産品中會使用這一功能，這樣可以将SQL EXPERT的改寫SQL的能力和穩定執行計劃的能力結合起來，那麼我們就對不能更改源代碼的應用具有了相當強大的SQL優化能力。

     也許我們會有疑問，假如穩定了執行計劃，那還搜集統計資訊幹嘛？這是因為幾個原因造成的，首先，現在的執行計劃對于未來發生了變化的資料未必就是合适的，存在着目前的執行計劃不滿足未來資料的變化後的效率，而新的統計資訊的情況下産生的執行計劃也并不是全部都合理的。那這個時候，我們可以采用新搜集的統計資訊，但是卻對新統計資訊下不良的執行計劃采用Oracle提供的執行計劃穩定性這個能力固定執行計劃，這樣結合起來我們可以建立滿意的高效的資料庫運作環境。

     我們還需要關注一個東西，Oracle提供的dbms_stats包除了具有搜集統計資訊的能力，還具有把資料庫中統計資訊(statistics)export/import的能力，還具有隻搜集統計資訊而使得統計資訊不應用于資料庫的能力(把統計資訊搜集到一個特定的表中而不是立即生效)，在這個基礎上我們就可以把統計資訊export出來再import到一個測試環境中，再運作我們的應用，在測試環境中我們觀察最新的統計資訊會導緻哪些執行計劃發生變化(DB EXPERT的Plan Version Tracer是模拟不同環境并自動檢查不同環境中執行計劃變化的工具)，是變好了還是變差了，我們可以把變差的這一部分在測試環境中使用hints或者利用工具(SQL EXPERT是在重寫SQL這一領域目前最強有力的工具)産生良好的執行計劃的SQL，利用這些SQL可以産生出OUTLINES，然後在産品資料庫應用最新的統計資訊的同時移植進這些OUTLINES。

     最後說一下我們不得不使用執行計劃穩定性能力的場合。我們假定Oracle的優化器的選擇都是準确的，但是優化器選擇的基礎就是我們的SQL，這些SQL才從根本上決定了運作效率，這是更重要的一個優化的環節。SQL是基礎(當然資料庫的設計是基礎的基礎)，一個SQL寫的好不好，就相當于我們同樣的要想去英國，但是我的起點在珠海，你的起點卻在西藏的最邊緣偏僻的一個地方，那不管你做怎樣的最優線路選擇，你都不如我在珠海去英國所花費的代價小。

 四、執行計劃的生成

4.1、Explain plan

1 SQL> explain plan for select * from jack;
 2 
 3 Explained.
 4 
 5 SQL> select * from table(dbms_xplan.display());
 6 
 7 PLAN_TABLE_OUTPUT
 8 --------------------------------------------------------------------------------
 9 Plan hash value: 949574992
10 
11 --------------------------------------------------------------------------
12 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
13 --------------------------------------------------------------------------
14 |   0 | SELECT STATEMENT  |     | 72229 |  6842K|   288   (1)| 00:00:04 |
15 |   1 |  TABLE ACCESS FULL| JACK | 72229 |  6842K|   288   (1)| 00:00:04 |
16 --------------------------------------------------------------------------
17 
18 8 rows selected.      

4.2、Autotrace

1 SQL> set timing on        -------------記錄所用時間
2 SQL> set autotrace on/traceonly  ------自動記錄執行計劃      

4.3、SQL_TRACE

     "SQL TRACE"是Oracle提供的用于進行SQL跟蹤的手段，是強有力的輔助診斷工具。在日常的資料庫問題診斷和解決中，"SQL TRACE"是非常常用的方法。

一般，一次跟蹤可以分為以下幾大步：

    1、界定需要跟蹤的目标範圍，并使用适當的指令啟用所需跟蹤。

    2、經過一段時間後，停止跟蹤。此時應該産生了一個跟蹤結果檔案。

    3、找到跟蹤檔案，并對其進行格式化，然後閱讀或分析。

五、檢視執行計劃

在9i及以前版本，Oracle隻能看到DML的執行計劃，從Oracle10g開始，可以通過EXPLAIN PLAN FOR檢視DDL語句的執行計劃了。對于研究CREATE TABLE AS SELECT、CREATE MATERIALIZED VIEW AS SELECT以及CREATE INDEX ALTER INDEX REBUILD等語句有很大的幫助。

舉個簡單的例子，Oracle的文檔上對于索引的建立有如下描述：

The optimizer can use an existing index to build another index.This results in a much faster index build.如果看不到DDL的執行計劃，隻能根據執行時間的長短去猜測Oracle的具體執行計劃，但是這種方法沒有足夠的說服力。但是通過DDL的執行計劃，就使得結果一目了然了。

1 SQL> create table jack as select * from dba_objects;
 2 
 3 Table created.
 4 
 5 SQL> explain plan for 
 6   2   create index jack_ind on jack(object_id);
 7 
 8 Explained.
 9 
10 SQL> select * from table(dbms_xplan.display);
11 
12 PLAN_TABLE_OUTPUT
13 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
14 Plan hash value: 776580901
15 
16 -----------------------------------------------------------------------------------
17 | Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
18 -----------------------------------------------------------------------------------
19 |   0 | CREATE INDEX STATEMENT |      | 86173 |  1093K|   342   (1)| 00:00:05 |
20 |   1 |  INDEX BUILD NON UNIQUE| JACK_IND |      |      |           |      |
21 |   2 |   SORT CREATE INDEX    |      | 86173 |  1093K|           |      |
22 |   3 |    TABLE ACCESS FULL   | JACK      | 86173 |  1093K|   288   (1)| 00:00:04 |
23 -----------------------------------------------------------------------------------
24 
25 
26 PLAN_TABLE_OUTPUT
27 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
28 Note
29 -----
30    - estimated index size: 3145K bytes
31 
32 14 rows selected.      

參考資料：http://yanguz123.iteye.com/blog/1542379

原文連結：http://www.cnblogs.com/Richardzhu/articles/2831868.html