合 Oracle中的ASMM和AMM介绍以及ORA-00845: MEMORY_TARGET not supported on this system错误

什么是ASMM和AMM？

ASMM（Automatic Shared Memory Management，自动共享内存管理）是Oracle 10g引入的概念。通过使用ASMM，就不需要手工设置相关内存组件的大小，而只为SGA设置一个总的大小，
Oracle的MMAN进程（Memory Manager Process，内存管理进程）会随着时间推移，根据系统负载的变化和内存需要，自动调整SGA中各个组件的内存大小。
ASMM的SGA中包含的组件及对应参数如下表所示：

在Oracle 10g中，必须将STATISTICS_LEVEL参数设置为TYPICAL（默认值)或者ALL才能启用ASMM功能，如果将其值设置为BASIC，那么会禁用很多新特性，比如像AWR、ASMM等。
如果使用SQL*Plus来设置ASMM，那么必须先把SGA中包含的组件的值设置为0。通过设置SGA_TARGET参数为非零值来启用ASMM功能。

可以通过以下SQL来计算SGA_TARGET的值：

设置SGA_TARGET的值，可以直接修改初始化参数文件后重启数据库，或者通过下面SQL命令进行修改：

示例如下所示：

在启用ASMM后，Oracle会自动调整SGA内部组件大小。若再手动指定某一组件值，则该值为该组件的最小值。例如，手动设置SGA_TARGET=8G，SHARED_POOL_SIZE=1G，
则ASMM在自动调整SGA内部组件大小时，保证Shared Pool不会低于1G。当设置了SGA_TARGET参数后，Oracle将会收集SGA相关的统计数据，并通过V$SGA_TARGET_ADVICE呈现出来，
因此，可以根据这些指导SGA_TARGET做相关的调整，以达到最佳情况。

Oracle 10g的ASMM实现了自动共享内存管理，但是具有一定的局限性。所以，在Oracle 11g中，Oracle引入了AMM（Automatic Memory Management，自动内存管理）的概念，实现了全部内存的自动管理。DBA可以仅仅通过设置一个目标内存大小的初始化参数（MEMORY_TARGET）和可选最大内存大小初始化参数（MEMORY_MAX_TARGET）就可以在大多数平台上实现AMM。
AMM可以使实例总内存保持相对稳定的状态，Oracle基于MEMORY_TARGET的值来自动调整SGA和PGA的大小。MEMORY_TARGET是动态初始化参数，可以随时修改该参数的值而不用重启数据库。
MEMORY_MAX_TARGET作为一个内存上限值，是一个静态参数，它是MEMORY_TARGET可以被配置的最大值。

如果内存发生变化，实例会自动在SGA和PGA之间做调整。若启用了AMM功能，而SGA_TARGET和PGA_AGGREGATE_TARGET没有显式的被设置，
则默认SGA_TARGET为MEMORY_TARGET的60%，PGA_AGGREGATE_TARGET为MEMORY_TARGET的40%。
如果MEMORY_MAX_TARGET设置为1400M，而MEMORY_TARGET设置为1000M，那么对于数据库实例而言，只有1000M可以使用，剩下的400M会被保留下来，
但会被Oracle的MMAN进程锁定。但是，因为MEMORY_MAX_TARGET是显式设置的，所以，可以在不重启数据库的情况下动态调整MEMORY_TARGET。
如果只设置了MEMORY_TARGET的值，而MEMORY_MAX_TARGET没有显式设置，那么MEMORY_MAX_TARGET的值默认是MEMORY_TARGET的大小。

当LOCK_SGA初始化参数的值设置为TRUE时，不能启用AMM，该参数的值默认为FALSE。

只要是设置了MEMORY_MAX_TARGET或MEMORY_TARGET，那么就说明启用了AMM。可以使用视图V$MEMORY_DYNAMIC_COMPONENTS动态查阅内存各组件的当前实时大小。

启用AMM

如果在创建数据库的时候未启用AMM，那么可以在建库后启用它，启用AMM需要重启数据库，具体步骤如下所示：

1、查询SGA_TARGET和PGA_AGGREGATE_TARGET的值，从而确定MEMORY_TARGET的最小值

2、确定自系统启动以来PGA的最大值，单位为bytes

3、通过以下方法来计算出SGA_TARGET的最大值

例如：在这里，SGA_TARGET的值为1648M，PGA_AGGREGATE_TARGET的值为409M，PGA的最大值为248586240/1024/1024=237M，所以，MEMORY_TARGET的值至少为1648+409=2057M。

