Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 6 April 2013

Check Cache Hit Ratio for the database

SELECT Sum(Decode(, 'consistent gets', a.value, 0)) "Consistent Gets",
       Sum(Decode(, 'db block gets', a.value, 0)) "DB Block Gets",
       Sum(Decode(, 'physical reads', a.value, 0)) "Physical Reads",
       Round(((Sum(Decode(, 'consistent gets', a.value, 0)) +
       Sum(Decode(, 'db block gets', a.value, 0)) -
       Sum(Decode(, 'physical reads', a.value, 0))  )/
       (Sum(Decode(, 'consistent gets', a.value, 0)) +
       Sum(Decode(, 'db block gets', a.value, 0))))*100,2) "Hit Ratio %"
FROM   v$sysstat a;
NOTE: The minimum figure of 90% is often quoted, but depending on the type of system this may not be possible.
Select  name, trunc( 1 - (physical_reads / (db_block_gets + consistent_gets)) ,4) as hit_ratio
   from    v$buffer_pool_statistics
    where    db_block_gets + consistent_gets > 0;
Select trunc( 1 - (phy.value - lob.value - dir.value)/ses.value ,4) as cache_hit_ratio
    from   v$sysstat ses, v$sysstat lob, v$sysstat dir, v$sysstat phy
    where = 'session logical reads'
    and = 'physical reads direct'
    and = 'physical reads direct (lob)'
    and = 'physical reads';
NOTE: If it is less than 0.9 and the shared pool hit ratio is good consider increase DB_CACHE_SIZE init parameter.
Display Library Cache hit Ratio:
Select gethits, gets, trunc(gethitratio,4) as gethitratio
from v$librarycache
where namespace = 'SQL AREA';
NOTE: If it is less than 0.9 consider increase SHARED_POOL_SIZE init parameter.
Select sum(pins) as Executions, sum(reloads) as Cache_Misses,
  trunc(sum(reloads)/sum(pins),4) as reload_pin_hitratio
  from v$librarycache;
NOTE: The Library Cache Reloads should be less than 1% of the pins. If it is greater than 0.1 consider increase SHARED_POOL_SIZE init parameter.
Select namespace, pins, reloads, invalidations
from v$librarycache
where invalidations > 0;
NOTE: The number of times objects of the namespace were marked invalid, causing reloads:
 Select value from v$parameter where name = 'shared_pool_reserved_size';
 Select free_space, trunc(free_space/to_number(p.value),4) as pct_free_space,
        request_misses, requests, trunc(request_misses/requests,4) as pct_req_misses,
        from  v$parameter p, v$shared_pool_reserved
        where = 'shared_pool_reserved_size';
NOTE: pct_free_memory should be greater than 0.5 (> 50%); pct_req_misses should be zero or near zero and not increasing; request_failures shoud be zero. if not consider increase share_pool_reserve_space.
Check Dictionary Cache:
Select sum(getmisses) as getmisses, sum(gets) as gets,
       trunc(sum(getmisses)/sum(gets),4) as miss_ratio
       from v$rowcache;
NOTE: The Dictionary Cache percent misses should be less than 0.15 (< 15%). if not consider increase shared_pool_size.


  1. Hi Sahid,

    I like your suggestion the way you have described the different topices from top to bottom.
    Even i have also same problem, i do not understand what should i practice. I have covered many topics like Data Guard, import/export, tablespace management, user management, Hot/cold user manage backup, RMAN backup and recovery, patching and upgradation from 10g to 11g but when i'm learning new thing i felt like im forgetting previous topics.
    And current im working and have 3+ exp in tech support at Thomson Reuters but i want to switch as Oracle dba. can you please guide me what are the basic topics required to start the oracle dba job and suggest some interview questions.

    1. Dear,‎
      Do not worry it happens, continue your practice and wait for the opportunity.‎
      It is always difficult to change the profile but once you will get the opportunity in the ‎desired profession you will not look backward.‎
      One things install your database once and manage it like you are working on production ‎database not as dummy or practice database. I mean do not re-install your database for ‎any issue. Try to solve it by yourself or with any other help.‎
      Hope you can understand!‎
      Thanks & Best Wishes
      Shahid Ahmed