数据库参数

–不用关闭和重启数据库就可以修改的参数
select v.name,v.value,v.isdefault,v.isses_modifiable,v.issys_modifiable
from v$parameter v where v.isses_modifiable!=’FALSE’ OR v.issys_modifiable!=’FALSE’; –访问内存要比读取磁盘快得多 –查询数据字典缓存的命中率 select ((1-(Sum(GetMisses)/(Sum(Gets)+Sum(GetMisses))))*100) “Hint Rate” from V$rowcache where Gets+GetMisses <> 0;
–监控库缓存重载率（接近0）
select Sum(PINS) “HITS” ,SUM(RELOADS) “MISS”,((SUM(RELOADS)/SUM(PINS))*100) “RELOAD %” FROM V$LIBRARYCACHE; –缓存命中率（接近100） select SUM(PINS) “HITS”,SUM(RELOADS) “MISSES”,SUM(PINS)/SUM(PINS)+SUM(RELOADS) “HIT RATIO” FROM V$LIBRARYCACHE;
–设置缓存和共享池的建议
–如果字典缓存命中率很低(低于95%),可以考虑增大shared_pool_size.
–如果库缓存率很高(>1%),可以考虑增大shared_pool_size
–创建一个表,在访问时即保存在keep池中
create table state_list (state_abbrev varchar2(2),state_desc varchar2(25)) Storage (buffer_pool keep);
select * from state_list;
–将该表保存在recycle池中
alter table state_list storage (buffer_pool recycle);
–将表重新改到keep池中
alter table state_list storage (buffer_pool keep);
–查看keep池中的磁盘和内存情况
select physical_reads “Disk read”,db_block_gets + consistent_gets “MEMORY READS”
FROM V$BUFFER_POOL_STATISTICS WHERE NAME=’KEEP’;