No AUTO RESULT_CACHE_MODE!!!

I have recently posted about the result cache feature in 11g.

I have recceived a number of comments on this post by Peter about the AUTO mode of the parameter who asked if it really works because he had tewsted and did not find the result he had expected. This made me test as well and I had the same experience as Peter.

So I went to the demo ground here at the OOW yesterday and asked the guys from Server Technologies directly what the algorithm used is and received a surprising answer:

They told me that only MANUAL and FORCE are supported settings for the parameter. AUTO is neither documented nor supported!!!!

I asked them why it is possible to set the parameter to AUTO in the Enterprise manager and also there is an error message telling me this:

SYS AS SYSDBA @ ocrl11 SQL> show parameter result_cache

NAME                                 TYPE        VALUE
———————————— ———– ——————————
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 1280K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
 

SYS AS SYSDBA @ ocrl11 SQL> alter system set result_cache_mode=nonsense;
alter system set result_cache_mode=nonsense
*
ERROR at line 1:
ORA-00096: invalid value NONSENSE for parameter result_cache_mode, must be from among FORCE, MANUAL, AUTO

And as Peter has found out: IT DOES NOT WORK WITH AUTO obviousely!
Very good catch Peter!

The guys from Server Technologies told me that it will probably never work and will never be supported with AUTO. And if I think it over it is not bad to manually control which statements are cached and which not.

=;-)