//统计每个当前用户的命中率
SELECT USERNAME,
V$SESSION.SID,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
100*(CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS)/(CONSISTENT_GETS+BLOCK_GETS) HitRatio
FROM V$SESSION,V$SESS_IO
WHERE V$SESSION.SID=V$SESS_IO.SID
AND (V$SESS_IO.CONSISTENT_GETS+V$SESS_IO.BLOCK_GETS)>0
AND V$SESSION.USERNAME IS NOT NULL；


//累积命中率
select sum(decode(name,'consistent gets',value,0)) consistent,
sum(decode(name,'db blocks gets',value,0)) dbblockgets,
sum(decode(name,'physical reads',value,0)) physrds,
round(((sum(decode(name,'consistent gets',value,0))+
sum(decode(name,'db blocks gets',value,0))-
sum(decode(name,'physical reads',value,0)))/
(sum(decode(name,'consistent gets',value,0))+
sum(decode(name,'db blocks gets',value,0))))*100,2) Hitratio
from v$SYSSTAT;



//高速缓存命中率
select name,value from v$sysstat where name in('physical reads','db block gets','consistent gets');

//计算数据库缓存命中率
select sum(pins-reloads)/sum(pins) from v$librarycache;

//计算字典缓存命中率
select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;

//调试回滚段
select sum(waits),sum(gets),sum(waits)/sum(gets) from v$rollstat;
select shrinks from v$rollstat;

//redo调试
select name,value from v$sysstat where name='redo buffer allocation retries';

//查看每个会话打开的游标数
select sid,count(*) from v$open_cursor
group by sid;