|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
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:
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), you set only a target memory size initialization parameter (
MEMORY_TARGET) and optionally a maximum memory size initialization parameter (
MEMORY_MAX_TARGET). The total memory that the instance uses remains relatively constant, based on the value of
MEMORY_TARGET, and the instance automatically distributes memory between the system global area (SGA) and the instance program global area (instance PGA). As memory requirements change, the instance dynamically redistributes memory between the SGA and instance PGA.
When automatic memory management is not enabled, you must size both the SGA and instance PGA manually.
MEMORY_TARGET initialization parameter is dynamic, you can change
MEMORY_TARGET at any time without restarting the database.
MEMORY_MAX_TARGET, which is not dynamic, serves as an upper limit so that you cannot accidentally set
MEMORY_TARGET too high, and so that enough memory is set aside for the 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
MEMORY_TARGET too low.
If you create your database with Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled. If you choose advanced installation, Database Configuration Assistant (DBCA) enables you to select automatic memory management.
Note:You cannot enable automatic memory management if the
LOCK_SGAinitialization parameter is
TRUE. See Oracle Database Reference for information about this parameter.
If you did not enable automatic memory management 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
Start SQL*Plus and connect to the database as
Calculate the minimum value for
MEMORY_TARGET as follows:
Determine the current sizes of
PGA_AGGREGATE_TARGET by entering the following SQL*Plus command:
SHOW PARAMETER TARGET
SQL*Plus displays the values of all initialization parameters with the string
TARGET in the parameter name.
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
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';
Compute the maximum value between the query result from step 2b and
SGA_TARGET to this value.
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 determined to be 120M, then
MEMORY_TARGET should be at least 392M (272M + 120M).
Choose the value for
MEMORY_TARGET that you want to use.
This can be the minimum value that you computed in step 2, or you can choose to use a larger value if you have enough physical memory available.
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.
Do one of the following:
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;
where n is the value that you computed in Step 4.
SPFILE clause sets the value only in the server parameter file, and not for the running instance. You must include this
SCOPE clause because
MEMORY_MAX_TARGET is not a dynamic initialization parameter.
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
Note:In a text initialization parameter file, if you omit the line for
MEMORY_MAX_TARGETand include a value for
MEMORY_TARGET, the database automatically sets
MEMORY_MAX_TARGETto the value of
MEMORY_TARGET. If you omit the line for
MEMORY_TARGETand include a value for
MEMORY_TARGETparameter defaults to zero. After startup, you can then dynamically change
MEMORY_TARGETto a nonzero value, provided that it does not exceed the value of
Shut down and restart the database.
See Chapter 3, "Starting Up and Shutting Down" for instructions.
If you started your Oracle Database instance with a server parameter file, enter the following commands:
ALTER SYSTEM SET MEMORY_TARGET = nM; ALTER SYSTEM SET SGA_TARGET = 0; ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;
where n is the value that you determined in step 3.
Note:The preceding steps instruct you to set
PGA_AGGREGATE_TARGETto 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.
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.
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. 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.
Enterprise Manager provides an easy-to-use graphical memory advisor to help you select an optimal size for
MEMORY_TARGET. See Oracle Database 2 Day DBA for details.