作者: 弦乐之花 | 可以转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明

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

下面内容摘自http://tonguc.yilmaz.googlepages.com/JozeSenegacnik-PGAMemoryManagementvO.zip

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在这个问题上面的处理的。