Home arrow Oracle arrow Page 3 - Generic Architecture for Caching Table Data: Hello Cache, How Are You Doing?

The Cache Turnover Ratio - Oracle

Now that you have set up a functioning cache, are you sure it's giving you the performance enhancements you need? Keep reading to learn three ways to check the performance of your cache.

TABLE OF CONTENTS:
  1. Generic Architecture for Caching Table Data: Hello Cache, How Are You Doing?
  2. The Cache Miss Ratio aka DB Reads
  3. The Cache Turnover Ratio
  4. Reporting the Ratio
By: Mark Vilrokx
Rating: starstarstarstarstar / 1
November 01, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

When data is removed from the cache to make room for another record, it is called a turnover.  A cache is most effective if it has a low turnover rate.  The turnover ratio can be calculated by dividing the number of times a record gets removed from the cache, versus the total number of cache hits:

            # Cache Turnovers

Hit Ratio = ----------------- * 100

            # Cache Hits

The lower the turnover ratio, the better your cache usage, and the more performance gain you are getting from using the cache.  A high cache turnover can indicate that the cache size is too small, i.e. it can help you pinpoint the issue (cache size too small) when used in conjunction with the Cache Hit and/or Cache Miss ratios.

In order to be able to calculate the ratio, we just need to keep track of the number of cache turnovers.

   g_total_cache_turnover   PLS_INTEGER    := 0;

And then we change our existing code to gather the numbers (if requested):

   PROCEDURE write_to_cache (
      p_dept_id     IN   dept.deptno%TYPE,
      p_dept_data   IN   department_tp
   )
   AS
   BEGIN
      IF (g_dept_cache.COUNT >= max_cache_size)
      THEN
         delete_from_cache (random_cached_dept_id);

         IF (gather_stats)
         THEN
            g_total_cache_turnover :=   g_total_cache_turnover
                                      + 1;
         END IF;
      END IF;

      g_dept_cache (p_dept_id).NAME := p_dept_data.NAME;
      g_dept_cache (p_dept_id).LOCATION := p_dept_data.LOCATION;
   END write_to_cache;

Now we just need to create a procedure to actually perform the calculations of the ratio:

   FUNCTION cache_turnover_ratio
      RETURN NUMBER
   AS
      l_cache_turnover_ratio   NUMBER (10, 5);
   BEGIN
      IF (g_total_reads <> 0)
      THEN
         l_cache_turnover_ratio :=
                                  (g_total_cache_turnover /
g_total_cache_hits)
                                * 100;
      END IF;

      RETURN l_cache_turnover_ratio;
   END cache_turnover_ratio;

Note that your turnover ratio can be 0% and the goal is to get as close to 0% as possible.



 
 
>>> More Oracle Articles          >>> More By Mark Vilrokx
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: