Reporting the Ratio - Oracle

After all this data gathering you probably would want to report on the data, so let's write a procedure that writes all the ratios to the screen:

   PROCEDURE log_ratios
   AS
   BEGIN
      DBMS_OUTPUT.put_line ('Hit Ratio Miss Ration Turnover Ratio');
      DBMS_OUTPUT.put_line ('--------- ----------- --------------
');
      DBMS_OUTPUT.put_line (   LPAD (TO_CHAR
(cache_hit_ratio,'9990.00'), 7)
                            || ' %'
                            || ' '
                            || LPAD (TO_CHAR
(db_hit_ratio,'999990.00'), 9)
                            || ' %'
                            || ' '
                            || LPAD (TO_CHAR
(cache_turnover_ratio,'999990.00'), 12)
                            || ' %'
                           );
   END log_ratios;

As usual, you can do whatever you want with these ratios, you don’t have to write them to the screen.  You can store them in a table, e.g. to see how your cache’s ratios evolves over time.  That way you can spot trends and act pro-actively, e.g. adjusting the cache size if over time the turnover ratio worsens.

Notice that these ratios are also session specific. If you lose your connection or reconnect, the ratio components will get reset (to NULL).  If you want to record ratios spanning several sessions you will have to store the values of the ratio components at the end of your code, and restore them at the beginning. 

On the other hand, if you do not want to record over several sessions and not even over different runs of your code in the same session, you will have to reset the cache manually.  You can add a Cache Reset procedure to your package that you call at the start of your code.  That way the ratio components are always the same (zero) at the start of your program, regardless of whether you just started a session, or have been in that same session for a while.

   PROCEDURE reset_stats
   AS
   BEGIN
      g_total_reads := 0;
      g_total_cache_hits := 0;
      g_total_db_hits := 0;
      g_total_cache_turnover := 0;
   END reset_stats;

Conclusion

This concludes the series on my Caching Architecture.  We have seen how to create a cache for our PL/SQL applications and how to make good use of it.  You should now be able find good candidate queries for caching and just follow the architecture to implement good caching logic.  Hopefully you can put this into good practice and your customers or manager will thank you for the fine job you did on improving the performance of the application.