Doc ID: Note:33883.1 Type: REFERENCE
Last Revision Date: 12-FEB-2004 Status: PUBLISHED
*** See Note 130955.1 for a description of the "STATISTIC" reference articles
Definition:
~~~~~~~~~~~
The "cache hit ratio" is a derived statistic referred to in a number of
manuals and articles. There is more than one definition of the cache hit
ratio in existence.
The hit ratio is intended to give an indication of how often the various
processes accessing the data buffers find the blocks in the Oracle buffer
cache. The exact value of the hit ratio is of less importance than the
ability to monitor it over time to notice any significant changes in the
profile of activity on the database.
*** Important: A very high hit ratio (close to 100%) is not necessarily good
The reasons for this are explained later.
Calculation:
~~~~~~~~~~~~
The most common formula in circulation for the hit ratio for the buffer cache
for Oracle7/8 is:
hit ratio = 1 - ( physical reads )
-----------------------------------
( consistent gets + db block gets )
This is often expressed as a percentage (by multiplying the result by 100).
However, in 7.3.4 and Oracle8 onwards the definition of the "physical reads"
statistic changed to include direct block reads as well as reads to get data
into the buffer cache. Hence the above formula only gives an lower bound for
the hit ratio on these releases.
A better formula in Oracle8i/9i is:
hit ratio =
1 - ( physical reads - (physical reads direct + physical reads direct (lob)) )
--------------------------------------------------------------------------
( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )
In Oracle8.0 onwards it is possible to use multiple buffer pools (by setting
the BUFFER_POOL_KEEP or BUFFER_POOL_RECYCLE init.ora parameters). Multiple
buffer pools are not discussed here but the hit ratios for each pool can be
seen using the V$BUFFER_POOL_STATISTICS view (which is created by the
CATPERF.SQL script in Oracle8 and is a standard view in Oracle8i onwards)
thus:
SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets ) ) "HIT_RATIO"
FROM V$BUFFER_POOL_STATISTICS
WHERE ( consistent_gets + db_block_gets ) !=0
;
(Note that this view shows 0 for CONSISTENT_GETS and DB_BLOCK_GETS in 8.1.7 and so
the above select is useless in this release - See Bug 1491213 )
The "Miss Ratio"
~~~~~~~~~~~~~~~~
Occasionally you may see reference to the "miss ratio". This is just
Miss ratio = 100% - Hit Ratio (expressed as a percentage)
Notes about the Hit Ratio
~~~~~~~~~~~~~~~~~~~~~~~~~
A good hit ratio is expected for OLTP type systems but decision support type
systems may have much lower hit ratios. Use of parallel query will make the
hit ratio less meaningful if using the first form of calculation based on
"physical reads" only.
A hit ratio close to 100% does not mean the application is good. It is quite
possible to get an excellent hit ratio by using a very unselective index in a
heavily used SQL statement.
Eg: Consider a statement like:
SELECT * FROM employee WHERE empid=1023 AND gender='MALE';
If EMPLOYEE is a large table and this statement always uses the GENDER index
rather than the EMPID index then you scan LOTS of blocks (from the GENDER
index) and find nearly all of them in the cache as everyone is scanning this
same index over and over again. The hit ratio is very HIGH but performance
is very BAD. A common 'variation' on an "unselective" index is a heavily
skewed index where there are a large number of entries with one particular
value (eg: a workflow status code of CLOSED) - the index may perform well for
some queries and very poorly for the most common value.
A few comments:
~~~~~~~~~~~~~~~
- The "good" hit ratio is generally considered to be one >80%
There is probably still scope for tuning if it is <90% *BUT*
note that the hit ratio is not the best measure of performance.
- The ratio can be artificially high in applications making
poor use of an UNSELECTIVE index.
- In Oracle8.1 onwards "physical reads direct" are recorded
- Some documentation incorrectly reports hit ratio to be:
Hit Ratio = Logical Reads / ( Logical Reads + Physical Reads )
this is incorrect for any version of Oracle.
Related:
~~~~~~~~
Bug to change "physical reads" to include direct reads in Bug 404802
Documented Hit Ratio is incorrect Bug 2002504
7.3.4/8.0 onwards
No comments:
Post a Comment