如果要分析某條 SQL 的性能問題，通常我們要先看 SQL 的執(zhí)行計劃，看看 SQL 的每一步執(zhí)行是否存在問題。 如果一條 SQL 平時執(zhí)行的好好的，卻有一天突然性能很差，如果排除了系統(tǒng)資源和阻塞的原因，那么基本可以斷定是執(zhí)行計劃出了問題。

看懂執(zhí)行計劃也就成了 SQL 優(yōu)化的先決條件。 這里的 SQL 優(yōu)化指的是 SQL 性能問題的定位，定位后就可以解決問題。

一． 查看執(zhí)行計劃的三種方法

1.1 設(shè)置 autotrace

SQL> set autotrace on

SQL> select * from dave;

ID NAME

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

8 安慶

1 dave

2 bl

1 bl

2 dave

3 dba

4 sf-express

5 dmm

已選擇 8 行。

執(zhí)行計劃

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

Plan hash value: 3458767806

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |

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

統(tǒng)計信息

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

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

609 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8 rows processed

SQL>

1.2 使用 SQL

SQL>EXPLAIN PLAN FOR sql 語句 ;

SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

示例：

SQL> EXPLAIN PLAN FOR SELECT * FROM DAVE;

已解釋。

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

或者：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3458767806

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |

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

已選擇 8 行。

執(zhí)行計劃

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

Plan hash value: 2137789089

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |

| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |

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

統(tǒng)計信息

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

25 recursive calls

12 db block gets

168 consistent gets

0 physical reads

0 redo size

974 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

8 rows processed

SQL>

1.3 使用 Toad,PL/SQL Developer 工具

二． Cardinality （基數(shù)） / rows

Cardinality 值表示 CBO 預(yù)期從一個行源（ row source ）返回的記錄數(shù)，這個行源可能是一個表，一個索引，也可能是一個子查詢。 在 Oracle 9i 中的執(zhí)行計劃中， Cardinality 縮寫成 Card 。 在 10g 中， Card 值被 rows 替換。

這是 9i 的一個執(zhí)行計劃，我們可以看到關(guān)鍵字 Card ：

執(zhí)行計劃

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=402)

1 0 TABLE ACCESS (FULL) OF 'TBILLLOG8' (Cost=2 Card=1 Bytes=402)

Oracle 10g 的執(zhí)行計劃，關(guān)鍵字換成了 rows ：

執(zhí)行計劃

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

Plan hash value: 2137789089

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |

| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |

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

Cardinality 的值對于 CBO 做出正確的執(zhí)行計劃來說至關(guān)重要。 如果 CBO 獲得的 Cardinality 值不夠準(zhǔn)確（通常是沒有做分析或者分析數(shù)據(jù)過舊造成），在執(zhí)行計劃成本計算上就會出現(xiàn)偏差，從而導(dǎo)致 CBO 錯誤的制定出執(zhí)行計劃。

在多表關(guān)聯(lián)查詢或者 SQL 中有子查詢時，每個關(guān)聯(lián)表或子查詢的 Cardinality 的值對主查詢的影響都非常大，甚至可以說， CBO 就是依賴于各個關(guān)聯(lián)表或者子查詢 Cardinality 值計算出最后的執(zhí)行計劃。

對于多表查詢， CBO 使用每個關(guān)聯(lián)表返回的行數(shù)（ Cardinality ）決定用什么樣的訪問方式來做表關(guān)聯(lián)（如 Nested loops Join 或 hash Join ）。

多表連接的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP

http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx

對于子查詢，它的 Cardinality 將決定子查詢是使用索引還是使用全表掃描的方式訪問數(shù)據(jù)。

三． SQL 的執(zhí)行計劃

生成 SQL 的執(zhí)行計劃是 Oracle 在對 SQL 做硬解析時的一個非常重要的步驟，它制定出一個方案告訴 Oracle 在執(zhí)行這條 SQL 時以什么樣的方式訪問數(shù)據(jù)：索引還是全表掃描，是 Hash Join 還是 Nested loops Join 等。 比如說某條 SQL 通過使用索引的方式訪問數(shù)據(jù)是最節(jié)省資源的，結(jié)果 CBO 作出的執(zhí)行計劃是全表掃描，那么這條 SQL 的性能必然是比較差的。

Oracle SQL 的硬解析和軟解析

http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

示例：

SQL> SET AUTOTRACE TRACEONLY; -- 只顯示執(zhí)行計劃，不顯示結(jié)果集

SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;

已選擇 13 行。

執(zhí)行計劃

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

Plan hash value: 992080948

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 13 | 988 | 6 (17)| 00:00:01 |

| 1 | MERGE JOIN | | 13 | 988 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 13 | 494 | 4 (25)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | EMP | 13 | 494 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

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

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

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

統(tǒng)計信息

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

0 recursive calls

0 db block gets

11 consistent gets

0 physical reads

0 redo size

2091 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

13 rows processed

SQL>

圖片是 Toad 工具查看的執(zhí)行計劃。 在 Toad 里面，很清楚的顯示了執(zhí)行的順序。 但是如果在 SQLPLUS 里面就不是那么直接。 但我們也可以判斷： 一般按縮進長度來判斷，縮進最大的最先執(zhí)行，如果有 2 行縮進一樣，那么就先執(zhí)行上面的。

3.1 執(zhí)行計劃中字段解釋：

ID: 一個序號，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)縮進來判斷。

Operation ： 當(dāng)前操作的內(nèi)容。

Rows ： 當(dāng)前操作的 Cardinality ， Oracle 估計當(dāng)前操作的返回結(jié)果集。

Cost （ CPU ）： Oracle 計算出來的一個數(shù)值（代價），用于說明 SQL 執(zhí)行的代價。

Time ： Oracle 估計當(dāng)前操作的時間。

3.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: 表示這個謂詞條件的值將會影響數(shù)據(jù)的訪問路勁（表還是索引）。

Filter ：表示謂詞條件的值不會影響數(shù)據(jù)的訪問路勁，只起過濾的作用。

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

3.3 統(tǒng)計信息說明：

dbblockgets ： 從 buffercache 中讀取的 block 的數(shù)量

consistentgets ： 從 buffercache 中讀取的 undo 數(shù)據(jù)的 block 的數(shù)量

physicalreads ： 從磁盤讀取的 block 的數(shù)量

redosize ： DML 生成的 redo 的大小

sorts(memory) ： 在內(nèi)存執(zhí)行的排序量

sorts(disk) ： 在磁盤上執(zhí)行的排序量

Physical Reads 通常是我們最關(guān)心的，如果這個值很高，說明要從磁盤請求大量的數(shù)據(jù)到 Buffer Cache 里，通常意味著系統(tǒng)里存在大量全表掃描的 SQL 語句，這會影響到數(shù)據(jù)庫的性能，因此盡量避免語句做全表掃描，對于全表掃描的 SQL 語句，建議增 加相關(guān)的索引，優(yōu)化 SQL 語句來解決。

關(guān)于 physical reads ， db block gets 和 consistent gets 這三個參數(shù)之間有一個換算公式：

數(shù)據(jù)緩沖區(qū)的使用命中率 =1 - ( physical reads / (db block gets + consistent gets) ) 。

用以下語句可以查看數(shù)據(jù)緩沖區(qū)的命中率：

SQL>SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

查詢出來的結(jié)果 Buffer Cache 的命中率應(yīng)該在 90 ％以上，否則需要增加數(shù)據(jù)緩沖區(qū)的大小。

Recursive Calls ： Number of recursive calls generated at both the user and system level.

Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls 。

