Before considering the following section, you should read the descriptions of the
MEMORY_MAX_TARGET initialization parameters in Oracle Database Performance Tuning Guide and Oracle Database Administrator's Guide for details. The
PGA_AGGREGATE_TARGET initialization parameter need not be set as
MEMORY_TARGET autotunes the SGA and PGA components.
The first group of parameters discussed in this section affects memory and resource consumption for all parallel operations, in particular, for parallel execution. These parameters are:
A second subset of parameters are discussed in "Parameters Affecting Resource Consumption for Parallel DML and Parallel DDL".
At the database level, so the system uses an appropriate amount of memory from the operating system.
At the operating system level for consistency.
On some platforms, you might need to set operating system parameters that control the total amount of virtual memory available, totalled across all processes.
A large percentage of the memory used in data warehousing operations (compared to OLTP) is more dynamic. This memory comes from Process Global Area (PGA), and both the size of process memory and the number of processes can vary greatly. Use the
PGA_AGGREGATE_TARGET initialization parameter to control both the process memory and the number of processes in such cases. Explicitly setting
PGA_AGGREGATE_TARGET along with
MEMORY_TARGET ensures that autotuning still occurs but
PGA_AGGREGATE_TARGET is not tuned below the specified value.