Solution to implementing caching layer in pl/sql

Question

I have a function with 1 argument (date) which encapsulates 1 query like

SELECT COUNT(*)
  FROM tbl
 WHERE some_date_field BETWEEN param_date - INTERVAL '0 1:00:00' DAY TO SECOND
                           AND param_date

What I want to do is to cache somewhere the result of this query with ttl = 1 minute. The cached result should be shared across all sessions, not just current one.

Any proposals?

PS: Yes, I know about oracle function result cache, but it doesn't fit the requirements.
PPS: Yes, we can create 2nd artificial argument with some value like date in format of yyyymmddhh24mi so it changes each minute and we're able to use function result cache, but I hope it is a solution which will allow me to hide the caching dependencies inside.

Answer 1

You want to cache the result of this query, and share the cache across all sessions. The only way I can think of is to wrap the query in a function call, store the result in a small table. The function will query the small table to see if the count has already been stored within the last 1 minute, and if so, return it.

You would keep the table small by running a job periodically to delete rows in the "cache table" that are older than 1 minute - or better still, perhaps truncate it.

However, I can only see this being of benefit if the original SELECT COUNT(*) is a relatively expensive query.

Answer 2

I'd use a global application context, and a job with a refresh interval of 1 minute to set the context.

PS: INTERVAL '1' HOUR is shorter and more meaningful than INTERVAL '0 1:00:00' DAY TO SECOND