Oracle PGA管理（一）

作者: 絃樂之花 | 可以轉載, 但必須以超連結形式標明文章原始出處和作者資訊及

網址http://shiri512003.itpub.net/post/37713/496819

[@more@]

下面內容摘自

Work area sizes in 9i/10gR1
• Serial operations: _smm_max_size
• min(5% PGA_AGGREGATE_TARGET, 100MB)
• Parallel operations: _smm_px_max_size
• 30% of PGA_AGGREGATE_TARGET / DOP
• DOP stands for degree of parallelism

Limits For Work Area Sizes in
10gR2
• Serial operations
• for P_A_T <= 500MB the parameter _smm_max_size = 20% of P_A_T
• for P_A_T between 500MB and 1000MB the parameter _smm_max_size = 100M
• for P_A_T > 1000 MB the parameter _smm_max_size = 10% of P_A_T
(upper bound not limited?)
• Parallel operations
• limit for parallel operations changed from 30% to 50% of PGA_AGGREGATE_TARGET / DOP.
• When DOP <=5 then _smm_max_size is used
• when DOP > 5 _smm_px_max_size/DOP limits the maximum memory
usage.
• _PGA_MAX_SIZE defaults to 2*_smm_max_size

下面資料來自一個10204庫

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1993M
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 1993
global memory bound 199.296875

SQL> alter system set pga_aggregate_target=10M;

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 10
global memory bound 2

SQL> alter system set pga_aggregate_target=500M;

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 500
global memory bound 100

SQL> alter system set pga_aggregate_target=510M;

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 510
global memory bound 100

SQL> alter system set pga_aggregate_target=1000M;

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 1000
global memory bound 100

SQL> alter system set pga_aggregate_target=1001M;

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 1001
global memory bound 100.097656

值得注意的是Joze Senegacnik在本篇文章中當時的一個質疑：

• for P_A_T > 1000 MB the parameter _smm_max_size = 10% of P_A_T
(upper bound not limited?)

事實上是有上限的，上限由隱含引數_pga_max_size決定，單程式序列操作PGA的上限不能超過該引數的1/2。不過比較有意義的是，預設情況下這個隱含引數會隨著pga_aggregate_target的變化而變化：

SQL>alter system set pga_aggregate_target=1000m;

System altered.

SQL>!ora _param pga_max_size

Session altered.

NAME VALUE
---------------------------------------- ----------------------------------------
_pga_max_size 209715200

SQL>alter system set pga_aggregate_target=4096M;

System altered.

SQL>!ora _param pga_max_size

Session altered.

NAME VALUE
---------------------------------------- ----------------------------------------
_pga_max_size 858992640

SQL>alter system set pga_aggregate_target=8192m;

System altered.

SQL>!ora _param pga_max_size

Session altered.

NAME VALUE
---------------------------------------- ----------------------------------------
_pga_max_size 1717985280

在大記憶體時代，個人還是認同oracle在這個問題上面的處理的。