Using function result_cache for timed caching*

June 17, 2008 Leave a comment

*Or “can I use result_cache to cache for 1 minute/day/hour/day/week/month”

There is already a plethora of excellent resources on the new 11g result_cache functionality, notably:

Including a couple of interesting angles on things here:

Now one of my first tasks now that we’re on 11g and EE (having upgraded from 9i SE) is to identify new features to implement from a performance perspective.

Having read some of the above resources, it won’t surprise you to know that you can’t just point anything at the result_cache and expect a dramatic improvement – quite the contrary sometimes. As Adrian Billington notes in his article, if you’re already doing session memory caching using pl/sql structures, you’re unlikely to see any saving in time – the opposite in fact – but the advantages of the result cache are “cross-session availability, managed SGA memory usage and protected data integrity”. Of course, depending on what you’re doing, you could even choose to use pl/sql caching on top of the result cache.

So, if you’ve got a result_cache target in mind – make sure you test it.

One of the things I’m currently looking at is whether to implement a function result_cache lookup for time sensitive information. The sort of thing I mean is information that changes daily or hourly or weekly independently of the underlying data changing.

For example, fund prices and valuation dates. A fund might have a scheduled set of valuation dates from which a certain price applies. And on any given day, you might be in a different valuation period.

Exactly the sort of thing you might cache using whatever caching method you might choose. And exactly the sort of thing that you could use function result_cache functionality for.

If the underlying data changes, the cache will be refreshed (having used the RELIES_ON clause).

If the timing point changes (the day/week/hour/minute/whatever), a new set of data is cached.

Here is a simple example of what I mean.

– Create a function that will cache by the minute. We will do this by creating a function with a single parameter that defaults to the current date truncated to the current minute and by calling it without specifying a parameter:


CREATE OR REPLACE FUNCTION f_test_minute_result_cache (
i_today IN DATE DEFAULT TRUNC(SYSDATE,'MI')
)
RETURN DATE
RESULT_CACHE
AS
BEGIN
RETURN SYSDATE;
END f_test_minute_result_cache;
/

Then call it:


dominic@11gTest>l
1 select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') now
2 ,to_char(trunc(sysdate,'MI'),'DD-MON-YYYY HH24:MI:SS') this_minute
3 ,to_char(f_test_minute_result_cache,'DD-MON-YYYY HH24:MI:SS') cached_result
4* from dual
dominic@11gTest>/

NOW THIS_MINUTE CACHED_RESULT
-------------------- -------------------- --------------------
17-JUN-2008 10:16:02 17-JUN-2008 10:16:00 17-JUN-2008 10:16:02

Elapsed: 00:00:00.00

Call it again, in the same minute, but a few seconds later:


dominic@11gTest>l
1 select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') now
2 ,to_char(trunc(sysdate,'MI'),'DD-MON-YYYY HH24:MI:SS') this_minute
3 ,to_char(f_test_minute_result_cache,'DD-MON-YYYY HH24:MI:SS') cached_result
4* from dual
dominic@11gTest>/

NOW THIS_MINUTE CACHED_RESULT
-------------------- -------------------- --------------------
17-JUN-2008 10:16:06 17-JUN-2008 10:16:00 17-JUN-2008 10:16:02

Elapsed: 00:00:00.00

You can see from “Now” that time has inevitably moved on, but as the default in parameter has not changed, the result has come from the cache.

Then, run the statement again a bit later:


dominic@11gTest>l
1 select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') now
2 ,to_char(trunc(sysdate,'MI'),'DD-MON-YYYY HH24:MI:SS') this_minute
3 ,to_char(f_test_minute_result_cache,'DD-MON-YYYY HH24:MI:SS') cached_result
4* from dual
dominic@11gTest>/

NOW THIS_MINUTE CACHED_RESULT
-------------------- -------------------- --------------------
17-JUN-2008 10:55:29 17-JUN-2008 10:55:00 17-JUN-2008 10:55:29

Elapsed: 00:00:00.00

Time has moved on, the default in parameter has moved on, a new result is generated and cached.

So, just to show that you could also use result_cache in this way. Doesn’t necessarily mean that you should though.

Filed under 11g, oracle, result cache

Leave a comment