dba常用sql－2(转) Rounders | 06 八月, 2007 16:39 常用sql2 //统计每个当前用户的命中率 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;[@more@]