oracle性能Statspack使用之命中率調整

　　二、 SGA各部分命中率介紹

　　1、數據緩存區命中率

　　select value from v$sysstat where name='physical reads'

　　select value from v$sysstat where name='physical reads direct'

　　select value from v$sysstat where name='physical reads direct (lob)'

　　select value from v$sysstat where name='consistent gets'

　　select value from v$sysstat where name='db block gets'

　　X= physical reads direct+ physical reads direct (lob)

　　命中率=100-(physical reads-x)/(consistent gets+ db block gets-x)*100

　　通常發現命中率低于90%，則應該考慮增大數據緩沖區。

　　select (100 - (n1.value - n2.value - n3.value) /

　　(n4.value + n5.value - n2.value - n3.value) * 100) as per

　　from (select value from v$sysstat where name = 'physical reads') n1,

　　(select value from v$sysstat where name = 'physical reads direct') n2,

　　(select value

　　from v$sysstat

　　where name = 'physical reads direct (lob)') n3,

　　(select value from v$sysstat where name = 'consistent gets') n4,

　　(select value from v$sysstat where name = 'db block gets') n5

　　2、共享池命中率

　　select sum(pinhits)/sum(pins)*100 "hit radio" from V$librarycache

　　通常共享池命中率低于95%，考慮增加共享池內存

　　3、排序

　　select name,value from V$sysstat where name like '%sort%'

文章來源于領測軟件測試網 http://www.anti-gravitydesign.com/