dba常用sql－2(轉)

常用sql2[@more@]

//統計每個當前使用者的命中率
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;