使用event api诊断数据导入imp性能

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次数，减少该等待事件

原文转自：http://www.ltesting.net