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.

It reads the blocks to the buffer, but puts them on the least recently used end of the LRU list, so they get aged out first. http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c08memor.htm#8550

You do run into aging each other's blocks out when you do lots of indexed reads. That's when it helps to use another buffer pool.

You can look at what segments are in the buffer with v$bh. http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.htm#33797

Table scans aren't necessarily bad. If you really do need most of the table, for example. Poke about in asktom.oracle.com for more info. You could make things worse going from full table scans to thrashing your buffers.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/nation/20061101-1133-paypal-halloween-explosion.html