Memory Parameter in Oracle11g

Oracle is enhancing the memory management in each version starting from oracle9i. Oracle9i introduced pga_aggregate_target to dynamically manage the memory for PGA. Oracle went one step further in 10g and introduced sga_target parameter to dynamically allocate the SGA memory. Oracle introduced memory_target parameter in oracle11g and dynamically allocate both SGA and PGA memory. This is one of exciting features that takes care of SGA as well as PGA in one parameter. We let Oracle to adjust the memory as the workload needs it.

I would like to write how this memory parameter works in oracle11g. How the memory is configured on each sub component when memory_target parameter is set to non zero?

Oracle11g introduced two memory parameters(memory_target, memory_max_target). oracle has become smart as in exchanging memory between SGA and PGA. This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.

Question and Answers :

1. What is the advantage of memory_target parameter? I do not see any reason to not to use this memory management parameter. If we set this parameter, then we do not need to size all other sub component of this parameter. Sub component parameters are like SGA_TARGET, PGA_AGGREGATE_TARGET etc. These memory area can be managed dynamically.

2. What is the best place to leverage this feature? If your database running in mixed load, Oracle will adjust all your memory setting accordingly to DB load. Let us say, your database is running on OLTP mode in the day time, and running batch job during the night time. DBA can not adjust the different component of the memory according to Database activity. This memory_target parameter balance the memory between shared_pool_size, redolog_buffer, large_pool_size, java_pool_size etc. This is ideal scenario to use this feature.

3. How do we enable AMM feature ? This feature can be enabled by setting memory_target and max_memory_target to non-zero values. LOCK_SGA parameter should be set to FALSE to enable this feature.

4. How do we disable AMM feature ? This feature can be disabled by setting zero for memory_target parameter. Please remember, sga_target, pga_aggregate_target parameter should be sized according to DB activities.

5. Do we need to check the Shared memory in server before implementing AMM? Yes. We need to check the current size of your shared memory file system before we set the AMM feature. On linux, we need to use the below command to check the shared memory.

[oracle@rate9812~]$ df -k /dev/shm

Filesystem 1k-blocks Used Available Use% Mounted on tmpfs

2023256 1065000 958256 53% /dev/shm

[oracle@rate9812~]$

Here the shared memory is around 2GB. So the memory target can be more than 2GB. If it is more than 2GB, the we get the below error.

ORA-00845: MEMORY_TARGET not supported on this system

6. Do we have any new data dictionary views to monitor AMM? Yes. we do have new views. Here are some. These views helps to monitor the Memory utilization.

V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE

6. How does this feature differ from Oracle10g memory feature? This parameter covers both SGA and PGA. In Oracle10g, we use individual parameter for SGA and PGA. Now it is not required to define or size the memory for SGA and PGA seperately.

7. Do we need to take care of any memory parameters after AMM feature is enabled? Log buffer and the non-default block size caches not part of dynamic memory allocation. These memory should be taken care manually.

The new data dictionary V$MEMORY_DYNAMIC_COMPONENTS will give the list of components which is taken care by database when we enable AMM feature.

Here are relevant topic to this article.

Oracle9i Memory feature
Oracle10g Memory feature
Oracle Memory components