SGA_TARGET

SGA_TARGET specifies the total size of all SGA components.

Property	Description
Parameter type	Big integer
Syntax	SGA_TARGET = integer [K | M | G]
Default value	0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMMEDIATE mode autotuning requests)
Modifiable	ALTER SYSTEM
Modifiable in a PDB	Yes
Range of values	Minimum value: The minimum SGA_TARGET value is the sum of the minimum sizes of the automatically tuned memory pools and the sizes of the manually sized components as described later in this section. The database buffer cache (DB_CACHE_SIZE) is usually a major contributor to the minimum SGA_TARGET requirement. Starting with Oracle AI Database 26ai, RU 23.26.3, for standard production database configurations, the minimum buffer cache size is at least 128 MB multiplied by the number of CPUs (64 MB multiplied by the number of CPUs on AIX). The buffer cache is also prevented from shrinking below 5% of SGA_TARGET. These buffer cache requirements, together with the memory requirements of other SGA components, determine the minimum SGA_TARGET. The actual minimum may therefore be larger, depending on the system configuration. Different minimum buffer cache requirements apply to earlier releases and certain database configurations, including Oracle AI Database Free and Developer's Instance user PDBs. See "DB_CACHE_SIZE" for details. If the specified SGA_TARGET is too small to accommodate the required memory components, the database reports ORA-00821 and indicates the minimum required value. Maxiumum value: SGA_MAX_SIZE Note that if MEMORY_MAX_TARGET > 0, then the value of SGA_TARGET also cannot exceed the value of MEMORY_MAX_TARGET.
Basic	Yes

If SGA_TARGET is specified, then the following memory pools are automatically sized:

• Buffer cache (DB_CACHE_SIZE)

  Note: Starting with Oracle AI Database 26ai, RU 23.26.3, if a value for SGA_TARGET is specified, the buffer cache will not shrink to a size that is less than 5% of SGA_TARGET.

• Shared pool (SHARED_POOL_SIZE)

• Large pool (LARGE_POOL_SIZE)

• Java pool (JAVA_POOL_SIZE)

• Streams pool (STREAMS_POOL_SIZE)

• Data transfer cache (DATA_TRANSFER_CACHE_SIZE)

If these automatically tuned memory pools are set to nonzero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

• Log buffer

• Other buffer caches, such as KEEP, RECYCLE, and other block sizes

• Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

In the Default value field, IMMEDIATE mode autotuning requests are necessary to avoid ORA-04031 errors. The DEFERRED and IMMEDIATE modes are reflected in the OPER_MODE column of the V$MEMORY_RESIZE_OPS view.

If Automatic Memory Management is enabled (MEMORY_TARGET is set to a positive value) and SGA_TARGET is also set to a positive value, the SGA_TARGET value acts as the minimum value for the size of the SGA.

Note:

This parameter is optional for pluggable databases (PDBs). When this parameter is set for a PDB, it specifies the maximum SGA that the PDB can use at any time. When this parameter is not set at the PDB level, the PDB has no limit for the amount of SGA it can use, other than the CDB’s SGA size.

To be able to use Resource Manager in a CDB to control the amount of memory each PDB can use:

• The NONCDB_COMPATIBLE initialization parameter must be set to FALSE at the CDB level (in the root of the CDB).

• The MEMORY_TARGET initialization parameter must not be set at the CDB level.

• You must set the SGA_TARGET initialization parameter at the CDB level.

• You must set SGA_TARGET in a PDB.

  If you set a PDB’s SGA_TARGET value and SGA_TARGET is not set at the CDB level, you will not receive an error message and the PDB’s SGA_TARGET value will not be enforced.

• When setting SGA_TARGET in a PDB, the following requirements must be met:

  • The value of SGA_TARGET in the PDB must be less than or equal to the SGA_TARGET value at the CDB level.

  • The value of SGA_TARGET in the PDB must be less than or equal to the SGA_MAX_SIZE value at the CDB level.

  • The value of SGA_TARGET in the PDB must be twice the PDB’s DB_CACHE_SIZE value, if the PDB’s DB_CACHE_SIZE value is set.

  • The value of SGA_TARGET in the PDB must be twice the PDB’s SHARED_POOL_SIZE value, if the PDB’s SHARED_POOL_SIZE value is set.

  • The value of SGA_TARGET in the PDB must be twice the PDB’s SGA_MIN_SIZE value, if the PDB’s SGA_MIN_SIZE value is set.

  When you set SGA_TARGET in a PDB to a value that does not meet these requirements, you receive an error.

See Also:

• “DB_CACHE_SIZE”

• “SHARED_POOL_SIZE”

• “LARGE_POOL_SIZE”

• “JAVA_POOL_SIZE”

• “STREAMS_POOL_SIZE”

• “V$MEMORY_RESIZE_OPS”

• “MEMORY_TARGET”

• “PGA_AGGREGATE_TARGET”

• Oracle AI Database Multitenant Administrator’s Guide for more information about the initialization parameters that control the memory usage of PDBs

• Oracle AI Database Administrator’s Guide for information on automatic memory management

• Oracle AI Database Administrator’s Guide for information on managing the SGA manually