11g, can automatically manage sga,pga, known as amm
MEMORY_MAX_TARGE: This parameter specifies the maximum value that MEMORY_TARGET can set. Memor_target specifies the available memory at the system level. It can reduce or increase the size of SGA and PGA as needed. In a text-based parameter file, if the MEMORY_MAX_TARGET parameter is excluded and the MEMORY_TARGET parameter is included, the database will dynamically set memory_max_target to memory_target. If the MEMORY_TARGET parameter is excluded and the MEMORY_MAX_TARGET parameter is included, the memory_target parameter defaults. After startup, you can set memory_target to non-zero, not exceeding the size of MEMORY_MAX_TARGET.
Monitoring amm
select * from vmemorytargetadviceorderbymemorysize In most cases, we use manual allocation of memory. For pga, the target size is set and managed automatically. For asmm, the sgatarget parameter specifies the size of sga available. Granes The Memory for Dynamic Components in the SGAisallocation of granules. Granulesizeis determinedby total SGAsize. General lyspeaking, onmostplatforms, if the total SGAsizeisequaltoorlessthan 1GB, the ngranulesizeis4MB. ForSGAslargerthan 1GB, granulesizeis16MB. Someplatdependence. Foresmample, 32. Bit Windows NT, the granulesizeis 8MB for SGAslargerthan 1GB. Consultation your roperation system specific documentation form ore details. Select fromvsgainfo;
Select * from vsgatargetadviceorder by sgasize; set sgaSELECT((SELECTSUM(value)FROMVSGA)-
(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
) "SGA_TARGET"
FROM DUAL;
Automatic sga management is enabled, but some components cannot be automatically managed, including three parts:
NK buffer (non-default block buffer); log buffer; keep & recycle buffer;
These non-automatic management components must be set manually, and the memory allocated automatically is sga_target minus this size.
Converting to asmm in amm case
1ALTER SYSTEM SET MEMORY_TARGET = 0;
2 Set the memory pools of sga to 0
The parameter SGA_TARGET can be dynamically adjusted to be larger or smaller.
To manage shared memory manually,
1 Set MEMORY_TARGET =0
2 Set SGA_TARGET =0, and set the size of each component of sga.
After setting sga_target, the size of each component of SGA can still be set to ensure that the dynamic adjustment of each component is not lower than the set value.
If PGA_AGGREGATE_TARGET is set, oracle ensures that memory allocation for all server process es and background processes does not exceed this value.
If you decide to tune SQL work areas manually, you must set the WORKAREA_SIZE_POLICY initialization parameter to MANUAL.
Memory Management View
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.In the test environment, after turning off amm, turning on asmm and restarting oracle with automatic pga management, it is found that memory_max_target parameter will still be valuable. http://blog.csdn.net/ghostliming/article/details/49081559
Using the parameter file, the value is removed from the parameter file. At this time, the value is actually zero in the parameter file. At startup, the view parameter has been set to 0.