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.