consistent gets, db block gets

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.

Consistent Gets : 数据请求总数在回滚段Buffer中
db block gets : 应该表示在内存buffer中的命中次数

--------------------------------------------------------------- 针对以上3个概念进行的说明解释及关系如下：
1、DB Block Gets（当前请求的块数目）
当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。

2、Consistent Gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）
这 里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操 作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产 生了一致性读。

3、Physical Reads（物理读）
就是从磁盘上读取数据块的数量，其产生的主要原因是：
1、 在数据库高速缓存中不存在这些块
2、 全表扫描
3、 磁盘排序

它们三者之间的关系大致可概括为：
逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了'phsical reads'。

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

------------------
TOM的解释




Lets run a query:

ops$tkyte@ORA817.US.ORACLE.COM> set autotrace traceonly statistics
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> select * from emp;

14 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1979  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

Here we had 4 db block gets.  Those were blocks read in CURRENT MODE.  The blocks that
were read are actually the blocks that tell us how to FULL SCAN the dept table (data
dictionary type of information).  We need to get that in CURRENT MODE (as of RIGHT NOW)
to get an accurate picture of what the table looks like.  

We also had 2 consistent gets -- these are blocks we read in "consistent read" mode --
also known as query mode.  This means we were reading them as of the POINT IN TIME the
query began.  See

http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c23cnsis.htm#17882

for a great discussion of this.


Now, if we do a delete:

ops$tkyte@ORA817.US.ORACLE.COM> delete from emp;

14 rows deleted.


Statistics
----------------------------------------------------------
          0  recursive calls
         20  db block gets
          1  consistent gets
          0  physical reads
       4220  redo size
       1009  bytes sent via SQL*Net to client
        796  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

we do lots more db block gets -- why?  because in order to delete the data we need to get
the block in CURRENT MODE, as it exists right then.

why did we do a consistent get?  because the "read" part of the delete uses the
consistent read mechanism -- we only delete data that existed in the table as of the
point in time the delete began.  Consider if DEPT was a 1,000,000 row table instead.  
It'll take a while to delete all of those rows.  As you are deleting however, other
sessions are inserting and committing data.  This consistent read mechanism makes it so
that we only delete the rows that existed WHEN WE BEGAN the delete.  We will not delete
this new data being inserted.

followup to comment one below

Think of the delete being processed like this:


  for x in ( select rowid from emp )   --- CONSISTENT GETS
  loop
     delete from emp where rowid = x.rowid;  --- CURRENT MODE GETS
  end loop;

that is in effect what is happening.  the READ portion of the delete, the portion of the
delete that finds rows to actually remove reads the table in consistent read mode.  For
each row that it finds that it wants to delete, it does a CURRENT MODE get on that block
to get the row as it exists now (so as to modify that row).

Consider what happens when we delete 1, 5, 10, and all rows from emp:


scott@ORA817.US.ORACLE.COM> set autotrace traceonly statistics;
scott@ORA817.US.ORACLE.COM> select * from emp;

14 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1979  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

scott@ORA817.US.ORACLE.COM>
scott@ORA817.US.ORACLE.COM> delete from emp where rownum <=1;

1 row deleted.


Statistics
----------------------------------------------------------
          0  recursive calls
          7  db block gets
          1  consistent gets
          0  physical reads
        516  redo size
        850  bytes sent via SQL*Net to client
        564  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@ORA817.US.ORACLE.COM> rollback;

Rollback complete.

scott@ORA817.US.ORACLE.COM> delete from emp where rownum <=5;

5 rows deleted.


Statistics
----------------------------------------------------------
          0  recursive calls
         11  db block gets
          1  consistent gets
          0  physical reads
       1660  redo size
        850  bytes sent via SQL*Net to client
        564  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

scott@ORA817.US.ORACLE.COM> rollback;

Rollback complete.

scott@ORA817.US.ORACLE.COM> delete from emp where rownum <=10;

10 rows deleted.


Statistics
----------------------------------------------------------
          0  recursive calls
         16  db block gets
          1  consistent gets
          0  physical reads
       3080  redo size
        850  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

scott@ORA817.US.ORACLE.COM> rollback;

Rollback complete.

scott@ORA817.US.ORACLE.COM> delete from emp where rownum <=15;

14 rows deleted.


Statistics
----------------------------------------------------------
          0  recursive calls
         22  db block gets
          1  consistent gets
          0  physical reads
       4272  redo size
        850  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

scott@ORA817.US.ORACLE.COM> rollback;

Rollback complete.


Each one reads blocks in current mode with a cardinality that relates to the number of
rows deleted. 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11903161/viewspace-687206/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11903161/viewspace-687206/