Oracle 11g中引入了革命性的Automatic Memory Management(AMM)特性，通过该特性DBA只需要为Instance指定一个参数(memory_target)，数据库软件就会根据SGA/PGA内存的实际使用统计信息来调优SGA/PGA内存区域的大小。从技术上说这是一个很cool的特性，可以说是Oracle所提倡的self_tuned即自身调优数据库软件大成的一个标志。但从另一方面来看，AMM也会给我们带来不少问题和困惑，DBA需要面对更多黑盒内隐藏的秘密了。
虽然我们无法彻底了解AMM的所有细节，但有一些关键性问题肯定会在我们使用AMM的过程造成许多的不确定性，在此我列出部分问题及其解答。
Question1:在使用AMM特性时，即设置了memory_target的情况下sga/pga内存区域默认各占百分之多少?
Answer:
这个问题存在多种不同的情景：
1)设置memory_target的同时，设置了sga_target及pga_aggregate_target参数：这种情况下设置的sga_target与pga_aggregate_target之和不能大于memory_target；默认sga大小等同于sga_target设定的值，可以从V$memory_Dynamic_Components视图中查询到pga target的current size可能远大于pga_aggregate_target(这里查询出来的current size仅代表pga的一个目标值，不是pga当前实际占用的内存)，一般来说这里的pga current size等于(memory_target-sga_target)，显然pga target的最小值会是pga_aggregate_target，而sga的最小值为sga_target.在此前提下sga/pga内存区域有较小的灵活性，实际上仅当memory_target>sga_target+pga_aggregate_target的情况下，sga/pga才可能发生扩展(grow)和收缩(shrink)
2)设置memory_target的同时，设置了sga_target而未设置pga_aggregate_target参数：这种情况下Oracle会自动调优以上2个参数，pga_aggregate_target的初始值为(memory_target-sga_target)，注意这里说的是初始值；这种情况下因为没有硬性地设置pga_aggregate_target，故而sga/pga内存区域有较大的灵活性去扩展(grow)和收缩(shrink)。更倾向于sga的扩展，当然这并不绝对。
3)设置memory_target的同时，设置了pga_aggregate_target而未设置sga_target：这种情况下Oracle会自动调优以上2个参数，sga_target的初始值为min(memory_target-pga_aggregate_target,sga_max_size)，注意这里说的是初始值；类似于以上的情况因为没有硬性地设置sga_target，故而sga/pga内存区域有较大的灵活性去扩展(grow)和收缩(shrink)。更倾向于pga的扩展，当然这并不绝对。
4)仅仅设置了memory_target参数，Oracle会自动调优sga/pga内存区域，且没有任何最小值或默认值的约束。当在初始化分配操作系统内存的时候存在一个分配pga/sga的策略；该策略会在实例启动阶段(instance startup)为SGA分配memory_target 60%的内存(该比例受到隐藏参数_memory_initial_sga_split_perc Initial default sga target percentage with memory target的影响，该参数默认为60)，而为PGA分配40%的内存。这种情况下sga/pga内存区域的游标最为灵活，可以不受限制地上下浮动，是真正意义上的AMM，但也最为危险！
5)没有设置memory_target参数，该情况下Oracle 11g的表现与10g中”一致”(并非完全一致，黑盒中藏了很多秘密，只是看上去这样，后面会提到)
Question2:”我在10g就感到ASMM很不好用，产生了很多问题；我是个保守派，对11g中的AMM一定也不感兴趣。我要彻底禁用AMM和ASMM，在11g中是否只要设置sga_target和memory_target参数为零就ok了？”
Answer:
在11.2.0.1之前的版本(包括11.1.0.7,11.1.0.6等)我们仅需要设置sga_target和memory_target参数为零就可以避免sga/pga内存区域的resize，但在Oracle 11g Release 2中引入了immediate memory allocation requests立即内存分配的特性，该特性会在automatic memory management被禁用的情况下始终生效，引入这一特性的直接目的是尽可能的避免ORA-04031错误的发生，一般来说该特性更多地表现出了其积极的一面，但如果你实际需要的是一块安宁的、如死水寂静的sga/pga内存区域的话，我们还是有办法禁用该immediate memory allocation requests特性的，这样可以彻底杜绝sga中内存组建resize的发生:
SQL> col DESCRIB for a60
SQL> set linesize 200 pagesize 2000
SQL> col name for a32
SQL> col value for a10
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%_memory_imm_mode_without_autosga%';
NAME VALUE DESCRIB
-------------------------------- ---------- ------------------------------------------------------------
_memory_imm_mode_without_autosga TRUE Allow immediate mode without sga/memory target
SQL> alter system set "_memory_imm_mode_without_autosga"=FALSE scope=both;
System altered.
/* 修改该参数需要重启实例 ！*/
Question3:”我是一个激进派，我喜欢Oracle self-tuned自动调优的种种特性，为了充分利用AMM的优势，我决定仅设置memory_target参数，而不设置sga_target和pga_aggregate_target参数，让Oracle自行去调优这2个参数。但是我有担心如果pga扩展过头，是不是可能造成sga频繁shrink，造成系统颠簸甚至实例hang住？”
Answer:你的担心是完全有理由的，已知的文档604080.1指出了在11.1.0.6到11.1.0.7版本中存在AMM下未设置PGA_AGGREGATE_TARGET导致Oracle所使用的总内存超过MEMORY_MAX_TARGET的bug 6346293(When not setting PGA_AGGREGATE_TARGET to an explicit value, MEMORY_MAX_TARGET can be exceeded)。该Bug在11.2以上版本中得到了修复。
显然通过R1多个版本的历练后，Oracle充分认识到了AMM”自由调度”所可能带来的问题，我相信Oracle已经得到了某些内存阀值的黄金比例(Another magic number),当然这些幻数隐藏得很深(可能由某个_memory开头的隐藏参数控制着，但我没有兴致去研究，这需要花费太多的时间)。通过这些经典阀值Oracle可以有效控制pga/sga在不过度扩展或收缩的情况下仍保留其灵活性。
针对那些和我一样热爱新特性的朋友，最好的建议可能是在设置memory_target的同时为sga_target、pga_aggregate_target设置最小的期望值：
Memory_Max_Target>=Memory_Target>>(Sga_Target+Pga_Aggregate_Target)
这样可以避免由AMM过于活跃所造成的系统颠簸，而又不失去其灵活性。
to be continued………….
PGA Memory Allocation Exceeds MEMORY_MAX_TARGET. [ID 604080.1]
Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.1.0.8 – Release: 11.1 to 11.1
Information in this document applies to any platform.
Symptoms
When MEMORY_MAX_TARGET is set, some views are showing that PGA is actually using more memory than the value of MEMORY_MAX_TARGET :
SQL> select name,value from v$system_parameter
where name in (
‘memory_max_target’,
‘memory_target’,
‘sga_max_size’,
‘sga_target’,
‘shared_pool_size’,
‘db_cache_size’,
‘large_pool_size’,
‘java_pool_size’,
‘pga_aggregate_target’,
‘workarea_size_policy’,
‘streams_pool_size’
)
/
NAME VALUE
—————————— ————-
sga_max_size 314572800
shared_pool_size 0
large_pool_size 0
java_pool_size 0
streams_pool_size 0
sga_target 0
memory_target 314572800
memory_max_target 314572800
db_cache_size 0
pga_aggregate_target 0
workarea_size_policy AUTO
SQL> select * from v$pgastat;
NAME VALUE UNIT
—————————————— ————– ————
aggregate PGA target parameter 71303168 bytes
aggregate PGA auto target 4456448 bytes
global memory bound 25165824 bytes
total PGA inuse 234960896 bytes
total PGA allocated 930527232 bytes
maximum PGA allocated 968351744 bytes
total freeable PGA memory 24969216 bytes
process count 38
max processes count 43
PGA memory freed back to OS 28450226176 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 24916992 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 536576 bytes
over allocation count 2195708
bytes processed 113574183936 bytes
extra bytes read/written 9022009344 bytes
cache hit percentage 93 percent
recompute count (total) 2195708
Cause
Unpublished Bug 6346293 MEMORY_MAX_TARGET COULD GET SET TO LESS THAN SGA+PGA WHEN PGA PARAM IS NOT SET
When not setting PGA_AGGREGATE_TARGET to an explicit value, MEMORY_MAX_TARGET can be exceeded.
Solution
1. Use the workaround by setting PGA_AGGREGATE_TARGET to an explicit value.
OR
2. Apply Patch.6346293 if available for your platform and DB version.
OR
3. Move to 11.2 where the bug is fixed.
SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled (MEMORY_TARGET/SGA_TARGET=0)
Applies to:
Oracle Server – Enterprise Edition – Version: 11.2.0.1 and later [Release: 11.2 and later ]
Information in this document applies to any platform.
Symptoms
SGA re-sizes are occurring after upgrade to 11.2 despite the fact that automatic memory management (AMM/ASMM) is disabled via the MEMORY_TARGET and SGA_TARGET parameters being set to zero.
Checking V$MEMORY_RESIZE_OPS / V$SGA_RESIZE_OPS shows an IMMEDIATE re-size having occurred, e.g.:
COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
—————————————————————- ————- ——— ——————————————————————————– ———— ———– ———- ——— ——————– ——————–
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 7.4357E+10 7.4088E+10 7.4088E+10 COMPLETE 12-OCT-2010 14:26:51 12-OCT-2010 14:26:51
shared pool GROW IMMEDIATE shared_pool_size 2684354560 2952790016 2952790016 COMPLETE 12-OCT-2010 14:26:51 12-OCT-2010 14:26:51
Changes
The database has been upgraded to 11.2.
Cause
This is expected behavior in 11.2 for immediate memory allocation requests, which added this as a new feature when automatic memory management was disabled.
Solution
Ignore the resize operation which is performed to prevent an ORA-4031 error from being raised, or set the parameter _MEMORY_IMM_MODE_WITHOUT_AUTOSGA to false in the instance to disable this feature with the consequence that in future an ORA-4031 error would be raised, e.g.:
connect / as sysdba
alter system set “_memory_imm_mode_without_autosga”=FALSE scope=both;
Hdr: 10205994 11.2.0.1 RDBMS 11.2.0.1 MEMORY MGMT PRODID-5 PORTID-23
Abstract: SGA RE-SIZES OCCURRING EVEN THOUGH AMM/ASMM ARE DISABLED
PROBLEM:
——–
1. Clear description of the problem encountered
SGA re-sizes are automatically occurring of 256Mb being moved from the buffer
cache to the shared pool, even though MEMORY_TARGET and SGA_TARGET are not
set.
2. Pertinent configuration information (MTS/OPS/distributed/etc)
None.
3. Indication of the frequency and predictability of the problem
This is occurring intermittently in the customers production db.
4. Sequence of events leading to the problem
Customer sees SQL library misses followed by waits on ‘cursor: pin S waiting
for X’ build ups prior to the shared pool growing.
5. Technical impact on the customer. Include persistent after effects.
This brings their application down as they have an execution call rate of a
consistent 40,000 per second, which sometimes bursts over 100k/s and even
200k/s, and so when this occurs the shared pool latch is held causing the
application sessions to hang.
DIAGNOSTIC ANALYSIS:
——————–
The alertlog clearly shows that AMM and ASMM should not be enabled, as
neither MEMORY_TARGET or SGA_TARGET are set. However the V$MEMORY_RESIZE_OPS
view shows SGA re-sizes occurring, moving 256Mb from the buffer cache to the
shared pool on 3 occasions, which shouldn’t be occurring?
WORKAROUND:
———–
None
RELATED BUGS:
————-
Bug:8211733 – SHARED POOL LATCH CONTENTION ON SHRINK
Bug:9267837 – MISSING CHANGES IN AUTO-SGA POLICY
REPRODUCIBILITY:
—————-
Problem reproduces intermittently in the customers production instance only,
and has not been reproduced in house.
The customer has set manually set MEMORY_TARGET and SGA_TARGET to zero, but
the problem still occurred. They have managed to reproduce this on a test
system by reducing the size of the shared pool and then forcing execution of
heavy shared pool queries. I have uploaded the MMON trace file generated
from setting _MEMORY_MANAGEMENT_TRACING=7, together with a 10046 user trace
of the culprit session which shows the shared pool re-size occurring at line
1937406.