statspack中Library Hit是如何計算的？

透過測試發現Library Hit 是pin的命中率！

首先透過statspack產生的report中

Library Hit %: 79.58

下面看看Library Hit 是如何計算出來的，當然大家也可以直接去看spreport script!

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