What is Oracle memory HIT and Memory Miss? Buffer Cache, Library Cache, Dictionary Cache HIT Ratio



The Eucharistic Miracles of the World
When a user submits a query to the oracle session, oracle will parse the query and check out for the result set blocks in the memory (SGA). If the server process can find out the required block from the SGA, then that is called a memory HIT. In case it is not readily available in the memory, it has to get it from the datafiles to the SGA and this is called a MISS.
How To Find out HIT Ratios?
v$sysstat is the data dictionary view to get the system statistics.
Column               Datatype             Description
STATISTIC#         NUMBER             Statistic number
       Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications.
NAME                 VARCHAR2(64)  Statistic name. You can get a complete listing of statistic names by
 querying the V$STATNAME view.
CLASS                 NUMBER             A number representing one or more statistics class.
                                                       The following class
                                                                numbers are additive:
             1 - User
             2 - Redo
             4 - Enqueue
             8 - Cache
             16 - OS
             32 - Real Application Clusters
             64 - SQL
             128 - Debug
VALUE                  NUMBER             Statistic value
STAT_ID               NUMBER             Identifier of the statistic

Buffer Cache HIT Ratio
The formula for finding out the buffer cache hit ratio is (1-physical reads)/(consistent gets+ db block gets) *100
SQL> l
  1  select name, value from v$sysstat
  2* where  name in ('consistent gets','db block gets','physical reads')
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                      84007264
consistent gets                                                   279903811
physical reads                                                     10771598

SQL>  select (1-10771586/(84001586+279876315))*100 from dual;


SQL> select     sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
  2     sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
  3     sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
  4     round((sum(decode(name, 'consistent gets',value, 0)) +
  5            sum(decode(name, 'db block gets',value, 0)) -
  6            sum(decode(name, 'physical reads',value, 0))) /
  7           (sum(decode(name, 'consistent gets',value, 0)) +
  8            sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
  9  from   v$sysstat
SQL> /

Consistent Gets DB Block Gets Physical Reads  Hit Ratio
--------------- ------------- -------------- ----------
      279883293      84002564       10771586      97.04

Formula If this value consistently below 80%  you have to increase the SGA size by increasing  db_cache_size or db_block_buffers depends on the oracle version which you work

The consistent gets Oracle metric is the number of times a consistent read (a logical RAM buffer I/O) was requested to get data from a data block. Or number of reads made to the block buffer to get the data in consistent mode.
DB Block gets:  The number of blocks accessed via single block gets(not through the consistent gets mechanism)
Physical Reads: cumulative number of the physical reads from the disk

Library Cache Hit Ratio

SQL> select sum(reloads) / sum(pins) * 100
  2  from v$librarycache;


If above ratio exceeds a value above 1, you need to increase the shared pool size.

Data dictionary HIT Ratio

Gets - Total number of requests for information on the data object
Cache Misses - Number of data requests resulting in cache misses

  1  select     sum(GETS),
  2     sum(GETMISSES),
  3     round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2) DictHITRatio
  4* from       v$rowcache
SQL> /

---------- -------------- ------------
  75544229         195940        99.74

The data dictionary hit ratio ideally should be above 90% and if it goes below that you need to consider increasing the value of SHARE_POOL_SIZE


Website Stats


Post a Comment


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer