伴隨自動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.
原文地址：http://www.eygle.com/archives/2006/04/oracle_pga_internal.Html
