Memory Management in Oracle

Memory Management In Oracle

Overtime Oracle has invested a great deal of time and effort in managing the memory more efficiently and transparently for the end-user.

Therefore it is advisable to use the automation features as much as possible.

Oracle 9i

Starting from Oracle 9i Oracle introduced the parameters: 

workarea_size_policy = [AUTO | MANUAL]
pga_aggregate_target =

This allows you define 1 pool for the PGA memory, which will be shared across sessions. When you often receive ORA-4030 errors, then this can be an indication that this value is specified too low. 

Oracle 10g

Automatic Shared Memory Management (ASMM) was introduced in 10g. The automatic shared memory management feature is enabled by setting the SGA_TARGET parameter to a non-zero value.

This feature has the advantage that you can share memory resources among the different components.  Resources will be allocated and deallocated as needed by Oracle automatically.

Automatic PGA Memory management is still available through the 'workarea_size_policy' and
 'pga_aggregate_target' parameters.

Oracle 11g

Automatic Memory Management (AMM) is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.