大家好，又见面了，我是你们的朋友全栈君。
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的值：
SELECT ((SELECT SUM(VALUE) FROM V$SGA) – (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)) “SGA_TARGET” FROM DUAL;
设置SGA_TARGET的值，可以直接修改初始化参数文件后重启数据库，或者通过下面SQL命令进行修改：
ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}];
示例如下所示：
ALTER SYSTEM SET SGA_TARGET = 992M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
在启用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需要重启数据库，具体步骤如下所示：
1、查询SGA_TARGET和PGA_AGGREGATE_TARGET的值，从而确定MEMORY_TARGET的最小值
SYS@lhrdb> SHOW PARAMETER TARGET
NAME TYPE VALUE
———————————— ———– ——————————
pga_aggregate_target big integer 409M
sga_target big integer 1648M
2、确定自系统启动以来PGA的最大值，单位为bytes
SYS@lhrdb> select value from v$pgastat where name=’maximum PGA allocated’;
VALUE
———-
248586240
3、通过以下方法来计算出SGA_TARGET的最大值
memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)
例如：在这里，SGA_TARGET的值为1648M，PGA_AGGREGATE_TARGET的值为409M，PGA的最大值为248586240/1024/1024=237M，所以，MEMORY_TARGET的值至少为1648+409=2057M。
4、设置系统参数启用AMM
ALTER SYSTEM SET MEMORY_MAX_TARGET = 2100M SCOPE=SPFILE;
—重启数据库
ALTER SYSTEM SET MEMORY_TARGET = 2057M;
ALTER SYSTEM SET SGA_TARGET = 0;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;
另外需要说明的一点是，使用AMM经常出现的一个错误是“ORA-00845: MEMORY_TARGET not supported on this system”。
[ZFZHLHRDB1:oracle]:/oracle>oerr ora 845
00845, 00000, “MEMORY_TARGET not supported on this system”
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.
这个错误原因是操作系统不支持MEMORY_TARGET参数或/dev/shm的大小设置不正确。解决方法就是将/dev/shm的值增大，至少需要大于数据库参数MEMORY_MAX_TARGET的值。修改步骤如下：
[root@LHRDB ~]# cat /etc/fstab | grep tmpfs #查看/dev/shm大小
tmpfs /dev/shm tmpfs defaults,size=1G 0 0
[root@LHRDB ~]# mount -o remount,size=4G /dev/shm #临时修改/dev/shm大小
[root@LHRDB ~]# vi /etc/fstab #永久修改/dev/shm大小， tmpfs /dev/shm tmpfs defaults,size=4G 0 0
[root@LHRDB ~]# df -h | grep shm
tmpfs 4.0G 0 4.0G 0% /dev/shm
[root@LHRDB ~]# df -h /dev/shm/
Filesystem Size Used Avail Use% Mounted on
tmpfs 4.0G 0 4.0G 0% /dev/shm
[root@LHRDB ~]# cat /etc/fstab | grep tmpfs
tmpfs /dev/shm tmpfs defaults,size=4G 0 0
再次启动数据库就可以正常启动了。
由于AMM不支持HugePage，而ASMM支持HugePage，所以，在生产库上强烈推荐使用ASMM。有关ASMM和AMM的区别如下表所示：
表 3–10 ASMM和AMM的区别
MOS文档“SGA and PGA Management in 11g’s Automatic Memory Management (AMM) (文档 ID 1392549.1)”对AMM和ASMM有非常详细的说明。
Oracle 11g AMM与ASMM切换
现在的Oracle正在往智能化方向发展。如果我们现在找一些8i/9i时代的Oracle书籍，怎么样配置合适的数据库各内存池大小是非常重要的话题。但是进入10g之后，自动内存池调节成为一个重要Oracle特性。
在10g时，Oracle推出了ASMM（Automatic Shared Memory Management），实现了Oracle SGA和PGA内部结构的自调节。进入11g之后，AMM（Automatic Memory Management）实现了参数MEMORY_TARGET，将SGA和PGA的规划全部统筹起来对待。
默认情况下，Oracle 11g是使用AMM的。我们在安装过程中，指定Oracle使用内存的百分比，这个取值就作为MEMORY_TARGET和MEMORY_MAX_TARGET的初始取值使用。如果这两个参数设置为非零取值，那么Oracle就是采用AMM管理策略的。
同时，如果我们设置这两个参数为0，则AMM自动关闭。对应的SGA_TARGET、PGA_AGGREGATE_TARGET参数取值非零之后，Oracle自动退化使用ASMM特性。
本篇简单介绍一下AMM和ASMM的相互切换。
1、实验环境介绍
我们选择11.2.0.3进行试验，当前状态为ASMM。
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
当前MEMORY_TARGET设置为零，AMM没有启用。
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 108M
sga_target big integer 252M
2、从ASMM到AMM
在11g中，如果使用ASMM，对应的内存共享段是真实的共享段。
[oracle@SimpleLinux ~]$ ipcs -m
—— Shared Memory Segments ——–
key shmid owner perms bytes nattch status
0x00000000 32768 oracle 640 4194304 32
0x00000000 65537 oracle 640 260046848 32
0x01606d30 98306 oracle 640 4194304 32
下面进行参数的调整，这里笔者有一个建议，很多时候启动umount阶段故障都是由于不当的参数修改造成的。在进行参数修改之前，可以使用create pfile进行一下参数备份，可以加快故障出现时候的系统修复速度。
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/dbs/spfileora11g.ora
SQL> create pfile from spfile;
Done
修改系统参数，将sga和pga的target值设置为0，memory的target设置非0。注意，这个过程很多参数是静态的参数，可以都在spfile可见性中进行修改，之后重启服务器生效。
SQL> alter system set memory_max_target=360m scope=spfile;
System altered
SQL> alter system set memory_target=360m scope=spfile;
System altered
SQL> alter system set sga_target=0m scope=spfile;
System altered
SQL> alter system set sga_max_size=0 scope=spfile;
System altered
SQL> alter system set pga_aggregate_target=0 scope=spfile;
System altered
重新启动数据库服务器，查看参数配置。
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 263651328 bytes
Fixed Size 1344284 bytes
Variable Size 176164068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2256896 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 360M
memory_target big integer 360M
parallel_servers_target integer 16
pga_aggregate_target big integer 0
sga_target big integer 0
AMM启动之后，系统共享段变为“虚拟”共享段。
[oracle@SimpleLinux dbs]$ ipcs -m
—— Shared Memory Segments ——–
key shmid owner perms bytes nattch status
0x00000000 163840 oracle 640 4096 0
0x00000000 196609 oracle 640 4096 0
0x01606d30 229378 oracle 640 4096 0
3、从AMM到ASMM
下面是如何从AMM到ASMM。要完全关闭AMM，一定将MEMORY_TARGET和MEMORY_MAX_TARGET都设置为0才行。
SQL> alter system set memory_max_target=0 scope=spfile;
System altered
SQL> alter system set memory_target=0 scope=spfile;
System altered
SQL> alter system set pga_aggregate_target=100m scope=spfile;
System altered
SQL> alter system set sga_target=260m scope=spfile;
System altered
SQL> alter system set sga_max_size=260m scope=spfile;
System altered
注意，此时如果重新启动系统，会报错。
SQL> startup force
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 272629760 cannot be set to more than MEMORY_MAX_TARGET 0.
SQL>
这个问题的原因是Oracle启动过程中对于参数的内部检查。因为MEMORY_MAX_TARGET被“显示”的赋值，与SGA_TARGET赋值相冲突。
解决的方法就是使用参数默认值。创建出pfile之后，将显示赋值为0的MEMORY_TARGET和MEMORY_MAX_TARGET记录行删除掉。再利用pfile启动数据库，重建spfile。
SQL> create pfile from spfile
2 ;
File created.
–修改前
*.db_recovery_file_dest=’/u01/app/fast_recovery_area’
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest=’/u01/app’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)’
*.log_checkpoints_to_alert=TRUE
*.memory_max_target=0
*.memory_target=0
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
–修改后
*.db_recovery_file_dest=’/u01/app/fast_recovery_area’
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest=’/u01/app’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)’
*.log_checkpoints_to_alert=TRUE
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
使用pfile启动数据库，重建spfile。
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=/u01/app/oracle/dbs/initora11g.ora
ORACLE instance started.
Total System Global Area 272011264 bytes
Fixed Size 1344372 bytes
Variable Size 176163980 bytes
Database Buffers 88080384 bytes
Redo Buffers 6422528 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 0
memory_target big integer 0
parallel_servers_target integer 16
pga_aggregate_target big integer 100M
sga_target big integer 260M
SQL> create spfile from pfile
2 ;
File created.
重新启动之后，ASMM切换完成。
SQL> startup force
ORACLE instance started.
Total System Global Area 272011264 bytes
Fixed Size 1344372 bytes
Variable Size 176163980 bytes
Database Buffers 88080384 bytes
Redo Buffers 6422528 bytes
Database mounted.
Database opened.
–真实的共享内存段结构
[oracle@SimpleLinux dbs]$ ipcs -m
—— Shared Memory Segments ——–
key shmid owner perms bytes nattch status
0x00000000 425984 oracle 640 8388608 25
0x00000000 458753 oracle 640 264241152 25
0x01606d30 491522 oracle 640 4194304 25
–HugePage使用情况
[oracle@SimpleLinux dbs]$ grep Huge /proc/meminfo
HugePages_Total: 67
HugePages_Free: 1
HugePages_Rsvd: 0
Hugepagesize: 4096 kB
[oracle@SimpleLinux dbs]$
4、结论
AMM和ASMM是我们管理数据库非常重要的工具，借助自我调节的机制，我们可以做到数据库自我管理。11g的AMM应该说是很方便的，但是在一些情况下，如HugePage，我们可能需要切换回ASMM。权当记录，留需要的朋友待查。
每一个Oracle的初学者在入门阶段都会接触到SGA/PGA的知识，如果是从10g开始学习那么会多或少会对ASMM有所了解，从使用的角度来说ASMM的出现极大地简化了Oracle内存初始化参数的设置，在ASMM的使用上高级DBA和初学者不会有太大的差别；很多人因此而认为ASMM极大程度地减少了数据库对于专业DBA的依赖：如果我们有一个足够智能的DB，那么为什么还要花费金钱雇佣DBA呢？这似乎是时下一种十分流行的想法。当然这种想法我个人是不能苟同的，ASMM一定程度上带来了便利，更大程度上它是一个黑盒，黑盒里面藏了很多秘密，这些秘密带来比手动管理更多的不确定性；在10g release 1和10.2的早期版本中ASMM扮演的角色有点像一个闯祸精，另一个让用户对ASMM很不待见的原因是ASMM直接拖慢了startup的速度。一个个人观点是ASMM也好AMM也罢，都要求产品数据库DBA掌握更多SGA/PGA相关的知识才能成功”驾驭”这些”有智力”的家伙，有点夸张的说这个时候的DBA很像一个chemist(需要和一大堆以1个或2个下划线开头的奇怪参数打交道)。
为了不辱使命我们真的有必要了解一下ASMM的基本知识，显然这并不是一件容易的事情……
Oracle的SGA基本内存组件从9i开始并没有非常大的变化，他们包括:
- Buffer Cache 我们常说的数据库高速缓存，虽然我一直不明白要冠以高速之名
- Default Pool 默认的缓冲池，大小由DB_CACHE_SIZE决定
- Keep Pool 持久的缓冲池，大小由DB_KEEP_CACHE_SIZE决定
- Non standard pool 非标准块标准池，大小由DB_nK_cache_size决定
- Recycle pool 回收池，大小由db_recycle_cache_size决定
- Shared Pool 共享池，大小由shared_pool_size决定
- Library cache 俗称的库缓存
- Row cache 行缓存，也叫字典缓存
- Java Pool java池，大小由Java_pool_size决定
- Large Pool 大池，大小由Large_pool_size决定
- Fixed SGA 固定的SGA区域，包含了Oracle内部的数据结构，一般被存放在第一个granule中
在9i中尚未引入ASMM，唯一的选择是手动管理的SGA，有时候也叫做MSMM。在9i中除去buffer cache的大小可以手动修改外，其余组件都无法动态修改。因为缺乏一种动态管理的机制，所以在9i中如果有某个内存区域有急用，也无法从其他有空闲内存的组件中捐献一些来解燃眉之急。
手动管理SGA的缺点在于:
- 个别组件如shared pool、default buffer pool的大小存在最优值，但组件之间无法交换内存
- 在9i中就提供了多种内存建议(advisor)，但都要求人工手动干预
- 无法适应工作负载存在变化的环境
- 往往会导致内存浪费，没有用到实处
- 若设置不当，引发著名的ORA-04031错误的可能性大大提高
ASMM的优势在于:
- 全自动的共享内存管理
- 无需再配置每一个内存组件大小参数
- 仅使用一个参数sga_target驱动
- 有效利用所有可用的内存，极大程度上减少内存浪费
- 对比MSMM其内存管理模式:
- 更加动态
- 更加灵活
- 并具备适应性
- 易于使用
- 一定程度上增强了性能，因为内存分配更为合理了
- 当某个组件急需更多内存时可以有效提供，因此可以一定程度避免ORA-04031的发生
ASMM主要可以囊括为三个部分：
1.由一个新参数sga_target驱动的管理模式
2.内存交换的模型，包括了：内存组件(memory component)，内存代理(memory broker)和内存交换机制(memory mechanism)
3.内存建议(memory advisor)
ASMM下一部分参数是自动设置的(Automatically set)，这些参数包括:shared_pool_size、db_cache_size、java_pool_size、large_pool _size和streams_pool_size；而另外一些是需要手动设置的静态参数，包括了:db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size、log_buffer以及固定SGA内存结构等，如果以上没有手动设置这些参数的话，除去log_buffer和fixed sga其他内存组件一般默认大小为零。
通过ASMM用户仅需要设置一个sga_target参数，其他参数都将由ASMM自行内部控制。但如果用户依旧设置了如db_cache_size、java_pool_size等参数，那么这些参数被认为是相关内存组件的最小限制，同时每个内存组件的大小也存在一个最大上限(内部的)。在实例启动阶段，Oracle会将必要的内存颗粒(granule，当SGA<1GB时granule大小为4M，当SGA>1GB时granule大小为16M)分配给内存组件，多余没有分配出去的全都分配给buffer cache，之后随着系统的不断活跃更多的内存颗粒(granule)将被分配给急需内存的组件。我们可以动态地修改sga_target参数，前提是所在的系统平台支持动态地共享内存(dism，主流平台都支持)。使用ASMM的一个必要条件是初始化参数statistics_level必须设置为typical或ALL，如果设置为BASIC那么MMON后台进程(Memory Monitor is a background process that gathers memory statistics (snapshots) stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds)将无法有效分析内存的使用的统计信息从而驱动ASMM的自动调优，实际上我们不能同时设置sga_target为非零值和statistics_level为BASIC:
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2000M
sga_target big integer 2000M
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 2000M
SQL> alter system set statistics_level=BASIC;
alter system set statistics_level=BASIC
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
如果使用了server parameter file即spfile的话，ASMM会在实例shutdown之前将当前实际的内存组件大小(Oracle认为这是最优的，但实际上可能不是)保存到spfile中，如果你使用strings命令打印过spfile的内容的可以发现一些以双下划线开头的参数，如:
G10R2.__db_cache_size=973078528
G10R2.__java_pool_size=16777216
G10R2.__large_pool_size=16777216
G10R2.__shared_pool_size=1006632960
G10R2.__streams_pool_size=67108864
这些在spfile保存的组件大小会在下次启动时被沿用，以达到将已经实践得出的”最佳值”记住的目的，这样下次就不用从头再”学习”了。
在ASMM的内存交换模型中存在三类组件
- 可调优组件(tunable):可调优组件是那些大小可以随之变化且若过小仅损害少量性能。buffer cache就是一个经典的例子，cache过小的情况下应用程序仍能正常运行，但带来的代价是更多的 IO。注意可调优组件同时存在一个下限。举例来说共享池由库缓存Library cache和其他一些 subheap子堆组成，那么共享池就存在一个下限要保证至少能放下某个一个时刻并行打开的游标(open cursors)以及负担其他共享池client的运行时内存分配需求。类似的buffer cache也存在一个下限，该下限至少要大于并行被pin住的buffer的总和(虽然这部分很小)
- 不可调组件(Un-tunable):不可调组件是那些存在最小下限的内存组件，这个最小下限足够让应用程序正常运行，超过这个上限并不会带来额外的性能收益。在这类组件中large pool是一个典型。
- 固定大小组件(Fixed Size)：自动调优框架之外的组件，一般为固定大小。这些组件的大小仅在手动调整时改变。例如非标准块大小的高速缓冲池
内存交换模型中内存大小调整的申请(memory resize request)存在三种不同的模式，它们分别是：
立即内存申请(Immediate Request)：这种申请模式一般出现在ASMM管理的某个自动调优组件在无法分配到连续可用内存块(chunk)时，为了避免出现OUT-OF-MEMORY(ORA-04031)的错误，系统尝试从其他候选组件中转移一个内存颗粒(granule)过来。需要注意的是当前可能没有可用的全空granule，这时就会出现不完整的转移，在此情形下系统会开始从候选组件中清理内存颗粒以满足内存申请，并将一个granule不完整地转移给急需内存的组件。
延迟内存申请(Deferred Request)：这种申请一般出现在系统认为存在一种更为合理的内存分配时，考虑在组件之间转移一个或多个granule。用以判定最佳内存分配的依据是MMON进程所提供的统计信息delta.
手动内存申请(Manual Request)：这种申请仅发生在用户使用alter system命令手动调整内存组件的大小时。在这种模式下仅允许使用空的内存颗粒参与大小调整。如果当时没有足够的空granule，那么针对组件grow操作会返回ORA-4033错误，而针对组件shrink操作会返回ORA-4034错误。
当ASMM被启用后，内存代理(Memory Broker)定期执行上图所示的活动。以上操作都处于延迟内存申请模式下(Deferred)。其目的是通过自动化的作业来调优自动调优组件(auto-tunable component)的大小以适应不断改变的工作负载，将内存分配到最需要它们的地方。MMON辅助进程会在后台不断将统计信息和内存建议定期地捕获到循环使用的内存中，并计算不同时期缓存信息之间的差值；MMON还会基于Memory Broker的策略分析这些差值并估算出长期和短期内的趋势。最后MMON基于以上分析生成一些内存组件的大小调整决议并将相应的申请发送到一个系统申请队列中(resize request system queue)。MMAN后台进程(Memory Manager is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases)会定期扫描系统申请队列并执行内存转移。
在10gR1中Shared Pool的shrink收缩操作存在一些缺陷，造成缺陷的原因是在该版本中Buffer Cache还没有能力共享使用一个granule，这是因为Buffer Cache的granule的尾部由granule header和Metadata(可能是buffer header或者RAC中的Lock Elements)拼接组成，在其尾部不容许存在空洞。另一个原因是当时的shared pool允许不同生命周期duration(以后会介绍)的chunk存放在同一个granule中，这造成共享池无法完全释放granule。到10gR2中通过对Buffer Cache Granule结构的修改允许在granule header和buffer及Metadata(buffer header或LE)存在缝隙，同时shared pool中不同duration的chunk将不在共享同一个granule，通过以上改进buffer cache与shared pool间的内存交换变得可行。此外在10gr2中streams pool也开始支持内存交换(实际根据不同的streams pool duration存在限制)
当某个组件扮演捐献者(Donor，下面的trace中会看到)角色时，它可能将一个不完整granule转移给buffer cache，那么在该granule被使用前需要完成以下步骤:
- 首先在该granule的尾部生成新的granule header
- 针对剩下的chunk判定在该granule中是否还有未使用的buffer header
- 如果有，那么将chunk中的内存转换为一个数据块。否则将之转换为一个metadata块
- 重复以2-4步骤，直到该granule被转换完
接着我们来了解一下内存转移的基本原理，当将buffer cache中的granule转移给shared pool时，将按照以下步骤:
- MMAN后台进程找到一块属于buffer cache的合适granule
- MMAN将看中的granule置入quiesce列表中(Moving 1 granule from inuse to quiesce list of DEFAULT buffer cache for an immediate req)
- DBWR将负责写出置入quiesced列表中granule里面的脏buffer(dirty buffer)
- MMAN将为shared pool调用消费回调函数(consume callback)，granule中free的chunk都会被shared pool消费(consume)掉，并对共享池新的内存分配可用。从这里开始该granule变成一个shared granule共享内存颗粒，注意不要认为这个时候该granule的空间全部属于共享池了，实际上有部分pin住的buffer及其Metadata(上述的buffer header和LE)的空间仍被buffer cache占用着
- 最终该granule将完整的转移给shared pool，这时此granule不再是一个shared共享的
实际使用中ASMM受到众多隐藏参数的影响，其中比较主要的参数有:
- _enabled_shared_pool_duration:该参数控制是否启用10g中特有的shared pool duration特性，当我们设置sga_target为0时该参数为false；同时在10.2.0.5前若cursor_space_for_time设置为true时该参数也为false，不过在10.2.0.5以后cursor_space_for_time参数被废弃
- _memory_broker_shrink_heaps:若该参数设置为0那么Oracle不会去收缩shared pool或java pool，当该参数大于0，会在shrink request失败后等待该参数指定秒数时间后再次申请
- _memory_management_tracing: 该参数控制针对MMON和MMAN后台进程包括内存建议(advisor)和内存代理(Memory Broker)决议的相关trace的级别；针对ORA-04031的诊断可以设置为36，设置为8启用针对启动期间组件大小的trace，设置为23启动针对Memory Broker decision的跟踪，设置为32将转储cache resize的细节；该参数的具体级别如下:
| Level | Contents |
| 0x01 | Enables statistics tracing |
| 0x02 | Enables policy tracing |
| 0x04 | Enables transfer of granules tracing |
| 0x08 | Enables startup tracing |
| 0x10 | Enables tuning tracing |
| 0x20 | Enables cache tracing |
接下来我们通过设置_memory_management_tracing隐藏参数和DUMP_TRANSFER_OPS转储来实地了解一次完整的内存转移，和不完整的内存转移。以下演示的完整trace文件可以从这里下载mman_trace、transfer_ops_dump。
SQL> alter system set "_memory_management_tracing"=63;
System altered Operation make shared pool grow and buffer cache shrink!!!..............
以下为一个完整granule转移的过程，包括了对default buffer pool的resize操作:
AUTO SGA: Request 0xdc9c2628 after pre-processing, ret=0
/* 这里的0xdc9c2628是前台进程的addr */
AUTO SGA: IMMEDIATE, FG request 0xdc9c2628
/* 这里可以看到前台进程的Immediate立即申请 */
AUTO SGA: Receiver of memory is shared pool, size=16, state=3, flg=0
/* 此次申请的收益人是shared pool，共享池，其大小为16个granule，处于grow状态 */
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=106, state=4, flg=0
/* 此处的捐献者是Default buffer cache，高速缓存，其大小为106个granule，处于shrink状态 */
AUTO SGA: Memory requested=3896, remaining=3896
/* 这里immeidate request所要求的空间是3896 bytes */
AUTO SGA: Memory received=0, minreq=3896, gransz=16777216
/* 这里没有free的granule，所以received为0，gransz为granule的大小 */
AUTO SGA: Request 0xdc9c2628 status is INACTIVE
/* 因为没有空的内存颗粒，先将申请置于inactive状态 */
AUTO SGA: Init bef rsz for request 0xdc9c2628
/* 为相关申请初始化before-process大小调整 */
AUTO SGA: Set rq dc9c2628 status to PENDING
/* 将request置于pending状态 */
AUTO SGA: 0xca000000 rem=3896, rcvd=16777216, 105, 16777216, 17
/* 返回起始地址为0xca000000的16M大小granule */
AUTO SGA: Returning 4 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 4, 1, a
AUTO SGA: Resize done for pool DEFAULT, 8192
/* 完成对default pool的resize */
AUTO SGA: Init aft rsz for request 0xdc9c2628
AUTO SGA: Request 0xdc9c2628 after processing
AUTO SGA: IMMEDIATE, FG request 0x7fff917964a0
AUTO SGA: Receiver of memory is shared pool, size=17, state=0, flg=0
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=105, state=0, flg=0
AUTO SGA: Memory requested=3896, remaining=0
AUTO SGA: Memory received=16777216, minreq=3896, gransz=16777216
AUTO SGA: Request 0x7fff917964a0 status is COMPLETE
/* shared pool成功收到16M的granule */
AUTO SGA: activated granule 0xca000000 of shared pool
以下为一个partial granule不完全内存颗粒的转移过程trace:
AUTO SGA: Request 0xdc9c2628 after pre-processing, ret=0
AUTO SGA: IMMEDIATE, FG request 0xdc9c2628
AUTO SGA: Receiver of memory is shared pool, size=82, state=3, flg=1
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=36, state=4, flg=1
/* 此处的受益者仍为shared pool，而捐献者是default buffer cache */
AUTO SGA: Memory requested=4120, remaining=4120
AUTO SGA: Memory received=0, minreq=4120, gransz=16777216
AUTO SGA: Request 0xdc9c2628 status is INACTIVE
AUTO SGA: Init bef rsz for request 0xdc9c2628
AUTO SGA: Set rq dc9c2628 status to PENDING
AUTO SGA: Moving granule 0x93000000 of DEFAULT buffer cache to activate list
AUTO SGA: Moving 1 granule 0x8c000000 from inuse to quiesce list of DEFAULT buffer cache for an immediate req
/* 以上将buffer cache中起始地址为0x8c000000的granule从使用中列表inuse list，
移动到静默列表quiesce list中 */
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: activated granule 0x93000000 of DEFAULT buffer cache
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
/ * 等待dbwr写出0x8c000000 granule中所有的dirty buffer */
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
......................................... AUTO SGA: Rcv shared pool consuming 8192 from 0x8c000000 in granule 0x8c000000; owner is DEFAULT buffer cache AUTO SGA: Rcv shared pool consuming 90112 from 0x8c002000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 24576 from 0x8c01a000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 65536 from 0x8c022000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 131072 from 0x8c034000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 286720 from 0x8c056000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 98304 from 0x8c09e000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 106496 from 0x8c0b8000 in granule 0x8c000000; owner is DEFAULT buffer cache
.....................
/* 以上shared pool开始消费0x8c000000 granule中的chunk，
但此granule的owner暂时仍为default buffer cache */
AUTO SGA: Imm xfer 0x8c000000 from quiesce list of DEFAULT buffer cache to partial inuse list of shared pool
/* 以上将0x8c000000 granule从default buffer cache的静默列表转移到shared pool的不完整inuse list */
AUTO SGA: Returning 4 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 4, 1, 20a
AUTO SGA: Init aft rsz for request 0xdc9c2628
AUTO SGA: Request 0xdc9c2628 after processing
AUTO SGA: IMMEDIATE, FG request 0x7fffe9bcd0e0
AUTO SGA: Receiver of memory is shared pool, size=83, state=0, flg=1
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=35, state=0, flg=1
AUTO SGA: Memory requested=4120, remaining=0
AUTO SGA: Memory received=14934016, minreq=4120, gransz=16777216
AUTO SGA: Request 0x7fffe9bcd0e0 status is COMPLETE
/* 以上一个partial transfer完成 */
对应于以上partial transfer我们可以通过DUMP_TRANSFER_OPS来了解该0x8c000000 partial granule的实际使用情况，如:
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump DUMP_TRANSFER_OPS 1;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R2/udump/g10r2_ora_21482.trc
=======================trace content==============================
GRANULE SIZE is 16777216
COMPONENT NAME : shared pool
Number of granules in partially inuse list (listid 4) is 23
Granule addr is 0x8c000000 Granule owner is DEFAULT buffer cache
/* 该0x8c000000 granule在shared pool的partially inuse list，
但这里它的owner仍为default buffer cache */
Granule 0x8c000000 dump from owner perspective
gptr = 0x8c000000, num buf hdrs = 1989, num buffers = 156, ghdr = 0x8cffe000
/ * 可以看到该granule的granule header地址位于0x8cffe000，
其中共有156个buffer block,1989个buffer header */
/* 以下granule中具体的内容，实际既包含了buffer cache也有shared pool chunk */
BH:0x8cf76018 BA:(nil) st:11 flg:20000
BH:0x8cf76128 BA:(nil) st:11 flg:20000
BH:0x8cf76238 BA:(nil) st:11 flg:20000
BH:0x8cf76348 BA:(nil) st:11 flg:20000
BH:0x8cf76458 BA:(nil) st:11 flg:20000
BH:0x8cf76568 BA:(nil) st:11 flg:20000
BH:0x8cf76678 BA:(nil) st:11 flg:20000
BH:0x8cf76788 BA:(nil) st:11 flg:20000
BH:0x8cf76898 BA:(nil) st:11 flg:20000
BH:0x8cf769a8 BA:(nil) st:11 flg:20000
BH:0x8cf76ab8 BA:(nil) st:11 flg:20000
BH:0x8cf76bc8 BA:(nil) st:11 flg:20000
BH:0x8cf76cd8 BA:0x8c018000 st:1 flg:622202
...............
Address 0x8cf30000 to 0x8cf74000 not in cache
Address 0x8cf74000 to 0x8d000000 in cache
Granule 0x8c000000 dump from receivers perspective
Dumping layout
Address 0x8c000000 to 0x8c018000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c018000 to 0x8c01a000 not in this pool
Address 0x8c01a000 to 0x8c020000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c020000 to 0x8c022000 not in this pool
Address 0x8c022000 to 0x8c032000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c032000 to 0x8c034000 not in this pool
Address 0x8c034000 to 0x8c054000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c054000 to 0x8c056000 not in this pool
Address 0x8c056000 to 0x8c09c000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c09c000 to 0x8c09e000 not in this pool
Address 0x8c09e000 to 0x8c0b6000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c0b6000 to 0x8c0b8000 not in this pool
Address 0x8c0b8000 to 0x8c0d2000 in sga heap(1,3) (idx=1, dur=4)
以上可以看到该granule真的是一个shared granule共享内存颗粒，其中不仅包含了部分buffer block，还包含了1号shared subpool共享池子池的durtaion为4的chunk，duration=4即execution duration;这类duration的chunk一般有着较短的生命周期，当其extent被置于quiesce list静默列表时将很有可能变得足够free。execution duration是共享池中唯一能可靠转移的，因此唯有该类duration所在的extent(一般来说一个extent占用一个granule)可以用来收缩。
以下我们列出一些有助于诊断ASMM问题的动态性能视图，仅供参考:
V$SGAINFO
Displays summary information about the system global area (SGA).
V$SGA
Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTAT
Displays detailed information about the SGA.
V$SGA_DYNAMIC_COMPONENTS
Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.
V$SGA_DYNAMIC_FREE_MEMORY
Displays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$SGA_RESIZE_OPS
Displays information about the last 400 completed SGA resize operations.
V$SGA_CURRENT_RESIZE_OPS
Displays information about SGA resize operations that are currently in progress. A resize operation is an enlargement or reduction of a dynamic SGA component.
V$SGA_TARGET_ADVICE
Displays information that helps you tune SGA_TARGET.
近期内会写一篇介绍shared pool duration的文章，作为对这篇的补充。
SGA and PGA Management in 11g’s Automatic Memory Management (AMM) (文档 ID 1392549.1)
In this Document
| Purpose |
| Scope |
| Details |
| Mandatory parameters for AMM |
| Optional parameters for AMM |
| How to control the memory under MEMORY_TARGET? |
| Give MMAN complete control |
| Take control of memory yourself |
| AMM details |
| CASE 1: Only MEMORY_TARGET is set |
| CASE 2: MEMORY_TARGET, SGA_TARGET and PGA_AGGREGATE_TARGET are set |
| CASE 3: MEMORY_TARGET, SGA_MAX_SIZE, SGA_TARGET and PGA_AGGREGATE_TARGET are set |
| Summary of case studies |
| ORA-4030 and ORA-4031 |
APPLIES TO:
Oracle Database – Enterprise Edition – Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 01-Apr-2016***
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.
SQL> alter system set memory_target = 1400M;
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.
How to control the memory under MEMORY_TARGET?
There are some options available to the Database Administrator to determine how memory is allocated to the various memory components.
Give MMAN complete control
You can consider MEMORY_TARGET as one big area of memory for MMAN to use. When you set no other parameters than MEMORY_TARGET, you give MMAN complete control of the area. MMAN divides this area in a ratio or 60% to SGA and 40% to PGA. But these are only initial settings, and MMAN will freely move memory between the two areas depending on memory pressures. Within the 60%, MMAN will resize the SGA components as it did under ASMM, but the difference is MMAN will now increase SGA_TARGET if the SGA is in need of more memory. Under ASMM, SGA_TARGET, once breached, would give a ORA-4031.
Take control of memory yourself
Recall that MEMORY_TARGET manages both SGA and PGA memory – and without limitations, MMAN will freely move memory back and forth between the two. If you wish to constrain aggressive PGA growth, set SGA_TARGET in the parameter file, and this now becomes a minimum size for the SGA. In this case PGA growth cannot shrink the SGA beyond this size.
If you want to constrain aggressive SGA growth, set SGA_MAX_SIZE in the parameter file, and this becomes the ceiling for the SGA. An amount of memory equal to SGA_MAX_SIZE is carved out of MEMORY_TARGET, and the SGA must fit within it. This limits the amount SGA can grow to.
Any of the SGA components can be set if you wish to make sure you have a certain minimum amount of memory for that component. The significant SGA components are:
- the shared pool (managed by the SHARED_POOL_SIZE instance parameter)
- the buffer cache (managed by the DB_CACHE_SIZE instance parameter)
- the Streams pool (managed by the STREAMS_POOL_SIZE instance parameter)
- the Java pool (managed by the JAVA_POOL_SIZE instance parameter)
- the large pool (managed by the LARGE_POOL_SIZE instance parameter)
If you wish to make any component larger (e.g. the buffer cache in order to maximize transaction throughput and minimize I/O), set the corresponding component parameter (DB_CACHE_SIZE for the example given) to the value you require. But remember, the new component size has to fit within the SGA_TARGET along with all other pools. So either MMAN will adjust SGA_TARGET to a larger value in case you did not set SGA_TARGET explicitly, or, you will have to reset SGA_TARGET manually to a larger value to accommodate for the component enlargement.
AMM details
One of the most significant changes of AMM, is that memory that was originally shared (SGA), can be released, and used by the Operating system for private memory (PGA). The reverse is also true: memory that was private to a process, can be released and used as shared memory. This movement of memory is contained within MEMORY_TARGET and managed by MMAN.
There are some important consequences of this. On a system with many large SQL statements, for example parallel executions, processes often do large sorts and table joins. These operations are memory intensive, and can consume a lot of the free memory under MEMORY_TARGET. As more of these processes start up, and need more private memory, MMAN will turn to the SGA in an attempt to reallocate shared memory as private memory.
Under the covers, if the PGA needs more memory, MMAN will run through the SGA free lists looking for memory chunks that are not currently in use. MMAN coalesces these free chunks, until it creates one contiguous chunk that is the size of a granule. The granule is then unlocked from the SGA, and put back in circulation for the OS to use as private memory for the PGA. Various Operating System functions are invoked to unlock the shared memory and return it to the O/S. These memory lock and unlock routines may differ by hardware platform. Running truss (or any similar utility) may show the O/S routines being used:
On UNIX, freeing memory from the SGA shows like the following in a truss output:
…
18039: munlock(0xc0000009c0000000, 16777216) <== this is the granule size (16M)
18039: madvise(0xc0000009c0000000, 0x1000000, MADV_DONTNEED) <== this is the command to free the granule
18039: munlock(0xc0000009c1000000, 16777216)
18039: madvise(0xc0000009c1000000, 0x1000000, MADV_DONTNEED)
18039: munlock(0xc0000009c2000000, 16777216)
18039: madvise(0xc0000009c2000000, 0x1000000, MADV_DONTNEED)
…
The number of the freed granules will directly correspond to the amount of memory being freed. For example, if the SGA_TARGET=4G and the granule size is 16M, and you reduce SGA_TARGET to 2G, you can use this command:
SQL> alter system set SGA_TARGET=2G;
The number of granules freed will be 2048M/16M = 128. So the truss report will show 128 calls to the OS routines munlock() and madvise(MADV_DONT_NEED). When the memory is freed (unmapped) it is now free for the O/S to reallocate.
The same process occurs during a resize operation that was initiated by MMAN, instead of the manual resize shown above.
If you consider that the memory eligible for PGA is approximately MEMORY_TARGET minus the SGA_TARGET, it becomes desirable to put a lower limit on SGA_TARGET. This prevents the PGA from taking too much memory from the SGA. The lower limit is set when the parameter file contains a value for SGA_TARGET. Without this setting, MMAN will continue to try to take any available memory from the SGA, as long as that memory is not currently in use (pinned).
On Linux the behavior of AMM can be demonstrated easily; when AMM is in use on Linux, the entire amount of MEMORY_TARGET creates /dev/shm segments when the instance starts up. This is visible if you run the following command:
$ ls -l /dev/shm | grep $ORACLE_SID
Note: these /dev/shm segments disappear if MEMORY_TARGET is not set, and MMAN reverts to using only the normal shared segments (visible by the ipcs -ma command).
For the SGA portion of MEMORY_TARGET, there is an equivalent amount of /dev/shm segments created. This appears as /dev/shm segments each the size of 1 granule. The PGA (or free memory) portion of memory_target appears as /dev/shm segments of 0 length.
For example, if MEMORY_MAX_TARGET=1G, the granule size is 4M, and there would be 256 /dev/shm segments created (256 * 4M = 1G). If there are no other memory parameters set, the initial SGA size will be 612M (60% of MEMORY_TARGET) and the initial PGA will be 412M (40% of MEMORY_TARGET). So there would be 153 /dev/shm segments with size of 4M (153 * 4M = 612M). The remaining /dev/shm segments will be zero length and represent free memory. These allocation can be seen if you query the V$SGAINFO fixed table. See the case studies that follow:
Example:
If the database name is testdb, the command would be:
$ ls -l /dev/shm | grep -i testdb
which results in:
-rw-r—– 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_0 <== these are shared memory segments used by SGA (each = 1 granule = 4M)
-rw-r—– 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_1
-rw-r—– 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_10
-rw-r—– 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_100
-rw-r—– 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_101
-rw-r—– 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_102
…
-rw-r—– 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_95 <== These segments with 0 length are free segments which can be (re)used by the O/S
-rw-r—– 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_96
-rw-r—– 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_97
-rw-r—– 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_98
-rw-r—– 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_99
If you dynamically resize the SGA (using an ALTER SYSTEM SET SGA_TARGET command), you will not see an immediate change in the number of /dev/shm segments, nor in V$SGAINFO. The changes will only occur when memory pressures dictate that the SGA or PGA needs to give up memory.
As the database instance matures and workloads differ, these values will all change. If SGA grows, the number of /dev/shm segments of size 1 granule will increase with a corresponding decrease in the number of /dev/shm segments of 0 length.
For other platforms, the O/S mechanism for moving shared memory between SGA and PGA is different, and will not be discussed here.
Perhaps the best way to show the memory management is by means of examples, and this document will show a few cases below.
CASE 1: Only MEMORY_TARGET is set
The following instance parameters are set:
- MEMORY_TARGET=1G
In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.
The following SQL statements highlight what is happening:
SQL> select * from v$sgainfo where name like ‘Maximum SGA%’ or name like ‘Free SGA%’;
NAME BYTES RES
——————————– ———- —
Maximum SGA Size 1068937216 No
Free SGA Memory Available 432013312
SQL> select component,current_size from v$memory_dynamic_components where component like ‘%Target%’;
COMPONENT CURRENT_SIZE
———————————– ————
SGA Target 641728512
PGA Target 432013312
Summary:
- Initially all memory (1GB) is assigned to shared memory. Here V$SGAINFO shows that ‘Maximum SGA size’ is 1GB. This is because SGA_MAX_SIZE is unset, and therefore defaults to MEMORY_TARGET.
- V$SGAINFO also shows that 412M is free memory. This free memory can be used either for the PGA, or for resizing the SGA.
- The initial ratio of memory allocation is 60% to SGA, 40% to PGA. This is the default if neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set. This means, of the 1GB of MEMORY_TARGET, SGA got 60% (612M) and free memory was 40% (412M).
- The V$MEMORY_DYNAMIC_COMPONENTS output shows SGA_TARGET was set to 612M and PGA_AGGREGATE_TARGET was set to 412M. The PGA_AGGREGATE_TARGET value of 412M does not mean there is already 412M of PGA, only that there is enough free memory for it to grow to 412M. If it needs to grow beyond 412M, MMAN will request memory from the SGA.
| Component | Value in the parameter file | MMAN assigned value |
|---|---|---|
| MEMORY_TARGET | 1024M | 1024M |
| SGA_MAX_SIZE | not set | 1024M |
| SGA_TARGET | not set | 612M (60% of MEMORY_TARGET) |
| PGA_AGGREGATE_TARGET | not set | 412M (40% of MEMORY_TARGET) |
CASE 2: MEMORY_TARGET, SGA_TARGET and PGA_AGGREGATE_TARGET are set
The following instance parameters are set:
- MEMORY_TARGET=1G
- SGA_TARGET=300M
- PGA_AGGREGATE_TARGET=100M
In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.
The following SQL statements highlight what is happening:
SQL> select * from v$sgainfo where name like ‘Maximum SGA%’ or name like ‘Free SGA%’;
NAME BYTES RES
——————————– ———- —
Maximum SGA Size 1068937216 No
Free SGA Memory Available 759169024
SQL> select component,current_size from v$memory_dynamic_components where component like ‘%Target%’;
COMPONENT CURRENT_SIZE
———————————– ————
SGA Target 314572800
PGA Target 759169024
Summary:
- Initially all memory (1GB) is assigned as shared memory. Here V$SGAINFO shows ‘Maximum SGA size’ is 1G. This is because SGA_MAX_SIZE is unset, so it defaults to MEMORY_TARGET.
- V$SGAINFO also shows that 724M is free memory. This amount is the difference between the MEMORY_TARGET and the SGA_TARGET. This free memory can be used either for the PGA, or for resizing the SGA.
- The V$MEMORY_DYNAMIC_COMPONENTS fixed table shows SGA_TARGET was set to 300M as requested, but PGA_AGGREGATE_TARGET was set to 724M, which is the remainder of the MEMORY_TARGET allocation. The PGA_AGGREGATE_TARGET value of 724M does not mean there is already 724M of PGA, only that there is enough free memory to grow to 724M.
- Because SGA_MAX_SIZE was defaulted to 1G, in theory, this is also the maximum size of the SGA (if there was no PGA).
| Component | value is parameter file | MMAN assigned value |
|---|---|---|
| MEMORY_TARGET | 1024M | 1024M |
| SGA_MAX_SIZE | not set | 1024M |
| SGA_TARGET | 300M | 300M |
| PGA_AGGREGATE_TARGET | 100M | 724M |
CASE 3: MEMORY_TARGET, SGA_MAX_SIZE, SGA_TARGET and PGA_AGGREGATE_TARGET are set
The following instance parameters are set:
- MEMORY_TARGET=1G
- SGA_MAX_SIZE=500M
- SGA_TARGET=300M
- PGA_AGGREGATE_TARGET=100M
In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.
The following SQL statements highlight what is happening:
SQL> select * from v$sgainfo where name like ‘Maximum SGA%’ or name like ‘Free SGA%’;
NAME BYTES RES
——————————– ———- —
Maximum SGA Size 521936896 No
Free SGA Memory Available 209715200
SQL> select component,current_size from v$memory_dynamic_components where component like ‘%Target%’;
COMPONENT CURRENT_SIZE
———————————– ————
SGA Target 314572800
PGA Target 759169024
Summary:
- Now that SGA_MAX_SIZE is set to 500M, the memory assigned as shared memory, shows up in V$SGAINFO as 500M under ‘Maximum SGA Size’.
- The ‘Free SGA Memory Available’ in V$SGAINFO is the difference between the SGA_MAX_SIZE and SGA_TARGET (500M-300M = 200M).
- The memory above SGA_MAX_SIZE (500M) is unavailable to the SGA so never shows up in V$SGAINFO.
- The V$MEMORY_DYNAMIC_COMPONENTS fixed table shows the SGA_TARGET is 300M as set in the parameter file, but the PGA_AGGREGATE_TARGET is 724M, which is the difference between MEMORY_TARGET and SGA_TARGET.
So the PGA has access to the memory above SGA_MAX_SIZE (500M) plus any free memory in the SGA (200M).
| Component | Value in the parameter file | MMAN assigned value |
|---|---|---|
| MEMORY_TARGET | 1024M | 1024M |
| SGA_MAX_SIZE | 500M | 500M |
| SGA_TARGET | 300M | 300M |
| PGA_AGGREGATE_TARGET | 100M | 724M |
Summary of case studies
The above examples show that:
- In AMM, shared memory is available for use by both the SGA and PGA.
- MMAN will lock, unlock, and transfer memory freely between the SGA and the PGA as necessary.
- The exceptions are:
- When SGA_MAX_SIZE is set in the parameter file, MMAN will not transfer memory into the SGA that would cause it to exceed this size.
- When SGA_TARGET is set in the parameter file, MMAN will not transfer any memory out of the sga that will cause it to go below this size.
Note: if MEMORY_MAX_TARGET would have been manually set to a value higher than MEMORY_TARGET the following situation would occur:
- The ‘Maximum SGA Size’ value in V$SGAINFO would show the MEMORY_MAX_TARGET value.
- The ‘Free SGA Memory Available’ in V$SGAINFO is the difference between the SGA_MAX_SIZE (which defaults to MEMORY_MAX_TARGET)and SGA_TARGET.
- All other parameters would act the same as described in the cases, as calculations for the initial memory sizes are based on MEMORY_TARGET and not MEMORY_MAX_TARGET.
ORA-4030 and ORA-4031
How can the database run out of memory, if it can freely move memory around between SGA and PGA?
Recall that ORA-4030 occurs when there is no more memory for the PGA, and ORA-4031 occurs when there is no more memory for the SGA.
If the SGA allocation is totally consumed, and the PGA (free memory) is totally consumed, then you have run out of memory and either ORA-4030 or ORA-4031 is imminent. This means the MEMORY_TARGET is undersized.
Some things that can initiate memory shortages are limits set on the SGA:
- If SGA_MAX_SIZE is set, this is an upper limit. If the processing load requires more memory than this, you can get ORA-4031.
- If SGA_TARGET is set, this is a minimum size for the SGA. If the processing load requires a lot of private memory (PGA), and that memory requirement exceeds MEMORY_TARGET – SGA_TARGET, you can get a ORA-4030. But if the process needing the memory is PL/SQL, it can take additional free memory from the server. However, it is still a good idea to set SGA_TARGET so there is always some memory available for the SGA.
For ORA-4030, there are also O/S limits that come into play. Notably, the 4G limits on 32-bit platforms, and the ulimit soft settings for UNIX type systems which limit the maximum memory a process can access.
“ipcs -m” Displays Incorrect Shared Memory Segment Sizes in Oracle 11G (文档 ID 731658.1)
In this Document
| Symptoms |
| Cause |
| Solution |
APPLIES TO:
Oracle Database – Enterprise Edition – Version 11.1.0.6 and later
Generic Linux
SYMPTOMS
The “ipcs -m” indicates allocated shared memory segments. In Oracle 11G all of the segments are between 808 and 4096 bytes, where as the instance has a 4GB SGA .Output of ipcs -m:
—— Shared Memory Segments ——–
key shmid owner perms bytes nattch status
0x00000000 65536 oracle 660 4096 0
0xd649823c 98305 oracle 660 4096 0
0x7905c133 131074 oracle 666 808 0
0xc90e3f3c 196611 oracle 660 4096 0
CAUSE
When Oracle on Linux is configured to use Auto Memory Management (AMM), it utilizes the POSIX implementation of SHM on Linux. The small shared memory segment just contains some metadata for new processes to find the /dev/shm/* files used for the SGA. POSIX shared memory is not reflected by the ipcs utility.
When AMM is disabled, Oracle falls back to using the System V SHM implementation as seen in earlier versions of Oracle for Linux.
SOLUTION
1. Use PMAP command. PMAP output shows that Oracle 11g uses /dev/shm for shared memory implementation instead. There are many 4MB files mapped to Oracle server processes address space. The total size shows the MEMORY_TARGET size. If MEMORY_TARGET is set to 0 then the shared memory segments will be displayed by the ipcs command.
E.g.:
$pmap 6124
.. .
20001000 4092K rw-s- /dev/shm/ora_ora11g_19693577_0
20400000 4096K rw-s- /dev/shm/ora_ora11g_19693577_1
20800000 4096K rw-s- /dev/shm/ora_ora11g_19693577_2
20c00000 4096K rw-s- /dev/shm/ora_ora11g_19693577_3
21000000 4096K rw-s- /dev/shm/ora_ora11g_19693577_4
…
2. /dev/shm also shows the shared memory segments in use for this instance.
$ ls -l /dev/shm/ |grep ora11g
-rw-r—– 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_0
-rw-r—– 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_1
-rw-r—– 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_177
-rw-r—– 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_178
-rw-r—– 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_179
转载：http://blog.itpub.net/26736162/viewspace-2138627/
发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/133718.html原文链接：https://javaforall.cn
【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛
【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...
