文档介绍：oracle
将公共表数据常驻oracle数据缓存中
1、首先评估公共数据表数据大小,决定db_keep_cache_size大小
alter system set db_keep_cache_size=100M scope=both;
2、修改表格属性,让它可以常驻内存
alter table tbtest storage(buffer_pool keep);
select table_name,tablespace_name,cache from user_tables;(此时cache为YES)
3、如果需要取消该属性,执行下面语句
alter table tbtest storage(buffer_pool default) ;
[***@more@]
SQL> ponent,current_size from v$ponents ponent='KEEP buffer cache';
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
KEEP buffer cache 12582912
这里keep pool 10M
查看keep pool剩余大小
SQL> select ,um_repl "total buffers", "free buffers" from x$kcbwds a, v$buffer_pool p
where = and ='KEEP';
NAME total buffers free buffers
-------------------- ------------- ------------
KEEP 1497 1497
可以看到没有使用过keep 池
ponent, current_size, min_size, max_size from v$ponents ponent in ('DEFAULT buffer cache','KEEP buffer cache','RECYCLE buffer cache');
--查看放入Keep的对象
select segment_name from dba_segments where BUFFER_POOL = 'KEEP';
SEGMENT_NAME
----------------------------------------
T1
--查看表的大小
select bytes/1024/1024||'M' from dba_segments where segment_name='T1';
--查看db_keep_cache_size实际占用空间
SELECT SUBSTR (SUM () * 8192 / 1024 / 1024, 1, 5) || 'M'Total_Size
FROM ( SELECT , COUNT (*)NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh,dba_segments dd
WHERE =
AND =
AND =
AND != 'DEFAULT'
GROUP BY
ORDER BY COUNT (*)) b;
注意事项
sqlserver:
Declare ***@db_id int, ***@tbl_id int
Use DATABASE_NAME
Set ***@db_id = DB_ID('DATABASE_NAME')
Set ***@tbl_id = Object_ID('Department')
pintable (***@db_id, ***@tbl_id)
可将表Department设置为驻留内存。
Declare ***@db_id int, ***@tbl_id int
Use DATABASE_NAME
Set ***@db_id = DB_ID('DATABASE_NAME')
Set ***@tbl_id = Object_ID('Department')
UNpintable (***@db_id, ***@tbl_id)
可将表Depart