Buffer Cache缓存对象

前言

  书接上回：Insert 引起的 db file sequential read 文章讲到解决的思路可以将索引缓存到keep buffer cache中，于是本文章做了个简单的Buffer Cache整理。

Buffer Cache里的缓冲池：

Default、Keep和Recycle三个子池会共享Buffer Cache大小

• 默认：DEFAULT buffer cache 此池始终存在。它相当于没有保留池和回收池的实例的缓冲区高速缓存，可通过DB_CACHE_SIZE 参数进行配置。

• 保留：KEEP buffer cache 此池用于保留内存中可能要重用的对象。将这些对象保留在内存中可减少 I/O操作。通过使池的大小大于分配给该池的各个段的总大小，可以将缓冲区保留在此池中。这意味着缓冲区不必执行过期处理。保留池可通过指定DB_KEEP_CACHE_SIZE参数的值来配置。

• 回收：RECYCLE buffer cache 此池用于内存中重用几率很小的块。回收池的大小要小于分配给该池的各个段的总大小。这意味着读入该池的块经常需要在缓冲区内执行过期处理。回收池可通过指定DB_RECYCLE_CACHE_SIZE 参数的值来配置。(本人基本没用过，暂不在此处做测试)

注：保留池或回收池中的内存不是默认缓冲池的子集。

默认池：DEFAULT buffer cache

测试一：缓存对象

SQL> -- 查看对像大小：
SQL> select segment_name, round(sum(bytes) / 1024 / 1204 / 1024,2)|| 'GB' as size_GB
  2    from dba_segments
  3   where segment_name in
  4   ('PK_ID')
  5  group by segment_name;
SEGMENT_NAME              SIZE_GB
------------------------ ---------------
PK_ID                     3.83GB

• 查看对象缓存情况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 set_ds, o.name object_name, count(*) BLOCKS
          from obj$ o, x$bh x
         where o.name in  ('PK_ID')
           and 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
 order by pd.bp_id ,bh.blocks;
-- 已经缓存在DEFAULT池
SUBCACHE     OBJECT_NAME      BLOCKS
------------ ---------------- ----------
DEFAULT      PK_ID           	1

• 刷新buffer_cache

alter system flush buffer_cache;

• 已经清空
  
• 57582个物理读
  
• 再查buffer_cache已经缓存进来
  
• 已经没有物理读
  

保留池：KEEP buffer cache

  不是把对象保持在keep pool，就会一直缓存在keep pool， 如果Keep池设置较小，而我们设置的Keep住对象较大，同样会有Keep池的对象被Age Out出去。在keep pool中对象永远是先进先出。
  如果不能把对象全部keep，一半在内存一半被Age Out出去。还是会产生大量的逻辑读，这种效果会大大打折扣，所以要不全部keep，要不就不用keep
  默认的情况下db_keep_cache_size=0，未启用，如果想要启用，需要手工设置db_keep_cache_size的值，设置了这个值之后 db_cache_size 会减少。

测试二：增大db_keep_cache_size会相应减小db_cache_size

• 查看 db_cache_size：（DEFAULT buffer cache） 大小：

SQL> col COMPONENT for a30
SQL> select COMPONENT,CURRENT_SIZE/1024/1024||'MB' MB from V_$SGA_DYNAMIC_COMPONENTS where COMPONENT in ('DEFAULT buffer cache','KEEP buffer cache');
COMPONENT                      MB
------------------------------ ------------------------------------------
DEFAULT buffer cache           14656MB
KEEP buffer cache              0MB
-- 或：
SQL> SELECT x.ksppinm NAME,y.ksppstvl/1024/1024 || 'MB' VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%__db_cache_size%';
NAME             VALUE     DESCRIB
---------------- --------- ------------------------------------------------------------------
__db_cache_size  14656MB   Actual size of DEFAULT buffer pool for standard block size buffers

• 设置：KEEP buffer cache，设置10G是为展示效果

-- RAC 指定大小：
SQL> alter system set db_keep_cache_size=10G scope=both sid='rac1';
System altered.
-- 再次查看：把db_cache_size的内存分出了10G给了KEEP buffer cache。
SQL>  select COMPONENT,CURRENT_SIZE/1024/1024||'MB' MB from V_$SGA_DYNAMIC_COMPONENTS where COMPONENT in ('DEFAULT buffer cache','KEEP buffer cache');

COMPONENT                      MB
------------------------------ ------------------------------------------
DEFAULT buffer cache           4416MB
KEEP buffer cache              10240MB

测试三：将对像定义到 keep buffer cache

