Page: 1 2

In this article I will discuss some issues with using Oracle 10g Automatic Memory Management (AMM) and too frequent resizing operation that may be consuming processing resources. 

Oracle AMM Pitfalls:

Although Oracle is the most robust and flexible databases and offer a large number of features for the ease of its users but there are some features which may not be right for you.  AMM is best suited to small instances that do not suffer from a RAM shortage. 

No support for Proactive Tuning:

AMM does not yet support proactive tuning whereby the DBA can find statistically significant pool usage trends and anticipate pool shortages before they occur. 

Memory Shuffle:

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

Reactive Memory Manager:

AMM is a reactive memory manager that waits until a problem occurs before resizing the RAM pools. 

Overhead of Dynamic Resizing Operations:

There are some issues with the 10g implementation of AMM, especially with the overhead of the dynamic resizing operations essentially an " alter system set poolregion=nn " command.

System Global Area:

AMM feature causes chaos within the System Global Area, a big heap of memory that is allocated by an Oracle instance and shared among Oracle processes.With AMM turned off, we see some iterations of increasing db_cache_size, shared_pool_size, large_pool_size, and sga_max_size .

Unsuitable for major RAM Pools:

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

AWR Report:

Consider below AWR report with a problem in proper allocation of RAM resources. We see a 15 megabyte log_buffer and a 976 mega data buffer:  

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 960M 976M Std Block Size: 8K
Shared Pool Size: 160M 144M Log Buffer: 15,192K

This system 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

Page: 1 2

 More Database Articles
   Database Security: Step by step guideline
   Performance Boost by improving Optimizer Statistics!
   Oracle – SQL Guide for Parallel Stored Procedures
   Let’s reveal the magic of Oracle Database with Web Services!!
   Testing the Oracle Recovery Plan