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 <bnsarma@xxxxxxxxx> 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

Other related posts: