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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

AMM vs. manual RAM allocation tips

Oracle Database Tips by Donald BurlesonJanuary 19, 2015

In an effort to compete with simple databases such as SQL Server, Oracle has undertaken to remove much of the inherent complexity by building automated tools to manage storage, RAM and tuning.

This "umbrella of simplicity" is amazing and has allowed the world's most flexible and robust database to be as simple to use as the lesser databases, using this one-sized-fits-all technique of automation.

However, it is critical to understand that the Oracle automation tools were developed for hands-off, small departmental applications, and automation is not always the best approach for a large mission-critical database with thousands of concurrent users.  Remember, with today's technology, any automated tool will never be able to optimize your database as well as a human expert.

This is especially true for Oracle's automatic memory management (AMM), which is not appropriate for large mission-critical databases, where a human expert can always do a better job in optimizing RAM resources. 

A competent Oracle professional will always be able to do a better job than an automated tool at forecasting and optimizing RAM resources for SQL statements.  This is true for all automated tools, not just Oracle AMM, and there is no automated substitute for human intuition and experience.

The biggest issue with AMM is that it is a "reactive" tool.  Unlike a proactive approach, AMM waits until it detects a problem before changing the SGA pool sizes.  But on the other hand, developing a proactive approach is not easy either. 

To be effective in developing predictive models for your database, you must understand statistical regression techniques and be able to analyze STATSPACK or AWR reports to identify RAM usage trends (called "signatures").  Once the repeating patterns of workload usage are identified (repeating in cycles of hour-of-the-day, day-of-the-week, and day-of the-month), you can automate scripts to re-optimize your SGA pools immediately before the workload changes.

Using predictive modeling techniques you can analyze historical trends, develop signatures of RAM usage, and schedule jobs that morph the SGA just-in-time, anticipating the upcoming workload change and fixing the problem before if effects the end-users.

For complete details and scripts for building a proactive RAM monitoring infrastructure, see my book Oracle Tuning: The Definitive Reference.   
 
Oracle designed AMM to simplify operations on small to midsize databases where it does an adequate job in managing the Ram pool in the absence of a DBA.  However, in case after case on large systems, I have seen frequent AMM re-size operations cause performance problems on Oracle databases.

As of Oracle 11g, I do not recommend using automatic memory management for large or for mission-critical databases.  This is especially true in cases when AMM is used with obsolete tools like shared servers (the multi-threaded server, or MTS). 

 
Never use MTS with AMM!

Using shared servers (MTS) with AMM and PL/SQL can hog all of the RAM on the Oracle instance!  Quest Software's Guy Harrison has this excellent warning about using the AMM with MTS:

"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!"

In sum, while the AMM is great for small hands-off databases with lots of RAM resources, manual RAM allocation will do a better job, especially for mission critical databases.

See my related notes on the perils of AMM:

 

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational