Re: db file sequential read, where does it read to?

hpuxrac wrote:
> Ben wrote:
> > 9.2.0.5 Ent Ed AIX5L
> >
> > I've got a few long running processes that are all performing full
> > table scans and they are waiting on db file % reads. When a file read
> > is performed, is it reading the blocks into the buffer and then on to
> > the user or does it just dump them straight out to the user? What I'm
> > getting at is, Could these table scans be aging each other's blocks out
> > of the buffer and thus causing more file reads?
> > I guess if I could do away with the table scans in general that would
> > solve my problem though...
> >
> > I hope that wasn't too confusing.
>
> As other responders have noted, full table scans do get dumped into the
> buffer cache but will tend to get flushed back out immediately
> depending on how many blocks are read in the full scans and how big the
> buffer cache is.
>
> If you set a 10046 trace and set it to level 12 you will get wait
> information that includes exactly what blocks are being read.
>
> Some long running batch process do better with full scans, it all
> depends.
>
> Cary Millsap has a good book optimizing oracle performance that might
> be of use.

Thanks for the replies, I have read several chapters of Cary Millsap's book and have learned a great deal from it. I just skimmed the chapters that he gets into great depth on quantative analysis and queing analysis (those made my head hurt).
I did a 10046 trace level 8, which led me to the file reads. Level 12 pulls the bind info, doesn't it? I can't remember. I may have been a little misleading in my question when I mentioned sequential reads and full table scans. There are a few sessions that were performing table scans and a few sessions that were performing index range scans. It looks as though there were about 5 sessions that keep fighting for disk reads on about four datafiles. The sql that is causing full scans is poorly written and I think that if it was optimized this would relieve some of our headache. Received on Thu Nov 02 2006 - 06:58:05 CST