Automatic Memory Management in Oracle 11g
Since quite a few days I had the chance to look at parts of the beta documentation for the Oracle 11g database and since a few days I also have the beta software.
One of the new features coming up is the enhencement of automatic memory management.
The whole automatic memory thing had already started with the release Oracle 9i where the parameter SGA_MAX_SIZE was introduced, to limit the maximum memory Oracle can use for the SGA. Within SGA_MAX_SIZE a number of caches were dynamically, but still manually adjustable. These caches included the
DB BUFFER CACHE, the
SHARED POOL, the
JAVA POOL and the
In those days the DBA had to check statistics in v$views in order to find out if the caches needed manual adjustment.
Starting with Oracle 10gR1 the new parameter SGA_TARGET was introduced which allowed us to limit the amount of SGA_MAX_SIZE which can be used by Oracle.
If you set SGA_TARGET to a value other than 0 (zero) then Automatic Shared Memory Management (ASMM) is enbaled in 10g. This meens that we allow Oracle to adjust these caches as the workload needs it. We can increase this value dynamically, but manually by adjusting SGA_TARGET up to SGA_MAX_SIZE. In most cases it does not make sense to set SGA_TARGET to a value different from SGA_MAX_SIZE. This is only for systems like a SUN FIRE which allow the dynamic reconfiguration of the server (adding memory boards while the server is running).
Within SGA_TARGET a number of caches are autotuned, including the
DB BUFFER CACHE, the
SHARED POOL, the
JAVA POOL, the
LARGE POOL and starting from 10gR2 also the new in 10gR1
At startup time SGA_TARGET is allocated and first of all the non dynamically adjustable caches get their catch. Afterwards the others, the automatically tunable caches get their share.
SGA_MAX_SIZE is reserved for ORACLE at startup time, but as long as it is not touched it is not used.
As of Oracle 11g we will have one parameter which we can use to allow Oracle not only to adjust these five caches in the SGA but also we allow Oracle to shrink and grow the entire SGA memory in order to hand over memory to the PGAs (process memory) and vice versa.
This parameter is called MEMORY_TARGET. With this we can specify how much memory we want to allow ORACLE to use all over, including SGA and PGAs.
MEMORY_TARGET is a dynamic parameter and can be changed with an ALTER SYSTEM SET... statement. It can be adjusted up to the value of MEMORY_MAX_TARGET. Now we do not only have Automatic Shared Memory Mamangement but Automatic Memory Management (system memory plus process memory).
Here is what the reference says:
MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the 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 is a feature we had expected to come up since a long time and I hope that it will work fine for at least most of the systems.
But I would suggest to first test it thoroughly before using it in production because all these new features which have AUTOMATIC in their names can have their issues at least in the first releases. And this is why they never are the default right away.