Generic Architecture for Caching Table Data: Supercharge Your Cache - Don’t Forget to Flush
(Page 3 of 4 )
If you run this code you will notice some very peculiar behavior. Although the cache gets switched off before the second call, it will refuse to query the database. In fact, if you run it a second time (and a third etc.), you will see the same behavior, it just will not go to the DB. That is until you reconnect (i.e. close this session and open a new one). The reason is that switching the cache off doesn’t actually clear it. The data that was in the cache before you switched it off stays in there until you disconnect your session. Therefore, when you query department 10, it will still find it in the cache and return the cached value.
Now you could have avoided this by adding the check to verify whether the cache is in use or not before you read from the cache. But that would hide the fact that there is still data in the cache, occupying valuable memory for no reason. To resolve this issue we will create a procedure that empties the cache and we will call this when the cache gets switched off.
PROCEDURE flush_cache
AS
BEGIN
g_dept_cache.DELETE;
END flush_cache;
Here are the changes needed in set_caching:
PROCEDURE set_caching (p_on IN BOOLEAN DEFAULTTRUE)
AS
BEGIN
g_caching := p_on;
IF (NOT p_on)
THEN
flush_cache;
END IF;
END set_caching;
Now, when you switch the cache off, you also clean it up and no further cache reads will occur, until you switch the cache back on that is.
And how do I know this switch actually works? Well, very simple, I can now do my performance test that I did in the first article with the same procedure, dept_data, and just switch the cache on and off before each test (in part 1 of this series I could only demonstrate this by calling the read_from_db function directly). It should be significantly slower with the cache switched off.
Next: Conclusion >>
More Oracle Articles
More By Mark Vilrokx
