Oracle有三种类型的buffer cache，分别是default、keep、recycle。分别用三个参数db_cache_size、db_keep_cache_size和db_recycle_cache_size控制三种buffer cache的大小。我们可以把访问热点的表放到keep类型的cache中，以防止这部分数据交换出内存，提高读取性能。关于如何确定什么对象放到keep池，Oracle文档上也提供了两个方法：

Method1：

COLUMN OBJECT_NAME FORMAT A40
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
     FROM DBA_OBJECTS o, V$BH bh
    WHERE o.DATA_OBJECT_ID = bh.OBJD
      AND o.OWNER         != 'SYS'
    GROUP BY o.OBJECT_NAME
    ORDER BY COUNT(*);

OBJECT_NAME                              NUMBER_OF_BLOCKS
---------------------------------------- ----------------
OA_PREF_UNIQ_KEY                                        1
SYS_C002651                                             1
..
DS_PERSON                                              78
OM_EXT_HEADER                                         701
OM_SHELL                                            1,765
OM_HEADER                                           5,826
OM_INSTANCE                                        12,644

 Method2：

1. Find the Oracle internal object number of the segment by entering the following query:

SELECT DATA_OBJECT_ID, OBJECT_TYPE
  FROM DBA_OBJECTS
 WHERE OBJECT_NAME = UPPER('segment_name');
Because two objects can have the same name (if they are different types of objects), use the OBJECT_TYPE column to identify the object of interest.

2. Find the number of buffers in the buffer cache for SEGMENT_NAME:

SELECT COUNT(*) BUFFERS
  FROM V$BH
 WHERE OBJD = data_object_id_value;
where data_object_id_value is from step 1.

3. Find the number of buffers in the instance:

SELECT NAME, BLOCK_SIZE, SUM(BUFFERS)
  FROM V$BUFFER_POOL
 GROUP BY NAME, BLOCK_SIZE
 HAVING SUM(BUFFERS) > 0;
Calculate the ratio of buffers to total buffers to obtain the percentage of the cache currently used by SEGMENT_NAME:

% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]

但是对V$BH视图的监控可能对系统造成不可预知的后果，所以我们在生产环境中一般通过AWR报告获取段的逻辑读和物理读信息进行取舍。

要调整db_keep_cache_size参数，可以直接在线调整，但是有时候SGA没有足够的空间分配给KEEP池，这时就必须通过修改SPFILE参数重启实例进行设置。以下是在RAC环境中的一次调整：

SQL> alter system set db_keep_cache_size=3584M scope=both sid='*';
alter system set db_keep_cache_size=3584M scope=both sid='*'
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance
SQL> alter system set db_keep_cache_size=3584M scope=both;
alter system set db_keep_cache_size=3584M scope=both
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
erpdb2

SQL> alter system set db_keep_cache_size=3584M scope=both sid='erpdb2';
alter system set db_keep_cache_size=3584M scope=both sid='erpdb2'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

在线调整必须每个节点单独进行，但这里SGA没有足够的剩余空间， 看来必须要通过spfile进行调整了

SQL> show parameter db_keep

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

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2  FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
  4  /
Enter value for par: db_cache_size
old   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%db_cache_size%'

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
__db_cache_size
12566134784
Actual size of DEFAULT buffer pool for standard block size buffers

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
db_cache_size
0
Size of DEFAULT buffer pool for standard block size buffers

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     TRUE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 20G
sga_target                           big integer 20G

SQL> alter system set db_keep_cache_size=3584M scope=spfile sid='*';

System altered.

SQL> alter table banping.mpaymentappl storage(buffer_pool keep);

Table altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size                  2111264 bytes
Variable Size            2214594784 bytes
Database Buffers         1.9243E+10 bytes
Redo Buffers               14663680 bytes
Database mounted.
Database opened.
SQL> show parameter keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer     7
db_keep_cache_size                   big integer 2560M

奇怪的事情发生了，这里的db_keep_cache_size参数仍然是调整之前的值，spfile参数在这个节点竟然没有生效，其他的节点是正常的。而系统确实是共用一个spfile的。那么再针对当前节点做一下调整：

SQL> alter system set db_keep_cache_size=3584M scope=both sid='erpdb2';
alter system set db_keep_cache_size=3584M scope=both sid='erpdb2'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

SQL> alter system set db_keep_cache_size=3584M scope=spfile sid='erpdb2';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size                  2111264 bytes
Variable Size            2147485920 bytes
Database Buffers         1.9311E+10 bytes
Redo Buffers               14663680 bytes
Database mounted.
Database opened.
SQL> show parameter keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer     7
db_keep_cache_size                   big integer 3584M

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2  FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
  4  /
Enter value for par: db_cache_size
old   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%db_cache_size%'

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
__db_cache_size
15535702016
Actual size of DEFAULT buffer pool for standard block size buffers

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------

db_cache_size
0
Size of DEFAULT buffer pool for standard block size buffers
SQL> exit

在SGA自动管理模式下，Oracle使用双下划线开头的隐含参数__db_cache_size参数控制了Default池的大小，而该参数是无法手动修改的。