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

Reporting the Ratio - 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.

  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



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
      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
      g_total_reads := 0;
      g_total_cache_hits := 0;
      g_total_db_hits := 0;
      g_total_cache_turnover := 0;
   END reset_stats;


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.

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