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

The Cache Miss Ratio aka DB Reads - 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 not found in the cache, it is called a cache miss.  A cache is only effective if it has a low miss rate.  The miss ratio can be calculated by dividing the number of times a record gets read from the DB (it only gets read from the DB if it wasn’t found in the cache, indicating a cache miss), versus the number of times a record was looked for:

            # Cache Misses

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

            # Total Reads

The lower the miss ratio, the fewer the data items get read from the database and the more performance gain you are experiencing from using the caching logic.  A high cache miss 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 db reads (on top of the total number of reads which we already keep track of). 

   g_total_db_hits      PLS_INTEGER    := 0;

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

   FUNCTION read_from_db (p_dept_id IN dept.deptno%TYPE)
      RETURN department_tp
   AS
      l_dept_data   department_tp;
      CURSOR csr_dept_data (p_dept_id IN dept.deptno%TYPE)
      IS
         SELECT dname, loc
           FROM dept
          WHERE deptno = p_dept_id;
   BEGIN
      OPEN csr_dept_data (p_dept_id);
      FETCH csr_dept_data INTO l_dept_data;

      IF (csr_dept_data%FOUND)
      THEN
         IF (gather_stats)
         THEN
            g_total_db_hits :=   g_total_db_hits
                               + 1;

         END IF;
      END IF;

      CLOSE csr_dept_data;
      RETURN l_dept_data;
   END read_from_db;

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

   FUNCTION db_hit_ratio
      RETURN NUMBER
   AS
      l_db_hit_ratio   NUMBER (10, 5);
   BEGIN
      IF (g_total_reads <> 0)
      THEN
         l_db_hit_ratio := (g_total_db_hits / g_total_reads) *
100;
      END IF;

      RETURN l_db_hit_ratio;
   END db_hit_ratio;



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