pga_aggregate_target的設定 (zt)

PGA_AGGREGATE_TARGET

WORKAREA_SIZE_POLICY

V$PGASTAT

_pga_max_size

[@more@]

伴隨自動PGA調整新特性的引入,Oracle隨之引入了一系列新的檢視,V$PGASTAT就是其中的一個.在V$PGASTAT中有這樣一個條目: global memory bound ,該條目記錄資料庫允許的最高PGA記憶體使用量,我們可以從不同的PGA引數設定來觀察一下Oracle執行的PGA上限.


SQL> alter system set pga_aggregate_target=&Nm;
Enter value for nm: 10m
old 1: alter system set pga_aggregate_target=&Nm
new 1: alter system set pga_aggregate_target=10m

System altered.

Elapsed: 00:00:00.05
SQL> SET autotrace traceonly
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000;

20000 rows selected.

Elapsed: 00:03:04.12

…….
SQL> SET autotrace off
SQL> SELECT sql_text, operation_type, POLICY, last_memory_used / 1024 / 1024,
2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a
4 WHERE l.hash_value = a.hash_value
5 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';

SQL_TEXT OPERATION_TYPE POLIC
-------------------------------------------------- ------------------ -----
LAST_MEMORY_USED/1024/1024 LAST_EXE LAST_TEMPSEG_SIZE
-------------------------- -------- -----------------
SELECT DISTINCT * FROM t WHERE ROWNUM < 500000 GROUP BY (SORT) AUTO
.548828125 206 PASSES 62914560

Elapsed: 00:00:00.02
SQL>
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');

NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 10
global memory bound .5

SQL> alter system set pga_aggregate_target=&Nm;
Enter value for nm: 30M
old 1: alter system set pga_aggregate_target=&Nm
new 1: alter system set pga_aggregate_target=30M

System altered.

Elapsed: 00:00:00.05
SQL> SET autotrace traceonly
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000;

20000 rows selected.

Elapsed: 00:00:53.30
………..
SQL> SET autotrace off
SQL> SELECT sql_text, operation_type, POLICY, last_memory_used / 1024 / 1024,
2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a
4 WHERE l.hash_value = a.hash_value
5 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';

SQL_TEXT OPERATION_TYPE POLIC LAST_MEMORY_USED/1024/1024
-------------------------------------------------- ------------------ ----- --------------------------
LAST_EXECUTION LAST_TEMPSEG_SIZE
-------------------- -----------------
SELECT DISTINCT * FROM t WHERE ROWNUM < 500000 GROUP BY (SORT) AUTO 1.48046875
6 PASSES 57671680


Elapsed: 00:00:00.02
SQL>
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');

NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 30
global memory bound 1.5

Elapsed: 00:00:00.00
我們可以注意到,PGA的global memory bound會一直處在5%的PGA_AGGREGATE_TARGET引數設定,直到5% PGA_AGGREGATE_TARGET超過100M,然後global memory bound被限制為100M,也就是滿足我們提到的:
對於序列操作，單個SQL操作能夠使用的PGA記憶體按照以下原則分配：
MIN（5% PGA_AGGREGATE_TARGET，100MB）
注意,修改PGA_AGGREGATE_TARGET引數可以使用如下命令:
alter system set pga_aggregate_target=4096M ;
修改引數後,通常需要之行操作才能看到檢視資訊的變化:

SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');

NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 10
global memory bound .5

SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');

NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 20
global memory bound 1

SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');

NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 40
global memory bound 2

SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');

NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 1024
global memory bound 51.1992188

SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');

NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 4096
global memory bound 100

實際上這個100M的上限是受到了另外一個隱含引數的控制,該引數為_pga_max_size,該引數的預設值為200M,單程式序列操作PGA的上限不能超過該引數的1/2.
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%&par%'
7 /
Enter value for par: pga_max
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%pga_max%'

NAME VALUE DESCRIB
--------------------------------------------- ---------------- ---
_pga_max_size 209715200 Maximum size of the PGA memory for one process
如果我們修改該引數, global memory bound將可以突破100M的上限:

SQL> alter system set "_pga_max_size"=400M;

System altered.
……………………
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');

NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 4096
global memory bound 200
對於PGA的控制,還有一系列的內部引數,列舉如下,僅供參考:

SQL> l
1 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6* AND x.ksppinm LIKE '%&par%'
SQL> /
Enter value for par: smm
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%smm%'

NAME VALUE DESCRIB
------------------------ ----- ----------------------------------------------------------------
_smm_auto_min_io_size 56 Minimum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_max_io_size 248 Maximum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_cost_enabled TRUE if TRUE, use the AUTO size policy cost functions
_smm_control 0 provides controls on the memory manager
_smm_trace 0 Turn on/off tracing for SQL memory manager
_smm_min_size 128 minimum work area size in auto mode
_smm_max_size 2560 maximum work area size in auto mode (serial)
_smm_px_max_size 15360 maximum work area size in auto mode (global)
_smm_bound 0 overwrites memory manager automatically computed bound
_smm_advice_log_size 0 overwrites default size of the PGA advice workarea history log
_smm_advice_enabled TRUE if TRUE, enable v$pga_advice

11 rows selected.

在Oracle9i之前，PGA的計算和控制都是比較複雜的事情，從Oracle9i開始，Oracle提供了一種SQL記憶體管理的新方法：自動化SQL執行記憶體管理（Automated SQL Execution Memory Management），使用這個新特性，Oracle可以自動調整S Q L記憶體區，而不用關閉資料庫，這一改進大大簡化了DBA的工作，同時也提高了Oracle資料庫的效能。

為實現自動的PGA管理，Oracle引入了幾個新的初始化引數：

1．PGA_AGGREGATE_TARGET- 此引數用來指定所有session總計可以使用最大PGA記憶體。這個引數可以被動態的更改,取值範圍從10M -- （4096G-1 ）bytes。
2。WORKAREA_SIZE_POLICY- 此引數用於開關PGA記憶體自動管理功能，該引數有兩個選項：AUTO 和 MANUAL，當設定為AUTO時，資料庫使用Oracle9i提供的自動PGA管理功能，當設定為MANUAL時，則仍然使用Oracle9i前手工管理的方式。預設的，Oracle9i中WORKAREA_SIZE_POLICY被設定為AUTO。

需要注意的是，在Oracle9i中，PGA_AGGREGATE_TARGET引數僅對專用伺服器模式下（Dedicated Server）的專屬連線有效，但是對共享伺服器（Shared Server）連線無效；從Oracle10g開始PGA_AGGREGATE_TARGET對專用伺服器連線和共享伺服器連線同時生效。

PGA_AGGREGATE_TARGET 引數同時限制全域性PGA分配和私有工作區記憶體分配：

1．對於序列操作，單個SQL操作能夠使用的PGA記憶體按照以下原則分配：
MIN（5% PGA_AGGREGATE_TARGET，100MB）
2．對於並行操作
30% PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 並行度）

要理解PGA的自動調整，還需要區分可調整記憶體（TUNABLE MEMORY SIZE）與不可調整記憶體（UNTUNABLE MEMORY SIZE）。可調整記憶體是由SQL工作區使用的，其餘部分是不可調整記憶體。啟用了自動PGA調整之後， Oracle仍然需要遵循以下原則：

UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE <= PGA_AGGREGATE_TARGET

資料庫系統只能控制可調整部分的記憶體分配，如果可調整的部分過小，則Oracle永遠也不會強制啟用這個等式。

另外，PGA_AGGREGATE_TARGET引數在CBO最佳化器模式下，對於SQL的執行計劃會產生影響。Oracle在評估執行計劃時會根據PGA_AGGREGATE_TARGET引數評估在Sort，HASH-JOIN或Bitmap操作時能夠使用的最大或最小記憶體，從而選擇最優的執行計劃。

對於PGA_AGGREGATE_TARGET引數的設定，Oracle提供這樣一個建議方案
1．對於OLTP系統
PGA_AGGREGATE_TARGET = ( * 80%) * 20%
2．對於DSS系統
PGA_AGGREGATE_TARGET = ( * 80%) * 50%

