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

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. Received on Wed Nov 01 2006 - 16:41:54 CST