AWR 中的library hit %命中率为负值

做awr报告时，显示library cache %为负值，下面是statspack 的计算函数，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;

查看本机，发现namespace 为body这列值在snap_id为5225、5226的时候出现异常：

SQL> select pins,pinhits,namespace from WRH$_LIBRARYCACHE where snap_id=5224 and namespace='BODY';

      PINS    PINHITS NAMESPACE
---------- ---------- ---------------
4292787387    2149876 BODY

SQL> c /5224/5225
  1* select pins,pinhits,namespace from WRH$_LIBRARYCACHE where snap_id=5225 and namespace='BODY'
SQL> /

      PINS    PINHITS NAMESPACE
---------- ---------- ---------------
4294482003    3845256 BODY

SQL>
SQL> c /5225/5226
  1* select pins,pinhits,namespace from WRH$_LIBRARYCACHE where snap_id=5226 and namespace='BODY'
SQL> /

      PINS    PINHITS NAMESPACE
---------- ---------- ---------------
   1250584    5581942 BODY

SQL> c /5226/5227
  1* select pins,pinhits,namespace from WRH$_LIBRARYCACHE where snap_id=5227 and namespace='BODY'
SQL> /

      PINS    PINHITS NAMESPACE
---------- ---------- ---------------
   3279083    7611288 BODY

从snap_id=5226开始，PINS  值  突然变小了，这个是跟64-bit hardware有关，当达到4294967296这个值后，计数从零开始了。所以这不算oracle BUG

参考文档：

http://www.orafaq.com/forum/t/183078/0/

 

library_hit.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21601207/viewspace-756389/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21601207/viewspace-756389/