Re: db file sequential read

Hi,

   tha avarage wait of the second query is 0,0026599666280088045253366784218125 seconds i.e. 2,7 msec (I don't know if it's good or not since you I don't know your disks configuration).
I think, as other posters said, than unless you are running summary query you are probably hitting a suboptimal access plan, may be it's a missing index, may be statistics are stale, etc. Did you check the query and the access plan together with the dev team? Giovanni

P.S. I'm a Java developer and I cannot understand your sentence about Java apps. Usually well instrumented Java apps can give enough information as non java-ones.
Some of mine could also enable 10046 event for the session under investigation and report query elapsed time and app server elapsed time

On 1/10/06, BN wrote:
> Greetings & Happy New Year
>
> ORacle 9iRel2 (9.2.0.5) and HP-UX 11, with 28 CPUs and 20 GB RAM.
>
> One of my applications users and DEV team have been complaining about slow
> performance ,
> They couldn't give me more info other than this, since its a JAVA based
> App.
>
> I checked v$session_Wait for a few days, and noticed that most of the waits
> are "db file sequential read", which can be normal in an applicaiton, untill
> I traced (10056) one of their sessions for an hour
>
> Here is some thing I noticed in the tkprof trace and need your comments
> .... I also pulled out datafile Average read (ms) for some of the data
> files that are in 20 to 40 msec range... Thinking for hot disks...
>
> SQL1:
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 2 0.00 0.00 0 0 0
> 0
> Execute 8 0.01 0.00 0 0 0
> 0
> Fetch 8 24.70 166.45 55127 108845 0
> 8
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 18 24.71 166.46 55127 108845 0
> 8
>
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited
> ---------- ------------
> db file sequential read 55127 0.31
> 155.23
> buffer busy waits 1
> 0.00 0.00
> latch free 1
> 0.00 0.00
>
> SQL2:
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 105 0.06 0.07 0 0 0
> 0
> Execute 105 0.31 0.37 0 0 0
> 0
> Fetch 112 227.12 1097.89 338008 1856048 0
> 185
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 322 227.49 1098.34 338008 1856048 0
> 185
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited
> ---------- ------------
> SQL*Net message to client 217 0.00
> 0.00
> SQL*Net more data to client 559 0.00
> 0.09
> SQL*Net message from client 217 0.34
> 5.47
> db file sequential read 338008 0.25
> 889.09
> SQL*Net more data from client 105 0.00
> 0.05
> latch free 24
> 0.00 0.00
> buffer busy waits 26104 0.13
> 52.62
> library cache pin 1
> 1.68 1.68
>
>
> Every thing they run mostly goes through "NESTED LOOPS" in the plan.
>
>
> --
> Regards & Thanks
> BN

--
--------------------------------------------------------------------
Another free oracle resource profiler
http://sourceforge.net/projects/oraresprof/
Now version 0.9
--
http://www.freelists.org/webpage/oracle-l