如何能使用Oracle并行作业处理.docx

如何能使用Oracle并行作业处理.docx

《如何能使用Oracle并行作业处理.docx》由会员分享，可在线阅读，更多相关《如何能使用Oracle并行作业处理.docx（21页珍藏版）》请在冰豆网上搜索。

如何能使用Oracle并行作业处理

如何使用Oracle並行作業處理

Oracle並行作業處理（OracleParallelExecution）主要是將一個大型的作業分解為多個獨立的部份，使的原本必須循序完成的大的作業可以由多個分工作同時完成此項作業，也就是說一般SQL語法預設是在單顆CPU上以序列（Serial）方式執行，就算是此系統上擁有多顆CPU，對於一句SQL語法而言還是無法利用到其他的CPU，如此就無法有效的善用系統資源

而Oracle並行作業處理就是為了能夠讓SQL語法善用系統所擁有的資原因應而生，但需注意的是使用Oracle並行作業處理的時機與前提是在處理一個很大的作業，如線上分析系統（OnlineAnalyticalProcessing；OLAP），較不適合使用在多使用者資料庫架構上，如線上即時交易系統（OnlineTransactionProcessing；OLTP），因為在此資料庫架構下所有的資料庫作業都是屬於小且快的作業，因此系統本身資源負擔就已經相當大。

如果啟用Oracle並行作業處理將會導致某個使用者連線佔用了所有的系統資源，使得其他使用者連線不得不去等待，甚至於導致Oracle為了將工作分解為多個獨立的部份而全面使用FullTableScan，基於這些種種原因，在OLTP架構下啟用Oracle並行作業處理反而可能會導致整體系統效能下降的情況，因此一般OLTP系統不建議使用。

而線上分析系統（OnlineAnalyticalProcessing；OLAP）主要的作業是擷取、分析及彙總大量的資料，因此可以考慮使用Oracle並行作業處理，但還是再次強調系統資源要足夠，如果本身的CPU、記憶體或是磁碟數量不足，Oracle並行作業處理對於整體效能是沒有多大的幫助。

本文將會針對Oracle並行作業處理的相關應用做詳盡的解說。

Oracle並行作業處理架構

Oracle並行作業處理（OracleParallelExecution）的架構基本上可分為兩類主要的角色也可稱為程序（Process）分工合作共同完成，而這兩個角色的功能分別依序如下：

●SlaverProcess：

在Oracle並行作業處理的架構下，主要是由一群SlaverProcess將工作分散為多個小的作業同時執行，每一個SlaverProcess負責一小部分，而這一群SlaverProcess稱為一個SlaverProcessSet，假設有一句SQL語法是針對Table1執行FullTableScan，而這一群SlaverProcess會將這全表格掃描作業分解為多個獨立的部分共同完成。

●ParallelExecutionCoordinator：

ParallelExecutionCoordinator以下簡稱Coordinator，當使用者執行SQL語法時，會由Coordinator將這句SQL語法的資料庫作業分為多個工作，同時對Oracle申請SlaverProcessSet，並決定要由多少個SlaverProcess來共同執行這項作業，除此之外Coordinator還會收集每個SlaverProcess所得到的結果，並將這些結果會總起來回覆給使用者；此部分須注意的是當Coordinator分配多個SlaverProcess去處理SQL語法時，這分配的數量稱為並行度（DegreeofParallelism；DOP），在一句SQL語法的執行過程中，最多有兩組SlaverProcessSet，這兩組SlaverProcessSet所擁有的SlaverProcess數量一定是一模一樣，因此SlaverProcess的數量最多可以是並行度的兩倍。

原則上Coordinator會將一個大資料庫作業劃分為多個工作單位並交由SlaverProcess處理，而工作劃分方式一般有兩種，如果此物件是分區物件（PartitionObject）如PartitionTable，則會將以分區為劃分的工作單位交由SlaverProcess作業，另一種是以物件的資料區塊範圍劃分，也就是說每個劃分的工作是以部份資料區塊為工作單位，所以每個SlaverProcess就負責此物件的部份資料區塊進行作業。

SlaverProcess與Coordinator基本作業方式如圖1所示。

▲圖1：

