keep pool

参考《oracle性能优化实务》

KEEP POOL的主要作用是最大限度减少IO存放最常用的对象减少DEFAULT POOL对敏感数据的影响什么对象适合放入KEEP POOL常用的表和索引相对静态的数据（相对静态的小表）变动比较大，但是对性能要求比较高的表如何启用KEEP POOL设置db_keep_cache_sizealert table|index ... storage (buffer_pool keep);（alter table cacahe只是把表放到LRU链的热端，不是放到keep池里）哪些对象可以放进keep poolTCH是访问次数SQL> Select   2    decode(pd.bp_id,1, 'KEEP', 2,'RECYCLE', 3,'DEFAULT', 4, '2K SUBCACHE',     3    5,'4K SUBCACHE', 6,'8K SUBCACHE',   4    7, '16K SUBCACHE', 8,'32K SUBCACHE', 'UNKNOWN') subcache,   5    bh.object_name object_name,  6    bh.blocks,  7    tch   8  from   9    x$kcbwds ds, 10    x$kcbwbpd pd, 11   (select /*+ use_hash(x) */ set_ds,                                                                12  o.name object_name, count(*) BLOCKS,tch                                                                         13  from obj$ o, x$bh x where o.dataobj# = x.obj                                                                    14  and x.state !=0 and o.owner# !=0                                                                                15  group by set_ds,o.name,tch) bh where ds.set_id >= pd.bp_lo_sid                                                  16  and ds.set_id <= pd.bp_hi_sid and pd.bp_size != 0 and ds.addr=bh.set_ds                                         17  order by subcache,object_name;                                                                                 SUBCACHE     OBJECT_NAME                        BLOCKS        TCH------------ ------------------------------ ---------- ----------DEFAULT      AQ$_MGMT_LOADER_QTABLE_L                1          1DEFAULT      AQ$_MGMT_NOTIFY_INPUT_QTABLE_T          1          1DEFAULT      AQ$_MGMT_NOTIFY_QTABLE_L                1          1DEFAULT      AQ$_MGMT_PAF_MSG_QTABLE_1_T             1          1DEFAULT      AQ$_MGMT_PAF_MSG_QTABLE_2_T             1          1DEFAULT      AQ$_MGMT_TASK_QTABLE_T                  1          1DEFAULT      AQ$_QUEUES                              1          2DEFAULT      AQ$_QUEUES                              1          5DEFAULT      AQ$_QUEUES_CHECK                        1          3DEFAULT      AQ$_QUEUE_TABLES                        1          1DEFAULT      AQ$_QUEUE_TABLES                        1          4