Memory Management in 11g:

Memory Management Auto tuned in 11g:

It is amazing memory management in Oracle 11g. Upto Oracle 9i one of the hectic process was to define the memory parameters (SGA and PGA) and there are some thumb role needs to followed for the each parameters. Now DBA sit back and relax since Oracle automated the Memory parameter completely on 11g.

Some lights on old versions:

Oracle 8i – db_block_buffer,shared_pool_size,large_pool_size,java_pool_size,sort_area_size,pga size,etc
Oracle 9i - introduced PGA_AGGREGATE_TARGET.
Oracle 10g - continued this era by automating SGA management using the SGA_TARGET and PGA_AGGREGATE_TARGET parameter.
Oracle 11g - Fully automated using MEMORY_TARGET

Oracle 10g onwards this parameter automated, but again there was some sort of calculation for 2 parameters called sga_target and pga_aggregate_target. Now, you can take long breath, leave it without any difficult, because 11g all these two parameters auto tuned.

In Oracle 10g setting the sga_target, I have seen many bugs. Some people use the workaround as normal sga setting.

Automatic Memory Management (AMM) is only supported on the major platforms like AIX,Linux, Solaris, Windows, and HP-UX.

There are 2 initialization parameters for AMM confugration:

1. MEMORY_MAX_TARGET: The maximum size the memor_target can be increased to without an instance restart (like earlier version sga_max_size). If the memory_max_target is not specified, it defaults to MEMORY_TARGET setting.

Using AMM, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas.

2. MEMORY_TARGET: Elaborating this parameter that Shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit.

Automatic Memory Management Setup:

The DBCA is also allows you to configure automatic memory management during database creation.

We can manually set, the appropriate MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters before creating the database or after.

Enabling automatic memory management on a system that didn't previously use it is a simple task.

Kindly use the following calculations:

MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, "maximum PGA allocated")

Assuming our required setting was 4G, we might issue the following statements.

Sqlplus “/as sysdba”

Set the static parameter. Leave some room for possible future growth without restart.

ALTER SYSTEM SET MEMORY_MAX_TARGET=5G SCOPE=SPFILE;

Set the dynamic parameters. Assuming Oracle has full control.

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;

ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;

Shutdown and restart the instance

SHUTDOWN IMMEDIATE;
STARTUP;
The database is restarted the MEMORY_TARGET parameter can be amended as required without an instance restart.

ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;

Dictionary View Informations:

V$MEMORY_TARGET_ADVICE
V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS

You can also, configure and get lot of information through ENERPRISE MANAGER, using graphics,etc.