Automatic memory management is enabled by using the memory_target  and memory_max_target initialization parameters.  The memory_target parameter specifies the amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. The memory_max_target AMM parameter specifies the max size that memory_target may take.

See the script on monitoring SGA RAM changes.

AMM can be problematic!

This is a case study from an actual client, obfuscated to protect their privacy, and showing some issues with using Oracle 10g Automatic Shared Memory Management (ASMM, AMM) and too frequent resizing operation that may be consuming processing resources.  Also see this important issue when using AMM with RAC.

When to use Oracle AMM

Oracle, being the world's most robust and flexible databases, offer a wealth of features, some of which may not be right for you.  In my experience, ASMM (AMM) is best suited to small instances that do not suffer from a RAM shortage.  In large mission-critical instances, a human DBA will manage their pool sizes because AMM does not yet support proactive tuning whereby the DBA can find statistically significant pool usage trends and anticipate pool shortages before they occur. 

AMM is also not well-suited to shops with too-little RAM because ASMM will continuous shuffle memory between pools, attempting to keep up with processing demands.

Warnings about AMM

Quest Software's Guy Harrison has this warning about using the AMM with PL/SQL collections:

"When you use MTS and AMM (or ASMM) together, PL/SQL programs that try to create large collections can effectively consume all available server memory with disastrous consequences . .

AMM allocates virtually all memory on the system to the large pool in order to accommodate the PL/SQL memory request.  First it consumes the buffer cache, then it reduces the PGA_AGGREGATE_TARGET - all the way to zero!"

The evolution of AMM

I first published the conceptual groundwork for AMM during Oracle9i with my book "Creating a Self-Tuning Oracle Database" where I showed how to monitor Oracle in real-time and issue the new 9i "alter system set poolsize=nn" commands to morph the main SGA pool sizes.  I later codified this approach in my book "Oracle Tuning: The Definitive Reference". 

While AMM was one of my favorite 10g new features, AMM has a few shortcomings and it's not yet as good as a human tuning expert.  First, ASMM is a "reactive" memory manager, waiting until a problem occurs before resizing the RAM pools and I have better long-term success by analyzing historical trends in AWR, finding repeating signatures of RAM shortages, and scheduling manual just-in-time resizing.  (For example, we may see a repeating pattern where every Friday at 3:00 PM we need more data buffer space). 

This SearchOracle 10g AMM article notes that there are some issues with the 10g implementation of ASMM, especially with the overhead of the dynamic resizing operations (essentially an "alter system set poolregion=nn" command):

"Schultz, a senior DBA with the University of Illinois, said the AMM feature wreaked havoc within the System Global Area, a "big heap of memory" that is allocated by an Oracle instance and shared among Oracle processes.

"Different memory components were being resized at very rapid paces -- two or three per second or something like that -- and that causes a slowdown in and of itself even without your database having any other cause for slowing down," Schultz explained."

We also see this note from the Indiana University, showing some perils of the earliest releases of AMM:

"The one thing that we think made more difference than anything was turning OFF Oracle's automatic memory management (AMM). With AMM turned off, we then went through some iterations of increasing db_cache_size, shared_pool_size, large_pool_size, and sga_max_size.

We eventually over-tuned these settings and started causing swapping in the OS. We now have backed those down to a reasonable number and Oracle seems to be performing well."

These problems may scare off some Oracle shops, but it's not uncommon for a first release of an important feature to have a few bugs.  ASMM has been enhanced in 11g, but it's still primarily designed for small databases where it is not feasible to proactively optimize the major RAM pools.  For more details intelligent memory allocation, see my latest book "Oracle Tuning: The Definitive Reference". 

Reader comments on ASMM:

I wanted to thank you for an article you wrote about the pitfalls of using ASMM.  I opened a MOSC service request to address the intermittent slowness of our Oracle 10g instance, and went through a long exchange of messages and trace file uploads.

Turns out that when Oracle installs, it sets the DB_CACHE_SIZE at 0.  In our production environment, client database sessions will be inactive for 90 percent of the time.  Perhaps this lack of activity might fool ASMM into allocating much less memory to database buffers, and force the database to perform new seeks with most selects and most updates.  Just speculating.

With our Windows client applications using data-bound controls, it appeared that whenever you edited a field value, the database was doing a seek just to run an update query.

Once I set the DB_CACHE_SIZE, the problem disappeared.

I think using ASMM is fine so long as you set minimum values for various pools and buffer cache.

Alton Chinn

San Francisco Planning Department

For more on 10g automatic memory management, also see my related notes on ASMM:

An actual AMM case study

Below is an AWR report for a database that had numerous issues, foremost a problem with proper allocation of RAM resources.  This is a 60 minute AWR report on a 10.2.0.1 instance.

Below we see a 15 megabyte log_buffer and a 976 meg data buffer:

The buffer cache advisory indicates that doubling the db_cache_size (this instance uses AMM) would only result in a slight drop in disk activity:

The primary bottlenecks on this system is logical I/O (consistent gets in RAM).  This is reflected by the excess amount of CPU time as well as the high waits on User I/O related metrics.  The CPU wait is also being influenced by parsing, which is occurring too frequently on the instance.

Below we see that CPU time consumes more than 90% of total call time during this one hour period:

Let's look close at this clients AMM issue.

Problem with frequent AMM resizing

Oracle 10g allows this instance to employ Automatic Memory Management, which allows the DBA to set one parameter for all memory settings.  Oracle will then manage these memory settings internally and resize them as necessary.

However, this database is showing an incredible number of resizes per day (nearly 50 resizes in a period of 5 hours were seen at one point).

For stable performance, you need stable parameters; changing them too frequently can cause questions as to why performance may rise/fall, seemingly at random.

Below we see the AMM resizing evidence from the AWR report:

Note that during the period above we see

When AMM is used but the DB_CACHE_SIZE and SHARED_POOL_SIZE are still explicitly set, Oracle will use these values as minimums when performing resize operations.
Many Oracle shops have had problems with AMM because of its frequency of resizing. Disabling AMM will provide more stable results for you.

For this system, our recommendation for AMM were:

 Option 1: Turn off AMM by setting parameters manually

Set SGA_MAX_SIZE = 1600M
Set DB_CACHE_SIZE = 1100M
Set SHARED_POOL_SIZE = 200M
Set JAVA_POOL_SIZE = 64M 

Option 2: Keep using AMM, but give it more room

Set SGA_MAX_SIZE = 1600M;
Set SGA_TARGET = 1600M;
Set DB_CACHE_SIZE = 1100M;
Set SHARED_POOL_SIZE = 200M;

 

How to disable AMM:  See these important notes on disabling AMM (Automatic Space Memory Management)