SQL> conn two/two
Connected.
SQL> create table t1 as select * from all_tables;
Table created.
-- 将T1 定义到keep
SQL> alter table t1 storage(buffer_pool keep);
Table altered.
SQL> select table_name,buffer_pool from user_tables where table_name='T1';
TABLE_NAME                     BUFFER_
------------------------------ -------
T1                             KEEP
-- 切换到sys
SQL> conn / as sysdba
Connected.
SQL> select decode(pd.bp_id,1,'KEEP',
  2                2,'RECYCLE',
  3                3,'DEFAULT',
  4                4,'2K SUBCACHE',
  5                5,'4K SUBCACHE',
  6                6,'8K SUBCACHE',
  7                7,'16K SUBCACHE',
  8                8,'32KSUBCACHE',
  9                'UNKNOWN') subcache,
 10         bh.object_name,
 11         bh.blocks
 12    from x$kcbwds ds,
 13         x$kcbwbpd pd,
 14         (select set_ds, o.name object_name, count(*) BLOCKS
 15            from obj$ o, x$bh x
 16           where o.name in  ('T1')
 17             and o.dataobj# = x.obj
 18             and x.state != 0
 19             and o.owner# != 0
 20           group by set_ds, o.name) bh
 21   where ds.set_id >= pd.bp_lo_sid
 22     and ds.set_id <= pd.bp_hi_sid
 23     and pd.bp_size != 0
 24     and ds.addr = bh.set_ds
 25   order by pd.bp_id ,bh.blocks;
-- 当时显示在DEFAULT,因为创建时就已经缓存到DEFAULT
SUBCACHE     OBJECT_NAME                        BLOCKS
------------ ------------------------------ ----------
DEFAULT      T1                                      1
DEFAULT      T1                                      1
DEFAULT      T1                                      1
--显示执行计划的统计信息，不显示执行计划内容
SQL> set autotrace traceonly statistics;
SQL> select count(*) from two.t1;
Statistics
----------------------------------------------------------
         72  recursive calls
          0  db block gets
         24  consistent gets
          4  physical reads --这里的物理读：当Oracle从标记的KEEP池拿数据的时候发现没有数据，就直接从磁盘读取了。并不会再到DEFAULT里取数据。
          0  redo size
        526  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
-- 关闭 trace
SQL> set autotrace off;
SQL> select decode(pd.bp_id,1,'KEEP',
  2                2,'RECYCLE',
  3                3,'DEFAULT',
  4                4,'2K SUBCACHE',
  5                5,'4K SUBCACHE',
  6                6,'8K SUBCACHE',
  7                7,'16K SUBCACHE',
  8                8,'32KSUBCACHE',
  9                'UNKNOWN') subcache,
 10         bh.object_name,
 11         bh.blocks
 12    from x$kcbwds ds,
 13         x$kcbwbpd pd,
 14         (select set_ds, o.name object_name, count(*) BLOCKS
 15            from obj$ o, x$bh x
 16           where o.name in  ('T1')
 17             and o.dataobj# = x.obj
 18             and x.state != 0
 19             and o.owner# != 0
 20           group by set_ds, o.name) bh
 21   where ds.set_id >= pd.bp_lo_sid
 22     and ds.set_id <= pd.bp_hi_sid
 23     and pd.bp_size != 0
 24     and ds.addr = bh.set_ds
 25   order by pd.bp_id ,bh.blocks;
-- t1的块重新缓存到KEEP一份，  DEFAULT并不会直接被age out 出去 。   
SUBCACHE     OBJECT_NAME                        BLOCKS
------------ ------------------------------ ----------
KEEP         T1                                      1
KEEP         T1                                      1
KEEP         T1                                      1
KEEP         T1                                      1
DEFAULT      T1                                      1
DEFAULT      T1                                      1
DEFAULT      T1                                      1
7 rows selected.

• 重建刷新缓存
  

对像 buffer cache 语法：

1、BUFFER_POOL 子句用于定义对象的默认缓冲池（不会直接刷到对应缓存池里），未明确设置缓冲池的对象中的块将进入默认缓冲池。
2、更改对象的默认缓冲池时，已缓存的块会一直保留在其当前缓冲区中，直到正常缓冲区管理活动将它们清除为止。
3、从磁盘读取的块将被放置在为该段新指定的缓冲池中。
4、由于多个缓冲池被分配给某一个段，所以有多个段的对象可以将块放置在多个缓冲池中。
5、语法为：BUFFER_POOL [KEEP | RECYCLE | DEFAULT]

SQL>  CREATE INDEX ind_name STORAGE (BUFFER_POOL KEEP);
SQL>  ALTER TABLE tab_name STORAGE (BUFFER_POOL RECYCLE);
SQL>  ALTER INDEX ind_name STORAGE (BUFFER_POOL DEFAULT);

• 取消keep:重新定义到默认池（default）即可：

SQL>  alter table tab_name storage(buffer_pool default);