Automatic Memory Management(AMM) features introduced with
Oracle11g is a really magic. This will eliminate
the memory based parameter calculations, which was hectic in early days.
The basic memory structures is as follows:
System Global Area
The SGA is a group of shared memory structures, known as SGA components,
that contain data and control information for one Oracle Database instance.
The SGA is shared by all server and background processes.
Program Global Area (PGA)
A PGA is a memory region that contains data and control information for a
server process. It is nonshared memory created by Oracle Database when a
server process is started. Access to the PGA is exclusive to the server
process. There is one PGA for each server process. Background processes also
allocate their own PGAs. The total PGA memory allocated for all background
and server processes attached to an Oracle Database instance is referred to
as the total instance PGA memory, and the collection of all individual PGAs
is referred to as the total instance PGA, or just instance PGA.
It contains global variables and data structures and control information for
a server process. example of such information is the runtime area of a
cursor. Each time a cursor is executed, a new runtime area is created for
that cursor in the PGA memory region of the server process executing that
cursor.
The performance of complex long running queries, typical in a DSS
environment, depend to a large extent on the memory available in the Program
Global Area (PGA) which is also called work area.
The relationships among these memory structures as follows:
1.1.1
Evolution of Memory Management Features
Memory management has evolved with each database releases
Oracle 8i
Oracle 8i the SGA infrastructure allowed for the sizing
of the DB Block buffer cache,Shared Pool and Large Pool changes need a
shutting down databases.Also the entire memory calculations are manual.
Oracle 9i
Oracle9i, the dynamic SGA infrastructure allowed for the sizing of the
Buffer Cache, Shared Pool and the Large Pool without having to shutdown the
database. Key features being
1.Dynamic Memory resizing
2.DB_CACHE_SIZE instead of
DB_BLOCK_BUFFERS
3.DB_nK_CACHE_SIZE for
multiple block sizes
4. PGA_AGGREGATE_TARGET
Introduction of Automatic PGA Memory management
Oracle Database 10g
ASMM was introduced in 10g. You enable the automatic shared memory
management feature by setting the SGA_TARGET parameter to a non-zero value.
Oracle Database 11g
AMM is being introduced in 11g. This enables automatic tuning of
SGA and PGA with use of two new parameters named MEMORY_MAX_TARGET
and MEMORY_TARGET.
The SGA components are the following:
|
Oracle Terms
|
Details
|
Actual parameters
|
|
Database Buffer Cache
|
The database buffer cache is
the portion of the SGA that holds copies of data blocks read from
datafiles. All users concurrently connected to the instance share
access to the database buffer cache.
|
DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE
|
|
Redo Log Buffer
|
The redo log buffer is a
circular buffer in the SGA that holds information about changes made
to the database. This information is stored in redo entries. Redo
entries contain the information necessary to reconstruct, or redo,
changes made to the database by INSERT, UPDATE, DELETE, CREATE,
ALTER, or DROP operations. Redo entries are used for database
recovery, if necessary.
|
LOG_BUFFER
|
|
Shared Pool
|
The shared pool portion of the
SGA contains the library cache, the dictionary cache, the result
cache, buffers for parallel execution messages, and control
structures.
|
SHARED_POOL_SIZE
SHARED_POOL_RESERVED_SIZE
RESULT_CACHE_SIZE *
|
|
Large Pool
|
Used for allocating session
memory for shared server, Oracle XA, or parallel query buffers or
for RMAN.
|
LARGE_POOL_SIZE
|
|
Java Pool
|
Java pool memory is used in
server memory for all session-specific Java code and data within the
JVM.
|
JAVA_POOL_SIZE
|
|
Streams Pool
|
The streams pool is used
exclusively by Oracle Streams. The Streams pool stores buffered
queue messages, and it provides memory for Oracle Streams capture
processes and apply processes.
|
STREAMS_POOL_SIZE
|
Oracle 11g works with various
memory management methods, which are based on the initialization parameter
settings.
Beginning with Oracle Database 11g, Oracle Database can
manage the SGA memory and instance PGA memory completely automatically. You
designate only the total memory size to be used by the instance, and Oracle
Database dynamically exchanges memory between the SGA and the instance PGA
as needed to meet processing demands. This capability is referred to as
automatic memory management. With this memory management method, the
database also dynamically tunes the sizes of the individual SGA components
and the sizes of the individual PGAs.
To achieve this, two new parameters have been introduced named
MEMORY_MAX_TARGET and MEMORY_TARGET. To do so (on
most platforms), you set only a target memory size initialization parameter
(MEMORY_TARGET) and optionally a maximum memory size initialization
parameter (MEMORY_MAX_TARGET).
a)Check the current values configured for SGA_TARGET and
PGA_AGGREGATE_TARGET
Make the values of pga_aggregate_target and sga_target.
b) On a maximum amount of memory that you would want to
allocate to the database which will determine the maximum value for the sum
of the SGA and instance PGA sizes. We decide to set to 1gb
c)Change the parameter in initialization parameter file.
Use Server Parameter File:
ALTER SYSTEM SET MEMORY_MAX_TARGET =
1gb SCOPE = SPFILE;
ALTER SYSTEM SET MEMORY_TARGET = 1gb SCOPE = SPFILE;
ALTER SYSTEM SET SGA_TARGET =0
SCOPE = SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0
SCOPE = SPFILE;
If you do not specify any value for MEMORY_MAX_TARGET and
only use MEMORY_TARGET then database automatically sets MEMORY_MAX_TARGET to
the value of MEMORY_TARGET.
If you remove 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
Note: MEMORY_MAX_TARGET since it
is a static parameter, this cannot be changed Dynamically and Instance has
to be bounced for modifying the value.
d)Shutdown and startup the database
1.1.4
Monitoring
and Tuning Automatic Memory Management
The dynamic performance view
V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned
memory components, including the total sizes of the SGA and instance PGA.
The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET
initialization parameter.
SQL> select * from
v$memory_target_advice order by memory_size;
You can also use V$MEMORY_RESIZE_OPS.
If you want to exercise more direct control over the size
of the SGA, you can disable automatic memory management and enable automatic
shared memory management.This feature was introduced in 10g with a parameter
known as SGA_TARGET. When automatic SGA memory management
is enabled, the sizes of the different SGA components are flexible and can
adapt to the needs of current workload without requiring any additional
configuration.
If you want complete control of individual SGA component
sizes, you can disable both automatic memory management and automatic shared
memory management. In this mode, you need to set the sizes of several
individual SGA components, thereby determining the overall SGA size. You
then manually tune these individual SGA components on an ongoing basis.
In this case you set SGA_TARGET and MEMORY_TARGET to 0 and set value for
other SGA components upto value of SGA_MAX_SIZE.
Using Automatic memory management , PGA memory is
allocated based upon value of MEMORY_TARGET. In case you enable automatic
shared memory management or manual shared memory management, you also
implicitly enable automatic PGA memory management.
With automatic PGA memory management, you set a target
size for the instance PGA by defining value for parameter named
PGA_AGGREGATE_TARGET and sizing of SQL work areas is automatic and
all *_AREA_SIZE initialization parameters are ignored for these sessions.
This feature is available from 9i.
At any given time, the total amount of PGA memory available to active work
areas on the instance is automatically derived from the parameter
PGA_AGGREGATE_TARGET. This amount is set to the value of
PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes (for
example, session memory). The resulting PGA memory is then allotted to
individual active work areas based on their specific memory requirements
If you wish to manually specify the maximum work area
size for each type of SQL operator (such as sort or hash-join) then you can
enable Manual PGA Memory management.
Make WORKAREA_SIZE_POLICY value to MANUAL and also
specify values for *_area_size such as SORT_AREA_SIZE, HASH_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc.
Oracle Database 11g supports this manual PGA memory management method,
Oracle strongly recommends that you leave automatic PGA memory management
enabled.
The Automatic Memory Management (AMM) feature uses
background process named Memory Manager (MMAN). This
process was introduced in 10g which assisted in Automatic Shared Memory
Management (ASMM) using SGA_TARGET. 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 resize operations
When creating the database manually,
simply set the appropriate MEMORY_TARGET and MEMORY_MAX_TARGET
initialization parameters before creating the database.
Assuming you want to use a similar
amount of memory to your current settings you will need to use the following
calculation.
MEMORY_TARGET = SGA_TARGET +
GREATEST(PGA_AGGREGATE_TARGET, \"maximum PGA allocated\")
The following queries show you how to
display the relevant information and how to combine it in a single statement
to calculate the required value.
COLUMN NAME FORMAT A30
COLUMN VALUE FORMAT A10
SELECT name, value
FROM
v$parameter
WHERE name IN
(\'pga_aggregate_target\', \'sga_target\')
UNION
SELECT \'maximum PGA allocated\' AS
name, TO_CHAR(value) AS value
FROM
v$pgastat
WHERE name =
\'maximum PGA allocated\';
Calculate MEMORY_TARGET
SELECT sga.value + GREATEST(pga.value,
max_pga.value) AS memory_target
FROM (SELECT TO_NUMBER(value) AS value
FROM v$parameter WHERE name = \'sga_target\') sga,
(SELECT TO_NUMBER(value) AS value FROM
v$parameter WHERE name = \'pga_aggregate_target\') pga,
(SELECT
value FROM v$pgastat WHERE name = \'maximum PGA allocated\') max_pga;
Assuming our required setting was 2G,
we might issue the following statements.
sqlplus \"/AS
SYSDBA\"
Make the static parameter. Leave some
room for possible future growth without restart.
ALTER
SYSTEM SET MEMORY_MAX_TARGET=4G SCOPE=SPFILE;
Make 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=2G SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
Once the database is restarted the
MEMORY_TARGET parameter can be amended as required without an instance
restart.
ALTER
SYSTEM SET MEMORY_TARGET=3G SCOPE=SPFILE;
Oracle 11g includes
four new V$ views to support automatic memory management:
V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE
The amount of memory
allocated to each dynamic component is displayed using the
V$MEMORY_DYNAMIC_COMPONENTS view.
COLUMN component
FORMAT A30
SELECT
component, current_size, min_size, max_size
FROM
v$memory_dynamic_components
WHERE
current_size != 0;
The
V$MEMORY_CURRENT_RESIZE_OPS and V$MEMORY_RESIZE_OPS views provide
information on current and previous component resize operations.
The
V$MEMORY_TARGET_ADVICE view provides information to help tune the
MEMORY_TARGET parameter. It displays a range of possible MEMORY_TARGET
settings, as factors of the current setting, and estimates the potential DB
Time to complete the current workload based on these memory sizes.
SELECT * FROM
v$memory_target_advice ORDER BY memory_size;
Enterprise Manager
includes the memory management configuration and advisor functionality in
the \"Memory Advisors\" .
