ITPub博客

首页 > 数据库 > Oracle > 双下划线开头的内存参数对Oracle AMM行为的影响

双下划线开头的内存参数对Oracle AMM行为的影响

原创 Oracle 作者:oliseh 时间:2014-12-12 15:37:56 0 删除 编辑

预备知识:__开头的参数表明最近一次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=5167382528SGA 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=8589934592SGA Target=4294967296sga 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

 

场景3 

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

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

*.memory_target=12G

*.pga_aggregate_target=5G

*.sga_target=4G

 

---查看结果pga_aggregate_target=7516192768SGA Target=5368709120sga 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

 

 

场景4 

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

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

*.memory_target=12G

*.pga_aggregate_target=5G

*.sga_target=4G

 

---查看结果pga_aggregate_target=8589934592SGA Target=4294967296sga 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

 

 

场景5 

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的值时,才能生效


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1365749/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1617045