IT工程師數位筆記本

基于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） 說明

使用 EXPLAIN PLAN 獲取SQL語句執行計劃

Oracle 執行計劃（Explain Plan） 說明

Sql優化系列之(1)__where子句條件優化

TABLE ACCESS FULL  全表掃描

PARTITION RANGE ITERATOR 分區迭代掃描

partition range single單個分區掃描