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 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 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 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);