发表于: 2007.10.15 23:17
分类: ORACLE , Bug
出处: http://yangtingkun.itpub.net/post/468/405763
---------------------------------------------------------------
Oracle11g中，如果设置了MEMORY_TARGET，则RESULT_CACHE_MAX_SIZE的设置在数据库重启后自动被置为0。
问题再现：
SQL> alter system set memory_target = 220m scope = spfile;
System altered.
SQL> alter system set result_cache_max_size = 10m scope = spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 209235968 bytes
Fixed Size 1298920 bytes
Variable Size 192941592 bytes
Database Buffers 12582912 bytes
Redo Buffers 2412544 bytes
Database mounted.
Database opened.
SQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 220M
SQL> show parameter result_cache_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 0
SQL> alter system set result_cache_max_size = 10m scope = both;
System altered.
SQL> show parameter result_cache_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 0
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
如果不设置MEMORY_TARGET而使用SGA_TARGET和PGA_AGGREGATE_TARGET，则不会导致RESULT_CACHE_MAX_SIZE被置为0值：
SQL> alter system reset memory_target scope = spfile sid = '*';
System altered.
SQL> alter system reset memory_max_target scope = spfile sid = '*';
System altered.
SQL> alter system set sga_target = 170m scope = spfile;
System altered.
SQL> alter system set pga_aggregate_target = 50m scope = spfile;
System altered.
SQL> alter system set result_cache_max_size = 10m scope = spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 209235968 bytes
Fixed Size 1298920 bytes
Variable Size 150998552 bytes
Database Buffers 54525952 bytes
Redo Buffers 2412544 bytes
Database mounted.
Database opened.
SQL> show parameter result_cache_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 10M
SQL> alter system set result_cache_max_size = 20m;
System altered.
SQL> show parameter result_cache_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 20M
不使用MEMORY_TARGET方式设置内存参数，则RESULT_CACHE_MAX_SIZE参数设置正常。
这个bug在metalink上还没有详细的描述。
re: 设置MEMORY_TARGET会导致RESULT_CACHE_MAX_SIZE在重启后归零 
