Generic Architecture for Caching Table Data: Hello Cache, How Are You Doing? - Reporting the Ratio
(Page 4 of 4 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
