Result Cache Malfunction in Oracle 21c

One of these days I was configuring result cache in an Oracle 21c database and encountered an unusual behavior. Recall, that result cache which is a slice of memory in the shared pool was introduced in Oracle 11g. It stores results of queries and subsequent ones can reuse it.

Looks like it does not work as documented in Oracle 21c. Two modes are available to configure it: manual by hinting the query and force which will make all queries to store their results in the result cache which is, by the way, not the recommended way. I was experimenting on Ubuntu 8. Here is the scenario (note that result_cache_max_size parameter value which enables or disables result cache is 1792K).

First, let’s create a small table and fill it with some data.

create table table1(id integer, name varchar2(30))
    
insert into table1 values (1, 'Name1');
insert into table1 values (2, 'Name2');
insert into table1 values (3, 'Name3');
commit;

The query is as simple as the following one:

select /*+ RESULT_CACHE */ * from table1;

Plan hash value: 963482612

---------------------------------------------
| Id  | Operation         | Name   | E-Rows |
---------------------------------------------
|   0 | SELECT STATEMENT  |        |        |
|   1 |  TABLE ACCESS FULL| TABLE1 |      3 |

Just to make sure you that the query will use the previously cached results we can execute in multiple times. Still, no effect – plan remains the same.
Tracing the optimizer reveals some details (though not yet clear why so):

Dumping Hints
=============
  atom_hint=(@=0x7f25d42835a8 err=38 resol=1 used=1 token=1192 org=1 lvl=2 txt=RESULT_CACHE ())
********** WARNING: SOME HINTS HAVE ERRORS *********
====================== END SQL Statement Dump ======================

That is, the optimizer considers RESULT_CACHE as a hint and errors out explicitly telling that it cannot parse it: err=38. I’ve tried the FULL hint to compare and for this one err=0. It is easy to see that it is not a configuration related problem, for example, minimum data size to be eligible for caching and etc. since the optimizer says that it faces an error. Moreover, just to exclude size related limitations, if any, I tried with a large data size – no effect.

To be continued…