分析oracle的執行計畫（explain plan）並對對sql進行最佳化實踐

標籤：

基於oracle的應用系統很多效能問題，是由應用系統sql效能低劣引起的，所以，sql的效能最佳化很重要，分析與最佳化sql的效能我們一般通過查看該sql的執行計畫，本文就如何看懂執行計畫，以及如何通過分析執行計畫對sql進行最佳化做相應說明。

一、什麼是執行計畫（explain plan）

執行計畫：一條查詢語句在oracle中的執行過程或訪問路徑的描述。

二、如何查看執行計畫

1.set autotrace on

2.explain plan for sql語句;

select plan_table_output from table(dbms_xplan.display());

3.通過第3方工具，如plsql developer（f5查看執行計畫）、toad等；

三、看懂執行計畫

1.執行計畫中欄位解釋

1. SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;  
2.  
3. 已選擇13行。  
4.  
5.    
6.  
7. 執行計畫  
8.  
9. ----------------------------------------------------------  
10.  
11. Plan hash value: 992080948  
12.  
13. ---------------------------------------------------------------------------------------  
14.  
15. | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
16.  
17. ---------------------------------------------------------------------------------------  
18.  
19. |   0 | SELECT STATEMENT             |        |    13 |   988 |     6  (17)| 00:00:01 |  
20.  
21. |   1 |  MERGE JOIN                  |        |    13 |   988 |     6  (17)| 00:00:01 |  
22.  
23. |   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |  
24.  
25. |   3 |    INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |  
26.  
27. |*  4 |   SORT JOIN                  |        |    13 |   494 |     4  (25)| 00:00:01 |  
28.  
29. |*  5 |    TABLE ACCESS FULL         | EMP    |    13 |   494 |     3   (0)| 00:00:01 |  
30.  
31. ---------------------------------------------------------------------------------------  
32.  
33.    
34.  
35. Predicate Information (identified by operation id):  
36.  
37. ---------------------------------------------------  
38.  
39.    4 - access("A"."EMPNO"="B"."MGR")  
40.  
41.        filter("A"."EMPNO"="B"."MGR")  
42.  
43.    5 - filter("B"."MGR" IS NOT NULL)  
44.  
45.    
46.  
47. 統計資訊  
48.  
49. ----------------------------------------------------------  
50.  
51.           0  recursive calls  
52.  
53.           0  db block gets  
54.  
55.          11  consistent gets  
56.  
57.           0  physical reads  
58.  
59.           0  redo size  
60.  
61.        2091  bytes sent via SQL*Net to client  
62.  
63.         416  bytes received via SQL*Net from client  
64.  
65.           2  SQL*Net roundtrips to/from client  
66.  
67.           1  sorts (memory)  
68.  
69.           0  sorts (disk)  
70.  
71.          13  rows processed  
72.  
73. SQL> 

對上面執行計畫欄欄位的解釋：

Id: 執行序列，但不是執行的先後順序。執行的先後根據Operation縮排來判斷（採用最右最上最先執行的原則看層次關係，在同一級如果某個動作沒有子ID就最先執行。 一般按縮排長度來判斷，縮排最大的最先執行，如果有2行縮排一樣，那麼就先執行上面的。）

如：上面執行計畫的執行順序為：3--》2--》5--》4--》1

Operation： 當前操作的內容。

Name：操作對象

Rows：也就是10g版本以前的Cardinality(基數)，Oracle估計當前操作的返回結果集行數。

Bytes：表示執行該步驟後返回的位元組數。

Cost（CPU）：表示執行到該步驟的一個執行成本，用於說明SQL執行的代價。

Time：Oracle 估計當前操作的時間。

 

2.謂詞說明：

Predicate Information (identified by operation id):

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

4 - access("A"."EMPNO"="B"."MGR")

filter("A"."EMPNO"="B"."MGR")

5 - filter("B"."MGR" IS NOT NULL)

 

Access: 表示這個謂詞條件的值將會影響資料的訪問路勁（全表掃描還是索引）。

Filter：表示謂詞條件的值不會影響資料的訪問路勁，只起過濾的作用。

