Result Cache Can Not Be Enabled, Continue...

I posted about Result Cache Can Not Be Enabled, Really?. but I didn't know the real reason, So I tested again for fixed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
DISABLED

SQL> alter system set result_cache_max_size=1M;

System altered.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
DISABLED

SQL> !strings spfileorcl.ora | grep sga\_
*.sga_max_size=1073741824
*.sga_target=0

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 1M

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1348968 bytes
Variable Size 729811608 bytes
Database Buffers 331350016 bytes
Redo Buffers 8822784 bytes
Database mounted.
Database opened.

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 0

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS

SGA_TARGET initialization parameter value = 0 and it was in spfile, then tested remove it (sga_target):

SQL> !strings spfileorcl.ora | grep sga\_
*.sga_max_size=1073741824
*.sga_target=0

SQL> alter system reset sga_target scope=spfile;

System altered.

SQL> !strings spfileorcl.ora | grep sga\_
*.sga_max_size=1073741824

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1348968 bytes
Variable Size 729811608 bytes
Database Buffers 331350016 bytes
Redo Buffers 8822784 bytes
Database mounted.
Database opened.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 1M

However, If Result Cache status still be "BYPASS". We may remove "SGA_MAX_SIZE" from SPFILE. (If we don't use it)

SQL> alter system reset sga_max_size scope=spfile;

System altered.

SQL> !strings spfileorcl.ora | grep sga\_

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1348968 bytes
Variable Size 729811608 bytes
Database Buffers 331350016 bytes
Redo Buffers 8822784 bytes
Database mounted.
Database opened.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

*** When we use Automatic Memory Management or don't need sga_target, sga_max_size initialization parameters on 11G, we should remove them from SPFILE. ***

Remark:
A RESULT_CACHE_MAX_SIZE initialization parameter to define the memory allocated to the result cache. Result cache is disabled, when this parameter to 0.
However, The Default value of this parameter depends on the SGA configured and the Memory management system.
0.25% of MEMORY_TARGET or
0.5% of SGA_TARGET or
1% of SHARED_POOL_SIZE