db file sequential read

From: BN <bnsarma_at_gmail.com>
Date: 2006-01-10 17:14:27
Message-id: 61292a9d0601100814s1c141284kec91f64368229742@mail.gmail.com

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:14:27 CST