Wednesday, August 30, 2006

Automatic Shared Memory Management

Starting with Oracle 10G R1 there is a new parameter sga_target which determines whether you want Oracle to manage the sizes of various pools within the SGA or whether you want to manually manage memory allocated to Oracle.
To use this feature the value of STATISTICS_LEVEL should either be set to TYPICAL or ALL.

But this feature is not without its fair share of issues especially on large and intensive databases.

The reason for this is two fold.
The following bugs exist

4587117 (P) LARGE_POOL_SIZE AND JAVA_POOL_SIZE DO NOT SHRINK IN ASMM Fixed in 11.0
4466399 (U)
4472338 (U)
(duplicate of the above 4466399)
4507532 (U)
(turned out to be a bad diagnosis, but the bug is real)
4920199 (U)

Secondly the default behaviour is that if the shared pool is stressed and requires more memory it is removed from the buffer cache. But the reverse is not true. However this default behaviour can be changed by setting _memory_broker_shrink_heaps=30
The default value for the above parameter is 0

The shrinking of the Java pool depends on the value of _memory_broker_shrink_java_heaps. The default value is 900.

You can set the following event to trace memory management. The MMON process trace file will update statistics related to the different pools every 5 mins.

SQL> alter system set "_memory_management_tracing"=31;

System altered.

The default value for the above parameter is 0

1 comment:

Paul said...

Thanks for this ASMM post it was most helpful.
PB