執行計劃中的各個參數解釋：

一、Recursive calls （遞歸調用）

1、參考：Oracle Database Reference, 10g Release 2 (10.2).pdf第916頁

?? ? ?Number of recursive calls generated at both the user?and system level. Oracle maintains tables used for?internal processing. When Oracle needs to make a?change to these tables, it internally generates an internal?SQL statement, which in turn generates a recursive call。

? ? 在用戶和在系統級別，都會生成recursive calls，Oracle維護了一些表用于內部的處理（數據字典）。當Oracle需要對這些表進行修改的時候，在Oracle內部生成了一些SQL語句，將會產生?recursive call

2、參考：Oracle Database Performance Tuning Guide, 11g Release 1 (11.1).pdf第470頁

? ?? ?Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue?additional statements. Such statements are called recursive calls or recursive SQL?statements. For example, if you insert a row into a table that does not have enough?space to hold that row, then Oracle makes recursive calls to allocate the space?dynamically. Recursive calls are also generated when data dictionary information is?not available in the data dictionary cache and must be retrieved from disk.
? ? ? ??If recursive calls occur while the SQL Trace facility is enabled, then TKPROF produces?statistics for the recursive SQL statements and marks them clearly as recursive SQL?statements in the output file. You can suppress the listing of Oracle internal recursive?calls (for example, space management) in the output file by setting the SYS?command-line parameter to NO. The statistics for a recursive SQL statement are?included in the listing for that statement, not in the listing for the SQL statement that?caused the recursive call. So, when you are calculating the total resources required to?process a SQL statement, consider the statistics for that statement as well as those for?recursive calls caused by that statement

? ? ? 為了執行一個用戶發出的SQL語句，Oracle必須發出一些額外的SQL語句，這些語句稱為recursive calls，例如，當你向一個表插入一行數據的時候，這個表沒有多于的空間來存儲這一行數據，Oracle就會產生?recursive calls來動態的分配存儲空間，Recursive calls在數據字典信息數據字典緩存不可用的情況下并且必須從磁盤中重新讀取的情況下也會生成。

? ? 如果在發送遞歸調用的時候，SQL跟蹤生效的話，那么TKPROF將會對遞歸SQL生成統計信息，并且將它們輸出到一個文件中，遞歸調用的語句包含在這些遞歸語句中，而不是引起這些遞歸調用的語句中，所以當統計SQL語句執行過程中消耗的資源時，也需要考慮在SQL語句執行中引起的遞歸調用所消耗的資源。

3、參考：http://www.cnblogs.com/chinhr/archive/2009/03/14/1412100.html

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

? ?簡單的說，遞歸調用是自己的SQL調用的代表，當你需要解析SQL的時候，你需要調用其他的查詢去獲取數據字典的信息，這些就是遞歸調用，空間管理，安全檢查，調用PL/SQL和SQL，這些都是遞歸調用。

4、?Beginning Oracle Database 11g Administration: From Novice to Professional-394頁

? ? ? ? Recursive calls result from all the work that is done behind the scenes by Oracle?before it can begin executing your query. When a query is submitted for execution,?Oracle first checks its syntax. It then checks the semantics—that is, it de- references?synonyms and views and identifies the underlying tables. It then computes the?signature (a.k.a. hash value) of the query and checks its cache of query execution
plans for a reusable query plan that corresponds to that signature. If a query plan?is not found, Oracle has to construct one; this results in recursive calls. The number?of recursive calls required can be large if the information Oracle needs to construct?the query plan is not available in the dictionary cache and has to be retrieved from?the database or if there are no statistics about tables mentioned in the query and?data blocks have to be sampled to generate statistics dynamically.

? ? ? ?遞歸調用是在Oracle執行查詢之后開始做的。當一個查詢開始執行的時候，Oracle首先檢查它的語義，隨后檢查同義詞和視圖中引用到的表，計算查詢語句的簽名（hash值），并查詢緩存中是否存在當前簽名的執行計劃，或者查詢語句的執行計劃沒有發現，那么它將會創建一個，這就是遞歸調用，當需要創建的執行計劃的一些信息在數據字典緩存中不存在，需要重數據庫中讀取，或者查詢使用的表沒有統計信息，遞歸調用的數量會非常的大，這時候，會進行動態的取樣來生成統計信息。

二：DB block gets

? ? ?1、DB block gets is the number of times Oracle needs the latest version of a data?block. Oracle does not need the latest version of a data block when the data is simply?being read by the user. Instead, Oracle needs the version of the data block that?was current when the query started. The latest version of a data block is typically?required when the intention is to modify the data.

? ? ? DB block gets是Oracle需要獲取最新版本數據所需要訪問的數據塊，當用戶讀取數據時，Oracle需要的不是最新版本的數據塊的信息，而是當查詢開始的時候，那個版本的數據塊信息，當Oracle修改數據的時候，Oracle需要最數據塊最新版本的信息。

? ? 2、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能滿足的個數)

? ? 在某個時間點需要訪問的數據塊的數量，當前模式的數據塊是從現在存在的塊進行檢索，而不是一致讀的模式，通常，一個查詢需要獲取的數據塊是當查詢開始的時候計算，而當前模式從從現在開始的時間讀取，而不是從過去的某個時間點開始讀取，在一個查詢中，你需要訪問數據字典進行一個全表掃描獲取數據字典的段信息。在修改數據的過程中，需要訪問當前模式的數據來修改寫入它。

