32bit oracle中SGA_MAX_SIZE 与 单个进程 PGA 的制约关系

阅读本文前请先阅读<<32bit oracle 扩展SGA原理 >>

  在我的测试中，由于我降低了SGA的起点(降低SGA起点和本文章没有实质上的关系，仅仅因为我的测试数据库正好是被降低了sga起点而已)，从0x5000000降低到0x42000000,也就是说，当没有降低的时候，SGA的理论极限值是约小于1.75G,我降低起点后，SGA的极限值大约是1.95G。

show">sys@OCN> show sga  

Total System Global Area 2064719084 bytes
Fixed Size                   453868 bytes
Variable Size             385875968 bytes
Database Buffers         1677721600 bytes
Redo Buffers                 667648 bytes
sys@OCN>

我的SGA_MAX_SIZE的大小这时为 2064719084 。

show">sys@OCN> show parameters sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 2064719084

实际上，我这个时候的SGA_MAX_SIZE比真实的SGA设置要大一些，这是我手工调大的。我们来观察单个数据库进程的maps

[oracle@ocnsb2 oracle]$ ps -ef|grep pmon
oracle   18346     1  0 12:16 ?        00:00:00 ora_pmon_roocn
oracle   18535 18493  0 13:04 pts/0    00:00:00 grep pmon

[oracle@ocnsb2 oracle]$ more  /proc/18346/maps
08048000-0a4b9000 r-xp 00000000 08:05 324868     /opt/oracle/products/9.2.0/bin/oracle
0a4b9000-0ad53000 rw-p 02470000 08:05 324868     /opt/oracle/products/9.2.0/bin/oracle
0ad53000-0adec000 rwxp 00000000 00:00 0
40000000-40016000 r-xp 00000000 08:02 448102     /lib/ld-2.2.4.so
40016000-40017000 rw-p 00015000 08:02 448102     /lib/ld-2.2.4.so
40017000-40018000 rw-p 00000000 00:00 0
40018000-40019000 r-xp 00000000 08:05 1038540    /opt/oracle/products/9.2.0/lib/libodmd9.so
40019000-4001a000 rw-p 00000000 08:05 1038540    /opt/oracle/products/9.2.0/lib/libodmd9.so
4001a000-40026000 r-xp 00000000 08:05 1038423    /opt/oracle/products/9.2.0/lib/libskgxp9.so
40026000-4002a000 rw-p 0000b000 08:05 1038423    /opt/oracle/products/9.2.0/lib/libskgxp9.so
4002a000-40038000 r-xp 00000000 08:05 1038537    /opt/oracle/products/9.2.0/lib/libskgxn9.so
40038000-40039000 rw-p 0000d000 08:05 1038537    /opt/oracle/products/9.2.0/lib/libskgxn9.so
40039000-4004d000 rw-p 00000000 00:00 0
4004d000-4032c000 r-xp 00000000 08:05 1038531    /opt/oracle/products/9.2.0/lib/libjox9.so
4032c000-4043c000 rw-p 002de000 08:05 1038531    /opt/oracle/products/9.2.0/lib/libjox9.so
4043c000-4043e000 rw-p 00000000 00:00 0
4043e000-40441000 r-xp 00000000 08:02 448115     /lib/libdl-2.2.4.so
40441000-40442000 rw-p 00002000 08:02 448115     /lib/libdl-2.2.4.so
40442000-40443000 rw-p 00000000 00:00 0
40443000-40465000 r-xp 00000000 08:02 448117     /lib/libm-2.2.4.so
40465000-40466000 rw-p 00021000 08:02 448117     /lib/libm-2.2.4.so
40466000-40475000 r-xp 00000000 08:02 448147     /lib/libpthread-0.9.so
40475000-4047d000 rw-p 0000e000 08:02 448147     /lib/libpthread-0.9.so
4047d000-40490000 r-xp 00000000 08:02 448120     /lib/libnsl-2.2.4.so
40490000-40491000 rw-p 00012000 08:02 448120     /lib/libnsl-2.2.4.so
40491000-40493000 rw-p 00000000 00:00 0
40493000-405c8000 r-xp 00000000 08:02 448111     /lib/libc-2.2.4.so
405c8000-405cd000 rw-p 00134000 08:02 448111     /lib/libc-2.2.4.so
405cd000-405f4000 rw-p 00000000 00:00 0
405f5000-405ff000 r-xp 00000000 08:02 448136     /lib/libnss_files-2.2.4.so
405ff000-40600000 rw-p 00009000 08:02 448136     /lib/libnss_files-2.2.4.so
40600000-40680000 rw-p 00000000 08:02 69445      /dev/zero
40680000-406c1000 rw-p 00000000 00:00 0
42000000-be400000 rw-B 00000000 00:04 524288     /SYSV0676004c (deleted)
bffe4000-bffee000 rwxp ffff7000 00:00 0
0a4b9000-0ad53000 rw-p 02470000 08:05 324868     /opt/oracle/products/9.2.0/bin/oracle
0ad53000-0adec000 rwxp 00000000 00:00 0
40000000-40016000 r-xp 00000000 08:02 448102     /lib/ld-2.2.4.so
40016000-40017000 rw-p 00015000 08:02 448102     /lib/ld-2.2.4.so
40017000-40018000 rw-p 00000000 00:00 0
40018000-40019000 r-xp 00000000 08:05 1038540    /opt/oracle/products/9.2.0/lib/libodmd9.so
40019000-4001a000 rw-p 00000000 08:05 1038540    /opt/oracle/products/9.2.0/lib/libodmd9.so
4001a000-40026000 r-xp 00000000 08:05 1038423    /opt/oracle/products/9.2.0/lib/libskgxp9.so
40026000-4002a000 rw-p 0000b000 08:05 1038423    /opt/oracle/products/9.2.0/lib/libskgxp9.so
4002a000-40038000 r-xp 00000000 08:05 1038537    /opt/oracle/products/9.2.0/lib/libskgxn9.so
40038000-40039000 rw-p 0000d000 08:05 1038537    /opt/oracle/products/9.2.0/lib/libskgxn9.so
40039000-4004d000 rw-p 00000000 00:00 0
4004d000-4032c000 r-xp 00000000 08:05 1038531    /opt/oracle/products/9.2.0/lib/libjox9.so
4032c000-4043c000 rw-p 002de000 08:05 1038531    /opt/oracle/products/9.2.0/lib/libjox9.so
4043c000-4043e000 rw-p 00000000 00:00 0
4043e000-40441000 r-xp 00000000 08:02 448115     /lib/libdl-2.2.4.so
40441000-40442000 rw-p 00002000 08:02 448115     /lib/libdl-2.2.4.so
40442000-40443000 rw-p 00000000 00:00 0
40443000-40465000 r-xp 00000000 08:02 448117     /lib/libm-2.2.4.so
40465000-40466000 rw-p 00021000 08:02 448117     /lib/libm-2.2.4.so
40466000-40475000 r-xp 00000000 08:02 448147     /lib/libpthread-0.9.so
40475000-4047d000 rw-p 0000e000 08:02 448147     /lib/libpthread-0.9.so
4047d000-40490000 r-xp 00000000 08:02 448120     /lib/libnsl-2.2.4.so
40490000-40491000 rw-p 00012000 08:02 448120     /lib/libnsl-2.2.4.so
40491000-40493000 rw-p 00000000 00:00 0
40493000-405c8000 r-xp 00000000 08:02 448111     /lib/libc-2.2.4.so
405c8000-405cd000 rw-p 00134000 08:02 448111     /lib/libc-2.2.4.so
405cd000-405f4000 rw-p 00000000 00:00 0
405f5000-405ff000 r-xp 00000000 08:02 448136     /lib/libnss_files-2.2.4.so
405ff000-40600000 rw-p 00009000 08:02 448136     /lib/libnss_files-2.2.4.so
40600000-40680000 rw-p 00000000 08:02 69445      /dev/zero
40680000-406c1000 rw-p 00000000 00:00 0
42000000-be400000 rw-B 00000000 00:04 524288     /SYSV0676004c (deleted)

