解析Oracle執行計劃的結果

對於得到的SQL執行計劃，一般如下：

執行計劃

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

--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    19 |    57   (2)|
|   1 |  SORT AGGREGATE    |              |     1 |    19 |            |
|   2 |   TABLE ACCESS FULL| T_ORGANISE | 17228 |   319K|    57   (2)|
--------------------------------------------------------------------------------

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        253  consistent gets
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

其中，上半部分，包括表的連線訪問方式等，這裡先不過多闡述，我們來看看下面的統計資訊：

 

　　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.

 

　　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.
當前模式塊意思就是在操作中正好提取的塊數目，而不是在一致性讀的情況下而產生的塊數。正常的情況下，一個查詢提取的塊是在查詢開始的那個時間點上存在的資料塊，當前塊是在這個時刻存在的資料塊，而不是在這個時間點之前或者之後的資料塊數目。

 

　　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.

這裡的概念是在處理你這個操作的時候需要在一致性讀狀態上處理多少個塊，這些塊產生的主要原因是因為由於在你查詢的過程中，由於其他會話對資料塊進行操作，而對所要查詢的塊有了修改，但是由於我們的查詢是在這些修改之前呼叫的，所以需要對回滾段中的資料塊的前映像進行查詢，以保證資料的一致性。這樣就產生了一致性讀。

 

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.

 

redo size 該操作產生的redo日誌大小。

　　redo block size is platform. specific. There is a method to determine the size by dumping the redo header, refer to note 154864.1. Redo blocks written does not include archive writes or multiplexed writes.

 

sorts (memory)  該操作所產生的資料在記憶體上的排序次數。

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.

rows processed 操作返回的記錄條數。

 

總結：

這其中主要涉及到了Oracle讀取資料的consistent mode和current mode這兩個模式，對於db block gets是在current mode下讀取的block數目（單位應該是“塊次”，同一個block讀取了兩個算做2），而consistent gets是在consistent mode下讀取的block數目（單位同上）。
current mode下讀取資料是為了保證讀取到的資料是當前時間點上最新的資料，這樣做的目的一般都是為了DML語句的需求，比如需要更新，自然需要知道最新的資料才行；consistent mode呢主要是為了保證Oracle資料一致讀的特性，一般都是在select情況下發生，讀到的資料可能是一個實際存在的block，也有可能需要根據scn資訊以及transaction相關資訊以及回滾段中資料來構造。
而physical reads是與logical reads相對的一個概念，兩者的區別是讀取的資料是從buffer中讀取到還是從disk上的db file中取到。透過v$sysstat也可以看到，裡面還有db block gets from cache以及consistent gets from cache兩項，且這兩項的數值與db block gets和consistent gets並不相同且小於後兩者。所以不管是db block gets還是consistent gets，都可能出現了physical reads和logical reads兩種情況（由buffer中的是否已經存在需要的資料），也即是說，db block gets與consistent gets兩者已經構成了一次資料庫操作中讀取的所有block的總次數了。因此，logical reads自然也就可以透過如下公式算的：logical reads = (db block gets + consistent gets) - physical reads。

由此，自然也就得出了cache命中率的公式：
Hit Ratio = (db block gets + consistent gets - physical reads) / (db block gets + consistent gets)
OR
Hit Ratio = 1 – (physical reads/(db block gets + consistent gets))