关于执行计划里recursive calls，db block gets和consistent ge
2024-07-21 02:07:20
供稿：网友
我们在实际工作中经常要看某个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.
