Oracle Keep & Recycle Buffer Pools
Version 11.2.0.3

General Information
The following demonstration was written by Charles Hooper and posted to comp.databases.oracle.server on 04-Jul-2007 (updated for 11gR2 by Morgan).
conn / as sysdba

shutdown immediate;

startup;

conn uwclass/uwclass

-- create 3 tables and indexes in the KEEP buffer pool and the RECYCLE buffer pool
CREATE TABLE t1 (
my_date   DATE          NOT NULL,
my_number NUMBER(12,10) NOT NULL,
my_row    NUMBER(12)    NOT NULL)
STORAGE (BUFFER_POOL KEEP);

CREATE UNIQUE INDEX t1_ind1
ON t1(my_date)
STORAGE (BUFFER_POOL KEEP);

CREATE TABLE t2 (
my_date   DATE          NOT NULL,
my_number NUMBER(12,10) NOT NULL,
my_row    NUMBER(12)    NOT NULL)
STORAGE (BUFFER_POOL KEEP);

CREATE UNIQUE INDEX t2_ind1
ON t2(my_date)
STORAGE (BUFFER_POOL KEEP);

CREATE TABLE t3 (
my_date   DATE          NOT NULL,
my_number NUMBER(12,10) NOT NULL,
my_row    NUMBER(12)    NOT NULL)
STORAGE (BUFFER_POOL KEEP);

CREATE UNIQUE INDEX t3_ind1
ON t3(my_date)
STORAGE (BUFFER_POOL KEEP);

CREATE TABLE t1_r (
my_date   DATE          NOT NULL,
my_number NUMBER(12,10) NOT NULL,
my_row    NUMBER(12)    NOT NULL)
STORAGE (BUFFER_POOL RECYCLE);

CREATE UNIQUE INDEX t1_ind1_R
ON t1_r(my_date)
STORAGE (BUFFER_POOL RECYCLE);

CREATE TABLE t2_r (
my_date   DATE          NOT NULL,
my_number NUMBER(12,10) NOT NULL,
my_row    NUMBER(12)    NOT NULL)
STORAGE (BUFFER_POOL RECYCLE);

CREATE UNIQUE INDEX t2_ind1_r
ON t2_r(my_date)
STORAGE (BUFFER_POOL RECYCLE);

CREATE TABLE t3_r (
my_date   DATE          NOT NULL,
my_number NUMBER(12,10) NOT NULL,
my_row    NUMBER(12)    NOT NULL)
STORAGE (BUFFER_POOL RECYCLE);

CREATE UNIQUE INDEX t3_ind1_r
ON t3_r(my_date)
STORAGE (BUFFER_POOL RECYCLE);

-- make certain that the two buffer pools are the same size
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 16777216;
ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE = 16777216;

-- load data into the tables
INSERT INTO t1
SELECT TRUNC(SYSDATE) + ROWNUM, COS(ROWNUM / 180 * 3.141592), ROWNUM
FROM dual
CONNECT BY LEVEL <= 100000;

COMMIT;

INSERT INTO t2
SELECT TRUNC(SYSDATE) + ROWNUM, COS(ROWNUM / 180 * 3.141592), ROWNUM
FROM dual
CONNECT BY LEVEL <= 100000;

COMMIT;

INSERT INTO t3
SELECT TRUNC(SYSDATE) + ROWNUM, COS(ROWNUM / 180 * 3.141592), ROWNUM
FROM dual
CONNECT BY LEVEL <= 100000;

COMMIT;

INSERT INTO t1_r
SELECT TRUNC(SYSDATE) + ROWNUM, COS(ROWNUM / 180 * 3.141592), ROWNUM
FROM dual
CONNECT BY LEVEL <= 100000;

COMMIT;

INSERT INTO t2_r
SELECT TRUNC(SYSDATE) + ROWNUM, COS(ROWNUM / 180 * 3.141592), ROWNUM
FROM dual
CONNECT BY LEVEL <= 100000;

COMMIT;

INSERT INTO t3_r
SELECT TRUNC(SYSDATE) + ROWNUM, COS(ROWNUM / 180 * 3.141592), ROWNUM
FROM dual
CONNECT BY LEVEL <= 100000;

COMMIT;

-- create one more table in each of the buffer pools and load with data
CREATE TABLE t4 (
my_date   DATE          NOT NULL,
my_number NUMBER(12,10) NOT NULL,
my_row    NUMBER(12)    NOT NULL)
STORAGE (BUFFER_POOL KEEP);

