oracle 11G 之 AMM

  今天是2013-06-28，今天把昨天看的文章内容进行一下实验验证。
一）AMM相关主要参数：
AMM为automatic  memory management ，就是我们可以设置一个内存大小给予oracle，然后让oracle自动管理自己的pga和sga的size，如果数据库使用的是dbca创建的方式，那么默认Amm就启动了，后台进程MMAn就是amm的进程， 但是如果我设置了lock_sga，那么就无法启动自动内存管理了。其中包括两部分一个是sga另一个是pga，需要设置的参数有两个，
1）memory_max_target：该参数是设置系统可以物理上分配给oracle的内存大小，是一个静态参数。
eg：
SYS@orcl#
SYS@orcl#alter system set memory_max_target=712M;
alter system set memory_max_target=712M
                 *
第 1 行出现错误:
ORA-02095: 无法修改指定的初始化参数

SYS@orcl#alter system set memory_max_target=772M scope=spfile;

系统已更改。

SYS@orcl#

2）memory_target：参数是实例可以使用的memory大小，是一个动态参数，该值不能超过memory_max_target
eg：
SYS@orcl#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            772M
memory_target                        big integer            768M
pga_aggregate_target                 big integer            256M
sga_target                           big integer            512M
SYS@orcl#alter system set memory_target=773M;
alter system set memory_target=773M
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-00837: 指定的值 MEMORY_TARGET 大于 MEMORY_MAX_TARGET

SYS@orcl#alter system set memory_target=769M;

系统已更改。

SYS@orcl#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            772M
memory_target                        big integer            769M
pga_aggregate_target                 big integer            256M
sga_target                           big integer            512M
SYS@orcl#

note：如果在参数文件中没有指定memory_target参数，而是指定了memory_max_target参数，那么memory_target参数默认设置为0，如果没有指定memory_max_target参数，而是指定了memory_target参数，那么memory_max_target参数默认等于memory_target参数：

eg：

oracle@oracle:~> sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on 星期五 6月 28 20:36:23 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@orcl#set linesize 200
SYS@orcl#show parameter memory

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
hi_shared_memory_address             integer                0
memory_max_target                    big integer            772M
memory_target                        big integer            769M
shared_memory_address                integer                0
SYS@orcl#alter system reset memory_target;

系统已更改。

SYS@orcl#show parameter memroy
SYS@orcl#show parameter memory

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
hi_shared_memory_address             integer                0
memory_max_target                    big integer            772M
memory_target                        big integer            769M
shared_memory_address                integer                0
SYS@orcl#startup force
ORACLE 例程已经启动。

Total System Global Area  805875712 bytes
Fixed Size                  2148720 bytes
Variable Size             444597904 bytes
Database Buffers          352321536 bytes
Redo Buffers                6807552 bytes
数据库装载完毕。
数据库已经打开。
SYS@orcl#show parameter memory_

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
hi_shared_memory_address             integer                0
memory_max_target                    big integer            772M
memory_target                        big integer            0
shared_memory_address                integer                0
SYS@orcl#alter system reset memory_max_target; 

系统已更改。

SYS@orcl#alter system set memory_target=769M;

系统已更改。

SYS@orcl#startup force
ORACLE 例程已经启动。

Total System Global Area  805875712 bytes
Fixed Size                  2148720 bytes
Variable Size             444597904 bytes
Database Buffers          352321536 bytes
Redo Buffers                6807552 bytes
数据库装载完毕。
数据库已经打开。
SYS@orcl#show parameter memory

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
hi_shared_memory_address             integer                0
memory_max_target                    big integer            772M
memory_target                        big integer            772M
shared_memory_address                integer                0

在想一下，如果我既不设置memory_target参数又不设置memory_max_target参数是不是oracle就无法启动了呢？答案是no，下面进行分析：

SYS@orcl#r
  1* select open_mode from v$database

OPEN_MODE
--------------------
READ WRITE

SYS@orcl#show parameter memory

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
hi_shared_memory_address             integer                0
memory_max_target                    big integer            0
memory_target                        big integer            0
shared_memory_address                integer                0
SYS@orcl#

如果我们设置了sga为0会怎么样呢？

SYS@orcl#r
  1* select open_mode from v$database

OPEN_MODE
--------------------
READ WRITE

SYS@orcl#show parameter memory

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
hi_shared_memory_address             integer                0
memory_max_target                    big integer            0
memory_target                        big integer            0
shared_memory_address                integer                0
SYS@orcl#show parameter sga

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga                             boolean                FALSE
pre_page_sga                         boolean                FALSE
sga_max_size                         big integer            516M
sga_target                           big integer            512M
SYS@orcl#alter system reset sga_target;

系统已更改。

SYS@orcl#alter system reset sga_max_size;
alter system reset sga_max_size
*
第 1 行出现错误:
ORA-32010: 无法在 SPFILE 中找到要删除的条目

SYS@orcl#shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SYS@orcl#startup
ORACLE 例程已经启动。

Total System Global Area  217157632 bytes
Fixed Size                  2142976 bytes
Variable Size             159386880 bytes
Database Buffers           50331648 bytes
Redo Buffers                5296128 bytes
数据库装载完毕。
数据库已经打开。
SYS@orcl#show parameter sga

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
lock_sga                             boolean
FALSE
pre_page_sga                         boolean
FALSE
sga_max_size                         big integer
208M
sga_target                           big integer
0
SYS@orcl#set linesize 200
SYS@orcl#show parameter sga

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga                             boolean                FALSE
pre_page_sga                         boolean                FALSE
sga_max_size                         big integer            208M
sga_target                           big integer            0
SYS@orcl#

二）启动AMM

如果在创建数据库的时候没有启动AMM那么可以采用如下设置进行启动：

1）确认oracle的memory_target 参数大小,并设置大小：
memory_target=sga_target+max(pga_aggregate_target,maximum PGA allocated)
pga_aggregate_target：参数确认：
SYS@orcl#select name,value from v$pgastat where name='maximum PGA allocated';

NAME                                                                                                                                  VALUE
-------------------------------------------------------------------------------------------------------------------------------- ----------
maximum PGA allocated                                                                                                             182932480

SYS@orcl#

2）确认memory_max_target，并设置大小:

For the MEMORY_MAX_TARGET initialization parameter, decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the SGA and instance PGA sizes. This number can be larger than or the same as the MEMORY_TARGET value that you chose in the previous step.

3)设置sga和pga参数：

SYS@orcl#alter system set sga_target=0 scope=spfile;

系统已更改。

SYS@orcl#alter system set pga_aggregate_target=0 scope=spfile;

系统已更改。

SYS@orcl#startup force;
ORACLE 例程已经启动。

Total System Global Area  534499328 bytes
Fixed Size                  2146024 bytes
Variable Size             184549656 bytes
Database Buffers          343932928 bytes
Redo Buffers                3870720 bytes
数据库装载完毕。
数据库已经打开。
SYS@orcl#show parameter pga

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
pga_aggregate_target                 big integer            0
SYS@orcl#show parameter pga

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
pga_aggregate_target                 big integer            0
SYS@orcl#show parameter sga

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga                             boolean                FALSE
pre_page_sga                         boolean                FALSE
sga_max_size                         big integer            512M
sga_target                           big integer            0
SYS@orcl#

至此：amm就设置完成了。
3）

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter.
v$memroy_dynamic_components显示了oracle内存已经分配的动态参数相关值。
eg：
SQL> select * from v$memory_dynamic_components;

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- ---------- -------------- -------------- -------------- ------------
shared pool                                                         176160768  159383552  176160768                   0          4 GROW           DEFERRED       2013/6/28 22:1      4194304
large pool                                                            4194304    4194304    4194304                   0          0 STATIC                                            4194304
java pool                                                            12582912    8388608   12582912                   0          2 GROW           IMMEDIATE      2013/6/28 22:0      4194304
streams pool                                                                0          0          0                   0          0 STATIC                                            4194304
SGA Target                                                          536870912  536870912  536870912                   0          0 STATIC                                            4194304
DEFAULT buffer cache                                                335544320  335544320  356515840                   0          6 SHRINK         DEFERRED       2013/6/28 22:1      4194304
KEEP buffer cache                                                           0          0          0                   0          0 STATIC                                            4194304
RECYCLE buffer cache                                                        0          0          0                   0          0 STATIC                                            4194304
DEFAULT 2K buffer cache                                                     0          0          0                   0          0 STATIC                                            4194304
DEFAULT 4K buffer cache                                                     0          0          0                   0          0 STATIC                                            4194304
DEFAULT 8K buffer cache                                                     0          0          0                   0          0 STATIC                                            4194304
DEFAULT 16K buffer cache                                                    0          0          0                   0          0 STATIC                                            4194304
DEFAULT 32K buffer cache                                                    0          0          0                   0          0 STATIC                                            4194304
Shared IO Pool                                                              0          0          0                   0          0 STATIC                                            4194304
PGA Target                                                          268435456  268435456  268435456                   0          0 STATIC                                            4194304
ASM Buffer Cache                                                            0          0          0                   0          0 STATIC                                            4194304

16 rows selected

SQL>
该v$memory_target_advice视图显示了oracle的建议参数。如下

SQL> select  * from v$memory_target_advice;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
        768                  1         1055                   1          0
        384                0.5         1086              1.0294          0
        576               0.75         1057              1.0021          0
        960               1.25         1055                   1          0
       1152                1.5         1013              0.9598          0
       1344               1.75         1013              0.9598          0
       1536                  2         1013              0.9598          0

7 rows selected

SQL>

##########################################↖(^ω^)↗####小海##############