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.

.