Re: db file sequential read wait

"Bosco Ng" <boscoklng_at_hotmail.com> 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

This doesn't help a great deal. You waited 162 minutes for Index access, 136 for cpu and 19 for parallel write. Unfortunately I can't tell if this is over 3 hours or 4 weeks.

> 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?

I'd

1. not trust the elapsed time figures unless you can compare them to the wall clock.
2. pick one of the statements and run as follows alter session set max_dumpfile_size=unlimited alter session set tracefile_identifier=bosco -- optional alter session set events '10046 trace name context forever, level 8' <run sql> alter session set events '10046 trace name context off'

run tkprof on the resulting trace file (with bosco in the name). If you run tkprof <tracefilename> bosco.txt sort=prsela,fchela,cpuela (and you trace multiple statements) the most time consuming one will be at the top of the file.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************