计算缓冲区命中率
select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio from v$sysstat phy ,v$sysstat cur, v$sysstat con where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets'; 缓冲区命中率在95%-90%为适当分配，90%-80%为关注调整，超过95%属于过度分配，应该减少。 每个会话的缓冲区命中率 select phy.sid,s.username,1-(phy.value)/(cur.value + con.value) "buffer_hit_ratio" from v$sesstat phy , v$sesstat cur , v$sesstat con ,v$statname s1 , v$statname s2 , v$statname s3 ,v$session s where s1.name='physical reads' and s2.name='db block gets' and s3.name='consistent gets' and phy.statistic#=s1.STATISTIC# and cur.statistic#=s2.STATISTIC# and con.statistic#=s3.STATISTIC# and cur.value<>0 and con.value<>0 and phy.sid=cur.sid and phy.sid=con.sid and phy.sid=s.sid; 查看个体缓冲池的命中率 select name,block_size,round((1-(physical_reads/(db_block_gets + consistent_gets)))*100) ||'%' ratio from v$buffer_pool_statistics where (db_block_gets + consistent_gets)>0; 将表缓存到缓存中 1 alter table departments storage (buffer_pool_keep); Alter table departments cache; Alter table departments nocache; 2 select /*+ cache(categories) */ * from categories; select /*+ nocache(categories) */ * from categories; 二、调整日志缓冲区 从v$sysstat 获取与重做日志缓冲区相关的所有统计 select name,value from v$sysstat where name like '%redo%'; 1 redo synch writes 1635661 2 redo synch time 226656 3 redo entries 45379291 4 redo size 12717224856 5 redo buffer allocation retries 2978 重做缓冲区分配重试 必须接近0 6 redo wastage 958088764 7 redo writer latching time 3997 8 redo writes 3527741 9 redo blocks written 27571439 10 redo write time 379533 11 redo log space requests 3718 重做日志空间请求 必须接近0 12 redo log space wait time 5019 重做日志空间等待时间必须接近0 13 redo log switch interrupts 0 14 redo ordering marks 317 日志缓冲区空间等待事件 select event,total_waits from v$system_event where event like 'log%'; log buffer space 接近0，如果缓冲区很大，仍不接近0，可能是LGWR不够快，I/O争用或者检查点太频繁造成。 日志缓冲区请求与记录项比率 select (r.value*5000)/e.value "redo requests/entries ratio" from v$sysstat r,v$sysstat e where r.name='redo log space requests' and e.name='redo entries'; 应该非常接近0 日志缓冲区重试与记录项比率 select (r.value*5000)/e.value "redo retries/entries ratio" from v$sysstat r,v$sysstat e where r.name='redo buffer allocation retries' and e.name='redo entries'; 应该小于1%