Just Learned - What happened, when segment bigger than KEEP pool?

I read idea "KEEP Pool – What is Wrong with this Quote?" That helped to think much about KEEP pool. What is good idea for it? If you read Expert Oracle Database Architecture book or this link
- Default pool: The location where all segment blocks are normally cached. This is the original¿and previously only¿buffer pool.
- Keep pool: An alternate buffer pool where by convention you would assign segments that were accessed fairly frequently, but still got aged out of the default buffer pool due to other segments needing space.
- Recycle pool: An alternate buffer pool where by convention you would assign large segments that you access very randomly, and which would therefore cause excessive buffer flushing but would offer no benefit because by the time you wanted the block again it would have been aged out of the cache. You would separate these segments out from the segments in the default and keep pools so that they would not cause those blocks to age out of the cache.

I had a idea - What happened, when segment bigger than KEEP pool?

SQL> show parameter db_keep_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size big integer 100M

SQL> alter system flush buffer_cache;

System altered.

SQL> alter table demo.tb_data storage(buffer_pool keep);

Table altered.

SQL> set autot on
SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
100500

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 376 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 103K| 376 (1)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1339 consistent gets
1334 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autot off
SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds
/

SUBCACHE OBJECT_NAME BLOCKS
------------ ------------------------------ ----------
KEEP TB_DATA 1334

SQL> set autot on
SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
100500

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 376 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 103K| 376 (1)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1339 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

After first query, data block was kept in KEEP Pool. When you query again, database will load from KEEP Pool. Then I tested with small KEEP Pool.

SQL> alter system set db_keep_cache_size=8M;

System altered.

SQL> show parameter db_keep_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size big integer 8M

SQL> select buffer_pool from dba_tables where table_name='TB_DATA' and owner='DEMO';

BUFFER_
-------
KEEP

SQL> alter system flush buffer_cache;

System altered.

SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds
/

no rows selected

SQL> set autot on
SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
100500

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 376 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 103K| 376 (1)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1337 consistent gets
1334 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autot off
SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds
/

SUBCACHE OBJECT_NAME BLOCKS
------------ ------------------------------ ----------
KEEP TB_DATA 306

SQL> set autot on
SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
100500

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 376 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 103K| 376 (1)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1337 consistent gets
1330 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
100500

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 376 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 103K| 376 (1)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1337 consistent gets
1330 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

What happened? From first query, database was kept 306 blocks in KEEP Pool ONLY!!!... When you query again, you will see "physical reads" value (not sure value... it displayed 1330). then changed buffer_pool default

SQL> alter table demo.tb_data storage (buffer_pool default);

Table altered.

SQL> select buffer_pool from dba_tables where table_name='TB_DATA' and owner='DEMO';

BUFFER_
-------
DEFAULT

SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
100500

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 376 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 103K| 376 (1)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
1430 consistent gets
1034 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
100500

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 376 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 103K| 376 (1)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1339 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds
/

SUBCACHE OBJECT_NAME BLOCKS
------------ ------------------------------ ----------
KEEP TB_DATA 306
DEFAULT TB_DATA 1028

Wow!!! ... TB_DATA object was kept in KEEP Pool (306) and DEFAULT Pool (1028). Interesting -) What happened? if changed back buffer_pool keep again.

SQL> select buffer_pool from dba_tables where table_name='TB_DATA' and owner='DEMO';

BUFFER_
-------
DEFAULT

SQL> set autot on
SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
100500

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 376 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 103K| 376 (1)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1339 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter table demo.tb_data storage (buffer_pool keep);

Table altered.

SQL> select count(*) from demo.tb_data;

COUNT(*)
----------
100500

Execution Plan
----------------------------------------------------------
Plan hash value: 143091466
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 376 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TB_DATA | 103K| 376 (1)| 00:00:01 |
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
1428 consistent gets
1330 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds
/

SUBCACHE OBJECT_NAME BLOCKS
------------ ------------------------------ ----------
KEEP TB_DATA 306
DEFAULT TB_DATA 1028

After changed buffer_pool=keep, A query didn't read data from DEFAULT Pool (I think!!!)... because it read "1330 physical reads" again.

However, KEEP Pool is just a buffer cache and had some situation what you should determine more... It's a good idea, if you will separate POOL to keep warm data by using KEEP Pool. One question!!! How many KEEP pool size should you set?
*** From sample, 1334 x db_block_size(8k) ~= 10M ***
*** This is sample, if you use it... you should determine more... ***
KEEP BUFFER POOL Does Not Work for Large Objects on 11g [ID 1081553.1]