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與進程私人空間虛擬位址的頂點之間的計算。