Sunday, October 11, 2009

Memory Target on 11g

If MEMORY_TARGET is set to a non-zero value:

  1. If SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered the minimum values for the sizes of SGA and the PGA respectively. MEMORY_TARGET can take values from SGA_TARGET + PGA_AGGREGATE_TARGET to MEMORY_MAX_SIZE.
  2. If SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, still both parameters will be auto-tuned. PGA_AGGREGATE_TARGET will be initialized to a value of (MEMORY_TARGET-SGA_TARGET).
  3. If PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, still both parameters will be auto-tuned. SGA_TARGET will be initialized to a value of min(MEMORY_TARGET-PGA_AGGREGATE_TARGET, SGA_MAX_SIZE (if set by the user)) and will auto-tune subcomps.
  4. If neither is set, they will be auto-tuned without any minimum or default values. The policy is to give 60% for sga and 40% for PGA at startup.

If MEMORY_TARGET is not set or set to set to 0 explicitly (default value is 0 for 11g):

  1. If SGA_TARGET is set it will only auto-tune the sizes of the sub-components of the SGA. PGA will be autotuned independent of whether it is explicitly set or not. Though the whole SGA(SGA_TARGET) and the PGA(PGA_AGGREGATE_TARGET) will not be auto-tuned, i.e., will not grow or shrink automatically.
  2. If neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set, it will follow the same policy as it have today; PGA will be auto-tuned and the SGA will not be auto-tuned and parameters for some of the sub-components will have to be set explicitly (for SGA_TARGET).
  3. If only MEMORY_MAX_TARGET is set, MEMORY_TARGET will default to 0 and it will not auto tune sga and pga. It will default to 10gR2 behavior within sga and pga.
  4. If sga_max_size is not user set, it will internally set it to MEMORY_MAX_TARGET.
  5. In a text initialization parameter file, 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. If you omit 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.

following views can be useful

* V$MEMORY_DYNAMIC_COMPONENTS has the current status of all memory components
* V$MEMORY_RESIZE_OPS has a circular history buffer of the last 800 SGA resize requests

metalink note 452512.1