關於執行計畫裡recursive calls，db block gets和consistent gets參數的解釋

我們在實際工作中經常要看某個sql語句的執行計畫，例如:

在sqlplus使用命令SET AUTOTRACE ON後，執行計畫顯示如下：

SQL>set autotrace on

SQL> select count(*) from emp;

  COUNT(*)
----------
        12

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        383  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

其中recursive calls，db block gets，consistent gets的具體含義是什嗎？

現整理出的具體解釋如下：

· 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:請求的資料區塊在buffer能滿足的個數)

· 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:資料請求總數在復原段Buffer中)

· 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資料區塊數量)

· 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.

（Sorts(disk):從磁碟上進行排序的數量)

Physical Reads通常是我們最關心的，如果這個值很高，說明要從磁碟請求大量的資料到Buffer Cache裡，通常意味著系統裡存在大量全表掃描的SQL語句，這會影響到資料庫的效能，因此盡量避免語句做全表掃描，對於全表掃描的SQL語句，建議增加相關的索引，最佳化SQL語句來解決。

關於physical reads ，db block gets 和consistent gets這三個參數之間有一個換算公式：

資料緩衝區的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。

在SQL語句裡體現如下：

用以下語句可以查看資料緩衝區的命中率：

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

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