Oracle内存占用高过时的调整策略

先贴出具体步骤

开启自动共享内存管理 ASMM

第一步：将MEMORY_MAX_TERGET和MEMORY_TARGET和设置为0

SQL> alter system set memory_max_target=0 scope=spfile;
System altered.

SQL> alter system set memory_target=0 scope=both;
System altered.

第二步：设置sga_target和pga_aggregate_target的数值

SQL> alter system set sga_target=1024m scope=both;
System altered.

SQL> alter system set pga_aggregate_target=512m scope=both;
System altered.

第三步：重启数据库

开启自动内存管理 AMM

自动内存管理(Automatic Memory Management)是11G中推出的新功能，10G中只有自动SGA管理。启用了AMM只需设置内存的大小，就可以自动的在SGA和PGA之间分配内存，省去了管理设置SGA和PGA的麻烦。

AMM的相关参数：

MEMORY_TARGET:SGA和PGA可以使用的内存的总的大小，可以动态的设置，最大值不超过MEMORY_MAX_TARGET。

MEMORY_MAX_TARGET：可以使用的内存的最大值，这个是静态参数，要修改必须重启数据库。

SGA_TARGET：SGA目标内存大小，启用AMM后，这个参数应该设为0.

PGA_AGGREGATE_TARGET：PGA目标内存大小，启用AMM后，应该设为0.

开启AMM：

要想开启AMM，只需要设置MEMORY_TARGET，同时设置SGA_TARGET和PGA_AGGREGATE_TARGET都为0。如果没有设置MEMORY_MAX_TARGET，在启动后MEMORY_MAX_TARGET自动设置为MEMORY_TARGET大小。这样就可以自动的在SGA和PGA之间分配内存。

可以动态的修改MEMORY_TARGET大小，只要MEMORY_TARGET<＝MEMORY_MAX_TARGET即可。

MEMORY_TARGET--操作系统的角度上 Oracle 所能使用的最大内存值。动态参数
MEMORY_MAX_TARGET--MEMORY_TARGET所能设定的最大值。非动态可调。

注意:
如果使用的是 pfile，设定了 MEMORY_TARGET 而没有指定 MEMORY_MAX_TARGET 的值，则实例启动后 MEMORY_MAX_TARGET 的值与 MEMORY_TARGET 相等。如果 pfile 中指定了 MEMORY_MAX_TARGET 而没有指定 MEMORY_TARGET ，实例启动后 MEMORY_TARGET 为 0 。

例外的情况：

1.设置了MEMORY_TARGET，又设置了SGA_TARGET和PGA_AGGREGATE_TARGET

此时SGA_TARGET+ PGA_AGGREGATE_TARGET要<＝MEMORY_TARGET。

SGA_TARGET和PGA_AGGREGATE_TARGET分别为SGA的PGA大小的下限。

如MEMORY_TARGET=1G，SGA_TARGET＝500M，PGA_AGGREGATE_TARGET＝300M，则SGA下限为500M，PGA下限为300M，以此为根据在SGA和PGA之间分配内存。

2.如果没设置MEMORY_TARGET，设置了SGA_TARGET和PGA_AGGREGATE_TARGET

这时开启了自动共享内存管理(SGA自动管理)，SGA内各个组件之间自动的分配内存

3.如果啥都没有设置，也是可以启动的，按照默认的情况，把内存的40%分给ORACLE，然后把60%分给SGA，把40%分给PGA。

一：自动内存管理（AMM）
默认安装的实例即是 AMM 方式。如下

SQL> show parameters target
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
archive_lag_target                   integer                0
db_flashback_retention_target        integer                1440
fast_start_io_target                 integer                0
fast_start_mttr_target               integer                0
memory_max_target                    big integer            1216M
memory_target                        big integer            1216M
pga_aggregate_target                 big integer            0
sga_target                           big integer            0

要注意到 SGA_TARGET 和都为 0 。

二：自动共享内存管理(Automatic Shared Memory Management, ASMM)
这是 10g 引入的管理方式，要使用这种方式，需要设置初始化参数 MEMORY_TARGET=0 ，然后显式的指定 SGA_TARGET 的值。

SQL> alter system set sga_target=1024m scope=both;
alter system set sga_target=1024m scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00839: SGA_TARGET cannot be modified to the specified value
SQL> alter system set memory_target=0 scope=both;
System altered.
SQL> alter system set sga_target=1024m scope=both;
System altered.
SQL>

这两个参数的修改是有严格顺序的，如果不遵守倒也没问题--Oracle 会报告错误。

三：手工共享内存管理

这个又更加原始了一些。因为原始，所以新的初始化参数 SGA_TARGET 与 MEMORY_TARGET 都要设置为 0. 然后手工设定 share_pool_size 、db_cache_size 等 sga 参数。要注意 RESULT_CACHE_SIZE 参数是 11g 新引入的，用来缓存 SQL 结果。

四.自动 PGA　内存管理

如果使用 AMM，则对 PGA 不用操心。如果要做到精细控制而切换到自动 PGA　内存管理模式，需要设定WORKAREA_SIZE_POLICY ＝ AUTO(默认即为 AUTO)，然后需要指定 PGA_AGGREGATE_TARGET 的值。如需要精确控制PGA，则 WORKAREA_SIZE_POLICY ＝ MANUAL

五.手动 PGA 管理

前提是 WORKAREA_SIZE_POLICY = manual ,然后分别指定 SORT_AREA_SIZE 等 PGA 相关的参数。估计现在没有人干这个吃力不讨好的事情了。这个模式大可以忽略。

六.AMM 的限制

如果初始化参数 LOCK_SGA ＝ true(默认false），则 AMM 是不可用的。

下面来看看在11g 中Memory_target 设置和不设置对SGA/PGA 的影响：
如果Memory_target 设置为非0 值
1、sga_target 和 pga_aggregate_target 已经设置大小
Memory_Target =SGA_TARGET+PGA_AGGREGATE_TARGET ，大小和 memory_max_size 一致

2、sga_target 设置大小， pga_aggregate_target 没有设置大小
pga_aggregate_target初始化值=memory_target-sga_target

3、sga_target 没有设置大小， pga_aggregate_target 设置大小
sga_target 初始化值=memory_target-pga_aggregate_target

4、sga_target 和 pga_aggregate_target 都没有设置大小
两个值没有最小值和默认值，Oracle 将根据数据库运行状况进行分配大小，但在数据库启动是会有一个固定比例来分配：
sga_target =memory_target *60%
pga_aggregate_target=memory_target *40%

如果Memory_target 设置为0
11g 中默认为0 则初始状态下取消了Memory_target 的作用，完全和10g 在内存管理上一致，完全向下兼容。

1、SGA_TARGET设置值
自动调节SGA 中的shared pool,buffer cache,redo log buffer,java pool,larger pool等内存空间的大小。PGA 则依赖pga_aggregate_target 的大小。

2、SGA_target 和PGA_AGGREGATE_TARGET 都没有设置
SGA 中的各组件大小都要明确设定，不能自动调整各组件大小。PGA不能自动增长和收缩

3： MEMORY_MAX_TARGET 设置而MEMORY_TARGET =0 这种情况先和10g 一样。

实验：关闭内存自动管理，设置SGA,PGA分别自动管理，SGA=500,PGA=200

[root@hong ~]# su - oracle

[oracle@hong ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 15 02:39:35 2016

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 939495424 bytes

Fixed Size 2258840 bytes

Variable Size 595593320 bytes

Database Buffers 335544320 bytes

Redo Buffers 6098944 bytes

Database mounted.

Database opened.

SQL> show parameter sga_target

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sga_target big integer 0

SQL> show parameter pga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target big integer 0

SQL> alter system set sga_target=500M scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=200M scope=spfile;

System altered.

SQL> alter system set memory_target=0 scope=spfile;

System altered.

SQL> show parameter target

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 0

memory_target big integer 0

parallel_servers_target integer 16

pga_aggregate_target big integer 200M

sga_target big integer 500M

(（（（（（上述三个参数sga_target，pga_aggregate_target，memory_target，相互影响他们的参数值，所以更改此类参数需加scope=spfile，不加默认scope=both，但是查看参数值时并未生效<span "="">；启动内存SGA,PGA分别自动管理，顺序为：alter system set sga_target=500m scope=spfile;alter system set pga_aggregate_target=200m scope=spfile; alter system set memory_target=0 scope=spfile;）））））

——重置内存自动管理memory_target：

SQL> alter system set memory_target=900M scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 939495424 bytes

Fixed Size 2258840 bytes

Variable Size 671090792 bytes

Database Buffers 260046848 bytes

Redo Buffers 6098944 bytes

Database mounted.

Database opened.

SQL> show parameter target

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 900M

memory_target big integer 900M

parallel_servers_target integer 16

pga_aggregate_target big integer 200M

sga_target big integer 500M

SQL> alter system set pga_aggregate_target=175M;

System altered.

SQL> alter system set sga_target=300M;

System altered.

SQL> show parameter target

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 900M

memory_target big integer 900M

parallel_servers_target integer 16

pga_aggregate_target big integer 175M

sga_target big integer 300M

SQL>

（内存自动管理生效，只要memory_target有值就以它为准）