HomeOracle Page 2 - Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications
Why a Caching Architecture? - Oracle
In this series I am going to introduce you to a generic PL/SQL caching architecture. The goal is to give you an understanding of how you can store Table data in PL/SQL collections (“the cache”) and how to retrieve data from those same PL/SQL collections. The proposed architecture will be generic enough for you to use throughout your applications everywhere you need to retrieve data from the database (and isn’t that what PL/SQL applications are all about?).
I have implemented caches myself on many occasions, but they were always an afterthought: code had been released and a customer complained about performance. You verify that you have tuned your queries to perfection and then you realize that caching will significantly increase performance, so you start patching up your code and add the caching logic.
I also started noticing that the way I implemented caching in different places was never exactly the same. Obviously you are dealing with different data so there have to be differences, but I am referring to my own implementations varying from code to code. Basically I was re-inventing the wheel every time I implemented caching logic and sometimes the wheel had 10 spokes and sometimes 11. Worst of all, occasionally, there should have been 10 spokes but there were only 9, i.e. I would introduce bugs into my own caching logic (that I implemented perfectly two weeks before in some other piece of code).
After having gone through several iterations of this, I finally decided enough is enough. I sat down to once and for all come up with a proper solution, some simple guidelines that I can just follow whenever I need to implement caching logic, a proper caching architecture. I also figured that maybe when I have these guidelines, I would not wait to implement caching till a customer tells me to, but actually design them in the system from the start.
And low and behold, that is exactly what happened. Once I was certain that this actually works and can be implemented anywhere, I got so confident about the architecture that I now build caching logic into my applications without even thinking about it. It takes me almost no extra effort or time to add caching logic to the code, they code has less bugs (nobody is perfect) and it always looks exactly the same. I cannot overstate how important that last note is. I for one have difficulties remembering why I myself coded some things the way I did, let alone when reviewing someone else’s code. Now it is easy because it is all the same, even if someone else actually wrote the code.