- 相關(guān)推薦
Oracle認證：Oracle內存結構研究-PGA篇
一、概述
SGA，PGA，UGA都是Oracle管理的內存區。
SGA(System Global Area)，即系統全局區，Oracle中最重要的內存區。
PGA(Process Global Area)，即程序全局區，一個(gè)進(jìn)程的專(zhuān)用的內存區。
UGA(User Global Area)，即用戶(hù)全局區，與特定的會(huì )話(huà)相關(guān)聯(lián)。
專(zhuān)用服務(wù)器連接模式，UGA在PGA中分配。
共享服務(wù)器連接模式，UGA在SGA中的Large Pool中分配。
如果采用專(zhuān)用服務(wù)器連接模式，PGA中包含UGA，其他區域用來(lái)排序，散列和位圖合并。
簡(jiǎn)單來(lái)講，PGA=UGA+排序區+散列區+位圖合并區。
二、PGA的管理模式
PGA分兩種管理模式：
1) 手動(dòng)PGA內存管理，用戶(hù)指定排序區和散列區所使用的內存，每個(gè)連接使用相同的內存。
2) 自動(dòng)PGA內存管理，告訴Oracle可以使用的PGA的總量，由Oraclce根據系統負載決定具體分配。
9iR1時(shí)默認為手動(dòng)PGA內存管理，9iR2以后默認為自動(dòng)PGA內存管理。
PGA內存可以動(dòng)態(tài)擴大和回收。
PGA內存管理模式由WORKAREA_SIZE_POLICY控制。
1) 設為MANUAL，啟用手動(dòng)內存管理。
2) 設為AUTO，并且PGA_AGGREGATE_TARGET不為0時(shí)，啟用自動(dòng)內存管理。
三、手動(dòng)PGA內存管理
有三個(gè)參數對PGA影響最大。
SORT_AREA_SIZE：對信息排序所用的內存總量
SORT_AREA_RETAINED_SIZE：排序后在內存中保存排序信息的內存總量。
HASH_AREA_SIZE：存儲散列列表所用的內存量。
下面對這三個(gè)參數進(jìn)行說(shuō)明：
1) SORT_AREA_SIZE：
如果SORT_AREA_SIZE設為512KB，SORT_AREA_RETAINED_SIZE也為512KB，則Oracle使用512KB的內存進(jìn)行排序，排序后所有數據都留在內存中。
2) SORT_AREA_RETAINED_SIZE：
如果SORT_AREA_SIZE設為512KB，SORT_AREA_RETAINED_SIZE設為384KB，則Oracle使用512KB的內存進(jìn)行排序，然后保留384KB的已排序數據，另外512KB-384KB=128KB的已排序數據會(huì )寫(xiě)到臨時(shí)表空間中。
如果SORT_AREA_RETAINED_SIZE沒(méi)有設置，則它的值為0，但是實(shí)際保留的排序數據和SORT_AREA_SIZE相同。
3) HASH_AREA_SIZE：
一個(gè)大集合和另個(gè)集合進(jìn)行連接時(shí)，會(huì )用到HASH_AREA_SIZE參數。較小的 表會(huì )放到這部分內存中作為驅動(dòng)表，然后大表進(jìn)行探索(PROBE)操作進(jìn)行連接。如果HASH_AREA_SIZE過(guò)小會(huì )影響兩個(gè)集合(表)連接時(shí)的性能。
注意點(diǎn)：
1) 如果需要排序的數據量大于SORT_AREA_SIZE，Oracle會(huì )分批進(jìn)行排序。把當前已排序的數據保存到臨時(shí)表空間中，然后對剩余的數據進(jìn)行排序。最后，還會(huì )對這些保存在臨時(shí)表空間中的已排序數據再進(jìn)行排序，因為每次保存到臨時(shí)表空間中的已排序數據只是部分數據的排序，對整體需排序的數據來(lái)說(shuō)只是部分局部有序。
2) *_AREA_SIZE只是對某個(gè)操作的限制，一個(gè)查詢(xún)可能有多個(gè)操作，每個(gè)操作都有自己的內存區。如果SORT_AREA_SIZE設為5MB，一個(gè)查詢(xún)可能會(huì )有10個(gè)排序操作，這樣一個(gè)查詢(xún)會(huì )占用50MB的排序內存。
3) 3，*_AREA_SIZE內存的分配是按需分配。如果一個(gè)查詢(xún)需要5MB內存進(jìn)行排序，就算分配1G的SORT_AREA_SIZE也不會(huì )全部使用，只會(huì )使用需要的5MB的內存量。
四、自動(dòng)PGA內存管理
要啟用自動(dòng)PGA內存管理，設置下列參數：
1，WORKAREA_SIZE_POLICY=AUTO
2，PGA_AGGREGATE_TARGET=非零
有關(guān)PGA_AGGREGATE_TARGET：
1) PGA_AGGREGATE_TARGET是一個(gè)目標值。連接數少的時(shí)候實(shí)際分配PGA內存會(huì )比它要小。連接數多的時(shí)候實(shí)際分配的PGA內存會(huì )比它要大，但是Oracle會(huì )努力保持總PGA保持在PGA_AGGREGATE_TARGET值內。
例如，PGA_AGGREGATE_TARGET 設為300MB。5個(gè)用戶(hù)連接時(shí)，每個(gè)用戶(hù)可能分配10MB的PGA內存，共分配50MB的PGA內存。300個(gè)用戶(hù)連接時(shí)每個(gè)用戶(hù)可能分配1.3MB的 PGA內存，共分配390MB的PGA內存。當用戶(hù)連接多時(shí)，Oracle會(huì )降低每個(gè)用戶(hù)的PGA內存使用量。
2) 一個(gè)串行查詢(xún)(非并行查詢(xún))可能包括多個(gè)排序/散列操作，每個(gè)排序/散列操作最多使用5%的PGA內存。
3) 一個(gè)并行查詢(xún)最多可用到30%的PGA內存，無(wú)論有多少并行進(jìn)程。
五、手動(dòng)PGA內存管理與自動(dòng)PGA內存管理
自動(dòng)PGA內存管理相對于手動(dòng)PGA內存管理有很多優(yōu)點(diǎn)
1， 當用戶(hù)連接少時(shí)
a) 手動(dòng)PGA內存管理不管有多少可用內存都按照預設值進(jìn)行分配。比如當前空閑內存為300MB，連接需要10MB的內存進(jìn)行排序，而我們設定的排序區大小為5MB，導致雖然有足夠的空閑內存卻無(wú)法分配給當前連接，造成執行效率低下。
b) 自動(dòng)PGA內存管理會(huì )根據當前空閑內存來(lái)進(jìn)行分配。當空閑內存為300MB，當前用戶(hù)需要10MB內存進(jìn)行排序，Oracle就會(huì )分配10MB內存給當前用戶(hù)。
2， 當用戶(hù)連接多時(shí)
a) 手動(dòng)PGA內存管理會(huì )完全按照預設值分配內存。如果物理內存總量為1G，排序區設為5MB，當有300個(gè)用戶(hù)連接時(shí)，Oracle會(huì )分配1.5G的內存，這已經(jīng)超過(guò)了我們的實(shí)際物理內存!
b) 自動(dòng)PGA內存管理會(huì )根據當前連接情況進(jìn)行分配。如果物理內存總量為1G，PGA_AGGREGATE_TARGET為300MB，當用戶(hù)數從10升到 300時(shí)，每個(gè)用戶(hù)連接的內存會(huì )從滿(mǎn)足需要的10MB慢慢減少到1.3MB，雖然最后總量也會(huì )超過(guò)PGA_AGGREGATE_TARGET，但比起手動(dòng) PGA內存管理要好很多了。
什么時(shí)候使用自動(dòng)PGA內存管理?什么時(shí)候使用手動(dòng)PGA內存管理?
白天系統正常運行時(shí)適合使用自動(dòng)PGA內存管理，讓Oracle根據當前負載自動(dòng)管理、分配PGA內存。
夜里用戶(hù)數少、進(jìn)行維護的時(shí)候可以設定當前會(huì )話(huà)使用手動(dòng)PGA內存管理，讓當前的維護操作獲得盡可能多的內存，加快執行速度。
如：服務(wù)器平時(shí)運行在自動(dòng)PGA內存管理模式下，夜里有個(gè)任務(wù)要大表進(jìn)行排序連接后更新，就可以在該操作session中臨時(shí)更改為手動(dòng)PGA內存管理，然后分配大的SORT_AREA_SIZE和HASH_AREA_SIZE(50%甚至80%內存，要確保無(wú)其他用戶(hù)使用)，這樣能大大加快系統運行速度，又不影響白天高峰期對系統造成的影響。
六、操作命令
系統級更改：
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;
會(huì )話(huà)級更改
ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL};
ALTER SESSION SET SORT_AREA_SIZE = 65536;
ALTER SESSION SET HASH_AREA_SIZE = 65536;
七、學(xué)以致用
1，排序區：
pga_aggregate_target為100MB，單個(gè)查詢(xún)能用到5%也就是5MB時(shí)排序所需時(shí)間
SQL> create table sorttable as select * from all_objects;
表已創(chuàng )建。
SQL> insert into sorttable (select * from sorttable);
已創(chuàng )建49735行。
SQL> insert into sorttable (select * from sorttable);
已創(chuàng )建99470行。
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from sorttable order by object_id;
已選擇198940行。
已用時(shí)間: 00: 00: 50.49
Session級修改排序區為30mb所需時(shí)間
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
會(huì )話(huà)已更改。
已用時(shí)間: 00: 00: 00.02
SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;
會(huì )話(huà)已更改。
已用時(shí)間: 00: 00: 00.01
SQL> select * from sorttable order by object_id;
已選擇198940行。
已用時(shí)間: 00: 00: 10.76
可以看到所需時(shí)間從50.49秒減少到10.31秒，速度提升很明顯。
2，散列區：
pga_aggregate_target為100MB，單個(gè)查詢(xún)能用到5%也就是5MB時(shí)表連接所需時(shí)間
SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;
已選擇49735行。
已用時(shí)間: 00: 00: 40.50
Session級修改散列區為30mb所需時(shí)間
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
會(huì )話(huà)已更改。
已用時(shí)間: 00: 00: 00.01
SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000;
會(huì )話(huà)已更改。
已用時(shí)間: 00: 00: 00.01
SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;
已選擇49735行。
已用時(shí)間: 00: 00: 04.47
所需時(shí)間由40.50秒提升到4.47秒，效果同樣很明顯。
備注：以上實(shí)驗皆執行全表掃描保證相關(guān)表讀入緩沖區中，避免因數據沒(méi)讀入緩存造成誤差。
結論：在9iR2版以后，PGA不再像以前那樣困擾DBA了，Oracle會(huì )幫我們做好PGA的分配。但這并不意味著(zhù)DBA不需要深入了解PGA了，掌握PGA并根據適當應用會(huì )讓工作如虎添翼。
【Oracle認證：Oracle內存結構研究-PGA篇】相關(guān)文章：
Oracle認證：ORACLE綁定變量BINDPEEKING03-08
Oracle認證作用03-19
Oracle認證簡(jiǎn)介11-30
Oracle最新認證03-09
Oracle認證途徑03-20
Oracle認證職業(yè)前景03-19
Oracle認證考試技巧03-19