如果要分析某條SQL
的性能問(wèn)題，通常我們要先看SQL
的執(zhí)行計(jì)劃，看看SQL
的每一步執(zhí)行是否存在問(wèn)題。 如果一條SQL
平時(shí)執(zhí)行的好好的，卻有一天突然性能很差，如果排除了系統(tǒng)資源和阻塞的原因，那么基本可以斷定是執(zhí)行計(jì)劃出了問(wèn)題。
看懂執(zhí)行計(jì)劃也就成了SQL優(yōu)化的先決條件。 這里的SQL優(yōu)化指的是SQL性能問(wèn)題的定位，定位后就可以解決問(wèn)題。
一． 查看執(zhí)行計(jì)劃的三種方法
1.1 設(shè)置autotrace
|
序號(hào) |
命令 |
解釋 |
|
1 |
SET AUTOTRACE OFF |
此為默認(rèn)值，即關(guān)閉Autotrace |
|
2 |
SET AUTOTRACE ON EXPLAIN |
只顯示執(zhí)行計(jì)劃 |
|
3 |
SET AUTOTRACE ON STATISTICS |
只顯示執(zhí)行的統(tǒng)計(jì)信息 |
|
4 |
SET AUTOTRACE ON |
包含2,3兩項(xiàng)內(nèi)容 |
|
5 |
SET AUTOTRACE TRACEONLY |
與ON相似，但不顯示語(yǔ)句的執(zhí)行結(jié)果 |
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í)行計(jì)劃
----------------------------------------------------------
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)計(jì)信息
----------------------------------------------------------
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語(yǔ)句;
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í)行計(jì)劃
----------------------------------------------------------
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)計(jì)信息
----------------------------------------------------------
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ù)期從一個(gè)行源（row source）返回的記錄數(shù)，這個(gè)行源可能是一個(gè)表，一個(gè)索引，也可能是一個(gè)子查詢(xún)。 在Oracle 9i中的執(zhí)行計(jì)劃中，Cardinality縮寫(xiě)成Card。 在10g中，Card值被rows替換。
這是9i的一個(gè)執(zhí)行計(jì)劃，我們可以看到關(guān)鍵字Card：
執(zhí)行計(jì)劃
----------------------------------------------------------
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í)行計(jì)劃，關(guān)鍵字換成了rows：
執(zhí)行計(jì)劃
----------------------------------------------------------
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的值對(duì)于CBO做出正確的執(zhí)行計(jì)劃來(lái)說(shuō)至關(guān)重要。 如果CBO獲得的Cardinality值不夠準(zhǔn)確（通常是沒(méi)有做分析或者分析數(shù)據(jù)過(guò)舊造成），在執(zhí)行計(jì)劃成本計(jì)算上就會(huì)出現(xiàn)偏差，從而導(dǎo)致CBO錯(cuò)誤的制定出執(zhí)行計(jì)劃。
在多表關(guān)聯(lián)查詢(xún)或者SQL中有子查詢(xún)時(shí)，每個(gè)關(guān)聯(lián)表或子查詢(xún)的Cardinality的值對(duì)主查詢(xún)的影響都非常大，甚至可以說(shuō)，CBO就是依賴(lài)于各個(gè)關(guān)聯(lián)表或者子查詢(xún)Cardinality值計(jì)算出最后的執(zhí)行計(jì)劃。
對(duì)于多表查詢(xún)，CBO使用每個(gè)關(guān)聯(lián)表返回的行數(shù)（Cardinality）決定用什么樣的訪問(wèn)方式來(lái)做表關(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
對(duì)于子查詢(xún)，它的Cardinality將決定子查詢(xún)是使用索引還是使用全表掃描的方式訪問(wèn)數(shù)據(jù)。
三． SQL 的執(zhí)行計(jì)劃
生成SQL的執(zhí)行計(jì)劃是Oracle在對(duì)SQL做硬解析時(shí)的一個(gè)非常重要的步驟，它制定出一個(gè)方案告訴Oracle在執(zhí)行這條SQL時(shí)以什么樣的方式訪問(wèn)數(shù)據(jù)：索引還是全表掃描，是Hash Join還是Nested loops Join等。 比如說(shuō)某條SQL通過(guò)使用索引的方式訪問(wèn)數(shù)據(jù)是最節(jié)省資源的，結(jié)果CBO作出的執(zhí)行計(jì)劃是全表掃描，那么這條SQL的性能必然是比較差的。
Oracle SQL的硬解析和軟解析
http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx
示例：
SQL> SET AUTOTRACE TRACEONLY; -- 只顯示執(zhí)行計(jì)劃，不顯示結(jié)果集
SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;
已選擇13行。
執(zhí)行計(jì)劃
----------------------------------------------------------
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)計(jì)信息
----------------------------------------------------------
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í)行計(jì)劃。 在Toad 里面，很清楚的顯示了執(zhí)行的順序。 但是如果在SQLPLUS里面就不是那么直接。 但我們也可以判斷：一般按縮進(jìn)長(zhǎng)度來(lái)判斷，縮進(jìn)最大的最先執(zhí)行，如果有2行縮進(jìn)一樣，那么就先執(zhí)行上面的。
3.1 執(zhí)行計(jì)劃中字段解釋?zhuān)?/span>
ID: 一個(gè)序號(hào)，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)縮進(jìn)來(lái)判斷。
Operation： 當(dāng)前操作的內(nèi)容。
Rows： 當(dāng)前操作的Cardinality，Oracle估計(jì)當(dāng)前操作的返回結(jié)果集。
Cost（CPU）：Oracle 計(jì)算出來(lái)的一個(gè)數(shù)值（代價(jià)），用于說(shuō)明SQL執(zhí)行的代價(jià)。
Time：Oracle 估計(jì)當(dāng)前操作的時(shí)間。
3.2 謂詞說(shuō)明：
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: 表示這個(gè)謂詞條件的值將會(huì)影響數(shù)據(jù)的訪問(wèn)路勁（表還是索引）。
Filter：表示謂詞條件的值不會(huì)影響數(shù)據(jù)的訪問(wèn)路勁，只起過(guò)濾的作用。
在謂詞中主要注意access，要考慮謂詞的條件，使用的訪問(wèn)路徑是否正確。
3.3 統(tǒng)計(jì)信息說(shuō)明：
db block gets ： 從buffer cache中讀取的block的數(shù)量
consistent gets： 從buffer cache中讀取的undo數(shù)據(jù)的block的數(shù)量
physical reads： 從磁盤(pán)讀取的block的數(shù)量
redo size： DML生成的redo的大小
sorts (memory) ：在內(nèi)存執(zhí)行的排序量
sorts (disk) ：在磁盤(pán)上執(zhí)行的排序量
Physical Reads通常是我們最關(guān)心的，如果這個(gè)值很高，說(shuō)明要從磁盤(pán)請(qǐng)求大量的數(shù)據(jù)到Buffer Cache里，通常意味著系統(tǒng)里存在大量全表掃描的SQL語(yǔ)句，這會(huì)影響到數(shù)據(jù)庫(kù)的性能，因此盡量避免語(yǔ)句做全表掃描，對(duì)于全表掃描的SQL語(yǔ)句，建議增 加相關(guān)的索引，優(yōu)化SQL語(yǔ)句來(lái)解決。
關(guān)于physical reads ，db block gets 和consistent gets這三個(gè)參數(shù)之間有一個(gè)換算公式：
數(shù)據(jù)緩沖區(qū)的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。
用以下語(yǔ)句可以查看數(shù)據(jù)緩沖區(qū)的命中率：
SQL>SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');
查詢(xún)出來(lái)的結(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:請(qǐng)求的數(shù)據(jù)塊在buffer能滿足的個(gè)數(shù))
當(dāng)前模式塊意思就是在操作中正好提取的塊數(shù)目，而不是在一致性讀的情況下而產(chǎn)生的塊數(shù)。正常的情況下，一個(gè)查詢(xún)提取的塊是在查詢(xún)開(kāi)始的那個(gè)時(shí)間點(diǎn)上存在的數(shù)據(jù)塊，當(dāng)前塊是在這個(gè)時(shí)刻存在的數(shù)據(jù)塊，而不是在這個(gè)時(shí)間點(diǎn)之前或者之后的數(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ù)請(qǐng)求總數(shù)在回滾段Buffer中的數(shù)據(jù)一致性讀所需要的數(shù)據(jù)塊)
這里的概念是在處理你這個(gè)操作的時(shí)候需要在一致性讀狀態(tài)上處理多少個(gè)塊，這些塊產(chǎn)生的主要原因是因?yàn)橛捎谠谀悴樵?xún)的過(guò)程中，由于其他會(huì)話對(duì)數(shù)據(jù)塊進(jìn)行操 作，而對(duì)所要查詢(xún)的塊有了修改，但是由于我們的查詢(xún)是在這些修改之前調(diào)用的，所以需要對(duì)回滾段中的數(shù)據(jù)塊的前映像進(jìn)行查詢(xún)，以保證數(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:實(shí)例啟動(dòng)后，從磁盤(pán)讀到Buffer Cache數(shù)據(jù)塊數(shù)量)
就是從磁盤(pán)上讀取數(shù)據(jù)塊的數(shù)量，其產(chǎn)生的主要原因是：
（1） 在數(shù)據(jù)庫(kù)高速緩存中不存在這些塊
（2） 全表掃描
（3） 磁盤(pán)排序
它們?nèi)咧g的關(guān)系大致可概括為：
邏輯讀指的是Oracle從內(nèi)存讀到的數(shù)據(jù)塊數(shù)量。一般來(lái)說(shuō)是'consistent gets' + 'db block gets'。當(dāng)在內(nèi)存中找不到所需的數(shù)據(jù)塊的話就需要從磁盤(pán)中獲取，于是就產(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)機(jī)文檔：
Statistics Descriptions
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/stats002.htm#i375475
3.4 動(dòng)態(tài)分析
如果在執(zhí)行計(jì)劃中有如下提示：
Note
------------
-dynamic sampling used for the statement
這提示用戶(hù)CBO當(dāng)前使用的技術(shù)，需要用戶(hù)在分析計(jì)劃時(shí)考慮到這些因素。 當(dāng)出現(xiàn)這個(gè)提示，說(shuō)明當(dāng)前表使用了動(dòng)態(tài)采樣。 我們從而推斷這個(gè)表可能沒(méi)有做過(guò)分析。
這里會(huì)出現(xiàn)兩種情況：
（1） 如果表沒(méi)有做過(guò)分析，那么CBO可以通過(guò)動(dòng)態(tài)采樣的方式來(lái)獲取分析數(shù)據(jù)，也可以或者正確的執(zhí)行計(jì)劃。
（2） 如果表分析過(guò)，但是分析信息過(guò)舊，這時(shí)CBO就不會(huì)在使用動(dòng)態(tài)采樣，而是使用這些舊的分析數(shù)據(jù)，從而可能導(dǎo)致錯(cuò)誤的執(zhí)行計(jì)劃。
總結(jié)：
在看執(zhí)行計(jì)劃的時(shí)候，除了看執(zhí)行計(jì)劃本身，還需要看謂詞和提示信息。 通過(guò)整體信息來(lái)判斷SQL 效率。