關於執行計劃裡recursive calls,db block gets和consistent gets引數的解釋
我們在實際工作中經常要看某個sql語句的執行計劃,例如:
在sqlplus使用命令SET AUTOTRACE ON後,執行計劃顯示如下:
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=985 Card=1 Bytes=26)
Statistics
----------------------------------------------------------
35 recursive calls
0 db block gets
1052 consistent gets
7168 physical reads
0 redo size
395 bytes sent via SQL*Net to client
512 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.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7916042/viewspace-892745/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- recursive calls ,db block gets , consistent gets的含義BloC
- recursive calls, db block gets,consistent gets,physical ReadsBloC
- Consistent Gets,Physical Reads和DB Block Gets的解釋(轉)BloC
- oracle buffer gets=db block gets+consistent getsOracleBloC
- (轉)關於 db block gets,consistent gets,physical reads的概念BloC
- 關於統計中Consistent Gets,Physical Reads和DB Block Gets的意義BloC
- db block gets 與 consistent read getsBloC
- DB Block Gets、Consistent Gets、Physical ReadsBloC
- 對'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解和解釋BloC
- 對'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解BloC
- consistent gets、db block gets的簡單精闢的理解BloC
- db block gets的解釋[轉]BloC
- DB Bocks gets & Consistent gets 區別
- Consistent Gets(就是logical read)+ DB Block Gets = 所謂的Logical ReadsBloC
- DB Bocks gets,Consistent gets And Physical reads 轉載
- consistent gets
- 【Oracle-記憶體管理】-DB Blocks gets,Consistent gets And Physical readsOracle記憶體BloC
- sqlplus中arrayseize引數以及consistent getsSQL
- 淺談consistent gets的計算
- consistent gets 到底指的是什麼?
- C語言關於指標,gets()和gets_s()函式的理解C語言指標函式
- oracle實驗記錄 (oracle consistent gets 計算)Oracle
- consistent gets暴漲的一種情況
- 行欲取導致sqlplus中trace的consistent gets遠大於實際塊數SQL
- db_file_multiblock_read_count引數對block讀取和執行計劃的影響BloC
- 執行計劃裡statistics資料的解釋
- db block get和consistent read getBloC
- [20111228]理解consistent gets*相關資訊
- [20111229]理解consistent gets*相關資訊[補充]
- 關於trace檔案中的recursive calls , elapse time及cpu time,
- gets函式的不安性詳解函式
- mongodb執行計劃解釋MongoDB
- [20111229]Consistent gets from cache (fastpath).txtAST
- gets函式的漏洞函式
- db_block_checksum 引數解析BloC
- pg中與執行計劃相關的配置(ENABLE_*)引數
- db2 執行計劃DB2
- java 執行緒池的初始化引數解釋和引數設定Java執行緒