4、设置系统参数启用AMM

ORA-00845: MEMORY_TARGET not supported on this system

另外需要说明的一点是，使用AMM经常出现的一个错误是“ORA-00845: MEMORY_TARGET not supported on this system”。

这个错误原因是操作系统不支持MEMORY_TARGET参数或/dev/shm的大小设置不正确。解决方法就是将/dev/shm的值增大，至少需要大于数据库参数MEMORY_MAX_TARGET的值。修改步骤如下：

再次启动数据库就可以正常启动了。

ASMM和AMM的区别

由于AMM不支持HugePage，而ASMM支持HugePage，所以，在生产库上强烈推荐使用ASMM。

有关ASMM和AMM的区别如下表所示：

MOS文档“SGA and PGA Management in 11g's Automatic Memory Management (AMM) (文档 ID 1392549.1)”对AMM和ASMM有非常详细的说明。

SGA and PGA Management in 11g's Automatic Memory Management (AMM) (文档 ID 1392549.1)

PURPOSE

The purpose of this document is to demonstrate how Automatic Memory Management (AMM) manages memory in the SGA and PGA.

SCOPE

The intended audience of this document is experienced Database Administrators and Oracle Support engineers interested in Automatic Memory Management.

DETAILS

Automatic Memory Management (hereafter called AMM) is introduced in Oracle 11g to automatically manage both the SGA and PGA for a database instance. It is an extension of Automatic Shared Memory Management (ASMM) which was introduced in Oracle 10g, which manages the SGA only.

The significant instance parameters used by AMM are:

• MEMORY_MAX_TARGET
• MEMORY_TARGET
• SGA_MAX_SIZE
• SGA_TARGET
• PGA_AGGREGATE_TARGET

AMM functionality is implemented by the Memory Manager process (hereafter called MMAN).

Mandatory parameters for AMM

AMM is enabled by the use of EITHER of these parameters:

• MEMORY_TARGET - defines the outer limits to which the sum of the SGA and PGA can grow.
• MEMORY_MAX_TARGET - defines the outer limit to which the MEMORY_TARGET can be manually, dynamically, extended (i.e. without a database restart).

Example:

If MEMORY_MAX_TARGET is set to 1400M, and MEMORY_TARGET is set to 1000M, only the 1000M is available to the instance. The remaining 400M is held in reserve, but locked by Oracle (MMAN). However, because MEMORY_MAX_TARGET is explicitly set, it now becomes possible to dynamically resize MEMORY_TARGET without a database restart.

If MEMORY_MAX_TARGET is the same as MEMORY_TARGET, or if it is not explicitly set, this dynamic increase would not be possible and a instance restart would be required.

Regardless of the Operating System used, when the instance starts up, an amount of memory equal to MEMORY_MAX_TARGET will be locked by MMAN.

MEMORY_MAX_TARGET will always be set, either explicitly in the parameter file, or implicitly by the MMAN background process and will always define the memory locked by the database instance. If MEMORY_MAX_TARGET is not set in the parameter file, it defaults to MEMORY_TARGET.

Optional parameters for AMM

All SGA memory parameters can be set in an AMM environment. If no SGA memory parameters are set, MMAN defaults in the following ratio:

60% to SGA_TARGET
40% to PGA_AGGREGATE_TARGET

Let's look at the following parameters:

• SGA_MAX_SIZE: this parameter sets the upper limits of the SGA within MEMORY_TARGET
• SGA_TARGET: this parameter sets the lower limits for the SGA within MEMORY_TARGET
• PGA_AGGREGATE_TARGET: this parameter is just a target for the total private memory the instance will allow for all processes. In AMM, this is a movable target and will slide up ad down as free space in MEMORY_TARGET is available and as the processing needs change.

The sum of SGA and 'used' PGA cannot exceed MEMORY_TARGET. The exception is PL/SQL collections (such as VARRAYs and other collection types) which will not honor the PGA_AGGREGATE_TARGET, but will continue to consume memory as long as more memory is requested by the procedure, and more memory is available on the server. This is memory in excess of MEMORY_MAX_TARGET.

If SGA_TARGET is explicitly set in the parameter file, it becomes the lower limit (minimum size) for the SGA. The PGA_AGGREGATE_TARGET will always get an amount of memory equal to MEMORY_TARGET - SGA_TARGET.

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。