SQL Script

Database Hit Ratios

Updated : 30-March-2000
Version : 2.0

Description

This script calculates the database hit ratio on you database. It also displays session hit rations so you can identify potential problems with individual users. A must to run if your having database performance problems.

Parameters

None.

SQL Source

CLEAR
SET HEAD ON
SET VERIFY OFF

col username form a10
col HitRatio format 999.99 heading 'Hit Ratio'
col CGets format 9999999999999 heading 'Consistent Gets'
col DBGets format 9999999999999 heading 'DB Block Gets'
col PhyGets format 9999999999999 heading 'Physical Reads'

select Username,
	 v$sess_io.sid,
       consistent_gets,
       block_gets,
       physical_reads,
       100*(consistent_gets+block_gets-physical_reads)/ 
		(consistent_gets+block_gets) HitRatio
from v$session, v$sess_io
where v$session.sid = v$sess_io.sid
and (consistent_gets+block_gets) > 0
and Username is NOT NULL
/

select 'Hit Ratio' Database,
       cg.value CGets,
       db.value DBGets,
       pr.value PhyGets, 
       100*(cg.value+db.value-pr.value)/(cg.value+db.value) HitRatio
from v$sysstat db, v$sysstat cg, v$sysstat pr
where db.name = 'db block gets'
and cg.name = 'consistent gets'
and pr.name = 'physical reads'
/