See these important notes scripts for detecting AMM resize operations.

When I first wrote my book "Creating a self tuning Oracle database" (hence revised in "Oracle Tuning: The Definitive Reference"), I noted that the future of Oracle is a proactive method whereby the major Oracle SGA RAM regions (shared pool, buffer caches) can be adjusted in anticipation of changes to processing needs.

When Oracle9i first allowed "alter system" commands to morph the SGA, Oracle 10g introduced Automatic Memory Management, a reactive tool to re-size the RAM regions. 

Note:  When using AMM by setting sga_target and sga_memory_max, the values for the 'traditional? pool parameters (db_cache_size, shared_pool_size, &c) are not ignored.  Rather, they will specify the minimum size that Oracle will always maintain for each sub-area in the SGA.

While AMM is fine for smaller systems, there are reports of AMM causing performance problems:

• They AMM to Please...Sometimes

• Oracle AMM resize operations can hurt performance

In sum, most shops continue to use self-created memory management tools because of the performance hits and the reactive nature of AMM, which does not anticipate upcoming changes in possessing, predictions which can now be made by analyzing STATSPACK and AWR data.

However, in a highly active database, dynamic memory reconfiguration can cause sporadic performance issues.

A case study in dynamic memory management

The IBM P590 AIX Mainframe

I was at a busy OLTP shop recently that had a one terabyte database with a ten gigabyte data buffer cache, running an IBM P590 with 18 CPU's and 128 gig RAM, split into logical partitions (LPAR's) for competing applications.

With only 1% of the data space available for caching, this database was heavily I/O bound, a typical workload profile for a well-tuned large OLTP environment.  Note that this database carries almost 100,000 reads per second, and over 4,000 user calls and about 300 transactions per second, with over 50% of wait times of sequential reads:

• STATSPACK report showing typical large OLTP load

Problems in paradise

The DBA related that he starting to get panicked phone calls from their end-users and he noted a huge rush against their library cache caused by a flood incoming SQL requests.

• STATSPACK report showing library cache load lock bottleneck

At the time, a 10 minute snapshot revealed that this system was experiencing 36,000 logical reads per seconds and it was hung on library cache load locks and pin events, taking over 80% of system elapsed time.

In this case, the DBA decided to increase his shared_pool_size, a act which was not necessarily the root cause of this problem, which I suspect was a flood of non-reusable SQL statements from an ad-hoc reporting tool. 

Another STATSPACK snapshot revealed Oracle in the process of adding RAM to the shared pool:

Note that the database continued to process the load, but we see a rare time-5 timed event "background parameter adjustment", as the "alter system set shared_pool_size=592m" command reconfigured the SGA:

While I've not yet analyzed the details of the DBA's analysis of this problem, it provides a fascinating insight into the behavior of a large OLTP system when the SGA of a busy OLTP system is altered.

Warnings about AMM

Quest Software's Guy Harrison has this warning about using the AMM:

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

Here are my related noted on Oracle dynamic memory management: