PGA 测试(转)

我们知道从9i开始oracle提供了自动管理pga的功能，从此我们可以不管sort_area_size，hash_area_size等等烦人的设置，也不用再去计算繁琐的pga大小。但是怎么控制每个session使用pga的大小呢，会不会出现一个session用完所有pga，而其他session得不多空间的状况吗？

带着这个疑问我们可以来做一个实验。

实验的步骤很简单，初始pga_aggregate_target设定为100m，我们做一个排序的大查询，然后查看pga的使用空间。

然后修改pga_aggregate_target为1g，在做一个大查询，查看pga使用空间。

SQL> startup pfile=/test/inittest.ora

ORACLE instance started.

Total System Global Area 2710294504 bytes

Fixed Size 454632 bytes

Variable Size 805306368 bytes

Database Buffers 1903992832 bytes

Redo Buffers 540672 bytes

Database mounted.

Database opened.

SQL> show parameter pga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target big integer 100000000

SQL> conn test/test

Connected.

SQL> select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

NAME VALUE

---------------------------------------------------------------- ----------

session pga memory 243208

session pga memory max 243208

这里可以看到初始连接时pga使用了240k左右

SQL> set autotrace trace;

SQL> select title from test order by starts,ends,title,zoo,city;

2997531 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=129273 Card=2932520

Bytes=173018680)

1 0 SORT (ORDER BY) (Cost=129273 Card=2932520 Bytes=173018680)

2 1 PARTITION RANGE (ALL)

3 2 TABLE ACCESS (FULL) OF 'TEST' (Cost=84738

Card=2932520 Bytes=173018680)

Statistics

----------------------------------------------------------

475 recursive calls

75 db block gets

930726 consistent gets

962505 physical reads

60 redo size

113681365 bytes sent via SQL*Net to client

2198688 bytes received via SQL*Net from client

199837 SQL*Net roundtrips to/from client

37 sorts (memory)

1 sorts (disk)

2997531 rows processed

SQL> set autotrace off;

SQL> select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

NAME VALUE

---------------------------------------------------------------- ----------

session pga memory 457496

session pga memory max 6355736

做完一个大查询，pga的使用峰值是达到了6m左右，这其中可能包含了一部分pga自己的开销

关闭数据库，修改pga_aggregate_target为1g，再重启数据库

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

[oracle@wanghai4 oracle]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Jul 18 11:46:53 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup pfile=/test/inittest.ora

ORACLE instance started.

Total System Global Area 2710294504 bytes

Fixed Size 454632 bytes

Variable Size 805306368 bytes

Database Buffers 1903992832 bytes

Redo Buffers 540672 bytes

Database mounted.

Database opened.

SQL> conn test/test

Connected.

SQL> select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

NAME VALUE

---------------------------------------------------------------- ----------

session pga memory 250792

session pga memory max 381864

SQL> show parameter pga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target big integer 1000000000

SQL> set autotrace trace;

SQL> select title from test order by starts,ends,title,zoo,city;

2997531 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=112995 Card=2932520

Bytes=173018680)

1 0 SORT (ORDER BY) (Cost=112995 Card=2932520 Bytes=173018680)

2 1 PARTITION RANGE (ALL)

3 2 TABLE ACCESS (FULL) OF 'TEST' (Cost=84738

Card=2932520 Bytes=173018680)

Statistics

----------------------------------------------------------

3740 recursive calls

28 db block gets

931577 consistent gets

955856 physical reads

60 redo size

113681365 bytes sent via SQL*Net to client

2198688 bytes received via SQL*Net from client

199837 SQL*Net roundtrips to/from client

77 sorts (memory)

1 sorts (disk)

2997531 rows processed

SQL> SQL> SQL> SQL> set autotrace off;

SQL> select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

NAME VALUE

---------------------------------------------------------------- ----------

session pga memory 456568

session pga memory max 50263928

可以看到这次大查询使用了近50m的pga空间，为什么第一次只使用了6m呢，这是因为oracle会限制每个session使用pga的最大空间，

每个session可以使用到最大至5%pga的总空间，所以第一次可以使用到5m左右的空间，加上pga自己的开销，使用了一共6m。第二次

由于总pga大小扩大了，所以session使用的pga大小也差不多是5%的总pga大小.oracle很好的控制了pga的使用，避免了一个session

占用大部分pga的情况。不过，还存在一种特例，在并行操作的情况下session pga可以使用到30%总pga空间。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7958405/viewspace-711579/，如需转载，请注明出处，否则将追究法律责任。