预备知识：__开头的参数表明最近一次Instance关闭前相关内存组件的内存使用量，以提供给DBA下一次起库时是否需要调整内存参数的建议
目的：验证__开头的内存参数对memory_target、sga_target、pga_aggregate_target设置值的影响，实验环境为oracle 11g restart

场景1：

memory_target=12G

pga_aggregate_target=0

sga_target=0

---查看结果pga_aggregate_target=5167382528、SGA Target=7717519360，满足60%、40%的规律

SQL> select * from v$memory_resize_ops where component in ('SGA Target','PGA Target') order by start_time;

COMPONENT            OPER_TYPE                  OPER_MODE          PARAMETER            INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS

-------------------- -------------------------- ------------------ -------------------- ------------ ----------- ---------- ------------------

START_TIME        END_TIME

----------------- -----------------

PGA Target           STATIC                                        pga_aggregate_target            0  5167382528 5167382528 COMPLETE

20140409 14:16:38 20140409 14:16:38

SGA Target           STATIC                                        sga_target                      0  7717519360 7717519360 COMPLETE

20140409 14:16:38 20140409 14:16:38

场景2：

memory_target=12G

pga_aggregate_target=5G

sga_target=4G

---查看结果pga_aggregate_target=8589934592、SGA Target=4294967296，sga target的值等于参数设置值，pga_aggregate_target的值等于memory_target-sga_target的值

SQL> select * from v$memory_resize_ops where component in ('SGA Target','PGA Target') order by start_time;

COMPONENT            OPER_TYPE                  OPER_MODE          PARAMETER            INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS

-------------------- -------------------------- ------------------ -------------------- ------------ ----------- ---------- ------------------

START_TIME        END_TIME

----------------- -----------------

PGA Target           STATIC                                        pga_aggregate_target            0  8589934592 8589934592 COMPLETE

20140409 14:46:22 20140409 14:46:22

SGA Target           STATIC                                        sga_target                      0  4294967296 4294967296 COMPLETE

---查询pga实际分配的内存量为72769536，按照metalink的说法8589934592这个值代表pga_aggregate_target当前可以用到的最大值，而不是目前已经使用的量

select name,value,unit from v$pgastat where name in ('aggregate PGA target parameter','total PGA inuse','total PGA allocated','maximum PGA allocated')

  2*

NAME                                                    VALUE UNIT

-------------------------------------------------- ---------- ------------------------

aggregate PGA target parameter                     8589934592 bytes

total PGA inuse                                      68191232 bytes

total PGA allocated                                  72769536 bytes

maximum PGA allocated                                74044416 bytes

 ：

zwcsdb.__ sga_target=5G   ---注意__参数一定要置顶，否则无效

zwcsdb.__pga_aggregate_target=6G  ---注意__参数一定要置顶，否则无效

*.memory_target=12G

*.pga_aggregate_target=5G

*.sga_target=4G

---查看结果pga_aggregate_target=7516192768、SGA Target=5368709120，sga target的值等于zwcsdb.__ sga_target的设置值，pga_aggregate_target值依然等于memory_target-sga_target的值

SQL> select * from v$memory_resize_ops where component in ('SGA Target','PGA Target') order by start_time;

COMPONENT            OPER_TYPE                  OPER_MODE          PARAMETER            INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS

-------------------- -------------------------- ------------------ -------------------- ------------ ----------- ---------- ------------------

START_TIME        END_TIME

----------------- -----------------

PGA Target           STATIC                                        pga_aggregate_target            0  7516192768 7516192768 COMPLETE

20140409 15:19:07 20140409 15:19:07

SGA Target           STATIC                                        sga_target                      0  5368709120 5368709120 COMPLETE

20140409 15:19:07 20140409 15:19:07

 ：

zwcsdb.__ sga_target=3G   ---注意__参数一定要置顶，否则无效

zwcsdb.__pga_aggregate_target=6G  ---注意__参数一定要置顶，否则无效

*.memory_target=12G

*.pga_aggregate_target=5G

*.sga_target=4G

---查看结果pga_aggregate_target=8589934592、SGA Target=4294967296，sga target的值还是等于*.sga_target参数，并不等于zwcsdb.__ sga_target值，即__开头的参数未生效

SQL> select * from v$memory_resize_ops where component in ('SGA Target','PGA Target') order by start_time;

Session altered.

SQL>

COMPONENT            OPER_TYPE                  OPER_MODE          PARAMETER            INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS

-------------------- -------------------------- ------------------ -------------------- ------------ ----------- ---------- ------------------

START_TIME        END_TIME

----------------- -----------------

PGA Target           STATIC                                        pga_aggregate_target            0  8589934592 8589934592 COMPLETE

20140409 15:24:38 20140409 15:24:38

SGA Target           STATIC                                        sga_target                      0  4294967296 4294967296 COMPLETE

20140409 15:24:38 20140409 15:24:38

 ：

zwcsdb.__ sga_target=5G   ---注意__参数一定要置顶，否则无效

zwcsdb.__pga_aggregate_target=3G  ---注意__参数一定要置顶，否则无效

*.memory_target=12G

*.pga_aggregate_target=5G

*.sga_target=4G

SQL> select * from v$memory_resize_ops where component in ('SGA Target','PGA Target') order by start_time;

COMPONENT            OPER_TYPE                  OPER_MODE          PARAMETER            INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS

-------------------- -------------------------- ------------------ -------------------- ------------ ----------- ---------- ------------------

START_TIME        END_TIME

----------------- -----------------

PGA Target           STATIC                                        pga_aggregate_target            0  8589934592 8589934592 COMPLETE

20140409 15:31:34 20140409 15:31:34

SGA Target           STATIC                                        sga_target                      0  4294967296 4294967296 COMPLETE

20140409 15:31:34 20140409 15:31:34

结论：

对于__开头的内存参数必须置顶才能生效，且__sga_target、__pga_aggregate_target这两个参数的设置值都必须大于sga_target、pga_aggregate_target的值时，才能生效