Automatic Memory Management(AMM)…

 

Reference:

http://www.oracledatabase12g.com/archives/automatic-memory-managementamm-on-11g.html

 

---

 Modified04-MAR-2009     TypeBULLETIN     StatusPUBLISHED 

In this Document
  Purpose
  Scopeand Application
  AutomaticMemory Management(AMM) on 11g
     
     Introductionto Database Memory Components
     Evolutionof Memory Management Features
     1.AutomaticMemory Management – For Both the SGA and Instance PGA
     2.Automatic Shared Memory Management – For the SGA
     3.Manual Shared Memory Management – For the SGA
     4.Automatic PGA Memory Management – For the Instance PGA
     5.ManualPGA Memory Management – For the Instance PGA
  References

---

Applies to:

Oracle Server Enterprise Edition - Version: 11.1.0.6 to11.1.0.8
Information in this document applies to any platform.
Oracle Server - Enterprise Edition - Version: 11.1.0.6

Purpose

The main goal of this document is to discuss Automatic MemoryManagement(AMM) features introduced with Oracle11g and how toenable it.

Scope andApplication

All the DBAs and users concerned with Database Administrationactivities.

AutomaticMemory Management(AMM) on 11g

Introduction toDatabase Memory Components

The basic memory structures associated with Oracle Databaseinclude:

■ System Global Area (SGA)

The SGA is a group of shared memory structures, known as SGAcomponents, that contain data and control information for oneOracle Database instance. The SGA is shared by all server andbackground processes.

■ Program Global Area (PGA)

A PGA is a memory region that contains data and control informationfor a server process. It is nonshared memory created by OracleDatabase when a server process is started. Access to the PGA isexclusive to the server process. There is one PGA for each serverprocess. Background processes also allocate their own PGAs. Thetotal PGA memory allocated for all background and server processesattached to an Oracle Database instance is referred to as the totalinstance PGA memory, and the collection of all individual PGAs isreferred to as the total instance PGA, or just instance PGA.

It contains global variables and data structures and controlinformation for a server process. example of such information isthe runtime area of a cursor. Each time a cursor is executed, a newruntime area is created for that cursor in the PGA memory region ofthe server process executing that cursor.

The performance of complex long running queries, typical in a DSSenvironment, depend to a large extent on the memory available inthe Program Global Area (PGA) which is also called work area.

Below figure illustrates the relationships among these memorystructures.

Evolution of MemoryManagement Features

Memory management has evolved with each database release

Oracle 9i

Beginning with Oracle9i, the dynamic SGA infrastructure allowed forthe sizing of the Buffer Cache, Shared Pool and the Large Poolwithout having to shutdown the database. Key features being

• Dynamic Memory resizing
• DB_CACHE_SIZE instead of DB_BLOCK_BUFFERS
• DB_nK_CACHE_SIZE for multiple block sizes
• PGA_AGGREGATE_TARGET Introduction of Automatic PGA Memorymanagement
  

Oracle Database 10g
 
Automatic Shared Memory Management (ASMM) was introduced in 10g.You enable the automatic shared memory management feature bysetting the SGA_TARGET parameter to a non-zero value.

Oracle Database 11g

Automatic Memory Management is being introduced in 11g. Thisenables automatic tuning of PGA and SGA with use of two newparameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

The most important SGA components are the following:

Component
Description
Parameter
Database Buffer CacheThe database buffer cache is theportion of the SGA that holds copies of data blocks read fromdatafiles. All users concurrently connected to the instance shareaccess to the database buffer cache.
DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE

Redo Log Buffer
The redo log buffer is a circularbuffer in the SGA that holds information about changes made to thedatabase. This information is stored in redo entries. Redo entriescontain the information necessary to reconstruct, or redo, changesmade to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, orDROP operations. Redo entries are used for database recovery, ifnecessary.
LOG_BUFFER
Shared Pool
The shared pool portion of theSGA contains the library cache, the dictionary cache, the resultcache, buffers for parallel execution messages, and controlstructures.
SHARED_POOL_SIZE
SHARED_POOL_RESERVED_SIZE
RESULT_CACHE_SIZE *

Large Pool
Used for allocating sessionmemory for shared server, Oracle XA, or parallel query buffers orfor RMAN.
LARGE_POOL_SIZE
Java Pool
Java pool memory is used inserver memory for all session-specific Java code and data withinthe JVM.
JAVA_POOL_SIZE
Streams Pool
The streams pool is usedexclusively by Oracle Streams. The Streams pool stores bufferedqueue messages, and it provides memory for Oracle Streams captureprocesses and apply processes.
STREAMS_POOL_SIZE

* RESULT_CACHE_MAX_SIZE isnew component which has been introduced as part of 11g Memoryarchitecture. The result cache is composed of the SQL query resultcache and PL/SQL function result cache, which share the sameinfrastructure.Results of queries and query fragments can be cachedin memory in the SQL query result cache. The database can then usecached results to answer future executions of these queries andquery fragments.  Similarly PL/SQL Function Resultcan also be cached.

You have to use RESULT_CACHE_MODE initialization parameter whichdetermines the SQL query result cache behavior. The possibleinitialization parameter values are MANUAL and FORCE.

Oracle Database 11g supports various memorymanagement methods, which are chosen by initialization parametersettings. Oracle recommends that you enable the automatic memorymanagement method.

1. Automatic Memory Management – For Both the SGAand Instance PGA
2. Automatic Shared Memory Management – For theSGA
3. Manual Shared Memory Management – For theSGA
4. Automatic PGA Memory Management – For the InstancePGA
5. Manual PGA Memory Management – For the InstancePGA

1.Automatic MemoryManagement – For Both the SGA and Instance PGA

Beginning with Oracle Database 11g, Oracle Database can manage theSGA memory and instance PGA memory completely automatically. Youdesignate only the total memory size to be used by the instance,and Oracle Database dynamically exchanges memory between the SGAand the instance PGA as needed to meet processing demands. Thiscapability is referred to as automatic memory management. With thismemory management method, the database also dynamically tunes thesizes of the individual SGA components and the sizes of theindividual PGAs.

To achieve this, two new parameters have been introduced namedMEMORY_MAX_TARGET andMEMORY_TARGET. To do so (on most platforms), youset only a target memory size initialization parameter(MEMORY_TARGET) and optionally a maximum memory size initializationparameter (MEMORY_MAX_TARGET).

If you create your database with DatabaseConfiguration Assistant (DBCA) and choose the basic installationoption, automatic memory management is enabled. Ifyou choose advanced installation, Database Configuration Assistant(DBCA) enables you to select automatic memory management.

Switching to Automatic MemoryManagement

1)Check the current values configured for SGA_TARGET andPGA_AGGREGATE_TARGET.

SQL>SHOW PARAMETER TARGET
NAME TYPE VALUE
------------------------------ ----------- ----------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 200M
sga_target big integer 500M

Add the values of pga_aggregate_target and sga_target. In our caseit would sum to 700 M

2)Decide on a maximum amount of memory that you would want toallocate to the database which will determine the maximum value forthe sum of the SGA and instance PGA sizes. In our case we decide toset to 808M

3)Change the parameter in initialization parameter file.

Using Spfile
========

SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 808MSCOPE = SPFILE;
SQL>ALTER SYSTEM SET MEMORY_TARGET = 808M SCOPE = SPFILE;
SQL>ALTER SYSTEM SET SGA_TARGET =0SCOPE = SPFILE;
SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0SCOPE = SPFILE;


Using Pfile
=======
If you have started the instance with Pfile, then edit the pfileand set the parameters manually


MEMORY_MAX_TARGET = 808M
MEMORY_TARGET = 808M
SGA_TARGET =0
PGA_AGGREGATE_TARGET = 0

In case you do not specify any value for MEMORY_MAX_TARGET and onlyuse MEMORY_TARGET then database automatically setsMEMORY_MAX_TARGET to the value of MEMORY_TARGET.

If you omit the line for MEMORY_TARGET and include a value forMEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero.After startup, you can then dynamically change MEMORY_TARGET to anon-zero value, provided that it does not exceed the value ofMEMORY_MAX_TARGET.

MEMORY_MAX_TARGET is a static parameter i.eit cannot be changed Dynamically and Instance has to be bounced formodifying the value. So ensure that you have set it to appropriatevalue.

4)Shutdown and startup the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  845348864 bytes
Fixed Size                  1303188 bytes
Variable Size             469765484 bytes
Database Buffers          369098752 bytes
Redo Buffers                5181440 bytes
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 808M
memory_target                        big integer 808M
pga_aggregate_target                 big integer 0
sga_target                           big integer 0



The preceding steps instruct you to set SGA_TARGET andPGA_AGGREGATE_TARGET to zero so that the sizes of the SGA andinstance PGA are tuned up and down as required, withoutrestrictions. You can omit the statements that set these parametervalues to zero and leave either or both of the values as positivenumbers. In this case, the values act as minimum values for thesizes of the SGA or instance PGA.

Note : - In case you setany parameter value to value which is higher then MEMORY_TARGET,then you get the following error. E.g Setting SGA_MAX_SIZE to valueof 900 M results in following

SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account, seealert log for more information

00844, 00000, "Parameter not taking MEMORY_TARGET into account, seealert log for more information"
// *Cause: The parameter was larger than MEMORY_TARGET.
// *Action: Set the parameter to a lower value thanMEMORY_TARGET.

Monitoring and Tuning Automatic MemoryManagement

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows thecurrent sizes of all dynamically tuned memory components, includingthe total sizes of the SGA and instance PGA.
The view V$MEMORY_TARGET_ADVICE provides tuning advice for theMEMORY_TARGET initialization parameter.

SQL> select * from v$memory_target_advice orderby memory_size;

You can also use V$MEMORY_RESIZE_OPS which has a circular historybuffer of the last 800 SGA resize requests.

2. Automatic SharedMemory Management – For the SGA

If you want to exercise more direct control over the size of theSGA, you can disable automatic memory management and enableautomatic shared memory management.This feature was introduced in10g with a parameter known as SGA_TARGET. Whenautomatic SGA memory management is enabled, the sizes of thedifferent SGA components are flexible and can adapt to the needs ofcurrent workload without requiring any additionalconfiguration.

Please refer to following document for setting SGA_TARGET

Note 295626.1 - How To Use Automatic Shared Memory Management(ASMM) In Oracle10g

In case you have enabled Automatic Memory Management , then toswitch to Automatic Shared Memory Management , please follow belowprocedure

SQL>Alter system set MEMORY_TARGET=0scope=both;
SQL>Alter system set SGA_TARGET=500Mscope=both;


3. Manual SharedMemory Management – For the SGA

If you want complete control of individual SGA component sizes, youcan disable both automatic memory management and automatic sharedmemory management. In this mode, you need to set the sizes ofseveral individual SGA components, thereby determining the overallSGA size. You then manually tune these individual SGA components onan ongoing basis.

In this case you set SGA_TARGET and MEMORY_TARGET to 0 and setvalue for other SGA components upto value of SGA_MAX_SIZE

Please refer to Note 148495.1 - Dynamic SGA

4. Automatic PGAMemory Management – For the Instance PGA

While using Automatic memory management , PGA memory is allocatedbased upon value of MEMORY_TARGET. In case you enable automatic shared memory management or manual shared memorymanagement, you also implicitly enable automatic PGA memorymanagement.

Automatic/Manual PGA memorymanagement is decided by  initialization parameterWORKAREA_SIZE_POLICY  which is asession- and system-level parameter that can take only two values:MANUAL or AUTO. The default is AUTO.

With automatic PGA memory management, you set a target size for theinstance PGA by defining value for parameter namedPGA_AGGREGATE_TARGET and sizing of SQL work areasis automatic and all *_AREA_SIZE initialization parameters areignored for these sessions. This feature is available from9i.

At any given time, the total amount of PGA memory available toactive work areas on the instance is automatically derived from theparameter PGA_AGGREGATE_TARGET. This amount is set to the value ofPGA_AGGREGATE_TARGET minus the PGA memory allocated for otherpurposes (for example, session memory). The resulting PGA memory isthen allotted to individual active work areas based on theirspecific memory requirements.

Please refer to following document for more details on AutomaticPGA Memory Management

Note 223730.1 - Automatic PGA Memory Managment in 9i and10g

5.Manual PGA MemoryManagement – For the Instance PGA

In case you wish to manually specify the maximum work area size foreach type of SQL operator (such as sort or hash-join) then you canenable Manual PGA Memory management.

Set WORKAREA_SIZE_POLICY value to MANUAL and also specify valuesfor *_area_size such as SORT_AREA_SIZE, HASH_AREA_SIZE,BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc.


Although the Oracle Database 11g supports this manual PGA memorymanagement method, Oracle strongly recommends that you leaveautomatic PGA memory management enabled.

Table below summarizes the various memory management methods

Memory Management Mode
For
You Set
Oracle Database AutomaticallyTunes
Automatic memorymanagement(AMM)


SGA and PGA- Total memory target size forthe Oracle instance (Memory_Target)

- (Optional) Maximum memory size for the Oracleinstance(Memory_max_Target)
- Total SGA size
- SGA component sizes
- Instance PGA size
- Individual PGA sizes

Automatic shared memorymanagement(ASMM)

(AMM disabled)


SGA
- SGA target size(SGA_TARGET)
- (Optional) SGA maximum size
(SGA_MAX_SIZE)
SGA component sizes
Manual shared memorymanagement

(AMM and ASMM disabled)


SGA
- Shared pool size(SHARED_POOL_SIZE)

-Buffer cache size
(DB_CACHE_SIZE or DB_BLOCK_BUFFERS)

-Java pool size
(JAVA_POOL_SIZE)

-Large pool size
(LARGE_POOL_SIZE)

- (Optional) SGA maximum size
(SGA_MAX_SIZE)


None
Automatic PGA memorymanagement

PGA
Instance PGA target size(PGA_AGGREGATE_TARGET)
Individual PGA sizes
Manual PGA memorymanagement

(not recommended)


PGA
Maximum work area size for eachtype of SQL operator

None


The Automatic  Memory Management (AMM) featureuses background process named Memory Manager(MMAN). This process was introduced in 10g which assistedin Automatic Shared Memory Management (ASMM) using SGA_TARGET. MMANserves as the SGA Memory Broker and coordinates the sizing of thememory components. The SGA Memory Broker keeps track of the sizesof the components and pending resize operations

References

NOTE:148495.1- Dynamic SGA
NOTE:223730.1 - Automatic PGA Memory Management in 9i and10g
NOTE:295626.1 - How To Use Automatic Shared Memory Management(ASMM) In Oracle10g

---

Related

---

Products

---

• Oracle Database Products > Oracle Database> Oracle Database > Oracle Server -Enterprise Edition

Keywords

---

SHARED POOL; INITIALIZATION PARAMETERS; DYNAMICSGA; DB_CACHE_SIZE; SHARED_POOL_SIZE; SGA_MAX_SIZEErrors

---

ORA-844