下一篇：Oracle11g Data Guard Redo Tranport Services安全性增强

Oracle11g新特性：Automatic Memory Management(AMM)

作者：NinGoo | 【转载须以超链接形式标明文章原始出处和作者信息】

Oracle9i引入pga_aggregate_target，可以自动对PGA进行调整；Oracle10引入sga_target，可以自动对SGA进行调整。Oracle11g则对这两部分进行综合，引入memory_target，可以自动调整所有的内存，这就是新引入的自动内存管理特性。

SQL> alter system set memory_target=200m scope=spfile;

System altered.

SQL> alter system set memory_target=200m scope=spfile;

System altered.

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

System altered.

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

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 209235968 bytes
Fixed Size 1298920 bytes
Variable Size 150998552 bytes
Database Buffers 54525952 bytes
Redo Buffers 2412544 bytes
Database mounted.
Database opened.

设置memory_target参数后，实际上Oracle会自动设置并调整两个以双下划线开头的隐含参数来分配SGA和PGA的内存，这和Oracle10g自动设置sga_target后分配db_cache_size和shared_pool_size的机制是一样的

SQL> select a.ksppinm name,b.ksppstvl value
2 from x$ksppi a,x$ksppcv b
3 where a.indx=b.indx
4 and (a.ksppinm like '%sga_target%'
5 or a.ksppinm like '%pga_aggregate_target%');

NAME VALUE
---------------------------------------- ------------------------------
sga_target 0
__sga_target 125829120
pga_aggregate_target 0
__pga_aggregate_target 79691776

如果memory_max_target/memory_target设置过大，可能导致instance无法启动，报ORA-00845错误

SQL> alter system set memory_max_target=300m scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

这个错误有点误导，实际上这并不是说该平台版本上不支持AMM特性，只是设置的memory_max_target超过了系统中设置的share memory（/dev/shm）而已。在Oracle11g for linux中似乎使用了一种新的机制来管理共享内存段，而不是传统的sys v shm了。在alert.ora中可以找到更准确的错误描述

Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 314572800 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 209715200 and used is 0 bytes.
memory_target needs larger /dev/shm

所以，这个错误有两种解决方法。第一种自然是减少memory_max_target/memory_target。第二种则是增加/dev/shm

[root@localhost ~]# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
shmfs 204800 0 204800 0% /dev/shm

[root@localhost ~]# umount /dev/shm
[root@localhost ~]# mount -t tmpfs shmfs -o size=300m /dev/shm

[root@localhost ~]# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
shmfs 358400 0 358400 0% /dev/shm

[root@localhost trace]# ls -l /dev/shm
total 0

增加/dev/shm后再次启动instance

SQL> startup
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size 1299624 bytes
Variable Size 255855448 bytes
Database Buffers 50331648 bytes
Redo Buffers 6373376 bytes
Database mounted.
Database opened.

再来看/dev/shm中的内容

[root@localhost trace]# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
shmfs 307200 122792 184408 40% /dev/shm
[root@localhost trace]# ls -l /dev/shm
total 122792
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_0
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_1
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_10
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_11
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_12
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_13
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_14
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_15
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_16
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_17
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_18
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_19
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_2
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_20
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_21
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_22
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_23
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_24
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_25
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_26
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_27
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_28
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_29
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_3
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_30
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_31
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_32
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_33
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_34
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_35
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_36
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_37
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_38
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_39
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_4
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_40
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_41
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_42
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_43
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_44
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_45
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_46
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_47
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_48
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_49
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_5
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_50
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_51
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_52
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_53
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_54
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_55
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_56
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_57
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_58
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_59
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_6
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_60
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_61
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_62
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_63
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_64
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_65
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_66
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_67
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_68
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_69
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_7
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_70
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_71
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_72
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_73
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_74
-rw-r----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_75
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_8
-rw-r----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_9

关于memory_target,sga_target,pga_aggregate_target之间的依赖关系，请参考Metalink Note:452512.1

本文网址：http://www.ningoo.net/html/2007/oracle11g_new_feature_automatic_memory_management.html

订阅到Google | 收藏到Del.icio.us | 推荐到鲜果

Posted by NinGoo 2007-09-10 15:12 星期一

相关文章	随机文章
Oracle11g新特性：Active Database Duplicate Oracle11g新特性：虚拟列virtual column Oracle11g新特性：RMAN压缩备份 Oracle11g新特性：server result cache续二 Oracle11g新特性：Flashback Data Archive Oracle11g新特性：dba_users不再显示密码	Analytic Workspace之导入导出 sqlplus小窍门：生成html报告编写插件修改Wordpress的RSS输出 Wordpress 2.3 Beta 1发布 Oracle10gR2 Streams(七)用户级复制台风，又见台风

本文Tags: oracle, Oracle11g, 新特性

1 条评论

At 2007.09.15 21:59, NinGoo said:

补充一点，/dev/shm是POSIX标准的shared memory实现，和sys v的shared memory不一样，不是分配大段的memory segment，而是以映射文件的方式实现，在上面看到的，每个小文件都是4M，也就是一个granularity的大小，这样，如果sga超过128M，则每个文件应该达到16M。

采用POSIX标准的shared memory实现，应该和AMM特性是相关的，这样才能将SGA多余的空间释放给PGA使用。

[Reply]

Oracle11g新特性：Automatic Memory Management(AMM)

1 条评论

订阅本站 | Subscribe

文章分类 | Categories

最近文章 | Recent posts

站点信息 | Statistics