Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle Automatic memory management (AMM) resize operations can hurt performance

Oracle Case Studies by Donald BurlesonOctober 15, 2015

See these important notes scripts for detecting AMM resize operations.

Notes:  ASMM  (AMM) and dynamic memory management has measurable overhead for resize operations, and some shop may want to disable AMM.  See my important notes on

Oracle dynamic memory management and how Oracle AMM resize operations can hurt performance with AMM resize scripts



Monitoring AMM Resize operations


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 instance.


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

Cache Sizes
~~~~~~~~~~~                       Begin        End
                             ---------- ----------
               Buffer Cache:       960M       976M  Std Block Size:         8K
           Shared Pool Size:       160M       144M      Log Buffer:    15,192K
This systems does 37k logical I/O's per second and only 11k disk reads per second:
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              2,032.83              3,143.61
              Logical reads:             37,829.33             58,499.90
              Block changes:                 10.37                 16.03
             Physical reads:             11,504.10             17,790.13
            Physical writes:                  0.45                  0.70
 . . .
  % Blocks changed per Read:    0.03    Recursive Call %:    76.37
 Rollback per transaction %:    0.09       Rows per Sort:    61.98
Note the 69% value for the buffer cache hit ratio.  This is not a concern because physical I/O is not a major wait event.
Instance Efficiency Percentages (Target 100%)
            Buffer Nowait %:   91.78       Redo NoWait %:   99.99
            Buffer  Hit   %:   69.59    In-memory Sort %:  100.00
            Library Hit   %:   85.19        Soft Parse %:   66.47
         Execute to Parse %:   20.23         Latch Hit %:   97.97
Parse CPU to Parse Elapsd %:   97.00     % Non-Parse CPU:   96.89
 Shared Pool Statistics        Begin    End
                              ------  ------
             Memory Usage %:   65.27   72.54
    % SQL with executions>1:   84.15   76.02
  % Memory for SQL w/exec>1:   92.84   88.18

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:

Buffer Pool Advisory                           DB/Inst: xxxx  Snap: 12101 
    Size for   Size      Buffers for   Read          Estimated
P    Est (M) Factor         Estimate Factor     Physical Reads
--- -------- ------ ---------------- ------ ------------------
D         96     .1           12,012    1.4     46,913,196,021
. . .
D        976    1.0          122,122    1.0     33,191,171,216
. . .
D      1,920    2.0          240,240    0.8     26,873,748,355

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:

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time                                          4,196          91.3
read by other session            11,210,906         498      0   10.8   User I/O
db file scattered read            8,482,493         448      0    9.8   User I/O
db file sequential read           5,895,042         107      0    2.3   User I/O
log file sync                         2,211          11      5    0.2     Commit


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:



SGA Memory Summary                      DB/Inst: xxxx/xxxx  Snaps: 12100-12101
                                                      End Size (Bytes)
SGA regions                     Begin Size (Bytes)      (if different)
------------------------------ ------------------- -------------------
Database Buffers                     1,006,632,960       1,023,410,176
Fixed Size                               1,219,160
Redo Buffers                            15,556,608
Variable Size                          234,882,472         218,105,256
sum                                  1,258,291,200
SGA breakdown difference                DB/Inst: xxxx/xxxx  Snaps: 12100-12101
-> ordered by Pool, Name
-> N/A value for Begin MB or End MB indicates the size of that Pool/Name was
   insignificant, or zero in that snapshot
Pool   Name                                 Begin MB         End MB  % Diff
------ ------------------------------ -------------- -------------- -------
java   free memory                              16.0           16.0    0.00
large  free memory                              15.9           15.9    0.00
shared ASH buffers                               8.0            8.0    0.00
shared CCursor                                   3.6            3.7    1.85
shared KCB Table Scan Buffer                     4.0            4.0    0.00
shared KGLS heap                                 1.9            2.1    5.79
shared KQR M PO                                  4.1            4.1    0.00
shared KSFD SGA I/O b                            4.0            4.0    0.00
shared PCursor                                   2.4            2.4   -3.42
shared XDB Schema Cac                            4.1            4.1    0.00
shared db_block_hash_buckets                     2.1            2.1    0.00
shared free memory                              55.6           39.5  -28.85
shared kglsim hash table bkts                    2.0            2.0    0.00
shared kglsim heap                               2.2            2.2    0.00
shared kglsim object batch                       5.3            5.3    0.00
shared library cache                            18.8           18.6   -0.86
shared row cache                                 3.6            3.6    0.00
shared sql area                                  9.8            9.8    0.27
stream free memory                              32.0           32.0    0.00
       buffer_cache                            960.0          976.0    1.67
       fixed_sga                                 1.2            1.2    0.00
       log_buffer                               14.8           14.8    0.00


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

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

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


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




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.