11g开始,能自动管理sga,pga,称为amm
MEMORY_MAX_TARGE:这个参数指定了 MEMORY_TARGET这个参数能够设置的最大值,memory_target这个参数指定了系统级别的可用内存,可以根据需要减少或增加sga,pga的大小。在一个基于文本的参数文件中,如果排除掉 MEMORY_MAX_TARGET 参数并包含MEMORY_TARGET这个参数,那么数据库会动态的设置memory_max_target为memory_target,如果排除掉MEMORY_TARGET 这个参数,并包含MEMORY_MAX_TARGET这个参数,memory_target参数默认为0,启动后,你可以设置memory_target为非0,并不超过MEMORY_MAX_TARGET的大小。
监控amm
select * from v
memorytargetadviceorderbymemorysize大多数的情况下,我们是使用手工分配内存的。对于pga,设置了target大小,也是自动管理的。对于asmm,sgatarget参数指定了sga可用的大小。granulesThememoryfordynamiccomponentsintheSGAisallocatedintheunitofgranules.GranulesizeisdeterminedbytotalSGAsize.Generallyspeaking,onmostplatforms,ifthetotalSGAsizeisequaltoorlessthan1GB,thengranulesizeis4MB.ForSGAslargerthan1GB,granulesizeis16MB.Someplatformdependenciesmayarise.Forexample,on32−bitWindowsNT,thegranulesizeis8MBforSGAslargerthan1GB.Consultyouroperatingsystemspecificdocumentationformoredetails.select∗fromv
sgainfo;
select * from v
sgatargetadviceorderbysgasize;设置sgaSELECT((SELECTSUM(value)FROMV
SGA) -
(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
) “SGA_TARGET”
FROM DUAL;
启用自动sga管理,但是有些部件是不能自动管理的,包括三部分:
nk buffer(非默认块的buffer);log buffer;keep&recycle buffer;
这些非自动管理组件必须手工设定,自动分配的内存是sga_target减去这部分大小。
amm情况下转换成asmm
1ALTER SYSTEM SET MEMORY_TARGET = 0;
2将sga各个内存池设置成0
SGA_TARGET 这个参数可以动态调整变大或变小。
为了使用手工管理共享内存,
1设置MEMORY_TARGET =0
2设置SGA_TARGET =0,并设置sga各个组件的大小。
在设置了sga_target后,依然可以设置sga各个组件的大小,保证各个组件的在动态调整的时候不低于设置的值。
如果设置了PGA_AGGREGATE_TARGET,oracle会确保所有的server process和后台进程的内存分配不会超过这个值。
-
自动pga管理下的监控视图
V$SYSSTAT
V$SESSTAT
V$PGASTAT
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
v$process中的
PGA_USED_MEM
PGA_ALLOCATED_MEM
PGA_MAX_MEM
三列
手工 管理pga的设置
- SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE
If you decide to tune SQL work areas manually, you must set the WORKAREA_SIZE_POLICY initialization parameter to MANUAL.
内存管理视图
View Description
V$SGA Displays summary information about the system global area (SGA).
V$SGAINFO Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTAT Displays detailed information about how memory is allocated within the shared pool, large pool, Java pool, and Streams pool.
V$PGASTAT Displays PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.
V$MEMORY_DYNAMIC_COMPONENTS Displays information on the current size of all automatically tuned and static memory components, with the last operation (for example, grow or shrink) that occurred on each.
V$SGA_DYNAMIC_COMPONENTS Displays the current sizes of all SGA components, and the last operation for each component.
V$SGA_DYNAMIC_FREE_MEMORY Displays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$MEMORY_CURRENT_RESIZE_OPS Displays information about resize operations that are currently in progress. A resize operation is an enlargement or reduction of the SGA, the instance PGA, or a dynamic SGA component.
V$SGA_CURRENT_RESIZE_OPS Displays information about dynamic SGA component resize operations that are currently in progress.
V$MEMORY_RESIZE_OPS Displays information about the last 800 completed memory component resize operations, including automatic grow and shrink operations for SGA_TARGET and PGA_AGGREGATE_TARGET.
V$SGA_RESIZE_OPS Displays information about the last 800 completed SGA component resize operations.
V$MEMORY_TARGET_ADVICE Displays information that helps you tune MEMORY_TARGET if you enabled automatic memory management.
V$SGA_TARGET_ADVICE Displays information that helps you tune SGA_TARGET.
V$PGA_TARGET_ADVICE Displays information that helps you tune PGA_AGGREGATE_TARGET.
在测试环境中,关闭amm,开启asmm,自动pga管理重启oracle后,发现memory_max_target参数依然会有值,参考http://blog.csdn.net/ghostliming/article/details/49081559
使用参数文件,在参数文件中去掉该值,此时参数文件中,这个值其实已经为0了,在启动,查看参数已被置为0。