也就是說，對於一個單純的資料庫伺服器，通常我們需要保留20%的實體記憶體給作業系統使用，剩餘80%可以分配給Oracle使用。Oracle使用的記憶體分為兩部分SGA和PGA，那麼PGA可以佔用Oracle消耗總記憶體的20%（OLTP系統）至50%（DSS系統）。這只是一個建議設定，更進一步的我們應該根據資料庫的具體效能指標來調整和最佳化PGA的使用。

supers123：

當workarea_size_policy 設定成auto ,pga_aggregate_target 這個變數設定的值有實際意義麼??這個引數的預設值25165824 bytes 是不是太少了, 但是這樣的設定很多系統跑起來也沒問題,是不是系統使用的記憶體可以超過這個值.有沒有上限呢,比如超過多少後就不能再擴充套件了?
還有關於單個使用者連線佔用的記憶體數量的計算方法是不是可以從v$sesstat 何v$process 表中大致的估算出.如何計算向對準確呢?如果某個查尋遇到非常大的排序操作,這個查詢在使用到多少pga_aggregate_target 指定的記憶體後進行磁碟排序呢 ？

eygle：

pga_aggregate_target 設定的是個期望值，如果pga_aggregate_target 的設定，不足以供不可調整部分使用，就會擴充套件，如果 workarea_size_policy 設定成auto 那麼你應該相應設定 PGA_AGGREGATE_TARGET 。
PGA記憶體主要分為兩部分
Untunable Memory Size + Tunable Memory Size你應該設定
Untunable Memory Size + Tunable Memory Size <=PGA_AGGREGATE_TARGET 如果Untunable Memory Size > PGA_AGGREGATE_TARGET 那麼Oracle會超出PGA_AGGREGATE_TARGET 的設定至於超出部分怎麼分配記憶體，我沒有仔細研究過不過估計跟workarea executions - optimal有關，應該是optimal size的一個函式，實際上對於單個程式
Untunable Memory Size > PGA_AGGREGATE_TARGET 這種狀況是不可能出現的。

至於程式PGA你可以查詢:V$PROCESS 我想以下欄位是你關心的
PGA_USED_MEM, PGA_ALLOC_MEM AND PGA_MAX_MEM

Lyra：

pga_aggregate_target此引數是指你的所有連線可使用的記憶體總量;每個連線使用的記憶體包含:此連線的SQL語句中的繫結變數(類似於C程式中的區域性變數,所以在Oracle中也稱此部分為stack),還有語句的一些控制程式碼資訊;以及此語句排序操作所用到的記憶體區。

其中消耗記憶體最大的就是排序區;其中排序區在使用時申請,使用完畢即釋放.因此你所需要的排序區可如下估算:單個連線需要4M*同時執行的最大連線數。而pga_aggregate_target的大小隻要略大於此估算即可.

yumail312：

那麼排序區就等於是eygle指的tunable memory size,其他部分如bind variable和handle都屬於untunable memory size了?不知理解的對否!
BTW:9i上為何設定了pga_aggregate_target後,sort_area_size就不起作用了呢?那麼是否可以只設定二者之一?另外:我的pga_aggregate_target=0,怎麼還是要為連線分配pga值呢?

Lyra：

關於以tunable memory size和untunable memory size的劃分PGA,我沒有研究過.當pga_aggregate_target的值非0時,表示你指定了預期PGA的最大值,這樣sort_area_size就不起作用了,這是Oracle9i才有的特性,其目的就是不讓使用者去管理更為底層的PGA分配,而是讓使用者指定個整體宏觀值,而讓Oracle自己去管理底層的分配,這樣管理起來就方便,而且效能也會提高,畢竟系統會比使用者更瞭解自己對記憶體的需求。這可能是Oracle的一個發展趨勢;另外為了保持向後相容,也保留了sort_area_size引數,這隻有在pga_aggregate_target=0的情況下才有意義。

supers123

lyra老兄說的4M是怎麼算出來的,我怎麼檢視v$process處理使用者的單個process 只有400k左右呢,還要加上哪些記憶體使用呢？

Lyra：
答:這是比較合理的上限值;當然如果你的記憶體較小,這個值也可估計的小些。你所看到的400k僅是些程式所消耗的要記憶體;象排序區等是不包含在內的。

Xzh2000：

在9i以後的版本,沒有必要將PGA搞的那麼清楚，只需要分析v$pgastat，然後調整PGA就OK啦。