db file sequential read

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

Other related posts: