-- name       : latch_hit_ratio.sql
-- date       : 14-Nov-2001
-- Author     : Pentest Limited
-- Description: Calculate the latch hit ratio.
-- limitation : needs access to v$latch and v$latchname
--
-- useage     : SQL> @latch_hit_ratio

set pages 50
set feed off
set verify off
set linesize 200

spool latch_hit_ratio.lis

col name head "Name" for a30
col miss head "Miss" for 99.99
col immediate_miss head "I-Miss" for 99.99

select 	substr (l.name,1,30) name,
       	(misses/(gets+.001))*100 miss,
       	(immediate_misses/(immediate_gets+.001))*100 immediate_miss
from 	v$latch l,
     	v$latchname ln
where 	l.latch#=ln.latch#
order by l.name;

spool off

set pages 24
set feed on
set verify on
set lines 80