关于memory_max_target,memory_target,sga_max_size,sga_target

最新推荐文章于 2024-09-06 12:31:36 发布

cituokang3777

最新推荐文章于 2024-09-06 12:31:36 发布

阅读量407

点赞数

文章标签：数据库 java

首先先看下11gr2的oracle的帮助文档中关于这几个参数的解释：

MEMORY_TARGET

Property Description

Parameter type Big integer

Syntax MEMORY_TARGET = integer [K | M | G]

Default value 0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMMEDIATE mode autotuning requests)

Modifiable ALTER SYSTEM

Range of values 152 MB to MEMORY_MAX_TARGET

Basic No

MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.

In a text-based initialization parameter file, if you omit MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, then the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

Total memory usage can grow beyond the value of MEMORY_TARGET. For example, memory is allocated to PL/SQL tables and varrays regardless of the value of MEMORY_TARGET as long as memory is available at the operating system level.

In the Default value field, IMMEDIATE mode autotuning requests are necessary to avoid ORA-04031 errors. The DEFERRED and IMMEDIATE modes are reflected in the OPER_MODE column of the V$MEMORY_RESIZE_OPS view.

MEMORY_MAX_TARGET

Property Description

Parameter type Big integer

Syntax MEMORY_MAX_TARGET = integer [K | M | G]

Default value 0

Modifiable No

Range of values 0 to the physical memory size available to the Oracle Database

Basic No

MEMORY_MAX_TARGET specifies the maximum value to which a DBA can set the MEMORY_TARGET initialization parameter. See the description of MEMORY_TARGET for more information about how the settings of MEMORY_MAX_TARGET and MEMORY_TARGET affect each other.

从上面描述中可以先明确几点：
1、memory_max_target值限制了memory_targe的值。如果你需要加大memory_target并且加大的值大于memory_max_target值需要先把memory_max_target 值调大。
2、如果没有设置memory_max_target参数，那么memory_max_target会自动取memory_target的参数值。
3、如果没有设置memory_target但是设置了memory_max_target，那么memory_target默认为0，需要手动设置大小
4、如果memory_target为0，那么需要数据库的模式为 IMMEDIATE mode，否则会报ORA-04031错误。这点我在11.1.0.7环境中未验证，不知道是否是11.2的，V$MEMORY_RESIZE_OPS这个视图中的 OPER_MODE都是空列.
另外，根据已经测试的情况：
1、初始话参数中不存在memory_max_target和memory_target，默认值为系统的shm的值，而shm值为系统物理内存的一半。
2、要改大memory_target需要先调整shm的值，然后修改memory_max_target，最后调整memory_target。
3、调整方法是shm需要umount /dev/shm，然后在fstab文件中default列后面加上：,sieze=nM，n为需要调整的内存值，一般我会把n值比要设置的memory_max_target大一些，接着mount /dev/shm，shm值就调整好了。另外umount之前需要把数据库和集群都关闭，否则shm无法umount。
4、memory_max_target是静态参数，修改的时候只能指定scope=spfile参数，memory_target是动态参数，可以直接修改，但是修改的时候还是必须保证修改的值小于memory_max_target的值，所以在增大玩memory_max_target后需要重启数据库保证参数生效才行。另外在集群环境下memory_target参数是单实例修改，需要后面加上参数sid='*'表示全部实例修改。

再来看下sga的2个参数:

SGA_MAX_SIZE

Property	Description
Parameter type	Big integer
Syntax	SGA_MAX_SIZE = integer [K \| M \| G]
Default value	Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
Modifiable	No
Range of values	0 to operating system-dependent

SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.

On 64-bit platforms and non-Windows 32-bit platforms, when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters. This causes more address space to be reserved for expansion of the SGA.

On Windows 32-bit platforms, the default value of SGA_MAX_SIZE is the largest of the following values:

60% of MEMORY_TARGET, if specified
60% of MEMORY_MAX_TARGET, if specified
25% of the total available virtual address space

SGA_TARGET

Property	Description
Parameter type	Big integer
Syntax	SGA_TARGET = integer [K \| M \| G]
Default value	0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMMEDIATE mode autotuning requests)
Modifiable	ALTER SYSTEM
Range of values	64 MB to operating system-dependent
Basic	Yes

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

Buffer cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool (JAVA_POOL_SIZE)
Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to nonzero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

Log buffer
Other buffer caches, such as KEEP, RECYCLE, and other block sizes
Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

这边可以看到sga_max_size、 sga_target和前面的memory类似，不过sga_target参数的介绍关于默认值0可能有问题，按http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory003.htm#BGBJAHEJ
这个文档的说法开启AMM（ Automatic Memory Management）的方法就是设置sga_target=0，同样在11.1中的 V$MEMORY_RESIZE_OPS 视图也没有看到OPEN_MODE的值。

关于设置 sga_max_size、 sga_target 的参数可以参看下这篇文档，http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory004.htm#BGBFDFJB
实际测试中发现默认的初始化文件中是不存在 sga_max_size，也不要去手动设置这个参数值，一旦使用alter system set sga_max_size 后参数文件中就会加上这个参数，而且不能设置成0，所以如果不小心设置了 sga_max_size 最好的方法是先生成pfile文件，将该参数删除在用pfile文件生成spfile文件，如果在集群环境下生成spfile的文件切记加上spfile文件的路径，否则会生成到dbs路径下。

AMM和ASMM( Automatic SGA Memory)这2者的区别就在于sga_target这个值，如果是0表示开启了AMM来管理sga，否则用开启ASMM管理sga。AMM是11g新增的功能，10g新增了ASMM，9i新增了APM( Automatic PGA Memory)

大概就这些先，有问题的请留言。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7863502/viewspace-740481/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7863502/viewspace-740481/