AMM(Automatic Memory Management)自动内存管理，分配一整块内存区域，Oracle数据库自动分配管理SGA和PGA的内存。具体通过设置两个参数MEMORY_MAX_TARGET和MEMORY_TARGET达到需求效果。
1.查看当前SGA_TARGET和PGA_AGGREGATE_TARGET参数。
SYS@oradb> 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 256 pga_aggregate_target big integer 2951M sga_target big integer 4G
2.修改相关参数，把sga和pga参数改为0
alter system set memory_max_target=16384m scope=spfile; alter system set memory_target=16384m scope=spfile; alter system set sga_target=0 scope=spfile; alter system set sga_max_size=0 scope=spfile; alter system set pga_aggregate_target=0 scope=spfile;
3.重启数据库实例:
SQL>shutdown immediate; SQL>startup; SYS@oradb> 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 16G memory_target big integer 16G parallel_servers_target integer 256 pga_aggregate_target big integer 0 sga_target big integer 0
4.监控和优化AMM的内存
动态性能视图V$MEMORY_DYNAMIC_COMPONENTS显示所有动态内存组件的当前大小。
动态性能视图V$MEMORY_RESIZE_OPS会有一个circular history buffer，查看最近16G内存的请求情况。
动态性能视图V$MEMORY_TARGET_ADVICE,会给与一个内存优化建议。
SYS@oradb> SELECT * FROM v$memory_target_advice ORDER BY memory_size; MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION ----------- ------------------ ------------ ------------------- ---------- 4096 .25 487 1 0 8192 .5 487 1 0 10240 .625 487 1 0 12288 .75 487 1 0 14336 .875 487 1 0 16384 1 487 1 0 18432 1.125 487 1 0 20480 1.25 487 1 0 22528 1.375 487 1 0 24576 1.5 487 1 0 26624 1.625 487 1 0 28672 1.75 487 1 0 30720 1.875 487 1 0 32768 2 487 1 0
备注:Automatic Shared Memory Management (ASMM) was introduced in 10g. You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value.
关于SGA_TARGET 动态内存建议视图如下:
SYS@oradb> select * from v$sga_target_advice; SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ---------- --------------- ------------ ------------------- ------------------- 2457 .25 488 1 42221 3685 .3749 488 1 42221 4914 .5 488 1 42221 6142 .6249 488 1 42221 7371 .75 488 1 42221 8599 .8749 488 1 42221 9828 1 488 1 42221 11056 1.1249 488 1 42221 12285 1.25 488 1 42221 13513 1.3749 488 1 42221 14742 1.5 488 1 42221 15970 1.6249 488 1 42221 17199 1.75 488 1 42221 18427 1.8749 488 1 42221 19656 2 488 1 42221