MEMORY_TARGET, SGA_TARGET and PGA_AGGREGATE_TARGET

The memory structures that must be managed are the system global area (SGA) and the instance program global area (instance PGA). Oracle Database supports various memory management methods, which are chosen by initialization parameter settings.

Automatic Memory Management

Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs. Oracle recommends automatic memory management for databases where the total size of the SGA and PGA memory is less than or equal to four gigabytes.

Manual Memory Management

If you prefer to exercise more direct control over the sizes of individual memory components, you can disable automatic memory management and configure the database for manual memory management. There are a few different methods available for manual memory management. Some of these methods retain some degree of automation. The methods therefore vary in the amount of effort and knowledge required by the DBA. These methods are:

• Automatic shared memory management – for the SGA
• Manual shared memory management – for the SGA
• Automatic PGA memory management – for the instance PGA
• Manual PGA memory management – for the instance PGA

Using Automatic Memory Management

You can allow the Oracle Database instance to automatically manage and tune memory for you.

• About 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), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).
• Enabling Automatic Memory Management
  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), then you can enable it at a later time. Enabling automatic memory management involves a shutdown and restart of the database.
• Monitoring and Tuning Automatic Memory Management
  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

About 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), 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.

Because the 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.

Note:

• If the total physical memory of a database instance is greater than 4 GB, then you cannot specify the Automatic Memory Management option during the database installation and creation. Oracle recommends that you use Automatic Shared Memory Management in such environments.
• You cannot enable automatic memory management if the LOCK_SGA initialization parameter is TRUE.

To enable automatic memory management:

Start SQL*Plus and connect to the Oracle Database instance with the SYSDBA administrative privilege.

See “Connecting to the Database with SQL*Plus” and “Database Administrator Authentication” for instructions.

Calculate the minimum value for MEMORY_TARGET as follows:

Determine the current sizes of SGA_TARGET and PGA_AGGREGATE_TARGET in megabytes by entering the following SQL*Plus commands:

SHOW PARAMETER SGA_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
sga_target                           big integer 272M

SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
pga_aggregate_target                 big integer 90M

Run the following query to determine the maximum instance PGA allocated in megabytes since the database was started:

SELECT VALUE/1048576 FROM V$PGASTAT WHERE NAME='maximum PGA allocated';

Compute the maximum 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.

For the 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;

The SCOPE = 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_TARGET and include a value for MEMORY_TARGET, then 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, then the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
Shut down and restart the database.

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:With MEMORY_TARGET set, the SGA_TARGET setting becomes the minimum size of the SGA and the PGA_AGGREGATE_TARGET setting becomes the minimum size of the instance PGA. By setting both of these to zero as shown, there are no minimums, and the SGA and instance PGA can grow as needed as long as their sum is less than or equal to the MEMORY_TARGET setting. The sizing of SQL work areas remains automatic.
You can omit the statements that set the SGA_TARGET and PGA_AGGREGATE_TARGET 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.

In addition, you can use the PGA_AGGREGATE_LIMIT initialization parameter to set an instance-wide hard limit for PGA memory. You can set PGA_AGGREGATE_LIMIT whether or not you use automatic memory management.

Monitoring and Tuning Automatic Memory Management

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.

• Query the V$MEMORY_TARGET_ADVICE view for 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 several 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.

Summary:

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 MEMORY_TARGET-PGA_AGGREGATE_TARGET. SGA_MAX_SIZE (if set by the user)) and will auto-tune sub- components.
• 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.

credit:https://oracle-help.com/oracle-database/relationship-memory_target-sga_target-pga_aggregate_target/