RE: db file sequential read

May be it's SQL that makes disks "hot", big numbers for "disk" and
"query" to retrieve just a few rows.
Does this sql calculate some "aggregates"?

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of BN
Sent: Tuesday, January 10, 2006 11:14 AM
To: oracle-l@xxxxxxxxxxxxx; Oracle-L@xxxxxxxxxxxxx
Subject: 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: