Using Automatic Memory Management
This article includes three sections:
·
Oracle Automatic Memory Management
·
Enabling Automatic Memory Management
·
Monitoring and Tuning Automatic Memory
Management
1.
Oracle
Automatic Memory Management
Automatic Memory Management has
introduced by Oracle from Oracle 10G, and enhanced in 11G, 12C. This is the
best way to manage Oracle Database memory. 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).
The
instance then tunes to the target memory size, redistributing memory as needed
between the system global area (SGA) and the instance program global area
(instance PGA).
If you create your database with Database
Configuration Assistant (DBCA) and choose the basic installation option,
automatic memory management is enabled.
2. Enabling Automatic Memory Management
a. Start
SQL*Plus and connect to the database as
SYSDBA.
b. Calculate the minimum value for
MEMORY_TARGET as follows
i.
Determine the current sizes of
SGA_TARGET and PGA_AGGREGATE_TARGET by entering
the following SQL*Plus command:
SHOW PARAMETER TARGET
NAME TYPE VALUE
------------------------------ ----------- ----------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 90M
sga_target big integer 272M
ii.
Run the following query to determine the
maximum instance PGA allocated since the database was started:
select value from v$pgastat where name='maximum PGA allocated';
iii.
Compute the maximum value between the
query result from step 2b and
PGA_AGGREGATE_TARGET.
Add SGA_TARGET to this
value.memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)
c. Choose
the value for
MEMORY_TARGET that
you want to use.
d. For
the
MEMORY_MAX_TARGET
initialization parameter, decide on a maximum amount of memory that you would
want to allocate to the database for the foreseeable future. That is, determine
the maximum value for the sum of the SGA and instance PGA sizes. This number
can be larger than or the same as the MEMORY_TARGET
value that you chose in the previous step.
e. If
you started your Oracle Database instance with a server parameter file, which
is the default if you created the database with the Database Configuration
Assistant (DBCA), enter the following command:
ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;
If you started your instance with a text
initialization parameter file, manually edit the file so that it contains the
following statements:
memory_max_target = nM
memory_target = mM
f. Shut
down and restart the database.
3. 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;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
180 .5 458 1.344 0
270 .75 367 1.0761 0
360 1 341 1 0
450 1.25 335 .9817 0
540 1.5 335 .9817 0
630 1.75 335 .9817 0
720 2 335 .9817 0
The row with the
MEMORY_SIZE_FACTOR
of 1 shows the current size of memory, as set by the MEMORY_TARGET initialization
parameter, and the amount of DB time required to complete the current workload.
In previous and subsequent rows, the results show a number of alternative MEMORY_TARGET sizes.
Reference resources: Oracle Metalink, Oracle 11G reference docs
