当发生单块读时，就会出现这种等待，大部分情况下是读索引，少部分情况下是读undo。构造索引范围扫描，用v$session_event中捕捉。
session1:
C:\Documents and Settings\guogang>sqlplus test/test
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 5月 23 20:10:12 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select distinct sid from v$mystat;
SID
----------
18
SQL> create table test as select * from dba_objects;
SQL> create index ind_t_id on test(object_id);
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> set autotrace traceonly
SQL> alter system flush buffer_cache;
SQL> select /*+index(t ind_t_id)*/ object_id from test t where object_id > 1;
执行计划
----------------------------------------------------------
Plan hash value: 3343177607
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63057 | 800K| 173 (0)| 00:00:03 |
|* 1 | INDEX RANGE SCAN| IND_T_ID | 63057 | 800K| 173 (0)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID">1)
统计信息
----------------------------------------------------------
32 recursive calls
0 db block gets
5006 consistent gets
517 physical reads
0 redo size
1043091 bytes sent via SQL*Net to client
53072 bytes received via SQL*Net from client
4789 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
71806 rows processed
session2:
刚连会话之时
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=18;
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
Disk file operations I/O 1
db file sequential read 11
SQL*Net message to client 14
SQL*Net message from client 13
events in waitclass Other 1
清除缓存之前
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=18;
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
Disk file operations I/O 3
log file sync 3
db file sequential read 573
db file scattered read 51
direct path write 4
SQL*Net message to client 24
SQL*Net message from client 23
events in waitclass Other 8
执行查询之后
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=18;
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
Disk file operations I/O 4
log file sync 3
db file sequential read 579
db file scattered read 116
direct path write 4
SQL*Net message to client 4819
SQL*Net message from client 4818
events in waitclass Other 9
session3:
select /*+index(t ind_t_id)*/ object_id
from
test t where object_id > 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4789 0.14 0.23 160 4936 0 71806
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4791 0.14 0.24 162 4938 0 71806
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
71806 INDEX RANGE SCAN IND_T_ID (cr=4936 pr=160 pw=0 time=37954 us cost=173 size=819741 card=63057)(object id 73406)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.01
SQL*Net message to client 4789 0.00 0.00
SQL*Net message from client 4789 0.00 0.18
db file scattered read 20 0.02 0.04
********************************************************************************