Ad-hoc Tips (HIT and MISS Ratios)

Posted on July 17, 2008. Filed under: Scripts |

Got few useful scripts to check HIT and MISS ratios… Wanted to share the same. Check out below scripts.

prompt
prompt =========================
prompt DATA DICTIONARY HIT RATIO
prompt =========================
prompt Should be higher than 90 else increase shared_pool_size in init.ora prompt

column “Data Dict. Gets” format 999,999,999
column “Data Dict. cache misses” format 999,999,999
select sum(gets) “Data Dict. Gets”,
sum(getmisses) “Data Dict. cache misses”,
trunc((1-(sum(getmisses)/sum(gets)))*100) “DATA DICT CACHE HIT RATIO”
from v$rowcache;

prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > 1 then increase the shared_pool_size in init.ora)
prompt
column “LIBRARY CACHE MISS RATIO” format 99.9999
column “executions” format 999,999,999
column “Cache misses while executing” format 999,999,999
select sum(pins) “executions”, sum(reloads) “Cache misses while executing”,
(((sum(reloads)/sum(pins)))) “LIBRARY CACHE MISS RATIO”
from v$librarycache;

prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should be > 70, and pin ratio > 70 . . .
prompt

column “reloads” format 999,999,999
select namespace, trunc(gethitratio * 100) “Hit ratio”,
trunc(pinhitratio * 100) “pin hit ratio”, reloads “reloads”
from v$librarycache;

Thanks,

OraclePitStop Team

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: