Hi, Looks like you are doing too many IO's for fetching 8 rows and 112 rows respectively. Might be you are using a non selective index for the query. Cheers Sriram Kumar On 1/10/06, BN <bnsarma@xxxxxxxxx> wrote: > > 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 >