Automatic Memory Management (AMM) in Oracle Database 11g Release 1
Oracle 9i automated PGA management by introducing PGA_AGGREGATE_TARGET parameter. Oracle 10g continued this trend by automating SGA management using the SGA_TARGET parameter. Oracle 11g takes this one step further by allowing you to allocate one chunk of memory, which Oracle uses to dynamically manage both the SGA and PGA.
AMM Parameters
Automatic memory management is configured using two new initialization parameters:
- MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the
MEMORY_MAX_TARGETlimit. The default value is "0". - MEMORY_MAX_TARGET: This defines the maximum size the
MEMORY_TARGETcan be increased to without an instance restart. If theMEMORY_MAX_TARGETis not specified, it defaults toMEMORY_TARGETsetting.
When using automatic memory management, the
SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.If you are using UNIX/Linux, before you consider using AMM you should check the current size of your shared memory file system. On Linux you do this by issuing the following command.
# df -k /dev/shmFilesystem 1K-blocks Used Available Use% Mounted ontmpfs 1029884 350916 678968 35% /dev/shm#The shared memory file system should be big enough to accommodate the
MEMORY_TARGET and MEMORY_MAX_TARGET values, or Oracle will throw the following error.ORA-00845: MEMORY_TARGET not supported on this systemTo adjust the shared memory file system size issue the following commands, specifying the required size of shared memory.
# umount tmpfs# mount -t tmpfs shmfs -o size=1200m /dev/shmAMM Configuration
Enabling automatic memory management on a system that didn't previously use it is a simple task. Assuming you want to use a similar amount of memory to your current settings you will need to use the following calculation.
MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, "maximum PGA allocated")AMM Tuning
In addition to the existing memory management V$ views, Oracle 11g includes four new V$ views to support automatic memory management:
Memory Management Data Dictionary Views
View |
V$SGA |
V$SGAINFO |
V$SGASTAT |
V$PGASTAT |
V$MEMORY_DYNAMIC_COMPONENTS |
V$SGA_DYNAMIC_COMPONENTS |
V$SGA_DYNAMIC_FREE_MEMORY |
V$MEMORY_CURRENT_RESIZE_OPS |
V$SGA_CURRENT_RESIZE_OPS |
V$MEMORY_RESIZE_OPS |
V$SGA_RESIZE_OPS |
V$MEMORY_TARGET_ADVICE |
V$SGA_TARGET_ADVICE |
V$PGA_TARGET_ADVICE |
Automatic SQL Execution Memory Management (PGA_AGGREGATE_TARGET)
http://www.oracle-base.com/articles/9i/MemoryManagement9i.php#AutomaticSQLExecutionMemoryManagement
Prior to Oracle9i optimization of the PGA memory structures could be very time consuming depending on the type of operations the system was performing. Oracle9i allows the DBA to leave configuration of the PGA up to Oracle by setting two initialization parameters:
WORKAREA_SIZE_POLICY = AUTOPGA_AGGREGATE_TARGET = 100000KThe
WORKAREA_SIZE_POLICY parameter tell the server that it should take over PGA memory management. The PGA_AGGREGATE_TARGET parameter specifies the total amount of memory the server can allocate to the PGA. Oracle quote the following equations as a base for calculating the value of this parameter:PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 20% for an OLTP systemPGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 50% for a DSS systemWhere TOTAL_MEMORY is the total available memory for the system. If multiple applications/instances are running on the machine the values should be adjusted accordingly.
For backwards compatibility Oracle9i allows manual configuration of the PGA using:
For backwards compatibility Oracle9i allows manual configuration of the PGA using:
WORKAREA_SIZE_POLICY = MANUALSORT_AREA_SIZE = ???HASH_AREA_SIZE = ???BITMAP_MERGE_AREA_SIZE = ???CREATE_BITMAP_AREA_SIZE = ???
No comments:
Post a Comment