oracle的sga参数,Oracle内存SGA,PGA参数设定方法

Shared Memory Management of the SGA

If automatic memory management is not enabled, then the system must use shared memory management of the SGA. Shared memory management is possible in either of the following forms:

Automatic shared memory management

This mode enables you to exercise more direct control over the size of the SGA and is the default when automatic memory management is disabled. The database tunes the total SGA to the target size and dynamically tunes the sizes of SGA components. Oracle Database remembers the sizes of the automatically tuned components across instance shutdowns if you are using a server parameter file.

Manual shared memory management

In this mode, you set the sizes of several individual SGA components and manually tune individual SGA components on an ongoing basis. You have complete control of individual SGA component sizes. The database defaults to this mode when both automatic memory management and automatic shared memory management are disabled.

Memory Management of the Instance PGA

If automatic memory management is not enabled, then the following modes are possible for management of PGA memory:

Automatic PGA memory management

When automatic memory management is disabled and PGA_AGGREGATE_TARGET is set to a nonzero value, the database uses automatic PGA memory management. In this mode, the PGA_AGGREGATE_TARGET specifies a target size for the instance PGA. The database then tunes the size of the instance PGA to this target and dynamically tunes the sizes of individual PGAs. If you do not explicitly set a target size, then the database automatically configures a reasonable default.

Manual PGA memory management

When automatic memory management is disabled and PGA_AGGREGATE_TARGET is set to 0, the database defaults to manual PGA management. Previous releases of Oracle Database required the DBA to manually specify the maximum work area size for each type of SQL operator (such as a sort or hash join). This technique proved to be very difficult because the workload is always changing. Although Oracle Database supports the manual PGA memory management method, Oracle strongly recommends automatic memory management.

Table 18-1 Memory Management Methods

Instance

SGA

PGA

Description

Initialization Parameters

Auto

n/a

n/a

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/a

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/a

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 operator.

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/a

Manual

Auto

You control the SGA manually by setting individual 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/a

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