为了更好的理解物理读和逻辑读的关系，模拟了以下实验：(DB 10.2.0.1.0 32bit)
1.在scott用户下创建测试表和相关索引
create table tl(id int,name varchar2(100));
begin ... end;（使用存储过程向表中插入1000条记录）
create index tl_idx on tl(id);（在id字段上创建一个b-tree索引）

2.第一次执行该sql（这是为了去掉recursive calls 对实验的影响)
select * from tl where id=1;

3.在sys用户下刷新buffer cache中的block(这是为了当再次执行sql时发生物理读)
alter system flush buffer cache;

4.在scott用户下再次执行sql(同时打开autotrace以观察执行计划和统计信息)
set autot on
select * from tl where id=1;
        ID NAME                                                                 
---------- ----------
         1

执行计划                                                                                                                     
----------------------------------------------------------
Plan hash value: 4160527729                                                           
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |     3 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TL     |     1 |     3 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TL_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=1)

统计信息                                                                                                                     
----------------------------------------------------------
          0  recursive calls                                                                                                
          0  db block gets                                                                                                   
          4  consistent gets                           
         16  physical reads                  
          0  redo size                             
        460  bytes sent via SQL*Net to client                                                                                
        384  bytes received via SQL*Net from client                                                                          
          2  SQL*Net roundtrips to/from client                        
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

    从以上结果发现该sql消耗了16个物理读和4个逻辑读，在这里我就疑惑了，为什么物理读的个数会超过逻辑读？
以我的理解，在没有发生直接路径访问的情况下，每个物理读都会被读入buffer cache中，所以应该一个物理读就会对应一个逻辑读啊。
怀疑可能是autotrace显示信息不够正确。于是再次模拟这个实验，这次与上次稍微不同，在第4步时没有开启autotrace的功能
而是用10046 trace代替。可结果依然是16个物理读+4个逻辑读，且sql执行过程中的等待事件为db file scatter read,
这里又不理解了，为什么走索引时却发生了这个等待事件，10046  trace内容如下：
=====================
PARSING IN CURSOR #4 len=28 dep=0 uid=65 oct=3 lid=65 tim=5977017214 hv=1673619045 ad='334be8ac'
select * from tl where id=99
END OF STMT
PARSE #4:c=0,e=654,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5977017211
BINDS #4:
EXEC #4:c=0,e=363,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5977045113
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=5977045461
WAIT #4: nam='db file scattered read' ela= 19511 file#=4 block#=73 blocks=8 obj#=55362 tim=5977065349
WAIT #4: nam='db file scattered read' ela= 5828 file#=4 block#=65 blocks=8 obj#=55361 tim=5977071666
FETCH #4:c=0,e=26416,p=16,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=5977072214
WAIT #4: nam='SQL*Net message from client' ela= 147 driver id=1111838976 #bytes=1 p3=0 obj#=55361 tim=5977072758
FETCH,tim=5977 #4:c=0,e=12,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1073123
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=55361 tim=5977073500
WAIT #4: nam='SQL*Net message from client' ela= 844 driver id=1111838976 #bytes=1 p3=0 obj#=55361 tim=5977074674
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=55361 op='TABLE ACCESS BY INDEX ROWID TL (cr=4 pr=16 pw=0 time=26411 us)'
STAT #4 id=2 cnt=1 pid=1 pos=1 obj=55362 op='INDEX RANGE SCAN TL_IDX (cr=3 pr=8 pw=0 time=20023 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=55361 tim=5977076054

    从10046来看，发生了两次离散读，且每次都消耗了8个物理读，正好符合统计的数字，而文件号4的65和73正好是表和索引的位图块。
SQL> select extent_id,block_id,blocks from DBA_extents where segment_name='TL' and owner='SCOTT';
EXTENT_ID   BLOCK_ID     BLOCKS                                                                                             
---------- ---------- ----------
         0         65          8                     
SQL> select extent_id,block_id,blocks from dba_extents where segment_name='TL_IDX' and owner='SCOTT';
EXTENT_ID   BLOCK_ID     BLOCKS                                                                                             
---------- ---------- ----------
         0         73          8   
   总的来说就是两个问题困扰着我：
1.以上执行的sql中为什么物理读大于逻辑读
2.执行计划中的index range scan为什么会发生db file scatter read等待事件
希望各位能够给予我解答，万分感谢！

在你这个是实验中，Oracle发生了物理预读，将两个extent全部读到了内存中，这也是Oracle的一个特点，也就是Oracle尽量的发生多块读。当发生db file scatter read的时候，Oracle尽量的将物理上连续的多块一次性读到内存。数据块都在内存中以后，根据实际要读的数据，只是读了3个数据块，这是很正常的一个实验。

另外，你要真正地理解db file scatter read，并不是走索引就一定不是db file scatter read，Oracle简单的认为单块读就是db file sequential read，多块读就是db file scatter read，这个和是否走索引没有必然的联系。
index range scan因为要访问多块，因此就出现了db file scatter read读。

oraunix 发表于 2012-6-3 12:26
另外，你要真正地理解db file scatter read，并不是走索引就一定不是db file scatter read，Oracle简单的认 ...

谢谢相老师的答复，我理解了！

现在不诡异了吧，你一看trace文件就全明白了。

不过还是表扬一下你看trace文件的习惯。

oraunix 发表于 2012-6-4 13:19
不过还是表扬一下你看trace文件的习惯。

哈哈 谢谢相老师的夸奖，听过您的课，这点功底还是要有的！