Re: SQL tuning nightmare - db file sequential reads

Hi all,

Thanks for all your posts and apologies for the delay in replying.

Steve,

I don't have permissions get the trace files in the environment where I am running the query. I am trying to simulate this behaviour on a system where I can lay my hands on the trace files. I have seen this on many occasions before. This usually happens when there are many queries running and all doing indexed scans or when there are only a couple of queries doing indexed scans on HUGE table(s).

Dan,

I too hope that JPL sir sees this post and responds.

Mladen,

<MG>
You are doing single block reads aka "db file sequential read" and your plan shows
that you're using the index.
</MG>

Agreed. But why on the same single block? Every time I query the V$SESSION_WAIT view it gives the same P1, P2, P3 parameters, thus indicating that the 'db file sequential read' event is accessing the same block.

Also agree in principle about trying out different anti-join mechanisms like minus, has_aj etc. Will try those things out for better performance.

An interesting (unrelated) point regarding the fast full scan - the event shown is 'sequential read' , but the FFS more resembles 'scattered read' isn't it?

Sybrand,

Excellent point. I remember this in our production system - whenever there are 5-6 sessions all running indexed scans on HUGE table(s), a couple them see 'buffer busy' and 'latch free' wait events. I have never been able to understand this. I will try to post this in greater detail later. I think the trace files will help here.

Michel,

<MC>
>From doc. about v$session_wait.seconds_in_wait:

<quote>
If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait
...
WAIT_TIME = duration of last wait
</quote>

Your last wait time lasted 1cs and it started 897s before. </MC>

Yikes! I have read this in the reference guide but am somewhat confused about what it means. I can understand 'Your last wait time lasted 1cs' but don't get what 'it started 897s before' means.

To summarise this long post... I have received lot of helpful suggestions and an action plan to dig deeper, but my question still stands about the strange (to me) behaviour of 'db file sequential read event' and what to make of it (other than the execution plan needs to be changed).

Thanks all,
Charu. Received on Wed Jul 05 2006 - 12:56:06 CDT