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

Generic Architecture for Caching Table Data: Hello Cache, How Are You Doing?

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.

  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



At this stage, we have all the basic functionality in place for our cache to be useful and usable.  We can read and write to it, refresh it, flush it and show its content.  In this part we will add code that allows you to gauge the effectiveness of the cache.  This can be used to help you fine-tune the optimum size of your cache in a particular instance.  Note that this does not add any functionality to the actual cache, it is just a tool that helps you understand how the cache is being used and allows you to make some informed decisions later on.

The Hit Ratio

When data is found in the cache, it is called a cache hit, and the effectiveness of a cache is judged by its hit rate.  A cache is only effective if it has a high hit rate.  The hit ratio can be calculated by dividing the number of times a record gets found in the cache (cache hit), versus the number of times a record was looked for:

            # Cache Hits

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

            # Total Reads

The higher the hit ratio, the better your cache usage, and the more performance gain you are getting from using the cache.  A low cache hit ratio can indicate that the cache size is too small, or that the data you are caching is not suitable for caching, i.e. there are too many distinct values that get queried resulting in high turnover (see later).

In order to be able to calculate the ratio, we just need to keep track of the number of successful cache reads and the total number of reads.  I do not want to slow down the cache’s performance, however, by gathering all these statistics, so first we introduce another switch to control whether cache stats are gathered or not:

   g_gather_stats     BOOLEAN        := FALSE;

This is followed by the usual getters and setters:

   PROCEDURE set_gather_stats (p_on IN BOOLEAN)
      g_gather_stats := NVL (p_on, FALSE);
   END set_gather_stats;

   FUNCTION gather_stats
      RETURN g_gather_stats;
   END gather_stats;

We also need places in which we can store the numbers, so let's create some more global variables:

   g_total_reads        PLS_INTEGER    := 0;
   g_total_cache_hits   PLS_INTEGER    := 0;

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

   FUNCTION read_from_cache (p_dept_id IN dept.deptno%TYPE)
      RETURN department_tp
      l_dept_data   department_tp;
      IF (g_dept_cache.EXISTS (p_dept_id))
         l_dept_data.NAME := g_dept_cache (p_dept_id).NAME;
         l_dept_data.LOCATION := g_dept_cache
         IF (gather_stats)
            g_total_cache_hits :=   g_total_cache_hits
                                  + 1;

         END IF;
      END IF;

      RETURN l_dept_data;
   END read_from_cache;

   FUNCTION dept_data (
      p_dept_id         IN   dept.deptno%TYPE,
      p_force_db_read   IN   BOOLEAN
      RETURN department_tp
      l_dept_data   department_tp;
      IF ((caching) AND (NOT (p_force_db_read)))
         l_dept_data := read_from_cache (p_dept_id);
      END IF;

      IF (l_dept_data.NAME IS NULL)
         l_dept_data := read_from_db (p_dept_id);

         IF ((l_dept_data.NAME IS NOT NULL) AND (caching))
            write_to_cache (p_dept_id, l_dept_data);
         END IF;
      END IF;

      IF (gather_stats)
         g_total_reads :=   g_total_reads
                          + 1;

      END IF;
      RETURN l_dept_data;
   END dept_data;

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

   FUNCTION cache_hit_ratio
      l_cache_hit_ratio   PLS_INTEGER;
      IF (g_total_reads <> 0)
         l_cache_hit_ratio := (g_total_cache_hits /
g_total_reads) * 100;
      END IF;

      RETURN l_cache_hit_ratio;
   END cache_hit_ratio;

Note that your hit ratio will never be 100% because the cache needs to get filled first, i.e. the first records that get queried will always result in a DB read because there is nothing in the cache yet.  The goal is to get as close to 100% as possible.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: