【Oracle】Oracle自动内存管理AMM

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

至此，数据库切换至AMM内存管理。

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

巴特西