db file sequential read wait

From: Bosco Ng <boscoklng_at_hotmail.com>
Date: 12 Nov 2003 00:03:09 -0800
Message-ID: <5b606863.0311120003.41cda004@posting.google.com>

Hi, I am trying to tune a system that has the following top 5 wait events in statspack report (9.2.0.3 on AIX, 1G buffer cache, 120M shared pool)

Top 5 Timed Events

---

% Total

Event                                               Waits    Time (s)

Ela Time

-------------------------------------------- ------------ -----------
--------
db file sequential read                         1,388,390       9,767 
  50.21
CPU time                                                        8,186 
  42.08
db file parallel write                             17,703       1,195 
   6.14
latch free                                         37,832         116 


    .60

log file sync                                       1,661          56 

.29

And what's making mad is that every insert / update / delete to some particular tables (single row / few rows update/insert/delete they are) has a big difference between CPU time and Elapsed time: (timed_statstics turned on):

                                                     CPU      Elapsd

  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) --------------- ------------ -------------- ------ -------- ---------

      7,532,522 134,111 56.2 4.4 180.03 807.96 Module: SQL*Plus

INSERT INTO TABLE_A       ( RDG_DATE,        XXXXXXXXX,
   INVALID_FLAG,        VALIDATION_BY_PASS,        XXXX,
    CAPTURE_DATETIME,        UPDATE_DATETIME,        CREATED_BY,

      6,531,957       48,399          135.0    3.8   122.03   1111.75
Module: SQL*Plus
DELETE FROM Table_B     WHERE SID IN     (
    SELECT bs.SID       FROM Table_C bs, Table_D sa
     WHERE bs.Svc_Agmt_SID = sa.SID       AND bs.Bill_SID IS NUL
L       AND sa.Acct_Num = :b1     )

These 2 are just examples. Can anyone suggest what direction should I look into?

Thanks a lot
Bosco Received on Wed Nov 12 2003 - 02:03:09 CST