喜欢就来多看看

Slow inserting

上一篇 / 下一篇 2007-02-12 00:00:00 / 个人分类：RDBMS

Slow inserting

28 October 1999

Here is a listing from V$SYSTEM_EVENT. What's causes all the pmon timer and smon timer waits? Increasing the number of block buffers, the shared pool and sort area size only has marginal impact on these waits. There are 50 rollback segments and I have resized them with large extents with only marginal impact. I am using Oracle 8.0.5.1 on HP K-class with 6 processors and raw partition. This database is used to benchmark a heavily inserting application. Could these waits be caused by the CPU being so much faster than I/O?

EVENT                       TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
--------------------------- ----------- -------------- ----------- ------------
rdbms ipc message                105790          14558     5503890   52.0265621
SQL*Net message from client     2818486              0     4438999   1.57495868
log file sync                   2771634            210     2842901   1.02571299
pmon timer                         4041           4035     1215290   300.739916
smon timer                           43             39     1193952   27766.3256
enqueue                          157396              2      918233   5.83390302
latch free                       623534         451472      914831     1.467171
buffer busy waits                636528           1557      549700   .863591232
log file parallel write          558785              0      187438   .335438496
db file parallel write            17350              4      108726   6.26662824
buffer deadlock                   48272          46659       93475   1.93642277
rdbms ipc reply                    8000              0       82468      10.3085
write complete waits               1193              0       14799   12.4048617
control file parallel write        4056              0        8055   1.98594675
SQL*Net message to client       2818487              0        4344   .001541252
db file sequential read            6823              0        1463   .214421809
log file switch completion           66              0        1126   17.0606061
free buffer waits                  1601              0         622   .388507183
db file scattered read              341              0         242   .709677419
sort segment request                  1              1         101          101

This database has several problems, but they are not the ones you think. The pmon timer and smon timer events are idle waits. These background processes wait on these event when they have no work to do.

Your first problem is that you don't have sufficient freelists on the table to which you are inserting, or possibly have a primary key index on a sequence generated primary key that is not a reverse key index. This is probably what is causing your buffer busy waits and buffer deadlock waits.

You also have enqueue waits that are probably a side effect of the lack of process freelists. Also, if your latch free waits are largely on the cache buffers chains latches, then that is probably another side effect.

The ratio of you log file sync to log file parallel write waits indicates that your log_buffer is much too big. Drop it back to 160K.

Your write complete waits and rdbms ipc reply waits indicate the either DBWn's write batch is too big, or you are checkpointing too intensively. Make sure that db_files is not much higher than the number of datafiles that you actually have. If you are using incremental checkpoints ( db_block_max_dirty_target) don't be too ambitious.

导入论坛引用链接收藏分享给好友推荐到圈子管理举报

TAG:

查看全部评论