DB Block Gets ： Number of times a CURRENT block was requested.

Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them. (DB Block Gets: 請求的數(shù)據(jù)塊在 buffer 能滿足的個數(shù) )
當(dāng)前模式塊意思就是在操作中正好提取的塊數(shù)目，而不是在一致性讀的情況下而產(chǎn)生的塊數(shù)。 正常的情況下，一個查詢提取的塊是在查詢開始的那個時間點上存在的數(shù)據(jù)塊，當(dāng)前塊是在這個時刻存在的數(shù)據(jù)塊，而不是在這個時間點之前或者之后的數(shù)據(jù)塊數(shù)目。

Consistent Gets ： Number of times a consistent read was requested for a block.

This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification. (Consistent Gets: 數(shù)據(jù)請求總數(shù)在回滾段 Buffer 中的數(shù)據(jù)一致性讀所需要的數(shù)據(jù)塊 )
這里的概念是在處理你這個操作的時候需要在一致性讀狀態(tài)上處理多少個塊， 這些塊產(chǎn)生的主要原因是因為由于在你查詢的過程中，由于其他會話對數(shù)據(jù)塊進行操 作，而對所要查詢的塊有了修改，但是由于我們的查詢是在這些修改之前調(diào)用的，所以需要對回滾段中的數(shù)據(jù)塊的前映像進行查詢，以保證數(shù)據(jù)的一致性。這樣就產(chǎn) 生了一致性讀。

Physical Reads ： Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. (Physical Reads: 實例啟動后，從磁盤讀到 Buffer Cache 數(shù)據(jù)塊數(shù)量 )

就是從磁盤上讀取數(shù)據(jù)塊的數(shù)量，其產(chǎn)生的主要原因是：
（ 1 ） 在數(shù)據(jù)庫高速緩存中不存在這些塊
（ 2 ） 全表掃描
（ 3 ） 磁盤排序
它們?nèi)咧g的關(guān)系大致可概括為：
邏輯讀指的是 Oracle 從內(nèi)存讀到的數(shù)據(jù)塊數(shù)量。一般來說是 'consistent gets' + 'db block gets' 。當(dāng)在內(nèi)存中找不到所需的數(shù)據(jù)塊的話就需要從磁盤中獲取，于是就產(chǎn)生了 'physical reads' 。

Sorts(disk):

Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.

bytes sent via SQL*Net to client:
Total number of bytes sent to the client from the foreground processes.

bytes received via SQL*Net from client:
Total number of bytes received from the client over Oracle Net.

SQL*Net roundtrips to/from client:
Total number of Oracle Net messages sent to and received from the client.

更多內(nèi)容參考 Oracle 聯(lián)機文檔：

Statistics Descriptions

http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/stats002.htm#i375475

3.4 動態(tài)分析

如果在執(zhí)行計劃中有如下提示：

Note

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

-dynamic sampling used for the statement

這提示用戶 CBO 當(dāng)前使用的技術(shù)，需要用戶在分析計劃時考慮到這些因素。 當(dāng)出現(xiàn)這個提示，說明當(dāng)前表使用了動態(tài)采樣。 我們從而推斷這個表可能沒有做過分析。

這里會出現(xiàn)兩種情況：

（1） 如果表沒有做過分析 ，那么 CBO 可以通過動態(tài)采樣的方式來獲取分析數(shù)據(jù)，也可以或者正確的執(zhí)行計劃。

（2） 如果表分析過 ，但是分析信息過舊，這時 CBO 就不會在使用動態(tài)采樣， 而是使用這些舊的分析數(shù)據(jù)，從而可能導(dǎo)致錯誤的執(zhí)行計劃。

總結(jié)：

在看執(zhí)行計劃的時候，除了看執(zhí)行計劃本身，還需要看謂詞和提示信息。 通過整體信息來判斷 SQL 效率。

整理自網(wǎng)絡(luò)

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

Blog ： http://blog.csdn.net/tianlesoftware

網(wǎng)上資源： http://tianlesoftware.download.csdn.net

相關(guān)視頻： http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群： 62697716( 滿 ); DBA2 群： 62697977( 滿 )

DBA3 群： 63306533; 聊天 群： 40132017

Oracle 執(zhí)行計劃（Explain Plan） 說明