以下转自:http://www.itpub.net/thread-1809208-2-1.html 作者:zcs0237
一、设置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 |