The Cache Turnover Ratio - Oracle

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.