--
-- Author: Ahbaid Gaffoor
-- Date: Tuesday 25th February 2003
-- File: hitratios.sql
--
-- Purpose: Get Crucial Cache Hit Ratios
--
-- License: You may reuse this software at will provided you quote and retain this header
--
-- Disclaimer: No warranty is provided with this software, no liability or guarantees
-- are given in any form
--
set feedback off
set serveroutput on size 400000
declare
bc_physical_reads number;
bc_consistent_gets number;
bc_db_block_gets number;
bc_hit_ratio number;
dd_hit_ratio number;
lc_hit_ratio number;
procedure print (s varchar2) is
begin
dbms_output.put_line(s);
end;
procedure cr is
begin
print(chr(10));
end;
begin
--
-- Calculate Buffer Cache Hit Ratio
--
-- V$SYSSTAT - physical reads, consistent gets, db block gets
--
select sum(decode(name,'physical reads',abs(value),0)) into bc_physical_reads from v$sysstat;
select sum(decode(name,'consistent gets',abs(value),0)) into bc_consistent_gets from v$sysstat;
select sum(decode(name,'db block gets',abs(value),0)) into bc_db_block_gets from v$sysstat;
bc_hit_ratio := round((1 - ( bc_physical_reads / (bc_consistent_gets + bc_db_block_gets) ))*100,2);
--
-- Calculate Data Dictionary Cache Hit Ratio
--
-- V$ROWCACHE - gets, getmisses
--
select round ( ( (sum(gets)-sum(getmisses)) / sum(gets) ) * 100,2) into dd_hit_ratio from v$rowcache;
--
-- Calculate Library Cache Hit Ratio
--
-- V$LIBRARYCACHE - pins, reloads
--
select round ( ( (sum(pins)-sum(reloads)) / sum(pins) ) *100,2) into lc_hit_ratio from v$librarycache ;
--
-- Print Results
--
cr;
print(upper(SYS_CONTEXT('USERENV','DB_NAME'))||' Hit Ratios for: '||to_char(sysdate,'Day dth Month YYYY, HH24:MI:SS'));
print(rpad('=',80,'='));
print ('Buffer Cache Hit Ratio: '||bc_hit_ratio||'%');
print ('Data Dictionary / Row Cache Hit Ratio: '||dd_hit_ratio||'%');
print ('Library Cache Hit Ratio: '||lc_hit_ratio||'%');
cr;
end;
/
set feedback on
|  |
