Oracle Memory Management Theory Chapter II

Goal

• Learn how Oracle Memory management works
• Master the way of ASMM management
• Master the way of AMM management
• Monitor Memory usage

When learning a point of knowledge, it is better to understand its history first. Oracle's recent releases have simplified memory management, from 9i through the Pga_aggregate_target parameters to achieve the PGA automatic management, 10g through automatic Shared memory Management (ASMM) automates the management of the SGA, and the automatic management of memory (AMM) is achieved by the 11g through Automatic Memories Management (SGA+PGA). With the current version of 11G, the DBA only needs to set 2 or one parameter to enable Oracle's automatic memory management.

Oracle's memory management approach

Oracle 11g introduces Parameters Mymory_max_target and Memory_target are the new features of Oracle automatic memory management, the introduction of AMM, a combination of 5 memory management forms.

• Automatic memory Management (AMM): memory_target= non-0, is automatic memory management, if the initialization parameter LOCK_SGA = True, then AMM is not available.
• Automatic shared memory Management (ASMM): Automatic memory Management in memory_target=0 and sga_target= not 0
• Manual shared Memory Management: memory_target=0 and sga_target=0 specify SGA parameters such as share_pool_size, db_cache_size, etc.
• Auto PGA Management: memory_target= non 0 and Workarea_size_policy=auto and pga_aggregate_target= values
• Manual PGA Management: Memory_target=0 and workarea_size_policy=manal then specify the PGA parameters such as sort_area_size, typically not using manual management of the PGA

ASMM Management method

The 1.1 ASMM is administered to the SGA (shared pool;buffer pool;large Pool;java pool;stream pool;redo buffer pool).    Note: Buffer pools are not included (Keep buffer pool; Recycle buffer pool;nk buffer pool) 1.2 Set ASMM management    i) get I1 value from manual shared memory management mode enabled ASMM        sga_target):               Select ((select SUM (value) from V$SGA) – (select Current_size from V$sga_dynamic_free_memory)) ' Sga_target ' from               DUAL;                 I2) Use this value to set the Sga_target.        i3) Set the (shared_pool_size,large_pool_size,java_pool_size,db_cache_size,streams_pool_size) value to 0.                              II) switch from automatic memory management mode (AMM) to asmm    II1) to set the initialization parameter Memory_target to 0.              ALTER SYSTEM SET memory_target = 0;              The database will set sga_target based on the current SGA memory allocation.        ii2) Set the value of the auto-sized SGA component (shared Pool;buffer pool;large Pool;java pool;stream pool;redo buffer pool) to 0.            

Amm Management method

1.1 Amm is administered to the SGA (shared pool;buffer pool;large Pool;java pool;stream pool;redo buffer pool) +PGA. Note: At this time Sga_target is 0 meaningless 1.2 set AMM Management 1. Use Sqlplus to SYSDBA link Database 2. Calculate the minimum value of Memory_target a. Determine current Sga_target and Pga_aggregate_targe T sql> Show parameter target NAME TYPE VALUE-------------- -------------------------------------------------archive_lag_target integer 0 DB_FL Ashback_retention_target integer 1440 fast_start_io_target integer 0 fast_sta                        Rt_mttr_target integer 0 memory_max_target big integer 0 memory_target Big integer 0 parallel_servers_target integer pga_aggregate_targe          T big integer 90M sga_target big integer 272M b. Querying the largest PGA since the database was started Select value from V$pgastat where name= ' maximum PGA allocated ';  C. calculate Memory_target memory_target = Sga_target + max (Pga_aggregate_target, maximum PGA allocated) For example: Sga_target = 272M   Pga_aggregate_target = 90M, and the maximum PGA allocated is 120M, then Memory_target >= 392M (272M + 120M).    3. Refer to 2c, modify the value of Memory_target alter SYSTEM SET memory_target=392m; 1.3 The relationship between Memory_target,sga_target and Pga_aggregate_target if you set Sga_target and Pga_aggregate_target to a value other than 0,        It can be considered as the lower value of the SGA and PGA sizes, respectively.  Memory_target can take values from Sga_target+pga_aggregate_target to Memory_max_size

Monitor Memory usage

        The v$memory_dynamic_components contains the current state of all memory components        v$memory_resize_ops a         loop history buffer that contains the most recently completed  memory resizing request        v$ Memory_target_advice      provides optimization recommendations for Memory_target initialization parameters