一、设置KEEP池

SYS@zcs11G>  alter system set db_keep_cache_size=32m scope=spfile;

shutdown immediate;

show parameter db_keep

--db_keep_cache_size                   big integer 32M

二、查看数据库中已经有那些表被标记为热点表

SYS@zcs11G> select '--',owner,segment_type,segment_name,buffer_pool from dba_segments where buffer_pool != 'DEFAULT';

-- no rows selected

三、设置t11表的storage(buffer_pool keep)属性

SYS@zcs11G>  drop table t11 purge;

create table t11 as select level id,lpad('z',4000,'z') name from dual  connect by level <= 5000;

alter table t11 storage(buffer_pool keep);

select '--',bytes/1024/1024,'MB' from dba_segments where segment_name='T11';

--              40 MB

四、查看是否在缓存keep中

SYS@zcs11G>  set pagesize 0

set autotrace off

col segment_name for a12

select '--',segment_name,buffer_pool from dba_segments where buffer_pool != 'DEFAULT';

-- T11           KEEP

SYS@zcs11G>  set linesize 999

select '--',a.status, count(*)  from V$BH a, user_objects b  

where a.objd=b.object_id and object_name='T11' group by object_name, a.status;

-- xcur             5001   T11中的数据已经全部放入KEEP池

五、体会keep池的优势

SYS@zcs11G>  set autotrace trace statistics;

select count(*) from t11;

Statistics

----------------------------------------------------------

       140  recursive calls

         0  db block gets

      5090  consistent gets

      5000  physical reads

SYS@zcs11G>  select count(*) from t11;

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

      5008  consistent gets

         0  physical reads