使用event api診斷數據導入imp性能
2024-07-21 02:34:15
供稿：網友
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 .026235081free buffer waits 215054
527065 2.45084955log file switch completion 85632
397213 4.63860473
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
log file sync 388381
213054 .548569575SQL*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.8693694SQL*Net more data from client 5254594
22194 .004223733enqueue 4787
14258 2.97848339EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
undo segment extension 9822757
11435 .001164133write complete waits 928
2936 3.1637931
buffer busy waits 133365
1382 .010362539EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
TIME_WAITED AVERAGE_WAIT
----------- ------------
SQL*Net message to client 9706
4 .000412116file open 22
1 .045454545 發現db file sequential read 居高，通常在單塊讀發生該事件，用于索引讀取；察看正在導入數據的表，果然索引俱全；導入數據的時候要維護索引，對每個導入的數據都要找到對應的索引葉結點插入新索引enry. 刪除索引后，該等待事件降低。 通常假如發生較高的log file sync 事件，表示導入進程提交過于頻繁。增加buffer參數可以減少commit次數，減少該等待事件
