buffer cache缓存对象 -m6米乐安卓版下载

前言

  书接上回：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);