Is there a way to force the result cache for ANY query and let the application handle invalidation?
In an extreme (not very wise) case, do this:
select /*+ force_result_cache */ * from dba_tables where table = 'XXX';
Basically, we want to cache complex queries (involving multi-level views, sysdate, 'connect by level <= x' row generator, etc) and let the app invalidate when necessary. The result cache has the basic functionality we desire: ability of the DB to intercept queries, compare binds, return a pre-queried result. But it's too limiting with what can be cached and doesn't allow any tolerance for stale data even by an explicit override. Granted, I understand the reasons and they make total sense, I'm just trying to extend its use to my application.
Currently we have our own cache using something similar to in-place MV's. That works but forces us to 1) do complete refreshes which are time consuming and 2) do so in a specific order because of view-over-view dependencies.
Any suggestions or alternatives would be appreciated. Thank you for your time.
I don't understand what you mean by "let the application handle invalidation". The result cache is fully managed by Oracle Database.
The first time you execute a query Oracle stores the result in the cache. If the data in the table(s) change, it invalidates the cache immediately.
For example. I've created a function that sleeps for 1s to make the gains clear and a table with 10 rows:
create or replace function f (p int)
return int as
begin
dbms_lock.sleep(1);
return p;
end;
/
create table t (
x int
);
insert into t
select rownum from dual connect by level <= 10;
commit;
The first execution counting 5 rows takes 5s. The second nearly 0s:
SQL> set timing on
SQL>
SQL> select /*+ result_cache */count(f(x)) from t
2 where x <= 5;
COUNT(F(X))
-----------
5
Elapsed: 00:00:05.52
SQL> select /*+ result_cache */count(f(x)) from t
2 where x <= 5;
COUNT(F(X))
-----------
5
Elapsed: 00:00:00.47
But as soon as you add another row to t, Oracle invalidates the cache. And the query takes 5s again:
SQL> insert into t values (11);
1 row created.
Elapsed: 00:00:00.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.31
SQL>
SQL> select /*+ result_cache */count(f(x)) from t
2 where x <= 5;
COUNT(F(X))
-----------
5
Elapsed: 00:00:05.46
So you can't "let the app manage it". And this makes it unsuitable for queries against tables with lots of DML.
If you're looking for a way to have a cache of stale data then MVs are the way to go. Create your MV over your complex query and set it up to refresh on a schedule. Then you could point your app at the MV.
Or you could change the query_rewrite_integrity to allow stale data with:
alter session set query_rewrite_integrity = stale_tolerated;
Then Oracle can use query rewrite to and return data from the stale MV automagically:
https://www.youtube.com/watch?v=2iRadmXtMGk https://www.youtube.com/watch?v=aSj9eDDUE-Y FWIW you can force result caching for all statements in a session with:
alter session set result_cache_mode = force;
Then disable it for specific queries with the no_result_cache hint:
SQL> alter session set result_cache_mode = force;
Session altered.
Elapsed: 00:00:00.40
SQL>
SQL> select count(f(x)) from t
2 where x <= 5;
COUNT(F(X))
-----------
5
Elapsed: 00:00:00.47
SQL> select /*+ no_result_cache */count(f(x)) from t
2 where x <= 5;
COUNT(F(X))
-----------
5
Elapsed: 00:00:05.94