RE: db file sequential read
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <bnsarma@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Jan 2006 11:25:31 -0500
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:
