statspack中的Library Hit是如何计算的

来源：赛迪网    作者：Reginald

通过本文的测试大家可以发现Library Hit是pin的命中率。

首先在通过statspack产生至report中。

Library Hit %: 79.58

从下面的示例中大家可以看到Library Hit 是如何计算出来的：

SQL> select snap_id,gets,gethits,pins,pinhits from 
stats$librarycache where snap
_id in (1,2);
SNAP_ID GETS GETHITS PINS PINHITS
---------- ---------- ---------- ---------- ----------
1 1483 1385 1920 1780
1 271 253 710 688
1 167 4 187 24
1 0 0 0 0
1 0 0 0 0
1 0 0 0 0
1 0 0 0 0
1 0 0 0 0
1 7767 2061 102328 97310
1 10218 5604 23100 15402
1 81 63 902 878
2 1483 1385 1921 1781
2 271 253 710 688
2 167 4 187 24
2 0 0 0 0
2 0 0 0 0
2 0 0 0 0
2 0 0 0 0
2 0 0 0 0
2 7775 2061 105886 100586
2 10707 6002 24600 16151
2 81 63 902 878

已选择22行。

SQL> select sum(gets),sum(gethits),sum(pins),
sum(pinhits) from stats$librarycach
e where snap_id=2;
SUM(GETS) SUM(GETHITS) SUM(PINS) SUM(PINHITS)
---------- ------------ ---------- ------------
20484 9768 134206 120108

SQL> select sum(gets),sum(gethits),sum(pins),
sum(pinhits) from stats$librarycach
e where snap_id=1;
SUM(GETS) SUM(GETHITS) SUM(PINS) SUM(PINHITS)
---------- ------------ ---------- ------------
--首先看看是不是在计算library hit时考虑了library lock,
通过下面计算发现没有包括

19987 9370 129147 116082

SQL> select 100*((9768 -9370 )+(120108 - 116082))/
((20484 - 19987)+(134206 -1291
47 )) from dual;
100*((9768-9370)+(120108-116082))/((20484-19987)+(134206-129147))
---------------------------------------------------------
79.6256299

－－完全利用pin来验证一下发现没有问题！

SQL> select 100*(120108 - 116082)/(134206 -129147 ) from dual;
100*(120108-116082)/(134206-129147)
-----------------------------------
79.5809449


--======================================

statspack package中计算library hit的函数如下：

function LIBRARYCACHE_HITRATIO RETURN number is

/* Returns Library cache hit ratio for the begin and end (bid, eid)
snapshot id's specified
*/

cursor LH (i_snap_id number) is
select sum(pins), sum(pinhits)
from stats$librarycache
where snap_id = i_snap_id
and dbid = db_ident
and instance_number = inst_num;

bpsum number;
bhsum number;
epsum number;
ehsum number;

begin

if not LH%ISOPEN then open LH (bid); end if;
fetch LH into bpsum, bhsum;
if LH%NOTFOUND then
raise_application_error
(-20100,'Missing start value for stats$librarycache');
end if; close LH;

if not LH%ISOPEN then open LH (eid); end if;
fetch LH into epsum, ehsum;
if LH%NOTFOUND then
raise_application_error
(-20100,'Missing end value for stats$librarycache');

end if; close LH;

return (ehsum - bhsum) / (epsum - bpsum);

end LIBRARYCACHE_HITRATIO;


－－＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
大家可以发现上面cursor LH (i_snap_id number) is
select sum(pins), sum(pinhits)
from stats$librarycache
的确只是计算了pin