雙下劃線開頭的記憶體引數對Oracle AMM行為的影響

預備知識：__開頭的參數列明最近一次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的值時，才能生效