Re: db file sequential read wait

The times on v$sql are not entirely trustworthy, and I believe there were some bugs especially relating to DML (as opposed to query).

On most platforms, CPU time is accurate to 1/100 of a second, and elapsed is reported to 1/1000000 of a second in 9.2 - so the dramatic differences could be the result of rounding errors.

I would pick on a session level report of CPU time (from v$sesstat) and wait time (from v$session_event) and identify sessions which seem to have a performance problem -perhaps by asking the users if they perceive their work to be running slowly - and then switch on extended tracing for one session at a time to see where the time is going.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"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

>

> 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