Oracle記憶體結構研究-PGA篇

一、概述
SGA，PGA，UGA都是Oracle管理的記憶體區。

SGA（System Global Area），即系統全域性區，Oracle中最重要的記憶體區。
PGA（Process Global Area），即程式全域性區，一個程式的專用的記憶體區。
UGA（User Global Area），即使用者全域性區，與特定的會話相關聯。
專用伺服器連線模式，UGA在PGA中分配。
共享伺服器連線模式，UGA在SGA中的Large Pool中分配。

如果採用專用伺服器連線模式，PGA中包含UGA，其他區域用來排序，雜湊和點陣圖合併。
簡單來講，PGA=UGA+排序區+雜湊區+點陣圖合併區。


二、PGA的管理模式。
PGA分兩種管理模式：
1)        手動PGA記憶體管理，使用者指定排序區和雜湊區所使用的記憶體，每個連線使用相同的記憶體。
2)        自動PGA記憶體管理，告訴Oracle可以使用的PGA的總量，由Oraclce根據系統負載決定具體分配。

        9iR1時預設為手動PGA記憶體管理，9iR2以後預設為自動PGA記憶體管理。
        PGA記憶體可以動態擴大和回收。

PGA記憶體管理模式由WORKAREA_SIZE_POLICY控制。
1)        設為MANUAL，啟用手動記憶體管理。
2)        設為AUTO，並且PGA_AGGREGATE_TARGET不為0時，啟用自動記憶體管理。


三、手動PGA記憶體管理
有三個引數對PGA影響最大。
SORT_AREA_SIZE：對資訊排序所用的記憶體總量
SORT_AREA_RETAINED_SIZE：排序後在記憶體中儲存排序資訊的記憶體總量。
HASH_AREA_SIZE：儲存雜湊列表所用的記憶體量。

下面對這三個引數進行說明：
1)        SORT_AREA_SIZE：
如果SORT_AREA_SIZE設為512KB，SORT_AREA_RETAINED_SIZE也為512KB，則Oracle使用512KB的記憶體進行排序，排序後所有資料都留在記憶體中。

2)        SORT_AREA_RETAINED_SIZE：
如果SORT_AREA_SIZE設為512KB，SORT_AREA_RETAINED_SIZE設為384KB，則Oracle使用512KB的記憶體進行排序，然後保留384KB的已排序資料，另外512KB-384KB=128KB的已排序資料會寫到臨時表空間中。
        如果SORT_AREA_RETAINED_SIZE沒有設定，則它的值為0，但是實際保留的排序資料和SORT_AREA_SIZE相同。

3)        HASH_AREA_SIZE：
一個大集合和另個集合進行連線時，會用到HASH_AREA_SIZE引數。較小的 表會放到這部分記憶體中作為驅動表，然後大表進行探索（PROBE）操作進行連線。如果HASH_AREA_SIZE過小會影響兩個集合（表）連線時的效能。

注意點：
1)        如果需要排序的資料量大於SORT_AREA_SIZE，Oracle會分批進行排序。把當前已排序的資料儲存到臨時表空間中，然後對剩餘的資料進行排序。最後，還會對這些儲存在臨時表空間中的已排序資料再進行排序，因為每次儲存到臨時表空間中的已排序資料只是部分資料的排序，對整體需排序的資料來說只是部分區域性有序。
2)        *_AREA_SIZE只是對某個操作的限制，一個查詢可能有多個操作，每個操作都有自己的記憶體區。如果SORT_AREA_SIZE設為5MB，一個查詢可能會有10個排序操作，這樣一個查詢會佔用50MB的排序記憶體。
3)        3，*_AREA_SIZE記憶體的分配是按需分配。如果一個查詢需要5MB記憶體進行排序，就算分配1G的SORT_AREA_SIZE也不會全部使用，只會使用需要的5MB的記憶體量。


四、自動PGA記憶體管理
要啟用自動PGA記憶體管理，設定下列引數：
1，WORKAREA_SIZE_POLICY=AUTO
2，PGA_AGGREGATE_TARGET=非零

有關PGA_AGGREGATE_TARGET：
1)        PGA_AGGREGATE_TARGET是一個目標值。連線數少的時候實際分配PGA記憶體會比它要小。連線數多的時候實際分配的PGA記憶體會比它要大，但是Oracle會努力保持總PGA保持在PGA_AGGREGATE_TARGET值內。

例如，PGA_AGGREGATE_TARGET 設為300MB。5個使用者連線時，每個使用者可能分配10MB的PGA記憶體，共分配50MB的PGA記憶體。300個使用者連線時每個使用者可能分配1.3MB的PGA記憶體，共分配390MB的PGA記憶體。當使用者連線多時，Oracle會降低每個使用者的PGA記憶體使用量。
2)        一個序列查詢（非並行查詢）可能包括多個排序/雜湊操作，每個排序/雜湊操作最多使用5%的PGA記憶體。
3)        一個並行查詢最多可用到30%的PGA記憶體，無論有多少並行程式。


