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.