Summary of memory_target in oracle 11g

The MEMORY_MAX_TARGET parameter is added to the memory_target summary 11g in oracle 11g. this parameter is as refreshing as it appears for the first time in 10g. Memory_max_target is used to set the maximum memory space occupied by Oracle to OS, and the maximum memory space occupied by Oracle SGA. Undoubtedly, the 11 GB Oracle goes further toward automatic memory management. 10 Gb sga_max_size of www.2cto.com is the Size of the Shared Pool Size, database buffer cache, large pool, java pool, and redo log buffer dynamically allocated, only the size of each memory block of SGA is re-allocated based on the Oracle running status. The. 11g MEMORY_MAX_TARGET parameter needs to be set separately in 10g PGA. The parameter contains two parts of memory, one is System global area (SGA) and the other is system global area (PGA ). Obviously, PGA and SGA can be dynamically managed in 11 GB. MEMORY_TARGET -- maximum memory value that Oracle can use from the operating system perspective. The maximum value that can be set by the dynamic parameter MEMORY_MAX_TARGET--MEMORY_TARGET. Non-dynamic adjustable. Tip: If pfile is used and MEMORY_TARGET is set, but the value of MEMORY_MAX_TARGET is not specified, the value of MEMORY_MAX_TARGET after the instance is started is equal to MEMORY_TARGET. If MEMORY_MAX_TARGET is specified in pfile but MEMORY_TARGET is not specified, MEMORY_TARGET is 0 after the instance is started. Www.2cto.com AMM starts a Memory Manager (mman) process in the background. The introduction of AMM makes Oracle memory management more flexible and diverse. There are 5 memory management modes combined. Automatic Memory Management automatic shared memory management manual shared memory management automatic PGA Management Manual PGA Management 1) Automatic Memory Management the default installed instance is AMM. The following SQL> show parameters target NAME TYPE VALUE contains invalid parameter archive_lag_target integer 0 contains integer 1440 fast_start_io_target integer 0 contains integer 0 memory_max_target big integer 1216 M memory_target big integer 1216 M contains big integer 0 sga_target big integer 0 Note that both SGA_TARGET and SGA_TARGET are 0. 2. automatic Shared Memory Management (ASMM) is a Management method introduced by 10 Gb. to use this method, you need to set the initialization parameter MEMORY_TARGET = 0, then explicitly specify the SGA_TARGET value. SQL> alter system set sga_target = 1024 m scope = both; alter system set sga_target = 1024 m scope = both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00839: SGA_TARGET cannot be modified to the specified value SQL> alter system set memory_target = 0 scope = both; System altered. SQL> alter system set sga_target = 1024 m scope = both; System altered. SQL> modify these two parameters There is a strict order. If it does not comply with the rules, there is no problem-Oracle will report an error. 3. Manual shared memory management is more primitive. Because it is original, the new initialization parameters SGA_TARGET and MEMORY_TARGET must be set to 0. Then, manually set the sga parameters such as ipv_pool_size and db_cache_size. Note that the RESULT_CACHE_SIZE parameter is newly introduced at 11 GB to cache SQL results. 4. Automatic PGA memory management if AMM is used, you don't have to worry about PGA. To switch to the automatic PGA memory management mode through fine-grained control, you need to set WORKAREA_SIZE_POLICY = AUTO (AUTO by default), and then specify the PGA_AGGREGATE_TARGET value. To precisely control the PGA, WORKAREA_SIZE_POLICY = MANUAL. (Thanks vongates) 5. Manually manage the PGA on the premise that WORKAREA_SIZE_POLICY = manual, and then specify PGA-related parameters such as SORT_AREA_SIZE. It is estimated that no one is doing this thankless thing now. This mode can be ignored. AMM restrictions if the initialization parameter LOCK_SGA = true, AMM is unavailable. Let's take A look at the impact of Memory_target setting and not setting on SGA/PGA in 11g:: if Memory_target is set to a non-zero value (in the following four cases, the size of SGA and PGA is allocated) 1: the sga_target and pga_aggregate_target parameters have been set. If the sga_target and pga_aggregate_target parameters have been set in Oracle, these two parameters are allocated to the minimum values and their target values respectively. Memory_Target = SGA_TARGET + PGA_AGGREGATE_TARGET. The size is the same as that of memory_max_size. 2: sga_target set size, pga_aggregate_target not set size so pga_aggregate_target initialization value = memory_target-sga_target 3: sga_target no set size, pga_aggregate_target set size so sga_target initialization value = memory_target-pga_aggregate_target 4: if neither sga_target nor pga_aggregate_target is set to the same size as sga_target and pga_aggregate_target in Oracle 11g, Oracle does not set the minimum and default values for these two values. Oracle allocates the size based on the database running status. However, there will be a fixed proportion of distribution at the database startup: www.2cto.com sga_target = memory_target * 60% pga_aggregate_target = memory_target * 40% B: If Memory_target is not set or = 0 (0 by default in 11g) if the default value is 0 in 11g, The Memory_target function is canceled in the initial state. It is completely consistent with 10g in memory management and completely backward compatible. (There are also three cases to allocate the size of SGA and PGA) 1: SGA_TARGET sets the value to automatically adjust the shared pool, buffer cache, redo log buffer, and java pool in SGA, the size of memory space such as larger pool. PGA depends on the size of pga_aggregate_target. Sga and pga cannot automatically increase or decrease. 2: Neither SGA_target nor PGA_AGGREGATE_TARGET set the size of each component in SGA. The size of each component cannot be adjusted automatically. PGA cannot automatically increase or contract. 3: Set MEMORY_MAX_TARGET, but MEMORY_TARGET = 0. This is the same as 10 Gb.