Re: Diff between KEEP pool and CACHE keywords

From: Svend Jensen <Svend_SPAMKILL__at_OracleCare.Com>
Date: Fri, 09 May 2003 20:55:13 +0200
Message-ID: <3EBBF991.2030804@OracleCare.Com>

April Nine wrote:
> Hi,
>
> suppose I run these commands:
>
> CREATE TABLE dropme2 (x DATE)
> STORAGE ( BUFFER_POOL KEEP);
>
> CREATE TABLE dropme3 (x DATE)
> STORAGE ( BUFFER_POOL KEEP)
> CACHE;
>
> How is the behavior of the resulting tables
> different from each other?

cache meaning full scans are not placed at LRU end of buffer, but in MRU end. At the higher end of buffer - takes longer to get aged out. If the table size is bigger than the object_optimal_size, only the last part of the full scan is kept, you loose (overwrite) the first part.

>
> I really want to make sure that blocks get aged
> out of the buffer cache very slowly (or possibly pinned).

Set the size of keep pool that it is able to hold all keep tables and indexes. (Or at least the part of the object blocks you address/read)

>
> Would dropme3 blocks get aged out slower than
> dropme2 blocks?

yes (some of it - depending)
>
> Do you know of any V$ views which might help me answer
> the question myself by running some experiments.
>

v$bh and x$bh. This script might help you on the way:

select decode(s.buffer_pool_id,0,'DEFAULT',1,'KEEP',2,'RECYCLE') buffer_pool,

        s.owner, s.segment_name, s.segment_type,
        count(bh.obj) blocks, round(avg(bh.tch),2) avg_use, max(bh.tch) 

max_use
from sys_dba_segs s, x$bh bh
where s.segment_objd = bh.obj
group by decode(s.buffer_pool_id,0,'DEFAULT',1,'KEEP',2,'RECYCLE'), s.segment_name, s.segment_type, s.owner
order by decode(s.buffer_pool_id,0,'DEFAULT',1,'KEEP',2,'RECYCLE'), count(bh.obj) desc,

           round(avg(bh.tch),2) desc, max(bh.tch) desc /

> -April Nine
> april94111_at_yahoo.com

rgds

/Svend Jensen Received on Fri May 09 2003 - 13:55:13 CDT