在謂詞中主要注意access，要考慮謂詞的條件，使用的訪問路徑是否正確。

 

四、 動態分析

如果在執行計畫中有如下提示：

Note

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

-dynamic sampling used for the statement

 

這提示使用者CBO當前使用的技術，需要使用者在分析計劃時考慮到這些因素。 當出現這個提示，說明當前表使用了動態採樣。 我們從而推斷這個表可能沒有做過分析。

這裡會出現兩種情況：

（1） 如果表沒有做過分析，那麼CBO可以通過動態採樣的方式來擷取分析資料，也可以或者正確的執行計畫。

（2） 如果表分析過，但是分析資訊過舊，這時CBO就不會在使用動態採樣，而是使用這些舊的分析資料，從而可能導致錯誤的執行計畫。

 

五、表訪問方式

1.Full Table Scan (FTS) 全表掃描

2.Index Lookup 索引掃描

There are 5 methods of index lookup:

index unique scan --索引唯一掃描

Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.

 

index range scan --索引局部掃描

Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .

 

index full scan --索引全域掃描

Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.

 

index fast full scan --索引快速全域掃描，不帶order by情況下常發生

Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.

 

index skip scan --索引跳躍掃描，where條件列是非索引的前置列情況下常發生

Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.

 

3.Rowid 物理ID掃描

This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid掃描是最快的訪問資料方式

 

六、表串連方式

請參照另一篇文章：Oracle 表串連方式詳解

http://www.fengfly.com/plus/view-210420-1.html

 

七、運算子

1.sort --排序，很消耗資源

There are a number of different operations that promote sorts:

(1)order by clauses (2)group by (3)sort merge join –-這三個會產生排序運算

 

2.filter --過濾，如not in、min函數等容易產生

Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans.

 

3.view --視圖，大都由內聯視圖產生(可能深入到視圖基表)

When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the ‘view‘ will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.

 

4.partition view --分區視圖

Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems.

 

附：oracle最佳化器（Optimizer）

 

Oracle 資料庫中最佳化器（Optimizer）是SQL分析和執行的最佳化工具，它負責指定SQL的執行計畫，也就是它負責保證SQL執行的效率最高，比如最佳化器決定Oracle 以什麼樣的方式來訪問資料，是全表掃描（Full Table Scan），索引範圍掃描（Index Range Scan）還是全索引快速掃描（INDEX Fast Full Scan：INDEX_FFS）;對於表關聯查詢，它負責確定表之間以一種什麼方式來關聯，比如HASH_JOHN還是NESTED LOOPS 或者MERGE JOIN。 這些因素直接決定SQL的執行效率，所以最佳化器是SQL 執行的核心，它做出的執行計畫好壞，直接決定著SQL的執行效率。

Oracle 的最佳化器有兩種：

RBO(Rule-Based Optimization): 基於規則的最佳化器

CBO(Cost-Based Optimization): 基於代價的最佳化器

從Oracle 10g開始，RBO 已經被棄用，但是我們依然可以通過Hint 方式來使用它。

 

在Oracle 10g中，CBO 可選的運行模式有2種：

（1） FIRST_ROWS(n)

Oracle 在執行SQL時，優先考慮將結果集中的前n條記錄以最快的速度反饋回來，而其他的結果並不需要同時返回。

（2） ALL_ROWS -- 10g中的預設值

Oracle 會用最快的速度將SQL執行完畢，將結果集全部返回，它和FIRST_ROWS（n）的區別在於，ALL_ROWS強調以最快的速度將SQL執行完畢，並將所有的結果集反饋回來，而FIRST_ROWS（n）則側重於返回前n條記錄的執行時間。

修改CBO 模式的三種方法：

（1） SQL 陳述式：

Sessions層級：

SQL> alter session set optimizer_mode=all_rows;

（2） 修改pfile 參數：

OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

（3） 語句層級用Hint（/* + ... */）來設定

Select /*+ first_rows(10) */ name from table;

Select /*+ all_rows */ name from table;

分析oracle的執行計畫（explain plan）並對對sql進行最佳化實踐