CREATE UNIQUE INDEX t4_ind1
ON t4(my_date)
STORAGE (BUFFER_POOL KEEP);

CREATE TABLE t4_r (
my_date   DATE          NOT NULL,
my_number NUMBER(12,10) NOT NULL,
my_row    NUMBER(12)    NOT NULL)
STORAGE (BUFFER_POOL RECYCLE);

CREATE UNIQUE INDEX t4_ind1_r
ON t4_r(my_date)
STORAGE (BUFFER_POOL RECYCLE);

INSERT INTO t4_r
SELECT TRUNC(SYSDATE) + ROWNUM, COS(ROWNUM / 180 * 3.141592), ROWNUM
FROM dual
CONNECT BY LEVEL <= 400000;

COMMIT;

INSERT INTO t4_r
SELECT TRUNC(SYSDATE) + ROWNUM, COS(ROWNUM / 180 * 3.141592), ROWNUM
FROM dual
CONNECT BY LEVEL <= 400000;

COMMIT;

-- see what is in the KEEP and RECYCLE pools
SELECT ds.buffer_pool, SUBSTR(do.object_name,1,9) OBJECT_NAME, ds.blocks OBJECT_BLOCKS, COUNT(*) CACHED_BLOCKS
FROM dba_objects_ae do, dba_segments ds, v$bh v
WHERE do.data_object_id = v.objd
AND do.owner=ds.owner(+)
AND do.object_name=ds.segment_name(+)
AND do.object_type = ds.segment_type(+)
AND ds.buffer_pool IN ('KEEP','RECYCLE')
GROUP BY ds.buffer_pool, do.object_name, ds.blocks
ORDER BY do.object_name, ds.buffer_pool;

/* on data load, the number cached blocks are different between the KEEP and RECYCLE pools.
   on shutdown and startup to clear the cache and collect statistics on the tables and indexes */

conn / as sysdba

shutdown immediate;

startup;

conn uwclass/uwclass

exec dbms_stats.gather_table_stats(ownnname=>USER, tabname=>'t1',cascade=>TRUE);
exec dbms_stats.gather_table_stats(ownnname=>USER, tabname=>'t2',cascade=>TRUE);
exec dbms_stats.gather_table_stats(ownnname=>USER, tabname=>'t3',cascade=>TRUE);
exec dbms_stats.gather_table_stats(ownnname=>USER, tabname=>'t4',cascade=>TRUE);

exec dbms_stats.gather_table_stats(ownnname=>USER, tabname=>'t1_r',cascade=>TRUE);
exec dbms_stats.gather_table_stats(ownnname=>USER, tabname=>'t2_r',cascade=>TRUE);
exec dbms_stats.gather_table_stats(ownnname=>USER, tabname=>'t3_r',cascade=>TRUE);
exec dbms_stats.gather_table_stats(ownnname=>USER, tabname=>'t4_r',cascade=>TRUE);

-- see what is in the buffer cache:
SELECT ds.buffer_pool, SUBSTR(do.object_name,1,9) OBJECT_NAME, ds.blocks OBJECT_BLOCKS, COUNT(*) CACHED_BLOCKS
FROM dba_objects_ae do, dba_segments ds, v$bh v
WHERE do.data_object_id = v.objd
AND do.owner = ds.owner(+)
AND do.object_name = ds.segment_name(+)
AND do.object_type = ds.segment_type(+)
AND ds.buffer_pool IN ('KEEP','RECYCLE')
GROUP BY ds.buffer_pool, do.object_name, ds.blocks
ORDER BY do.object_name, ds.buffer_pool;

-- on statistics collection, the number cached blocks are different between the KEEP and RECYCLE pools

conn / as sysdba

shutdown immediate;

startup;

conn uwclass/uwclass

--  update some rows
UPDATE t1
SET my_row = my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);

COMMIT;

UPDATE t2
SET my_row = my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);

COMMIT;

UPDATE t3
SET my_row = my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);

COMMIT;

UPDATE t4
SET my_row = my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);

COMMIT;

UPDATE t1_r
SET my_row=my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);

COMMIT;

UPDATE t2_r
SET my_row=my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);

COMMIT;

UPDATE t3_r
SET my_row=my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);

COMMIT;

UPDATE t4_r
SET my_row=my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);

COMMIT;

