Re: Full scans of table bigger than buffer cache.

On Tue, 24 Apr 2001 21:59:53 +0100, "terry norman" <scott_at_tiger9.freeserve.co.uk> wrote:

>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? What would be the

The short answer is "yes", which means nothing. ;-) Problem is: the algorithms for cache keep changing and Oracle is not always very keen on telling everybody the details of what they do. Thus, you might find what you think happens say in V8.0 will completely change in V8.1.5, then yet again on V8.1.7. Caution is the word here. Problem is mostly to do with the algorithms used by Oracle to populate/flush each of the caches.

Having said that, it's not as bleak as it may sound. You can indeed have some control over what happens by playing with the cache areas and the multiblock-reads parameter. The idea is to either favour a big table to be mostly in memory all the time and/or allow for more equitable use.

>effect of setting the table as "cache"? 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.

Can you not use indexes to reduce the full scanning? I'll assume not, but that would be your best option.

I'd stay away from using the CACHE option. It's described as "deprecated" in the latest versions. It's a better bet to use the three buffer cache categories (KEEP, RECYCLE and DEFAULT) and assign tables to them as needed. Most likely in future this will be increased, possibly to even "cache per tablespace" and stuff like that, making it much easier to handle cases like yours.

<RANT>
<LONG OVERDUE, Mr. ORACLE, the cache/tablespace!!!! I really hope it won't miss 9i or you'll be in deep poo competing with other databases...>
</RANT>

I'd give it a try to setup a *big* KEEP area, then assigning your problem table to it. This is in detriment of having other tables in KEEP cache, but your case is special (don't do this to your normal databases, folks!). Then you can assign all other tables to DEFAULT cache (don't really have to, it's done by definition) and have it suitably sized to cope with their normal load. This means you'll isolate the havoc the large table causes into its own cache area and let the rest of the database live in peace.

Another option, if you can live with some degradation on that table and want to favour others: assign it to a small RECYCLE cache and leave KEEP and DEFAULT to the others. Or make RECYCLE larger! That's the other side of this and you'd be going into somewhat uncharted areas here: the algorithm for RECYCLE might change and leave you in high water with a large RECYCLE. I've tried this before and it worked, but I don't think it can be made into a "rule of thumb" at all. It's too dependent on the version you're running.

Note that the LRU mechanism still applies to each of the cache areas. Independently for each, with settings for each of them. That's where things can come unstuck with a large RECYCLE. If the algorithm is set to agressively free the RECYCLE cache, then having a large RECYCLE won't help at all even if only one table is really using it.

I do strongly recommend you read the section in the performance tuning manual and the database and SQL manuals dealing with the buffer caches and their use. It's very enlightening and you'll need it to setup things properly. There is also a paper in both Metalink and OTN about this which goes into much more detail. If you can't find it, give me a ping offline and I'll send it over.

There are more subtle things that can be done. There are non-documented parameters in init.ora that control how much each of the caches is filled up or used up in terms of %LRU. Let's not go there yet, the above should be plenty enough to give you some relief.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Tue Apr 24 2001 - 19:42:22 CDT