关于consistent 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大致的意思是对数据块进行一致性读取的次数，这并不意味着是读取的数据块的数量。测试如下：

SQL>  set array 15
SQL>  select * from test;

154080 rows selected.

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      12051  consistent gets
          0  physical reads
          0  redo size
   14193925  bytes sent via SQL*Net to client
     113376  bytes received via SQL*Net from client
      10273  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     154080  rows processed

SQL> set array 5000

SQL> select * from test;

154080 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1969  consistent gets
          0  physical reads
          0  redo size
   12893318  bytes sent via SQL*Net to client
        725  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     154080  rows processed

很明显，当设置不同的array size时，同样的查询，其consistent gets值是差别很大的，当array size越大时，其consistent gets大小越接近于block数量，可以进行推断，当array size设置较小时，比如15，数据库每次从buffer cache中只取得15条记录，而接下来需要读取的15条记录很大可能与前15条记录位于同一数据块上，这样，就对同一个block进行了多次的读取，从而导致consistent reads数量远远大于block数量。

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

转载于:http://blog.itpub.net/10972173/viewspace-592301/