ORACLE11G中大表不能放入KEEP池中

把一个大表放入KEEP池中，但却失败。是个ORACLE11G的BUG：

 

Keep pool does`t work immediately on big tables

The KEEP buffer pool does not work for large keep objects.
This occurs because tables with size >10% of the cache size are treated
as 'large tables' for their reads and this results in execution of a new
_SERIAL_DIRECT_READ path in 11g.

Patch 8897574 solved this issue.

 

打上补丁后对表执行全表扫：

 

SQL> select /*+ full(t) */ count(0) from t1 t;

  COUNT(0)
----------
 4050030

 

表已经在KEEP池中：

 

Select decode(pd.bp_id,1,'KEEP', 2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K
SUBCACHE','UNKNOWN') subcache,
       bh.object_name,
       bh.blocks
  from x$kcbwds ds,
       x$kcbwbpd pd,
       (select /*+ use_hash(x) */
         set_ds, o.name object_name, count(*) BLOCKS
          from obj$ o, x$bh x
         where 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 and pd.bp_id=1;

 
SUBCACHE     OBJECT_NAME  BLOCKS
------------ ------------------ ----------
KEEP        T1         4858