DB block gets分為以下兩種情況：

db block gets direct：Number of times a CURRENT block was requested?bypassing the buffer cache (for example, a direct load?operation). This is a subset of "db block gets" statistics?value.

db block gets from cache：Number of times a CURRENT block was requested from?the buffer cache. This is a subset of "db block gets"?statistics value.

三：Consistent gets （a.k.a：also know as）：Beginning Oracle Database 11g Administration: From Novice to Professional-412頁

? ? ? Consistent gets, a.k.a. logical reads, is the number of operations to retrieve a consistent?version of a data block that were performed by Oracle while constructing?query plans and while executing queries. Remember that all data blocks read during?the execution of any query are the versions that were current when the query?started; this is called read consistency. One of the principal objectives of query tuning?is to reduce the number of consistent get operations that are required.

? ? ?一致讀，也叫邏輯讀，是Oracle在執行查詢的時候，構建查詢計劃訪問數據塊一致版本的操作，記住：Oracle獲取的所以數據塊都是查詢開始時候的版本，這叫做讀一致性，優化查詢的一個原則是減少一致讀的操作。

? ? ? 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。

? ? 一致讀，Oracle一致讀請求的塊的數量，也就是在進行一致讀模式的時候，需要處理的數據塊，則需要從回滾段讀取回滾的數據塊。

? ? ??Number of times a user process has applied rollback?entries to perform a consistent read on the block?Work loads that produce a great deal of consistent?changes can consume a great deal of resources. The?value of this statistic should be small in relation to the?"consistent gets" statistic.

? ? ?Oracle執行一致讀的時候，處理的數據塊的個數。

四：Physical reads

? ? ? ?Physical reads is the number of operations to read data blocks from the disks that?were performed by Oracle because the blocks were not found in Oracle’s cache?when they were required. The limit on the number of physical reads is therefore?the number of consistent gets.

? ?物理讀是指由于數據塊在Oracle的緩存中未被發現，而需要從磁盤中讀取的數據塊的個數，物理讀的個數是由一致讀來限制的。

五：Redo size（重做的數量）

? ? ?Redo size is the amount of information Oracle writes to the journals that track?changes to the database. This metric applies only when the data is changed in?some way.

六：SQL*Net from client、SQL*Net to client

? ? ?Bytes sent via SQL*Net to client, bytes received via SQL*Net from client, and SQL*Net?roundtrips to/from client are fairly self- explanatory; they track the number and size?of messages sent to and from Oracle and the user

? ? 主要是指Oracle的網絡向客戶端發送的數據流量的大小

七：Sorts (memory)、sorts (disk)?

? ? ?Sorts (memory) and sorts (disk) track the number of sorting operations performed?by Oracle during the course of our query. Sorting operations are performed in?memory if possible; they spill onto the disks if the data does not fit into Oracle’s?memory buffers. It is desirable for sorting to be performed in memory because?reading and writing data to and from the disks are expensive operations.?

? ?? 最好在內存中進行排序和，在磁盤中進行數據的讀和寫是非常昂貴的操作。

下面的內容是摘取自博客：http://www.cnblogs.com/chinhr/archive/2009/03/14/1412100.htm

?和Oracle Database Performance Tuning Guide, 11g Release 1 (11.1)的131頁：

? ? ??

　　 ?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％以上，否則需要增加數據緩沖區的大小。

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

db block gets：Number of times a CURRENT block was requested.
consistent gets：Number of times a consistent read was requested for a block.
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.
---------------------------------------------------------------
針對以上3個概念進行的說明解釋及關系如下：
1、DB Block Gets（當前請求的塊數目）
當前模式塊意思就是在操作中正好提取的塊數目，而不是在一致性讀的情況下而產生的塊數。正常的情況下，一個查詢提取的塊是在查詢開始的那個時間點上存在的數據塊，當前塊是在這個時刻存在的數據塊，而不是在這個時間點之前或者之后的數據塊數目。
2、Consistent Gets（數據請求總數在回滾段Buffer中的數據一致性讀所需要的數據塊）
這里的概念是在處理你這個操作的時候需要在一致性讀狀態上處理多少個塊，這些塊產生的主要原因是因為由于在你查詢的過程中，由于其他會話對數據塊進行操 作，而對所要查詢的塊有了修改，但是由于我們的查詢是在這些修改之前調用的，所以需要對回滾段中的數據塊的前映像進行查詢，以保證數據的一致性。這樣就產 生了一致性讀。
3、Physical Reads（物理讀）
就是從磁盤上讀取數據塊的數量，其產生的主要原因是：
1、 在數據庫高速緩存中不存在這些塊
2、 全表掃描
3、 磁盤排序
它們三者之間的關系大致可概括為：
邏輯讀指的是Oracle從內存讀到的數據塊數量。一般來說是'consistent gets' + 'db block gets'。當在內存中找不到所需的數據塊的話就需要從磁盤中獲取，于是就產生了'phsical reads'。

?

?

轉載于:https://www.cnblogs.com/caroline/archive/2012/12/13/2815646.html

總結

以上是生活随笔為你收集整理的执行计划中的参数解释的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。