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

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

相關文章