Re: Full scans of table bigger than buffer cache.

"terry norman" <scott_at_tiger9.freeserve.co.uk> writes:

> ... times as big as the buffer cache, does Oracle limit the number
> of blocks that get used for that scan, to leave some for others, or
> does it just take up all the buffer space it can thereby depressing
> everybody else's hit rate?

The first assumption is true. Oracle by default does not waste the buffer cache for full table scans.

> What would be the effect of setting the
> table as "cache"?

Then the second assumption becomes true. You only want this for "small" tables. If the table is bigger than the cache then a FTS will eventually flush out buffers from the beginning of the FTS. If the table is twice as big as the cache then the buffers are useless for future scans.

> Is there any point is using enough buffer cache to
> get the whole table in, o.s. paging permitting? In this particular
> case the table is about 15% of the data volume. I'm trying to minimise
> full scans, but they still occur, sometimes a few together.

If you do several FTS on the same table, then yes, caching the table and giving enough cache will help. Received on Tue Apr 24 2001 - 17:29:16 CDT