Automatic Memory Tuning in Oracle 11g

Automatic Memory Tuning in Oracle 11g is now possible by setting the memory_max_target parameter

In 11g, all memory can be tuned automatically by setting one parameter. Prior to Oracle 11g, the DBA set the sga_target and sga_max_size parameters, allowing Oracle to reallocate RAM within the SGA. The PGA was independent, as governed by the pga_aggregate_target parameter.

Now in Oracle 11g we see the memory_max_target parameter which governs the total maximum RAM for both the PGA and SGA regions and the new memory_target parameter which governs the existing sizes. This allows RAM to be de-allocated from the SGA and transferred to the PGA.

As of Oracle 10g, Automatic Memory Management (AMM) only allows shifting of RAM within the confines of sga_max_size. Now in Oracle 11g, we see this new Automatic Shared Memory Management (ASMM).

The memory_target parameter is dynamic (changeable with “alter system” commands), whereby RAM can be de-allocated from an instances SGA/PGA and re-allocated to another instance.

MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed. In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.

If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero.

After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

This entry was posted in Oracle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s