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

Did you analyze the table and is the table properly indexed

Michael Tubuo Ngong Received on Wed Nov 12 2003 - 10:24:14 CST