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

上一篇：如何估计undo表空间大小

下一篇：产生undo的测试