当进行多块读时，就会产生这种等待事件。实验准备做一个表的全扫描和索引全扫描，用v$session_event中捕捉。
session1:
C:\Documents and Settings\guogang>sqlplus test/test
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 5月 23 19:21:50 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
----------
17
SQL> create table test as select * from dba_objects;
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> alter system flush buffer_cache;
SQL> set autotrace traceonly
SQL> select * from test;
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63057 | 12M| 287 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| TEST | 63057 | 12M| 287 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
328 recursive calls
0 db block gets
5829 consistent gets
1029 physical reads
0 redo size
3433516 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)
71809 rows processed
SQL> create index ind_t_id on test(object_id);
SQL> select count(object_id) from test;
执行计划
----------------------------------------------------------
Plan hash value: 3744170542
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 48 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IND_T_ID | 63057 | 800K| 48 (0)| 00:00:01 |
----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
228 consistent gets
159 physical reads
0 redo size
433 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
session2:
在未执行select * from test之前
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=17;
EVENT TOTAL_WAITS
------------------------------ -----------
Disk file operations I/O 3
log file sync 2
db file sequential read 573
db file scattered read 23
direct path write 1
SQL*Net message to client 24
SQL*Net message from client 23
SQL*Net break/reset to client 2
events in waitclass Other 6
在执行select * from test之后
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=17;
EVENT TOTAL_WAITS
------------------------------ -----------
Disk file operations I/O 4
log file sync 2
db file sequential read 580
db file scattered read 109
direct path write 1
SQL*Net message to client 4818
SQL*Net message from client 4817
SQL*Net break/reset to client 2
events in waitclass Other 7
在建索引之后
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=17;
EVENT TOTAL_WAITS
------------------------------ -----------
Disk file operations I/O 4
log file sync 2
db file sequential read 609
db file scattered read 109
direct path write 4
SQL*Net message to client 4819
SQL*Net message from client 4818
SQL*Net break/reset to client 2
events in waitclass Other 9
在执行select count(object_id ) from test之后
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=17;
EVENT TOTAL_WAITS
------------------------------ -----------
Disk file operations I/O 4
log file sync 2
db file sequential read 609
db file scattered read 126
direct path write 4
SQL*Net message to client 4826
SQL*Net message from client 4825
SQL*Net break/reset to client 2
events in waitclass Other 9
已选择9行。
session3:
E:\oracle11g\guogang\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_716.trc 716.txt sys=no aggregate=no
select *
from
test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 1 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4789 0.25 0.41 686 5737 0 71809
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4791 0.26 0.43 687 5738 0 71809
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
71809 TABLE ACCESS FULL TEST (cr=5737 pr=686 pw=0 time=435584 us cost=287 size=13052799 card=63057)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 5 0.02 0.03
SQL*Net message to client 4789 0.00 0.00
db file scattered read 43 0.02 0.09
SQL*Net message from client 4789 0.00 1.08
********************************************************************************
select count(object_id)
from
test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 61 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.04 159 167 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.05 159 228 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=167 pr=159 pw=0 time=0 us)
71806 INDEX FAST FULL SCAN IND_T_ID (cr=167 pr=159 pw=0 time=26366 us cost=48 size=819741 card=63057)(object id 73400)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 17 0.01 0.03
SQL*Net message from client 2 0.00 0.00
********************************************************************************