library hit %: 79.58

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