Oracle Automatic Memory Management

Before assigning the appropriate size to the database with the new automatic memory management feature, you must first find the current amount of memory allocated to SGA and PGA. But it does not mean that sga _

Before assigning the appropriate size to the database with the new automatic memory management feature, you must first find the current amount of memory allocated to SGA and PGA. But it does not mean that sga _

Automatic Memory Management type

Enable Automatic Memory Management

To enable automatic memory management, you must set the memory_target parameter to set the target memory of the instance. You can also specify the memory_max_target initialization parameter to set the maximum memory size of an instance.

Before assigning the appropriate size to the database with the new automatic memory management feature, you must first find the current amount of memory allocated to SGA and PGA. However, this does not mean simply adding the sga_target and pga_target parameters to the memory. The reason is: Unlike the sga_target parameter, the database does not take over the memory allocated to the pga_target parameter immediately. The database only assigns the PGA to each part according to the pga_target parameter settings. Therefore, your pga_target may be allocated a lot, but the database only uses a small part within the given time.

Recommended reading:

Automatic Oracle Memory Management

Solution to physical memory check and temporary temp space insufficiency during Oracle Installation

Therefore, you need to perform the following operations to determine the memory size for automatic memory management.

1. Search for the current SGA size

SQL> show parameter sga

NAME TYPE VALUE
-----------------------------------------------------------------------------
Sga_target big integer 600 M

2. Search for the size allocated to PGA within a given time period

SQL> select value from v $ pgastat where;

VALUE
----------
248707072

3. the query results obtained in step 2 show that the maximum memory allocated by PGA is about 236 mb. perform the following operation to get an incorrect PGA estimation.

SQL> show parameter pga _

NAME TYPE VALUE
-----------------------------------------------------------------------------
Pga_aggregate_target big integer 400000000

Settings:

Alter system set memory_target = 1200 M scope = spfile;

Alter system set memory_max_target = 1000 M scope = spfile;

Alter system set sga_target = 1200 M scope = spfile;

Alter system set pga_aggregate_target = 1200 M scope = spfile;

Summary of oracle memory management methods:

InstanceSGAPGADescriptionInitialization Parameters

Auto

N/

N/

The database tunes the size of the instance based on a single instance target size.

You set:

Total memory target size for the database instance (MEMORY_TARGET)

Optional maximum memory size for the database instance (MEMORY_MAX_TARGET)

N/

Auto

Auto

The database automatically tunes the SGA based on an SGA target.

The database automatically tunes the PGA based on a PGA target.

You set:

SGA target size (SGA_TARGET)

Optional SGA maximum size (SGA_MAX_SIZE)

Instance PGA target size (PGA_AGGREGATE_TARGET)

N/

Auto

Manual

The database automatically tunes the SGA based on an SGA target.

You control the PGA manually, setting the maximum work area size for each type of SQL.

You set:

SGA target size (SGA_TARGET)

Optional SGA maximum size (SGA_MAX_SIZE)

PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE

N/

Manual

Auto

You control the SGA manually by setting inpidual component sizes.

The database automatically tunes the PGA based on a PGA target.

You set:

Shared pool size (SHARED_POOL_SIZE)

Buffer cache size (DB_CACHE_SIZE)

Large pool size (LARGE_POOL_SIZE)

Java pool size (JAVA_POOL_SIZE)

Streams pool size (STREAMS_POOL_SIZE)

Instance PGA target size (PGA_AGGREGATE_TARGET)

N/

Manual

Manual

You must manually configure SGA component sizes.

You control the PGA manually, setting the maximum work area size for each type of SQL operator.

You must manually configure SGA component sizes. You set:

Shared pool size (SHARED_POOL_SIZE)

Buffer cache size (DB_CACHE_SIZE)

Large pool size (LARGE_POOL_SIZE)

Java pool size (JAVA_POOL_SIZE)

Streams pool size (STREAMS_POOL_SIZE)

PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE

Continue reading: