看了朝阳写的索引扫描还是全表扫描（Index Scan Or Full Table Scan）？以后，请教了朝阳几个问题。
最后，讨论的焦点集中在了Logical data block reads 上，Concepts上关于逻辑读是这样定义的，Logical data block reads include data block reads from both memory and disk.我的理解是，其中的“data block reads from memory”是指的一般意义的从buffer中获得数据的情况，而from disk则表示类似dml这类操作去访问数据块，dml 操作数据最终也是在访问buffer的过程。那么，当一个buffer为空的情况下，读取数据的hit ratio应该为0，公式是
hit ratio = 1 - [physical reads/(block gets + consistent gets)]
那么，0 = 1 - [1/(0+1)]，即产生一个物理读和一个逻辑读。
当然，也有特殊情况，请教了Fenng才明白，物理读仍然是可以获取数据的
Direct reads are performed for parallel scans, for reads from temporary tablespaces, and for LOB access. Blocks are read directly into private buffers in the PGA, rather than into the database buffer cache in the SGA. There are no cache hits, because blocks are not searched for in the cache before being read.
主要表现在 临时表 和 LOB 的情况下，有直接读的情况产生。
另外，今天看朝阳的文章还明白了一个关于Index Scan的理解
在根据索引读取数据的时候，与是否数据在buffer里面并无关系，关键是看索引的键值的顺序和数据存放的物理顺序（在buffer中和磁盘上顺序是一样的）。首先索引会根据条件将符合条件的rowid全部收集起来，然后是根据键值的顺序来get，正是因为这个顺序可能导致读取多个块。
作者: sopher | 可以转载, 转载时务必以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.color-cc.com/2006/08/physical_reads_and_logical_rea.html
Comments (1)
1
richard (Web)
Posted on August 1, 2006 21:08
session logical reads
The total number of requests to access a block, whether in memory or on disk.
physical reads
The total number of requests to access a data block that resulted in access to datafiles on disk. The block could have been read into the cache or read into local memory by a direct read.
physical reads direct
The number of blocks read, bypassing the buffer cache, excluding direct reads for large objects (LOBs).
