SQL> select event,TOTAL_WAITS, TIME_WAITED,AVERAGE_WAIT from v$session_event where sid=18 order by TIME_WAITED desc;
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
db file sequential read 47724914
1252067 .026235081
free buffer waits 215054
527065 2.45084955
log file switch completion 85632
397213 4.63860473
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file sync 388381
213054 .548569575
SQL*Net message from client 9706
87956 9.06202349
latch free 43258
74329 1.71827176
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file switch (checkpoint incomplete) 1110
27605 24.8693694
SQL*Net more data from client 5254594
22194 .004223733
enqueue 4787
14258 2.97848339
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
undo segment extension 9822757
11435 .001164133
write complete waits 928
2936 3.1637931
buffer busy waits 133365
1382 .010362539
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
SQL*Net message to client 9706
4 .000412116
file open 22
1 .045454545
发现db file sequential read 居高,通常在单块读发生该事件,用于索引读取;察看正在导入数据的表,果然索引俱全;导入数据的时候要维护索引,对每个导入的数据都要找到对应的索引叶结点插入新索引enry. 删除索引后,该等待事件降低。
通常假如发生较高的log file sync 事件,表示导入进程提交过于频繁。增加buffer参数可以减少commit次数,减少该等待事件