Oracle內(nèi)存管理之PGA探索

dodba

    一、概述
　　SGA，PGA，UGA都是Oracle管理的內(nèi)存區(qū)。
　　SGA（System Global Area），即系統(tǒng)全局區(qū)，Oracle中最重要的內(nèi)存區(qū)。
　　PGA（Process Global Area），即程序全局區(qū)，一個(gè)進(jìn)程的專(zhuān)用的內(nèi)存區(qū)。
　　UGA（User Global Area），即用戶(hù)全局區(qū)，與特定的會(huì)話(huà)相關(guān)聯(lián)。
　　專(zhuān)用服務(wù)器連接模式，UGA在PGA中分配。
　　共享服務(wù)器連接模式，UGA在SGA中的Large Pool中分配。
　　如果采用專(zhuān)用服務(wù)器連接模式，PGA中包含UGA，其他區(qū)域用來(lái)排序，散列和位圖合并。
　　簡(jiǎn)單來(lái)講，PGA=UGA+排序區(qū)+散列區(qū)+位圖合并區(qū)。
　　二、PGA的管理模式。
　　PGA分兩種管理模式：
　　1) 手動(dòng)PGA內(nèi)存管理，用戶(hù)指定排序區(qū)和散列區(qū)所使用的內(nèi)存，每個(gè)連接使用相同的內(nèi)存。
　　2) 自動(dòng)PGA內(nèi)存管理，告訴Oracle可以使用的PGA的總量，由Oraclce根據(jù)系統(tǒng)負(fù)載決定具體分配。
　　9iR1時(shí)默認(rèn)為手動(dòng)PGA內(nèi)存管理，9iR2以后默認(rèn)為自動(dòng)PGA內(nèi)存管理。
　　PGA內(nèi)存可以動(dòng)態(tài)擴(kuò)大和回收。
　　PGA內(nèi)存管理模式由WORKAREA_SIZE_POLICY控制。
　　1) 設(shè)為MANUAL，啟用手動(dòng)內(nèi)存管理。
　　2) 設(shè)為AUTO，并且PGA_AGGREGATE_TARGET不為0時(shí)，啟用自動(dòng)內(nèi)存管理。
　　三、手動(dòng)PGA內(nèi)存管理
　　有三個(gè)參數(shù)對(duì)PGA影響最大。
　　SORT_AREA_SIZE：對(duì)信息排序所用的內(nèi)存總量
　　SORT_AREA_RETAINED_SIZE：排序后在內(nèi)存中保存排序信息的內(nèi)存總量。
　　HASH_AREA_SIZE：存儲(chǔ)散列列表所用的內(nèi)存量。
　　下面對(duì)這三個(gè)參數(shù)進(jìn)行說(shuō)明：
　　1) SORT_AREA_SIZE：
　　如果SORT_AREA_SIZE設(shè)為512KB，SORT_AREA_RETAINED_SIZE也為512KB，則Oracle使用512KB的內(nèi)存進(jìn)行排序，排序后所有數(shù)據(jù)都留在內(nèi)存中。
　　2) SORT_AREA_RETAINED_SIZE：
　　如果SORT_AREA_SIZE設(shè)為512KB，SORT_AREA_RETAINED_SIZE設(shè)為384KB，則Oracle使用512KB的內(nèi)存進(jìn)行排序，然后保留384KB的已排序數(shù)據(jù)，另外512KB-384KB=128KB的已排序數(shù)據(jù)會(huì)寫(xiě)到臨時(shí)表空間中。
　　如果SORT_AREA_RETAINED_SIZE沒(méi)有設(shè)置，則它的值為0，但是實(shí)際保留的排序數(shù)據(jù)和SORT_AREA_SIZE相同。
　　3) HASH_AREA_SIZE：
　　一個(gè)大集合和另個(gè)集合進(jìn)行連接時(shí)，會(huì)用到HASH_AREA_SIZE參數(shù)。較小的 表會(huì)放到這部分內(nèi)存中作為驅(qū)動(dòng)表，然后大表進(jìn)行探索（PROBE）操作進(jìn)行連接。如果HASH_AREA_SIZE過(guò)小會(huì)影響兩個(gè)集合（表）連接時(shí)的性能。
　　注意點(diǎn)：
　　1) 如果需要排序的數(shù)據(jù)量大于SORT_AREA_SIZE，Oracle會(huì)分批進(jìn)行排序。把當(dāng)前已排序的數(shù)據(jù)保存到臨時(shí)表空間中，然后對(duì)剩余的數(shù)據(jù)進(jìn)行排序。最后，還會(huì)對(duì)這些保存在臨時(shí)表空間中的已排序數(shù)據(jù)再進(jìn)行排序，因?yàn)槊看伪４娴脚R時(shí)表空間中的已排序數(shù)據(jù)只是部分?jǐn)?shù)據(jù)的排序，對(duì)整體需排序的數(shù)據(jù)來(lái)說(shuō)只是部分局部有序。
　　2) *_AREA_SIZE只是對(duì)某個(gè)操作的限制，一個(gè)查詢(xún)可能有多個(gè)操作，每個(gè)操作都有自己的內(nèi)存區(qū)。如果SORT_AREA_SIZE設(shè)為5MB，一個(gè)查詢(xún)可能會(huì)有10個(gè)排序操作，這樣一個(gè)查詢(xún)會(huì)占用50MB的排序內(nèi)存。
　　3) 3，*_AREA_SIZE內(nèi)存的分配是按需分配。如果一個(gè)查詢(xún)需要5MB內(nèi)存進(jìn)行排序，就算分配1G的SORT_AREA_SIZE也不會(huì)全部使用，只會(huì)使用需要的5MB的內(nèi)存量。
　　四、自動(dòng)PGA內(nèi)存管理
　　要啟用自動(dòng)PGA內(nèi)存管理，設(shè)置下列參數(shù)：
　　1，WORKAREA_SIZE_POLICY=AUTO
　　2，PGA_AGGREGATE_TARGET=非零
　　有關(guān)PGA_AGGREGATE_TARGET：
　　1) PGA_AGGREGATE_TARGET是一個(gè)目標(biāo)值。連接數(shù)少的時(shí)候?qū)嶋H分配PGA內(nèi)存會(huì)比它要小。連接數(shù)多的時(shí)候?qū)嶋H分配的PGA內(nèi)存會(huì)比它要大，但是Oracle會(huì)努力保持總PGA保持在PGA_AGGREGATE_TARGET值內(nèi)。
　　例如，PGA_AGGREGATE_TARGET 設(shè)為300MB。5個(gè)用戶(hù)連接時(shí)，每個(gè)用戶(hù)可能分配10MB的PGA內(nèi)存，共分配50MB的PGA內(nèi)存。300個(gè)用戶(hù)連接時(shí)每個(gè)用戶(hù)可能分配1.3MB的 PGA內(nèi)存，共分配390MB的PGA內(nèi)存。當(dāng)用戶(hù)連接多時(shí)，Oracle會(huì)降低每個(gè)用戶(hù)的PGA內(nèi)存使用量。
　　2) 一個(gè)串行查詢(xún)（非并行查詢(xún)）可能包括多個(gè)排序/散列操作，每個(gè)排序/散列操作最多使用5%的PGA內(nèi)存。
　　3) 一個(gè)并行查詢(xún)最多可用到30%的PGA內(nèi)存，無(wú)論有多少并行進(jìn)程。
　　五、手動(dòng)PGA內(nèi)存管理與自動(dòng)PGA內(nèi)存管理
　　自動(dòng)PGA內(nèi)存管理相對(duì)于手動(dòng)PGA內(nèi)存管理有很多優(yōu)點(diǎn)
　　1， 當(dāng)用戶(hù)連接少時(shí)
　　a) 手動(dòng)PGA內(nèi)存管理不管有多少可用內(nèi)存都按照預(yù)設(shè)值進(jìn)行分配。比如當(dāng)前空閑內(nèi)存為300MB，連接需要10MB的內(nèi)存進(jìn)行排序，而我們?cè)O(shè)定的排序區(qū)大小為5MB，導(dǎo)致雖然有足夠的空閑內(nèi)存卻無(wú)法分配給當(dāng)前連接，造成執(zhí)行效率低下。
　　b) 自動(dòng)PGA內(nèi)存管理會(huì)根據(jù)當(dāng)前空閑內(nèi)存來(lái)進(jìn)行分配。當(dāng)空閑內(nèi)存為300MB，當(dāng)前用戶(hù)需要10MB內(nèi)存進(jìn)行排序，Oracle就會(huì)分配10MB內(nèi)存給當(dāng)前用戶(hù)。
　　2， 當(dāng)用戶(hù)連接多時(shí)
　　a) 手動(dòng)PGA內(nèi)存管理會(huì)完全按照預(yù)設(shè)值分配內(nèi)存。如果物理內(nèi)存總量為1G，排序區(qū)設(shè)為5MB，當(dāng)有300個(gè)用戶(hù)連接時(shí)，Oracle會(huì)分配1.5G的內(nèi)存，這已經(jīng)超過(guò)了我們的實(shí)際物理內(nèi)存！
　　b) 自動(dòng)PGA內(nèi)存管理會(huì)根據(jù)當(dāng)前連接情況進(jìn)行分配。如果物理內(nèi)存總量為1G，PGA_AGGREGATE_TARGET為300MB，當(dāng)用戶(hù)數(shù)從10升到 300時(shí)，每個(gè)用戶(hù)連接的內(nèi)存會(huì)從滿(mǎn)足需要的10MB慢慢減少到1.3MB，雖然最后總量也會(huì)超過(guò)PGA_AGGREGATE_TARGET，但比起手動(dòng) PGA內(nèi)存管理要好很多了。
　　什么時(shí)候使用自動(dòng)PGA內(nèi)存管理？什么時(shí)候使用手動(dòng)PGA內(nèi)存管理？
　　白天系統(tǒng)正常運(yùn)行時(shí)適合使用自動(dòng)PGA內(nèi)存管理，讓Oracle根據(jù)當(dāng)前負(fù)載自動(dòng)管理、分配PGA內(nèi)存。
　　夜里用戶(hù)數(shù)少、進(jìn)行維護(hù)的時(shí)候可以設(shè)定當(dāng)前會(huì)話(huà)使用手動(dòng)PGA內(nèi)存管理，讓當(dāng)前的維護(hù)操作獲得盡可能多的內(nèi)存，加快執(zhí)行速度。
　　如：服務(wù)器平時(shí)運(yùn)行在自動(dòng)PGA內(nèi)存管理模式下，夜里有個(gè)任務(wù)要大表進(jìn)行排序連接后更新，就可以在該操作session中臨時(shí)更改為手動(dòng)PGA內(nèi)存管理，然后分配大的SORT_AREA_SIZE和HASH_AREA_SIZE（50%甚至80%內(nèi)存，要確保無(wú)其他用戶(hù)使用），這樣能大大加快系統(tǒng)運(yùn)行速度，又不影響白天高峰期對(duì)系統(tǒng)造成的影響。
　　六、操作命令
　　系統(tǒng)級(jí)更改：
　　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à)級(jí)更改
　　ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
　　ALTER SESSION SET SORT_AREA_SIZE = 65536；
　　ALTER SESSION SET HASH_AREA_SIZE = 65536；
　　七、學(xué)以致用
　　1，排序區(qū)：
　　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級(jí)修改排序區(qū)為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，散列區(qū)：
　　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級(jí)修改散列區(qū)為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í)驗(yàn)皆執(zhí)行全表掃描保證相關(guān)表讀入緩沖區(qū)中，避免因數(shù)據(jù)沒(méi)讀入緩存造成誤差。
　　結(jié)論：在9iR2版以后，PGA不再像以前那樣困擾DBA了，Oracle會(huì)幫我們做好PGA的分配。但這并不意味著DBA不需要深入了解PGA了，掌握PGA并根據(jù)適當(dāng)應(yīng)用會(huì)讓工作如虎添翼。 來(lái)源:考試大-Oracle認(rèn)證考試