关于执行计划里recursive calls,db block gets和consistent gets参数的解释

原创 2004年09月21日 11:05:00

我们在实际工作中经常要看某个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%以上,否则需要增加数据缓冲区的大小。

版权声明:本文为博主原创文章,未经博主允许不得转载。

Oracle 有关 Consistent gets 的测试

一.Consistentgets 说明我们使用autotrace 查看执行计划和统计信息时,会显示类似如下信息: 0  recursive calls   :递归调用。一般原因:dictionary ...
  • tianlesoftware
  • tianlesoftware
  • 2012年09月16日 17:26
  • 5470

oracle 执行计划里的cost(%CPU)与consistent gets

有一次做实验,产生了以下的疑惑: oracle 执行计划里的cost(%CPU)与consistent gets之间是什么关系? 为什么会存在consistent gets大,而 Cost 小的情况...
  • badyflf
  • badyflf
  • 2016年04月12日 22:27
  • 1137

oracle 执行计划 consistent gets 特别高sql执行慢 解决办法

原始sql  update  aia_t_alarm_case c    set c.mapx = (select t.mapx from AIA_T_MAP_TOOL t where t.id ...
  • weixin_36707770
  • weixin_36707770
  • 2017年01月11日 17:17
  • 449

+++对于db block gets consistent gets physical reads的大收集和总结

对于db block gets consistent gets physical reads的大收集和总结 http://space.itpub.net/13387766/viewspace-421...
  • orion61
  • orion61
  • 2012年07月11日 13:37
  • 321

consistent gets减少,cost增加?

在一条SQL语句中,当使用索引时,cosistent gets 减少,而cost增加。理论上在稳定后的执行计划中,physical reads为零值的前提下, cost应当相应减少。下面来看看其原由...
  • robinson_0612
  • robinson_0612
  • 2011年11月14日 11:49
  • 3459

db block gets的解释

LOGIC IO(逻辑读次数)= db block gets + consistent gets   consistent get : 在一致读模式下所读的快数,包括从回滚段读的快数。  db ...
  • l106439814
  • l106439814
  • 2012年07月23日 19:39
  • 904

++++update对consistent gets的影响

update对consistent gets的影响 http://blog.csdn.net/biti_rainy/article/details/39489
  • orion61
  • orion61
  • 2012年07月11日 13:38
  • 199

对于'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解和解释

对于'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解和解释 http://blog.csdn.net/yuhua3272004/arti...
  • orion61
  • orion61
  • 2012年03月12日 14:02
  • 249

db block gets 的含义

db block gets 的含义http://www.itpub.net/thread-706812-1-1.html
  • orion61
  • orion61
  • 2011年07月25日 20:25
  • 476

关于 db block gets,consistent gets,physical reads的概念

在Oracle的文档中有这样的解释:Recursive Calls: Number of recursive calls generated at both the user and system l...
  • rabbitbug
  • rabbitbug
  • 2009年12月16日 01:18
  • 6113
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:关于执行计划里recursive calls,db block gets和consistent gets参数的解释
举报原因:
原因补充:

(最多只允许输入30个字)