这里可以看出单个进程中根据SGA_MAX_SIZE预分配了0x42000000 -- 0xbe400000 这段虚拟内存空间，虽然实际SGA并没有这么大，但是正是这段空间的预留，为9i中动态增大SGA提供了可能。也就是说进程能访问的虚拟地址空间是登陆上数据库的时候决定的，这样即使sga真正地发生了变化，只要在SGA_MAX_SIZE之内，该进程都可以访问这些虚拟内存空间。

  select">sys@OCN>  select  (to_number('be400000','xxxxxxxxxx') - to_number('42000000','xxxxxxxxxx'))/1024/1024 from dual;

(TO_NUMBER('BE400000','XXXXXXXXXX')-TO_NUMBER('42000000','XXXXXXXXXX'))/1024/1024
---------------------------------------------------------------------------------
                                                                             1988

sys@OCN>

 

bffe4000-bffee000 rwxp ffff7000 00:00 0
[oracle@ocnsb2 oracle]$

从这里可以看出进程的私有内存分配的顶点，我们看

0xbe400000--0xbffe4000 属于oracle进程可使用的 PGA 的空间大小

select">sys@OCN> select to_number('bffe4000','xxxxxxxxxx') - to_number('be400000','xxxxxxxxxx')  from dual;

TO_NUMBER('BFFE4000','XXXXXXXXXX')-TO_NUMBER('BE400000','XXXXXXXXXX')
---------------------------------------------------------------------
                                                             29245440

sys@OCN>

从这里可以看出PGA能使用的空间大约是 29245440 字节

我们来做一个测试

  show">sys@OCN>  show parameters pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 524288000
sys@OCN>

在 oracle 中单个进程的PGA的使用遵循的原则是 小于 min(pga_aggregate_target *5%, 100MB),在这样的设置下我执行对两个大表做hash join的查询，结果是成功完成。然后我修改pga_aggregate_target  = 1024M

  alter">sys@OCN>  alter system set pga_aggregate_target  = 1024m;

System altered.

  show">sys@OCN>  show parameters pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1073741824
sys@OCN>

当我再运行这个查询的过程中，我同时观察该进程的PGA使用量

在session 1 中执行

  1* select * from company c,member m where c.admin_member_id = m.login_id
  /">alibaba@OCN>  /
ERROR:
ORA-04030: out of process memory when trying to allocate 254476 bytes (hash-join
subh,kllcqas:kllsltba)

 

no rows selected

Execution Plan

 

在session2中观察结果(不停地执行下面的查询观察变化)

sys@OCN>   select  spid,USERNAME,PGA_USED_MEM ,PGA_ALLOC_MEM,PGA_FREEABLE_MEM, PGA_MAX_MEM    from v$process;

SPID         USERNAME                       PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------ ------------------------------ ------------ ------------- ---------------- -----------
                                                       0             0                0           0
18346        oracle                               147425        476981                0      476981
18348        oracle                               244313        570985                0      570985
18350        oracle                              1304737       1635765                0     1635765
18354        oracle                              2945957       3300193                0     3300193
18356        oracle                               200089        534729                0      534729
18358        oracle                              4368569       4741969                0     4741969
18360        oracle                               151577        551357                0      551357
18362        oracle                               165841        510101                0      510101
18364        oracle                               156625        485493                0      485493
18366        oracle                               157009        485493                0      485493
18368        oracle                              4362441       4748549                0     4748549
18370        oracle                              4362441       4715245                0     4715245
18388        oracle                               191285        790553           196608      790553
18406        oracle                             29041865      30095501                0    30095501

我们发现，执行hash join的进程的PGA最大达到29041865 之后，就报出错误4030而返回了，基本吻合前面关于 SGA_MAX_SIZE与进程私有空间虚拟地址的顶点之间的计算。