-- see what is in the buffer cache
SELECT ds.buffer_pool, SUBSTR(do.object_name,1,9) OBJECT_NAME, ds.blocks OBJECT_BLOCKS, COUNT(*) CACHED_BLOCKS
FROM dba_objects_ae do, dba_segments ds, v$bh v
WHERE do.data_object_id=V.OBJD
AND do.owner=ds.owner(+)
AND do.object_name=ds.segment_name(+)
AND DO.OBJECT_TYPE=DS.SEGMENT_TYPE(+)
AND ds.buffer_pool IN ('KEEP','RECYCLE')
GROUP BY ds.buffer_pool, do.object_name, ds.blocks
ORDER BY do.object_name, ds.buffer_pool;

-- on data update, the number cached blocks are different between the KEEP and RECYCLE pools

-- try a larger update on a single table
UPDATE t3
SET my_row=my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+50000);

COMMIT;

UPDATE t3_r
SET my_row=my_row+100
WHERE my_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+50000);

COMMIT;

-- see what is in the buffer cache
SELECT ds.buffer_pool, SUBSTR(do.object_name,1,9) OBJECT_NAME, ds.blocks OBJECT_BLOCKS, COUNT(*) CACHED_BLOCKS
FROM dba_objects_ae do, dba_segments ds, v$bh v
WHERE do.data_object_id=V.OBJD
AND do.owner=ds.owner(+)
AND do.object_name=ds.segment_name(+)
AND DO.OBJECT_TYPE=DS.SEGMENT_TYPE(+)
AND ds.buffer_pool IN ('KEEP','RECYCLE')
GROUP BY ds.buffer_pool, do.object_name, ds.blocks
ORDER BY do.object_name, ds.buffer_pool;

-- on data update of a single table, the number cached blocks for the table affected by the update is roughly the same.
-- try performing full tablescans on all of the test tables.

SELECT COUNT(*)
FROM t1
WHERE my_number<10;

SELECT COUNT(*)
FROM t2
WHERE my_number<10;

SELECT COUNT(*)
FROM t3
WHERE my_number<10;

SELECT COUNT(*)
FROM t4
WHERE my_number<10;

SELECT COUNT(*)
FROM t1_r
WHERE my_number<10;

SELECT COUNT(*)
FROM t2_r
WHERE my_number<10;

SELECT COUNT(*)
FROM t3_r
WHERE my_number<10;

SELECT COUNT(*)
FROM t4_r
WHERE my_number<10;

-- see what is in the buffer cache
SELECT ds.buffer_pool, SUBSTR(do.object_name,1,9) OBJECT_NAME, ds.blocks OBJECT_BLOCKS, COUNT(*) CACHED_BLOCKS
FROM dba_objects_ae do, dba_segments ds, v$bh v
WHERE do.data_object_id=V.OBJD
AND do.owner=ds.owner(+)
AND do.object_name=ds.segment_name(+)
AND DO.OBJECT_TYPE=DS.SEGMENT_TYPE(+)
AND ds.buffer_pool IN ('KEEP','RECYCLE')
GROUP BY ds.buffer_pool, do.object_name, ds.blocks
ORDER BY do.object_name, ds.buffer_pool;
In this test case, there is a difference in the caching mechanisms for the KEEP and RECYCLE buffer caches.

Please let me know if there is a mistake in this test case, other than CACHED_BLOCKS exceeds OBJECT_BLOCKS in some cases.
Jonathan Lewis' commentary with respect to the keep and recycle pools:

I don't think there's a lot of difference - but the positioning of the 'mid-point marker' may be different, there are/were a couple of hidden parameters about that.

The most significant thing is that when you create read consistent copies of blocks from the KEEP pool, they will (usually, and depending on version) be created in the RECYCLE pool to avoid wasting space in the KEEP pool.
 
Pool Segments
Buffered Blocks Query
Status Description
cr Consistent read
free Not currently in use
irec In instance recovery mode
mrec In media recovery mode
read Being read from disk
scur Shared current
xcur Exclusive
set linesize 121
col owner format a20
col object_name format a30
col object_type format a15

SELECT b.inst_id, do.owner, do.object_name, do.object_type, COUNT(b.block#) "Cached Blocks", ds.buffer_pool, b.status
FROM gv$bh b, dba_objects_ae do, dba_segments ds
WHERE b.OBJD = do.data_object_id
AND do.object_name = ds.segment_name
AND do.owner = 'UWCLASS'
GROUP BY b.inst_id, do.owner, do.object_name, do.object_type, ds.buffer_pool, b.status
ORDER BY 2, 3, 1;

Related Topics
 

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved