result_cache_max_size is zero in BYPASS mode though a non-zero value is set
Whenever result cache is require to be enabled in the database, the cache needs some space in memory to create a cache area for the result sets.
Sometimes, if the cache does not have sufficient space to get accommodated in memory, then the status of result cache is BYPASS mode. It is always recommended to have sufficient memory.
Size of result cache defaults to 1% of shared pool. Based on the number of cached results needed, the size should be increased by DBA accordingly.
Enable result cache by setting the result_cache_max_size to a value. It still reflect 0 without allocating any cache. This also means that the result cache cannot be used for any query.
SQL> ALTER system set result_cache_max_size=100M;
System altered.
SQL> show parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
multishard_query_partial_results string not allowed
result_cache_max_result integer 5
result_cache_max_size big integer 0
After increasing shared pool, value of result cache max size still shows 0 and the status is in BYPASS mode
SQL> ALTER system set shared_pool_size=500M;
System altered.
SQL> SELECT dbms_result_cache.status() FROM dual;
DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS
SQL> ALTER system set result_cache_max_size=100M;
System altered.
SQL> SELECT dbms_result_cache.status() FROM dual;
DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS
Then, i decided to increase the memory itself so that size of shared pool can adjust accordingly based on the result cache size. Also a database bounce is needed.
SQL> ALTER system set memory_max_target=4000M scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1258289976 bytes
Fixed Size 9145144 bytes
Variable Size 1191182336 bytes
Database Buffers 50331648 bytes
Redo Buffers 7630848 bytes
Database mounted.
Database opened.
After bounce of database, the result cache is allocated.
SQL> ALTER system set result_cache_max_size=100M;
System altered.
SQL> show parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
multishard_query_partial_results string not allowed
result_cache_max_result integer 5
result_cache_max_size big integer 100M