Re: Diff between recycle pool and nocache keywords

CACHE and NOCACHE keywords are entirely redundant in Oracle 8 and above if you choose to use multiple buffer pools instead. The only difference they have is to determine whereabouts on the LRU list for an undifferentiated buffer cache the blocks are loaded during a full table scan -right up the MRU end of the list (CACHE) or about half-way down (NOCACHE). That was so that big full tablescans wouldn't flush your good data out of the single cache.

With multiple caches, you direct the big full tablescans to the recycle pool, and thus your useful data is safe and sound in the KEEP or DEFAULT pools, regardless. Thus, CACHE and NOCACHE are redundant.

Specifically, however, NOCACHE is the default, so your two create table statements are entirely identical in effect. You don't *say* NOCACHE for the first one, but it's implied anyway.

If you want a view to check things with, then v$bh will allow you to see what is in the buffer cache. In 9i, you can run /rdbms/admin/catclust.sql to create a v$cache which does the same sort of job, but better. (It's a RAC view, really, but works in a single instance).

Regards
HJR "April Nine" <april94111_at_yahoo.com> wrote in message news:cdf76ce3.0305090730.6f31359d_at_posting.google.com...
> Hi,
>
> suppose I run these commands:
>
> CREATE TABLE dropme2 (x DATE)
> STORAGE ( BUFFER_POOL RECYCLE);
>
> CREATE TABLE dropme3 (x DATE)
> STORAGE ( BUFFER_POOL RECYCLE)
> NOCACHE;
>
> How is the behavior of the resulting tables
> different from each other?
>
> I really want to make sure that blocks get aged
> out of the buffer cache very quickly.
>
> Would dropme3 blocks get aged out quicker than
> dropme2 blocks?
>
> Do you know of any V$ views which might help me answer
> the question myself by running some experiments.
>
> -April Nine
> april94111_at_yahoo.com
Received on Fri May 09 2003 - 17:13:59 CDT