五、手動PGA記憶體管理與自動PGA記憶體管理
自動PGA記憶體管理相對於手動PGA記憶體管理有很多優點
1，        當使用者連線少時
a)        手動PGA記憶體管理不管有多少可用記憶體都按照預設值進行分配。比如當前空閒記憶體為300MB，連線需要10MB的記憶體進行排序，而我們設定的排序區大小為5MB，導致雖然有足夠的空閒記憶體卻無法分配給當前連線，造成執行效率低下。
b)        自動PGA記憶體管理會根據當前空閒記憶體來進行分配。當空閒記憶體為300MB，當前使用者需要10MB記憶體進行排序，Oracle就會分配10MB記憶體給當前使用者。
2，        當使用者連線多時
a)        手動PGA記憶體管理會完全按照預設值分配記憶體。如果實體記憶體總量為1G，排序區設為5MB，當有300個使用者連線時，Oracle會分配1.5G的記憶體，這已經超過了我們的實際實體記憶體！
b)        自動PGA記憶體管理會根據當前連線情況進行分配。如果實體記憶體總量為1G，PGA_AGGREGATE_TARGET為300MB，當使用者數從10升到300時，每個使用者連線的記憶體會從滿足需要的10MB慢慢減少到1.3MB，雖然最後總量也會超過PGA_AGGREGATE_TARGET，但比起手動PGA記憶體管理要好很多了。

什麼時候使用自動PGA記憶體管理？什麼時候使用手動PGA記憶體管理？
白天系統正常執行時適合使用自動PGA記憶體管理，讓Oracle根據當前負載自動管理、分配PGA記憶體。
夜裡使用者數少、進行維護的時候可以設定當前會話使用手動PGA記憶體管理，讓當前的維護操作獲得儘可能多的記憶體，加快執行速度。

如：伺服器平時執行在自動PGA記憶體管理模式下，夜裡有個任務要大表進行排序連線後更新，就可以在該操作session中臨時更改為手動PGA記憶體管理，然後分配大的SORT_AREA_SIZE和HASH_AREA_SIZE（50%甚至80%記憶體，要確保無其他使用者使用），這樣能大大加快系統執行速度，又不影響白天高峰期對系統造成的影響。


六、操作命令
系統級更改：
ALTER SYSTEM SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100000000;
ALTER SYSTEM SET SORT_AREA_SIZE = 65536 SCOPE = SPFILE；
ALTER SYSTEM SET HASH_AREA_SIZE = 65536 SCOPE = SPFILE；

會話級更改
ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
ALTER SESSION SET SORT_AREA_SIZE = 65536；
ALTER SESSION SET HASH_AREA_SIZE = 65536；


七、學以致用
1，排序區：
pga_aggregate_target為100MB，單個查詢能用到5%也就是5MB時排序所需時間

SQL> create table sorttable as select * from all_objects;

表已建立。

SQL> insert into sorttable (select * from sorttable);

已建立49735行。

SQL> insert into sorttable (select * from sorttable);

已建立99470行。

SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from sorttable order by object_id;

已選擇198940行。

已用時間:  00: 00: 50.49

Session級修改排序區為30mb所需時間
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

會話已更改。

已用時間:  00: 00: 00.02
SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;

會話已更改。

已用時間:  00: 00: 00.01
SQL> select * from sorttable order by object_id;

已選擇198940行。

已用時間:  00: 00: 10.76

可以看到所需時間從50.49秒減少到10.31秒，速度提升很明顯。

2，雜湊區：
pga_aggregate_target為100MB，單個查詢能用到5%也就是5MB時表連線所需時間

SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;

已選擇49735行。

已用時間:  00: 00: 40.50

Session級修改雜湊區為30mb所需時間
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

會話已更改。

已用時間:  00: 00: 00.01
SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000;

會話已更改。

已用時間:  00: 00: 00.01
SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;

已選擇49735行。

已用時間:  00: 00: 04.47

所需時間由40.50秒提升到4.47秒，效果同樣很明顯。

備註：以上實驗皆執行全表掃描保證相關表讀入緩衝區中，避免因資料沒讀入快取造成誤差。


結論：在9iR2版以後，PGA不再像以前那樣困擾DBA了，Oracle會幫我們做好PGA的分配。但這並不意味著DBA不需要深入瞭解PGA了，掌握PGA並根據適當應用會讓工作如虎添翼。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10113559/viewspace-617147/，如需轉載，請註明出處，否則將追究法律責任。