執行計劃裡statistics資料的解釋

SQL> set autotrace on statistics
SQL> select * from tss;

        ID
----------
         1
         2
         3


Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
        572 bytes sent via SQL*Net to client
        487 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          3 rows processed

現在來解釋一下每一個statistics：

1、DB Block Gets（當前請求的塊數目）當前模式塊意思就是在操作中正好提取的塊數目，而不是在一致性讀的情況下而產生的塊數。正常的情況下，一個查詢提取的塊是在查詢開始的那個時間點上存在的資料塊，當前塊是在這個時刻存在的資料塊，而不是在這個時間點之前或者之後的資料塊數目。

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

3、Physical Reads（物理讀）就是從磁碟上讀取資料塊的數量，其產生的主要原因是： 1、 在資料庫快取記憶體中不存在這些塊 2、 全表掃描 3、 磁碟排序

它們三者之間的關係大致可概括為：邏輯讀指的是Oracle從記憶體讀到的資料塊數量。一般來說是'consistent gets' + 'db block gets'。當在記憶體中找不到所需的資料塊的話就需要從磁碟中獲取，於是就產生了'phsical reads'。

關於physical reads ，db block gets 和consistent gets這三個引數之間有一個換算公式：
資料緩衝區的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。

select 1-physical_reads/(db_block_gets+consistent_gets) Buffer Cache
from
   (select value physical_reads from v$sysstat where name='physical reads'),
   (select value consistent_gets from v$sysstat where name='consistent gets'),
   (select value db_block_gets from v$sysstat where name='db block gets')

查詢出來的結果Buffer Cache的命中率應該在90％以上，否則需要增加資料緩衝區的大小。

4、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.

5、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.

最後我們看下db block gets和consistent gets的區別

buffer_gets=db block gets + consistent gets = LOGIC IO(邏輯讀次數）(as opposed to physical io)

consistent gets : 通過不帶for update的select 讀的次數
db block gets : 通過update/delete/select for update讀的次數.


consistent get ：在一致讀模式下所讀的快數，包括從回滾段讀的快數。
db block gets ：在當前讀模式下所讀的快數，比較少和特殊，例如資料字典資料獲取，在DML中，更改或刪除資料是要用到當前讀模式。

consistent gets：consistent_gets是從回滾段中讀到的前映（或叫讀取一致性影像）, 看見的資料是查詢開始的時間點的，所以若存在block在查詢開始後發生了變化的情況，則必須產生 before image 然後讀資料，這就是一致讀的含義
查詢就是表示 consistent gets (query mode)，因為查詢要保證所獲取的資料的時間點的一致性，所以叫一致讀，即使是從當前 buffer 獲得的資料，也叫 consistent gets ，這僅僅表達一種模式一種期望，並不表示真實的是從 當前buffer 獲得 還是從回滾段獲取資料產生的 bufore image 。

db block gets：current mode , 不管這個塊上的資料是否可能存在 before image ,也就是說不管是否存在回滾中資料可以 回滾，只看見當前最新塊的資料，即使別人正在更新，也看見別人更新狀態的資料，比如dml的時候就不需要看見別人更改前的資料，而是看見正在更改的，當然同時，若操作相同資料則被lock住。也就是說一次查詢中看見的資料可能不在同一個時間點上，比如一個大的dml，當dml 開始更新一個非常大的表後，這個表更新的過程中，有一個程式去把該表末尾的一個記錄更新了，然後這個大更新抵達該記錄的時候會被阻塞的，若該程式事物提交，則大更新會覆蓋該事務的更新，也就是說，這個大更新所看見的資料是當前的，不具有時間點的一致性，所以叫 current mode，個人認為db block gets這個詞用的不好, 容易讓人誤解. 如果改成inconsistent gets可能會更準確一些。

轉自：http://hi.baidu.com/learnfordba/blog/item/0dcb9858ef756b232934f022.html

英文原文：
· 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.
· 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.
· 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.
· 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.