Friday, September 16, 2011

Automatic Shared Memory Management ( ASMM ) In Oracle 10g

Automatic Shared Memory Management ( ASMM ) In Oracle 10g


Concept


In Oracle version 9.0.1, some SGA parameters have been defined as 'Dynamic' parameters. The "ALTER SYSTEM" command may be used to grow/shrink their current values.

The whole SGA size is limited by the SGA_MAX_SIZE parameter that is the defined upper limit. Each SGA parameter is allocated in terms of Granules that is the Allocation Unit. The Granule size will depend on the SGA_MAX_SIZE value and hardware platform.

Depending on the 9i version, the following parameters have been defined as dynamic parameters:

9.0.1: Shared Pool and Default Buffer Cache
9.2.0: Shared Pool, Default Buffer Cache, Large Pool

When you grow/shrink the dynamic size of one of the above parameters, the freed memory won't be reallocated to another dynamic component automatically. You must do it manually if needed.

In 10G, the ASMM has been introduced to relieve DBAs from sizing some parts of the SGA by them.

When enabled, it lets Oracle decide of the right size for some components of the SGA:

  • Database buffer cache (Default pool)
  • Shared pool
  • Large pool
  • Java pool
  • 10gR2 the streams pool is included

They are called auto-tuned parameters.

The main objectives to justify this new functionality are:



Distribute the available memory depending of the current Workload. The Automatic Shared Memory Management feature uses a new background process named Memory Manager (MMAN). MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resizes operations.
.
Enhance the memory usage depending of the activity. Avoid the memory errors like ORA-4031.

Configuration


The ASMM is driven by one init parameter: SGA_TARGET.
When set to 0, the ASMM is disabled and you run with the old method, so you need to define the above auto-tuned parameters by yourself.
The default value for SGA_TARGET is 0 so ASMM disabled.

The conditions to enable the ASMM mechanism are:
STATISTICS_LEVEL=TYPICAL or ALL
SGA_TARGET > 0
When you use a value greater than 0, the ASMM is enabled and the memory will be spread between all components: auto-tuned and manual parameters.

The SGA_TARGET value will therefore define the memory size sharable between auto-tuned and manual parameters.
The manual parameters are:
DB__CACHE_SIZE
DB_nK_CACHE_SIZE (non default block size)
LOG_BUFFER
FIXED SGA
STREAMS_POOL_SIZE
Amonst these manual parameters, some of them are modifiable or fixed (defined at startup only):
Modifiable: DB__CACHE_SIZE, STREAMS_POOL_SIZE
Fixed: DB_nK_CACHE_SIZE, FIXED SGA, LOG_BUFFER

Based on workload information, automatic shared memory tuning:

  • Captures statistics periodically in the background
  • Uses the different memory advisories
  • Performs "what-if" analysis to determine best distribution of memory
  • Moves memory to where it is most needed
  • Has no need to configure parameters for the worst-case scenario
  • Resurrects component sizes from last shutdown if SPFILE is used

Post a Comment