Latch Hit Ratios

col name for a36 heading 'Name'
col gets for 999,999,999,999 heading 'Gets'
col misss for 999,999,999 heading 'Misses'
col wtwhr for 999.99 heading 'Willing to|wait HR'
col spgt for 999,999,999 heading 'Spin|Gets'
col spinr for 999.99 heading 'Spin|Ratio'
col imgt for 999,999,999 heading 'Immediate|Gets'
col imms for 999,999,999 heading 'Immediate|Misses'
col nwhr for 999.99 heading 'No Wait|Hit Ratio'
col allev for a5 heading 'Alert|Level'

select name,
gets,
misses misss,
( gets - misses ) / gets wtwhr,
rpad( '*', least( round( ( 10 - 10 * ( gets - misses ) / gets ) ), 5 ), '*' ) allev,
spin_gets spgt,
( spin_gets / decode( misses, 0, 1, misses) ) * 100 spinr,
immediate_gets imgt,
immediate_misses imms,
( immediate_gets - immediate_misses ) / decode( immediate_gets, 0, 1, immediate_gets ) nwhr
from v$latch
where gets > 0
and misses > 0
order by gets + immediate_gets
/