Oracle 自动内存管理

在用新的自动内存管理特性给数据库分配适当的尺寸之前，必须首先查找当前有多少内存分配给了SGA和PGA。但是并不意味简单的将sga_

自动内存管理类型

开启自动内存管理

为开启自动内存管理，，必须设置memory_target参数，该参数设置实例的目标内存。也可以指定memory_max_target初始化参数设置一个实例的最大内存尺寸。

在用新的自动内存管理特性给数据库分配适当的尺寸之前，必须首先查找当前有多少内存分配给了SGA和PGA。但是并不意味简单的将sga_target和pga_target参数求和相加的内存需求。原因是：与sga_target参数不同，数据库不立即接管分配给pga_target参数的内存。数据库只根据pga_target参数设置将PGA分配给每个部分，因此，你的pga_target也许分配得很大，但是数据库在给定时间内却只是用了很小的一部分。

推荐阅读：

Oracle 内存自动管理

Oracle安装过程物理内存检查及临时temp空间不足解决办法

因此需要做如下操作，来确定自动内存管理的内存大小

1、查找当前SGA大小 

SQL> show parameter sga

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

2、查找给定时间内分配给PGA的大小

SQL> select value from v$pgastat where;

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

3、2步得到的查询结果显示PGA分配的最大内存大约为236M，执行一下操作 得到一个错误的PGA估计

SQL> show parameter pga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 400000000

设置：

alter system set memory_target=1200M scope=spfile;

alter system set memory_max_target=1000M scope=spfile;

alter system set sga_target=1200M scope=spfile;

alter system set pga_aggregate_target=1200M scope=spfile;

 

oracle内存管理方法的总结：

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

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

 继续阅读：