转载于：https://blog.csdn.net/robinson1988/article/details/4712443
题目：
An application repeatedly accesses small lookup tables, causing a lot of physical I/O operations. What do you recommend to minimize this? A. Confifure the nonstandard buffer cache with a buffer size greater than the size of the default buffer cacheB. Increase the size of the shared pool C. Configure the RECYCLE buffer cache and alter the tables to use the RECYCLE cache D. Configure the KEEP buffer cache and alter the tables to use the KEEP cache 答案：D如果某个表是热点表，经常被访问，就应该考虑将其放入 keep buffer cache.防止其被挤出default pool 。从而减少physical read所带来的I/O开销。
默认的情况下 db_keep_cache_size=0,未启用，如果想要启用，需要手工设置db_keep_cache_size的值，设置了这个值之后
db_cache_size 会减少。
并不是我们设置了keep pool 之后 热点表就一定能够缓存在 keep pool ，keep pool 同样也是 由LRU 链表管理的，当keep pool 不够的时候，最先缓存到 keep pool 的对象会被挤出，不过与default pool 中的 LRU 的管理方式不同，在keep pool 中表永远是 从MRU 移动到LRU，不会由于你做了FTS而将表缓存到LRU端，在keep pool中对象永远是先进先出。
以下是测试： 环境12.2.0.1
SQL> select component,current_size from v$sga_dynamic_components
2 where component='KEEP buffer cache';
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
KEEP buffer cache 0
手动分配keep pool
SQL> alter system set db_keep_cache_size=10m;
System altered.
SQL> select component,current_size from v$sga_dynamic_components where component='KEEP buffer cache';
COMPONENT CURRENT_SIZE
-------------------------------------------------------------------------------------------------------------------------------- ------------
KEEP buffer cache 12582912
查看剩余大小：
SQL> select p.name,a.cnum_repl "total buffers",a.anum_repl "free buffers" from x$kcbwds a, v$buffer_pool p where a.set_id=p.LO_SETID and p.name='KEEP';
NAME total buffers free buffers
---------------------------------------- ------------- ------------
KEEP 1464 1464
SQL> create table test_keep_buffer(a number,b varchar2(100));
Table created.
SQL> alter table test_keep_buffer storage (buffer_pool keep);
Table altered.
SQL> insert into test_keep_buffer values(1,'robinson');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> insert into test_keep_buffer values (1,'luobinsen');
1 row created.
SQL> commit;
Commit complete.
SQL> select p.name,a.cnum_repl "total buffers",a.anum_repl "free buffers" from x$kcbwds a, v$buffer_pool p where a.set_id=p.LO_SETID and p.name='KEEP';
NAME total buffers free buffers
---------------------------------------- ------------- ------------
KEEP 1464 1464
我们发现这里并没有变化，不知道为什么。
SQL> select table_name,cache,blocks from dba_tables where owner='SYS' and buffer_pool='KEEP';
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CACHE BLOCKS
---------------------------------------- ----------
TEST_KEEP_BUFFER
Y
如果这里参数为N 的话可以用alter table cache来改变。
总结：如果表经常使用，而且表较小，可以设置 keep pool ，将table 全部 cache 到 keep pool, keep pool 要么 全部 cache 一个table ，要么 不cache ,所以，对于大表来说，如果想要 cache 到 keep pool， 就需要设置 较大的 keep pool ,以容纳 大的 table ,否者就没有作用了 。
————————————————
版权声明：本文为CSDN博主「robinson1988」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/robinson1988/article/details/4712443