Using Automatic
Memory Management - By Adarsh Kumar
This
section provides background information on the automatic memory management
feature of Oracle Database, and includes instructions for enabling this
feature. The following topics are covered:
·
About Automatic Memory Management· Enabling Automatic Memory Management
· Monitoring and Tuning Automatic Memory Management
The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. To do so (on most platforms),
To Enable AMM you set only MEMORY_TARGET and optionally MEMORY_MAX_TARGET.
Also SGA_TARGET =
0; PGA_AGGREGATE_TARGET = 0;
You can omit the statements that set
these parameter values to zero and leave either or both of the values as
positive numbers. In this case, the values act as minimum values for the sizes
of the SGA or instance PGA.
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).
Because
the target memory initialization parameter is dynamic, you can change the target
memory size at any time without restarting the database. The maximum memory size
serves as an upper limit so that you cannot accidentally set the target memory
size too high, and so that enough memory is set aside for the Oracle Database
instance in case you do want to increase total instance memory in the future.
Because certain SGA components either cannot easily shrink or must remain at a
minimum size, the instance also prevents you from setting the target memory
size too low.
Note: You cannot enable AMM if the LOCK_SGA initialization parameter is TRUE.
If you did not enable AMM upon database creation (either by selecting the proper options in DBCA or by setting the appropriate initialization parameters for the CREATE DATABASE SQL statement), you can enable it at a later time. Enabling automatic memory management involves a shutdown and restart of the database.
To enable automatic memory management
1. Start SQL*Plus and connect to the database as SYSDBA.
2. Calculate the minimum value for MEMORY_TARGET as follows:
Determine the current sizes of SGA_TARGET and PGA_AGGREGATE_TARGET
sql>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
a. Get the maximum instance PGA allocated since the database was started:
select value from v$pgastat where name='maximum PGA allocated';
3. Calculate the value for MEMORY_TARGET that you want to use.
This can be the minimum value or you can choose to use a larger value if you have enough physical memory available.
b. memory_target = sga_target + max(pga_aggregate_target, ‘maximum PGA allocated’)
For example, if SGA_TARGET is 272M and PGA_AGGREGATE_TARGET is 90M as shown above, and if the
maximum PGA allocated is is 120M, then MEMORY_TARGET should be at least 392M (272M + 120M).
4. For MEMORY_MAX_TARGET 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.
Shut down and restart the database with the Folllowing Parameters to Enable AMM.
If you started your Oracle Database instance with a server parameter file, enter the following commands:
ALTER SYSTEM SET MEMORY_TARGET = nM; See 2 Above
ALTER SYSTEM SET MEMORY_MAX_TARGET = nM See4 AboveALTER SYSTEM SET SGA_TARGET = 0;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;
Default Behavious of Thsese Parameters:
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.
Set SGA_TARGET and PGA_AGGREGATE_TARGET to zero
so that the sizes of the SGA and instance PGA are tuned up and down as
required, without restrictions.
You
can omit the statements that set these parameter values to zero and leave
either or both of the values as positive numbers. In this case, the values act
as minimum values for the sizes of the SGA or instance PGA.
V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.
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 0360 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. For each alternative
size, the database shows the size factor (the multiple of the current size),
and the estimated DB time to complete the current workload if the MEMORY_TARGET
parameter were changed to the alternative size. Notice that for a total memory
size smaller than the current MEMORY_TARGET size, estimated DB time increases.
Notice
also that in this example, there is nothing to be gained by increasing total
memory size beyond 450MB. However, this situation might change if a complete
workload has not yet been run.
