关于自动PGA管理的进一步探讨

    伴随自动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

中国网管联盟bitsCN.com

-------------------------- -------- -----------------
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

网管朋友网www_bitscn_net

---------------------------------------------------------------- ----------
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 网管u家u.bitsCN.com

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 网管网www_bitscn_com
-------------------------------------------------- ------------------ ----- --------------------------
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'); 网管u家u.bitsCN.com

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

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 ;

网管论坛bbs_bitsCN_com