oracle wait event之db file sequential read

---會話1建立測試表並插入資料
SQL> create table t_network(a int);

Table created.

--構建索引,產生db file sequential read
SQL> create index idx_t_single on t_single(a);

Index created.

SQL> insert into t_network values(1);

1 row created.

SQL> commit;

Commit complete.

--會話 清空共享池,便於觀察 db file sequential read
SQL> alter system flush shared_pool;
 
System altered

SQL>  select event,total_waits,time_waited,average_wait from v$session_event  where sid=1 order by total_waits desc;
 
EVENT                                                            TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
db file sequential read                                                  139          99         0.71  --此等待事件增大，是因為會讀取與本話相關的一些字典表的資料
SQL*Net message to client                                                 24           0            0
SQL*Net message from client                                               23      260608     11330.78
log file sync                                                              5           0         0.09
Disk file operations I/O                                                   4           0         0.06
events in waitclass Other                                                  3           6         1.93
SQL*Net break/reset to client                                              2           0         0.01
enq: RO - fast object reuse                                                1           1         0.81
 
8 rows selected

---讀取表記錄
SQL> select * from t_single;

         A
----------
         1
         2

 

---對比發現db file sequential read等待事件加大了
SQL> /
 
EVENT                                                            TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
db file sequential read                                                  142         104         0.73
SQL*Net message to client                                                 26           0            0
SQL*Net message from client                                               25      263763     10550.52
log file sync                                                              5           0         0.09
Disk file operations I/O                                                   4           0         0.06
events in waitclass Other                                                  3           6         1.93
SQL*Net break/reset to client                                              2           0         0.01
enq: RO - fast object reuse                                                1           1         0.81
 
8 rows selected

---表已在buffer cache中
SQL> /

         A
----------
         1
         2

---對比發現如表已在buffer cache則db file sequential read不會增加
SQL> /
 
EVENT                                                            TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
db file sequential read                                                  142         104         0.73
SQL*Net message to client                                                 28           0            0
SQL*Net message from client                                               27      280384     10384.58
log file sync                                                              5           0         0.09
Disk file operations I/O                                                   4           0         0.06
events in waitclass Other                                                  3           6         1.93
SQL*Net break/reset to client                                              2           0         0.01
enq: RO - fast object reuse                                                1           1         0.81
 
8 rows selected
 
 
小結:
     1,db file sequential read即伺服器程式發出讀取表記錄所屬的資料塊時，如不在buffer cache,則呼叫io自物理磁碟把對應的資料塊讀取到buffer cache，伺服器程式等待這個io動作完成的過程
     2,所讀取的資料塊不在buffer cache而在物理磁碟上