Re: db file sequential read wait

boscoklng_at_hotmail.com (Bosco Ng) wrote in message news:<5b606863.0311120003.41cda004_at_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

I would start tuning the application and sql statements. A buffer cache of 1G is just ridiculous and clearly demonstrates you are just trying to throw more iron at your problems. This is not going to help.

Sybrand Bakker
Senior Oracle DBA Received on Wed Nov 12 2003 - 09:41:10 CST