一个小表,
记录数 < 100,
表名 TAB
修改放入内存的方式:
alter table TAB storage (buffer_pool keep);
(原文链接 http://ddbiz.com/?p=197)
那么这个表是在什么时候放入缓存的呢?
?1. 需要数据库重起
?2. alter后第一次访问这个表时,该表就被放入缓存
续:
1. 察看系统中有哪些数据被钉入内存:
col owner format a15
col segment_name format a25
col buffer_pool format a15
select owner, segment_type, segment_name, buffer_pool
from dba_segments
where buffer_pool != 'KEEP'
dba_segments.buffer_pool 取值范围为3个: 'DEFAULT', 'KEEP', 'RECYCLE
2. 当前系统的数据访问命中率:
select 1 - ((physical.value - direct.value - lobs.value) / logical.value) "Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.name = 'physical reads'
and direct.name='physical reads direct'
and lobs.name='physical reads direct (lob)'
and logical.name='session logical reads';