RE: db file sequential read

From: Igor Neyman <ineyman_at_perceptron.com>
Date: 2006-01-10 17:25:31
Message-id: F4C27E77F7A33E4CA98C19A9DC6722A29D5084@EXCHANGE.corp.perceptron.com

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of BN Sent: Tuesday, January 10, 2006 11:14 AM To: oracle-l_at_freelists.org; Oracle-L_at_freelists.org 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

Received on Tue Jan 10 2006 - 17:25:31 CST