Oracle並行作業處理基本架構圖-1

圖1是以全表格掃描（FullTableScan）為範例，但在一般資料庫的作業中除了全表格掃描外，其他的作業如排序作業等，也可以使用Oracle並行作業處理，也就是說當有其他作業的需求時，Oracle將會多出一組SlaverProcessSet來處理相關的並行作業，舉一個例子當使用者執行的SQL語法有排序的需求時的執行計畫如圖2。

▲圖2：

基本排序執行計畫

以上的SQL語法中含有排序作業，由以上的執行計畫可得知Oracle再處理整個作業包含了3個部份分別是，全表格掃描、排序與返回結果給使用者，而當並行作業啟用時Coordinator會執行以下的步驟：

1.解析SQL語法並決定並行度。

2.決定與分配SlaverProcessSet。

3.將相對應的SQL指令傳送送給SlaverProcess，並進行相關的作業。

4.將最終結果傳送給使用者。

而以上的範例將會有兩組SlaverProcessSet，一組負責全表格掃描，另一組負責排序作業，整個並行處理架構如圖3所示。

▲圖3：

Oracle並行作業處理基本架構圖-2

以上的圖示顯示Coordinator•將會建立兩組SlaverProcessSet分別是SlaverProcessSet1與SlaverProcessSet2，而每組的SlaverProcess數量將由並行度決定，整個運作的過程首先是SlaverProcessSet1先對TABLE1作全表格掃描，並將結果提供給SlaverProcessSet2建立雜湊表格（HashTable）並進行相關的排序作業。

以這樣的運作方式來看SlaverProcessSet1就是所謂的資料產生者（Producer），SlaverProcessSet2就是資料消費者（Consumer），而中間的關連就是建立在雜湊表格（HashTable），這兩組SlaverProcessSet與Coordinator之間的資料傳遞就稱為Inter-OperationParallelism，而每一組SlaverProcessSet的內部並行度運作稱為Intra-OperationParallelism。

當SQL語法有排序或表格連結等作業時，會有兩組SlaverProcessSet存在，而資料產生者、資料消費者與Coordinator之間的資料傳遞都是在Oracle的記憶體中運作，也就是說Inter-OperationParallelism的作業是在SGA中完成的，這塊在SGA中使用的記憶體區塊稱為TableQueue。

而這些作業對TableQueue有以下三種狀況：

●當資料產生者向TableQueue內寫資料時，此作業稱為PXSEND。

●當資料消費者從TableQueue內讀取資料時，此作業稱為PXRECEIVE。

●當Coordinator從TableQueue內讀取資料時，此作業稱為PXCOORDINATOR。

以上的三種狀況可以由Oracle並行作業處理的執行計畫明確的顯示出來，如圖4所示。

▲圖4：

Oracle並行作業處理執行計畫示意圖

以上Oracle並行作業處理執行計畫可分為三個部份來看，分別是ID5、ID5與ID7為第一組，ID2、ID3與ID4為第二組，ID0與ID1為第三組，而這三組的作業內容與順序依序如下：

1.第一組主要負責對TABLE1執行全表格掃描，而ID5此步驟就是將第一組所產生的結果傳遞給第二組，此組的運作是利用並行的方式執行。

2.第二組主要是將這些結果作雜湊運算，而ID2此步驟主要是將這雜湊運算的結果傳遞給第一組，此組的運作是利用並行運作，此組的運作是利用並行的方式執行。

3.第三組主要就是Coordinator的運作，也就是將結果傳送給使用者，此組的運作是利序列（Serial）方式執行。

以上這三組的作業就是之前所提的Inter-OperationParallelism作業，而Oracle並行作業處理執行計畫重點解說如下所列：

●Operation：

此欄位顯示此SQL語法的執行作業，如同上面所敘述的PXSEND代表資料產生者向TableQueue內寫資料等等。

●Name：

此欄位顯示此執行作也所用到的物件名稱，TQxxxxx代表的是TableQueue。

●TQ：

此欄位代表TableQueue的值，相同的值所對應到的是同一組SlaverProcessSet，而以上的範例顯示一共有兩組SlaverProcessSet。

●IN-OUT：

此欄位代表各種作業之間的相互關係，與並行作業相關的訊息如下所列。

●P.jpg'>S：

全名是ParalleltoSerial，代表此作業是由並行作業（Parallel）傳遞資料到序列作業（Serial），如SlaverProcess到Coordinator。

●P.jpg'>P：

全名是ParalleltoParallel，代表此作業是由一組並行作業（Parallel）傳遞資料到另一組並行作業（Parallel），如SlaverProcessSet1到SlaverProcessSet2。

●PCWP：

全名是ParallelCombinedWithParent，代表此並行作業是合併父作業，並由同一組SlaverProcessSet完成，因此不需要Process之間的資料交換處理。

●PCWC：

全名是ParallelCombinedWithChild，代表此並行作業是合併子作業，並由同一組SlaverProcessSet完成，因此不需要Process之間的資料交換處理。

●PQDistrib：

此欄位代表資料產生者、資料消費者與Coordinator之間資料傳遞的方式，而主要資料發送傳遞的方式如下所列。

●QC（ORDER）：

此類的資料傳遞方式是以循序的方式發送，而不是隨機的選擇，如以上的例子就是當排序完成後，資料產生者以循序的方式發送給Coordinator，一般來說此類的資料傳遞方式會出現在有ORDERBY的語句中。

●QC（RANDOM）：

此類的資料傳遞方式是以隨機的選擇發送，也就是說資料產生者沒有任何的順序以隨機的方式的方式發送給Coordinator，一般來說此類的資料傳遞方式會出現在有ORDERBY的語句中。

●RANGE：

此類的資料傳遞方式是按照一定的規則劃分為多個區塊，分別發送給不同的資料消費者。

●ROUND-ROBIN：

此類的資料傳遞方式是每個資料產生者輪流的向每個資料消費者發送一筆資料，如此資料就可以很平均的分散在每一個資料消費者。

●HASH：

此類的資料傳遞方式是每個資料產生者都會利用雜湊運算得到一個雜湊值（HashValue），並根據這個雜湊值來決定發送給不同的資料消費者。

●BROADCAST：

此類的資料傳遞方式是每個資料產生者把所產生的資料以廣播的方式發送給每一個資料消費者。

Oracle並行作業處理的種類

以上介紹的是Oracle並行作業處理的基本架構與原理，而實際上Oracle並行作業處理的種類可分為三類型，這三種類型分別是並行查詢作業、並行DML作業與並行DDL作業，以下將分別介紹這三種並行查詢作業的注意事項與啟用方式。

並行查詢作業（ParallelQuery；PQ）

並行查詢作業可以將一句SQL語法分成多個不重疊片段，然後在分別在各自獨立的CPU上透過多個子查詢進行並行作業，而每個子查詢在都是屬於一個OracleProcess，在作業系統上會是以p000方式呈現，如圖5所示。

▲圖5：

並行作業Process示意圖

在圖5中的每一個Process都會負責一個子查詢並分別負責讀取表格中的一部份，當所有子查詢完畢以後，會將結果會傳遞給Coordinator匯總為最終的結果並傳送給使用者。

在此需注意的是Oracle並行查詢作業主要是根據CPU的個數來決定Process數量，一般來說當安裝Oracle資料庫時，Oracle會自動偵測伺服器上的CPU個數，並將此數量設定在參數檔內的CPU_COUNT，而Oracle並行作業預設就是使用此參數。

並行DML作業（ParallelDML；PDML）

並行DML作業主要包含Insert....select、Delete、Update與Merge，當並行作業開啟時Oracle可以使用多個並存執行伺服器來執行這些作業，如果在一個有組夠CPU與磁碟I/O的伺服器上，對於大規模的DML操作，並行DML作業能夠將執行速度大量的提升，因此並行DML作業相當的適合在資料倉庫重作大量資料的批次更新。

而並行DML作業的方式是主要是將每個並行DML作業中的Process都當成一個獨立的Transaction，而每個獨立的部份都會分配到表格的一部份單獨的來執行DML作業，因此每個Process都有自己獨立的倒回區段（UndoSegment），當這些Process都完成作業後會統一交由Coordinator來進行全部Process的commit，如圖6所示。

▲圖6：

並行DML作業

並行DML作業比並行查詢作業稍微複雜，因為在一個正常DML的作業中所牽涉到的問題如BeforeImage/AfterImage與Commit/Rollback等遠比單純的查詢所要考慮的因素來的多，因此並行DML作業在執行上會有所限制，限制的項目如下所列：

●並行DML作業的功能預設是被關閉，只能使用AlterSession的方式啟用，並在DML語法內加上Hint指令使用並行DML作業，相關語法在下一個小節會陸續介紹。

●並行DML作業不支援觸發器（Trigger）。

●並行DML作業不支援遠端資料庫作業也就是DBLink。

●並行DML作業不支援OracleReplication。

●並行DML作業不支援叢集表格（ClusterTable）。

●並行DML作業不支援延遲完整性限制（DeferredIntegrityConstraint）、自我關聯完整性限制（self-referentialintegrityConstraint），在某些情況下也不會支援完整性限制等。

●並行DML作業不支援一般表格上LOB屬性的欄位，但支援分區表格（PartitionTable）上是LOB屬性的欄位。

●並行DML作業不支援BitmapIndex索引在一般表格上，但支援BitmapIndex在分區表格（PartitionTable）上。

●並行DML作業不支援DeleteCascade語法。

●並行DML作業不支援Direct-PathInsert。

並行DDL作業（ParallelDDL；PDDL）

如果並行查詢作業與並行DML作業由所提供的作業功能來看，是較為貼近使用者的需求，那並行DDL作業就是專門為資料庫管理員設計的功能，因為並行DDL作業主要是提供資料庫管理員，在日常維護與管理資料庫的工作上能夠充分的利用系統所有的資源並加快其作業的速度，而並行DDL作業可提供以下的DDL語法。

並行DDL作業支援一般表格的作業如下所列：

●CREATEINDEX：

在此作業的過程中對表格的掃描、資料的排序等都可以並行作業。

●CREATETABLE...ASSELECT：

此作業過程中查詢的作業與資料載入到新的表格中會使用並行作業。

●ALTERINDEX...REBUILD：

通常資料庫管理員在索引結構不佳的狀態下，會使用此語法重建索引，但在重建的過程中會使得此索引或是此表格的SQL語法效能不佳，如使用並行DDL作業，在索引重建的過程中可以讓多個Process同時並行作業，加快索引建置的速度。

並行DDL作業支援分區表格（PartitionTable）的作業如下所列：

●CREATEINDEX：

在此作業的過程中對分區表格的掃描、資料的排序等都可以並行作業。

●CREATETABLE...ASSELECT：

此作業過程中查詢的作業與資料載入到新的表格中會使用並行作業。

●ALTERTABLE...MOVEPARTITION：

此語法主要用於分區表格中把資料搬移到其他的Partition，如此可以減少資料碎片（DataFragmentation），如使用並行DDL作業，在重整的過程中可以讓多個Process同時並行作業，加快重整速度。

MOVEPARTITION須注意的是此作業將會影響所有的索引，讓索引失效必須重新Rebuild。

●ALTERTABLE...SPLITPARTITION：

將原本的Partition分割為多個Partition，如使用並行DDL作業，在分割的過程中可以讓多個Process同時並行作業，加快分割速度。

●ALTERTABLE...COALESCEPARTITION：

此語法主要是主要是將Partition整併，如使用並行DDL作業，在Partition整併的過程中可以讓多個Process同時並行作業，加快整併速度。

●ALTERINDEX...REBUILDPARTITION：

此語法主要是重新建置分區表格上的索引，也可以將索引搬移到另一個Partition，如使用並行DDL作業，可加快重建速度。

●ALTERINDEX...SPLITPARTITION：

把原本個GlobalIndex的一個Partition分為兩個Partition，如使用並行DDL作業，在分割的過程中可以讓多個Process同時並行作業，加快分割速度。

Oracle並行作業處理的啟用與關閉方式

啟用Oracle並行作業主要有三種方式，依序如下。

針對物件啟用/關閉Oracle並行作業：

此種方式可在建置物件時啟用相關的並行作業，也可以使用Alter的語法設定，相關的語法如下所示。

●建置表格時啟用並行作業的相關語法範例如下：

●建置索引時啟用並行作業的相關語法範例如下：

●使用CREATETABLE...ASSELECT時啟用並行作業的相關語法範例如：

「

●使用Alter語法將表格或索引啟用並行作業的相關語法範例如下：

●使用Alter語法將表格或索引關閉並行作業的相關語法範例如下：

上述方式是將並行度（DegreeofParallelism；DOP）設定為1，除此之外還可使用NOPARALLEL參數，語法範例如下：

針對使用者連線啟用/關閉Oracle並行作業：

此種方式可在建置物件時啟用相關的並行作業，也可以使用Alter的語法設定。

使用ALTERSESSION方式啟用與關閉並行查詢作業的相關語法範例如下：

使用ALTERSESSION方式強制啟用並行查詢作業與指定並行度的相關語法範例如：

「ALTERSESSIONFORCEPARALLELQUERYPARALLEL4;」。

使用ALTERSESSION方式啟用與關閉並行DML作業的相關語法範例如下：

使用ALTERSESSION方式強制啟用並行DML作業與指定並行度的相關語法範例如：

「ALTERSESSIONFORCEPARALLELDMLPARALLEL4;」。

啟用並行DML作業之後相關DML語法內需使用HINT，相關語法範例如下所列。

1.Insert…Select語法如下：

2.Update語法如下：

UPDATE/*+PARALLEL（TEST_A4）TEST_ASETCOL1=COL1+1;

除了上述單純update語法外，還結合子查詢使用並行作業。

須注意的是當PARALLEL沒有指定並行度時則代表是使用預設的並行度：

3.Delete語法如下：

使用ALTERSESSION方式啟用與關閉並行DDL作業的相關語法範例如下：

使用ALTERSESSION方式強制啟用並行DDL作業與指定並行度的相關語法範例如：

ALTERSESSIONFORCEPARALLELDDLPARALLEL4;

針對SQL語法啟用Oracle並行作業：

此種方式是透過HINT的方式啟用並行作業，但需注意的是透過HINT的方式啟用並行作業，主要是告訴OracleOptimizer可以使用並行作業，並不是強制要求OracleOptimizer一定要是用並行作業，因為OracleOptimizer會根據分析選擇最小的成本運作，而並行作業相關的HINT一共有四種依序如下

●/*+PARALLEL（TABLENAMEDOP）*/相關語法如：

「SELECT/*+PARALLEL（TABLE14）*/*FROMTABLE1;」。

●/*+NO_PARALLEL（TABLENAMEDOP）*/相關語法如：

「SELECT/*+NO_PARALLEL（TABLE14）*/*FROMTABLE1;」。

●/*+PARALLEL_INDEX（INDEXDOP）*/相關語法如：

「SELECT/*+PARALLEL_INDEX（INDEX14）*/*FROMTABLE1;」。

●/*+NO_PARALLEL_INDEX（INDEXDOP）*/相關語法如：

「SELECT/*+NO_PARALLEL_INDEX（INDEX14）*/*FROMTABLE1;」。

除了上述的這些指另外還可以在SQL*Loader與資料庫回復中使用並行處理作業。

SQL*Loader語法使用並行處理作業如下：

資料庫回復中使用並行處理作業：

Oracle並行作業處理相關設定

前面提到Oracle並行作業處理的開啟、關閉與使用方式，除此之外在Oracle並行作業處理還有一些相關的參數可調整並行的整體運作，相關參數依序如下：

●PARALLEL_MAX_SERVERS

此參數主要是限制並行作業最大的並行度，由於Oracle的並行作業是依照伺服務器上CPU的數量為基準，而實際的預設值會按照資料庫的版本有所不同，如表1所示，也就是說PARALLEL_MAX_SERVERS此參數一般預設與伺服務器上CPU的數量緊密的關連，但如果此台伺服器上有多個資料庫運作時，則並行作業的運作將會占據所有CPU的資源，所以可使用此參數來設定一個合理的數值，有效的運用系統資源，如果PARALLEL_MAX_SERVERS設定為0時代表此資料庫禁止使用並行作業。