MEMORY_TARGET, SGA_TARGET and PGA_AGGREGATE_TARGET

Today’s article is about to add some deep knowledge in the Oracle’s database knowledge. We are going to have look at basic elements of Oracle database. This article is most useful for freshers and beneficial for experienced DBAs too.

In Oracle Database we read about MEMORY_TARGET, SGA_TARGET, and PGA_AGGREGATE_TRAGET. Let’s have a technical definition of these teams.

MEMORY_TARGET:- It is a database initialization parameter (introduced in Oracle 11g) that can be used for automatic PGA and SGA memory sizing.

With the help of MEMORY_TARGET we can have help in the following:

• A single parameter for total SGA and PGA sizes
• Automatically sizes SGA components and PGA
• Memory is transferred to where most needed
• Uses workload information
• Uses internal advisory predictions
• Can be enabled by DBCA at the time of Database creation.

SGA_TARGET: It is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.

With the help of SGA_TARGET we can have help in the following:

• Single parameter for total SGA size
• Automatically sizes SGA components
• Memory is transferred to where most needed
• Uses workload information
• Uses internal advisory predictions
• STATISTICS_LEVEL must be set to TYPICAL

PGA_AGGREGATE_TARGET: It is a database initialization parameter and controls the total amount of execution memory that can be allocated by Oracle for the Process global area (PGA).

Relation between MEMORY_TARGET, SGA_TARGET, and PGA_AGGREGATE_TARGET 

If MEMORY_TARGET  is set to a non–zero value there are possibilities are mention below:-

• 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_TARGET.
• If SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still auto-tune both parameters PGA_AGGREGATE_TARGET will be initialized to the value of MEMORY_MAX_TARGET.
• If PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, we will still auto-tune both parameters. SGA_TARGET will be initialized to a value of MEMOERY_TARGET-PGA_AGGREGATE_TARGET. SGA_MAX_SIZE (if set by the user)) and will auto-tune sub camps.
• If neither is set, they will be auto-tuned without any minimum or default values.  we will have the policy of distributing the total memory_target parameter in a fixed ratio to the SGA and PGA during initialization. The policy is to give 60% for SGA and 40% for PGA at startup.

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

• If  SGA_TARGET is set we will only auto-tune the sizes of the sub-components of the SGA. PGA will be auto-tuned independent of whether it is explicitly set or not. Though whole  SGA(SGA_TARGET)  and the PGA(PGA_AGGREATE_TARGET) will not be auto-tuned, will not grow or shrink automatically.
• If neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set, we will follow the same policy as we have. 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).
• If only MEMORY_MAX_TARGET is set,  MEMORY_TARGET will default to 0 and will not auto-tune SGA and PGA. It will default to 10r2 behavior within SGA and PGA.
• If SGA max size is not user set, we will internally set it to MEMORY_MAX_TARGET.

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.

Thank you for giving your valuable time to read the above information.

Source

If you want to be updated with all our articles send us the Invitation or Follow us:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp