Oracle自动内存管理AMM

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

更多Oracle相关信息见Oracle 专题页面 https://www.558idc.com/topicnews.aspx?tid=12