# oracle命中率查询

1.  缓冲区命中率:

select (1-(sum(decode(name, 'physical reads',value,0))/(sum(decode(name, 'db block gets',value,0))
+sum(decode(name,'consistent gets',value,0))))) * 100 "Hit Ratio"
from v\$sysstat;

2.数据字典缓存命中率：

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v\$rowcache;

3.库缓存命中率：

select Sum(Pins)/(Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio" from V\$LibraryCache;

4.PGA内存排序命中率

select a.value "Disk Sorts", b.value "Memory Sorts",round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2)"Pct Memory Sorts" from v\$sysstat a, v\$sysstat b where   a.name = 'sorts (disk)'and b.name = 'sorts (memory)';

5. 空闲的数据缓冲区的比例

select decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state) "BLOCK STATUS",count(*) from x\$bh group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state);

6. 最浪费内存的前10个语句占所有语句的比例

select sum(pct_bufgets) "Percent" from (select rank() over ( order by buffer_gets desc ) as rank_bufgets,to_char(100 * ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v\$sqlarea ) where rank_bufgets < 11;

7.调整滥用磁盘读操作的主要语句

select disk_reads, substr(sql_text,1,4000) from v\$sqlarea  order by disk_reads desc;
8.表和与它们相关联的索引应当放置在不同的物理磁盘上，以便减少文件I/O。

• 博文量
17
• 访问量
20419