Re: db file sequential read

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 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
>
Received on Tue Jan 10 2006 - 17:25:12 CST