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

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

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

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

Oracle並行作業處理架構

Oracle並行作業處理（Oracle Parallel Execution）的架構基本上可分為兩類主要的角色也可稱為程序（Process）分工合作共同完成，而這兩個角色的功能分別依序如下：
●Slaver Process：在Oracle並行作業處理的架構下，主要是由一群Slaver Process將工作分散為多個小的作業同時執行，每一個Slaver Process負責一小部分，而這一群Slaver Process稱為一個Slaver Process Set，假設有一句SQL語法是針對Table1執行Full Table Scan，而這一群Slaver Process會將這全表格掃描作業分解為多個獨立的部分共同完成。
●Parallel Execution Coordinator：Parallel Execution Coordinator以下簡稱Coordinator，當使用者執行SQL語法時，會由Coordinator將這句SQL語法的資料庫作業分為多個工作，同時對Oracle申請Slaver Process Set，並決定要由多少個Slaver Process來共同執行這項作業，除此之外Coordinator還會收集每個Slaver Process所得到的結果，並將這些結果會總起來回覆給使用者；此部分須注意的是當Coordinator分配多個Slaver Process去處理SQL語法時，這分配的數量稱為並行度（Degree of Parallelism；DOP），在一句SQL語法的執行過程中，最多有兩組Slaver Process Set，這兩組Slaver Process Set所擁有的Slaver Process數量一定是一模一樣，因此Slaver Process的數量最多可以是並行度的兩倍。

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


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

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


▲圖2：基本排序執行計畫

以上的SQL語法中含有排序作業，由以上的執行計畫可得知Oracle再處理整個作業包含了3個部份分別是，全表格掃描、排序與返回結果給使用者，而當並行作業啟用時Coordinator會執行以下的步驟：
1. 解析SQL語法並決定並行度。
2. 決定與分配Slaver Process Set 。
3. 將相對應的SQL指令傳送送給Slaver Process，並進行相關的作業。
4. 將最終結果傳送給使用者。

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


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

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

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

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

而這些作業對Table Queue有以下三種狀況：
●當資料產生者向Table Queue內寫資料時，此作業稱為PX SEND。
●當資料消費者從Table Queue內讀取資料時，此作業稱為PX RECEIVE。
●當Coordinator從Table Queue內讀取資料時，此作業稱為PX COORDINATOR。

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


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

以上Oracle並行作業處理執行計畫可分為三個部份來看，分別是ID 5、ID 5與ID 7為第一組，ID 2、ID 3與ID 4為第二組，ID 0與ID 1為第三組，而這三組的作業內容與順序依序如下：
1. 第一組主要負責對TABLE1執行全表格掃描，而ID 5此步驟就是將第一組所產生的結果傳遞給第二組，此組的運作是利用並行的方式執行。
2. 第二組主要是將這些結果作雜湊運算，而ID 2此步驟主要是將這雜湊運算的結果傳遞給第一組，此組的運作是利用並行運作，此組的運作是利用並行的方式執行。
3. 第三組主要就是Coordinator的運作，也就是將結果傳送給使用者，此組的運作是利序列（Serial）方式執行。

以上這三組的作業就是之前所提的Inter-Operation Parallelism作業，而Oracle並行作業處理執行計畫重點解說如下所列：
●Operation：此欄位顯示此SQL語法的執行作業，如同上面所敘述的PX SEND代表資料產生者向Table Queue內寫資料等等。
●Name：此欄位顯示此執行作也所用到的物件名稱，TQxxxxx代表的是Table Queue。
●TQ：此欄位代表Table Queue的值，相同的值所對應到的是同一組Slaver Process Set，而以上的範例顯示一共有兩組Slaver Process Set。
●IN-OUT：此欄位代表各種作業之間的相互關係，與並行作業相關的訊息如下所列。
●P.jpg'>S：全名是Parallel to Serial，代表此作業是由並行作業（Parallel）傳遞資料到序列作業（Serial），如Slaver Process到Coordinator。
●P.jpg'>P：全名是Parallel to Parallel，代表此作業是由一組並行作業（Parallel）傳遞資料到另一組並行作業（Parallel），如Slaver Process Set 1到Slaver Process Set 2。
●PCWP：全名是Parallel Combined With Parent，代表此並行作業是合併父作業，並由同一組Slaver Process Set完成，因此不需要Process之間的資料交換處理。
●PCWC：全名是Parallel Combined With Child，代表此並行作業是合併子作業，並由同一組Slaver Process Set完成，因此不需要Process之間的資料交換處理。
●PQ Distrib：此欄位代表資料產生者、資料消費者與Coordinator之間資料傳遞的方式，而主要資料發送傳遞的方式如下所列。
●QC（ORDER）：此類的資料傳遞方式是以循序的方式發送，而不是隨機的選擇，如以上的例子就是當排序完成後，資料產生者以循序的方式發送給Coordinator，一般來說此類的資料傳遞方式會出現在有ORDER BY的語句中。
●QC（RANDOM）：此類的資料傳遞方式是以隨機的選擇發送，也就是說資料產生者沒有任何的順序以隨機的方式的方式發送給Coordinator，一般來說此類的資料傳遞方式會出現在有ORDER BY的語句中。
●RANGE：此類的資料傳遞方式是按照一定的規則劃分為多個區塊，分別發送給不同的資料消費者。
●ROUND-ROBIN：此類的資料傳遞方式是每個資料產生者輪流的向每個資料消費者發送一筆資料，如此資料就可以很平均的分散在每一個資料消費者。
●HASH：此類的資料傳遞方式是每個資料產生者都會利用雜湊運算得到一個雜湊值（Hash Value），並根據這個雜湊值來決定發送給不同的資料消費者。
●BROADCAST：此類的資料傳遞方式是每個資料產生者把所產生的資料以廣播的方式發送給每一個資料消費者。

Oracle並行作業處理的種類

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

並行查詢作業（Parallel Query； PQ）

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


▲圖5：並行作業Process示意圖

在圖5中的每一個Process都會負責一個子查詢並分別負責讀取表格中的一部份，當所有子查詢完畢以後，會將結果會傳遞給Coordinator匯總為最終的結果並傳送給使用者。在此需注意的是Oracle並行查詢作業主要是根據CPU的個數來決定Process數量，一般來說當安裝Oracle資料庫時，Oracle會自動偵測伺服器上的CPU個數，並將此數量設定在參數檔內的CPU_COUNT，而Oracle並行作業預設就是使用此參數。

並行DML作業（Parallel DML； PDML）

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

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


▲圖6：並行DML作業

並行DML作業比並行查詢作業稍微複雜，因為在一個正常DML的作業中所牽涉到的問題如Before Image/After Image與Commit/Rollback等遠比單純的查詢所要考慮的因素來的多，因此並行DML作業在執行上會有所限制，限制的項目如下所列：
●並行DML作業的功能預設是被關閉，只能使用Alter Session的方式啟用，並在DML語法內加上Hint指令使用並行DML作業，相關語法在下一個小節會陸續介紹。
●並行DML作業不支援觸發器（Trigger）。
●並行DML作業不支援遠端資料庫作業也就是DB Link。
●並行DML作業不支援Oracle Replication。
●並行DML作業不支援叢集表格（Cluster Table）。
●並行DML作業不支援延遲完整性限制（Deferred Integrity Constraint）、自我關聯完整性限制（self-referential integrity Constraint），在某些情況下也不會支援完整性限制等。
●並行DML作業不支援一般表格上LOB屬性的欄位，但支援分區表格（Partition Table）上是LOB屬性的欄位。
●並行DML作業不支援Bitmap Index索引在一般表格上，但支援Bitmap Index在分區表格（Partition Table）上。
●並行DML作業不支援Delete Cascade語法。
●並行DML作業不支援Direct-Path Insert。

並行DDL作業（Parallel DDL；PDDL）

如果並行查詢作業與並行DML作業由所提供的作業功能來看，是較為貼近使用者的需求，那並行DDL作業就是專門為資料庫管理員設計的功能，因為並行DDL作業主要是提供資料庫管理員，在日常維護與管理資料庫的工作上能夠充分的利用系統所有的資源並加快其作業的速度，而並行DDL作業可提供以下的DDL語法。 並行DDL作業支援一般表格的作業如下所列：
●CREATE INDEX：在此作業的過程中對表格的掃描、資料的排序等都可以並行作業。
●CREATE TABLE... AS SELECT：此作業過程中查詢的作業與資料載入到新的表格中會使用並行作業。
●ALTER INDEX ... REBUILD：通常資料庫管理員在索引結構不佳的狀態下，會使用此語法重建索引，但在重建的過程中會使得此索引或是此表格的SQL語法效能不佳，如使用並行DDL作業，在索引重建的過程中可以讓多個Process同時並行作業，加快索引建置的速度。

並行DDL作業支援分區表格（Partition Table）的作業如下所列：
●CREATE INDEX：在此作業的過程中對分區表格的掃描、資料的排序等都可以並行作業。
●CREATE TABLE...AS SELECT：此作業過程中查詢的作業與資料載入到新的表格中會使用並行作業。
●ALTER TABLE ... MOVE PARTITION：此語法主要用於分區表格中把資料搬移到其他的Partition，如此可以減少資料碎片（Data Fragmentation ），如使用並行DDL作業，在重整的過程中可以讓多個Process同時並行作業，加快重整速度。MOVE PARTITION須注意的是此作業將會影響所有的索引，讓索引失效必須重新Rebuild。
●ALTER TABLE ... SPLIT PARTITION：將原本的Partition分割為多個Partition，如使用並行DDL作業，在分割的過程中可以讓多個Process同時並行作業，加快分割速度。
●ALTER TABLE ... COALESCE PARTITION：此語法主要是主要是將Partition整併，如使用並行DDL作業，在Partition整併的過程中可以讓多個Process同時並行作業，加快整併速度。
●ALTER INDEX ... REBUILD PARTITION：此語法主要是重新建置分區表格上的索引，也可以將索引搬移到另一個Partition，如使用並行DDL作業，可加快重建速度。
●ALTER INDEX ... SPLIT PARTITION：把原本個Global Index的一個Partition分為兩個Partition，如使用並行DDL作業，在分割的過程中可以讓多個Process同時並行作業，加快分割速度。

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

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

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

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




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




●使用CREATE TABLE ... AS SELECT時啟用並行作業的相關語法範例如：「




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




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




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




針對使用者連線啟用/關閉Oracle並行作業：此種方式可在建置物件時啟用相關的並行作業，也可以使用Alter的語法設定。使用ALTER SESSION方式啟用與關閉並行查詢作業的相關語法範例如下：




使用ALTER SESSION方式強制啟用並行查詢作業與指定並行度的相關語法範例如：「ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;」。

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




使用ALTER SESSION方式強制啟用並行DML作業與指定並行度的相關語法範例如：「ALTER SESSION FORCE PARALLEL DML PARALLEL 4;」。

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

1. Insert…Select語法如下：




2. Update語法如下：

UPDATE /*+ PARALLEL(TEST_A 4) TEST_A SET COL1=COL1+1;

除了上述單純update語法外，還結合子查詢使用並行作業。須注意的是當PARALLEL沒有指定並行度時則代表是使用預設的並行度：




3. Delete語法如下：




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




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

ALTER SESSION FORCE PARALLEL DDL PARALLEL 4;

針對SQL語法啟用Oracle並行作業：此種方式是透過HINT的方式啟用並行作業，但需注意的是透過HINT的方式啟用並行作業，主要是告訴Oracle Optimizer可以使用並行作業，並不是強制要求Oracle Optimizer一定要是用並行作業，因為Oracle Optimizer會根據分析選擇最小的成本運作，而並行作業相關的HINT一共有四種依序如下
●/*+ PARALLEL（TABLENAME DOP）*/相關語法如：「SELECT /*+ PARALLEL（TABLE1 4）*/ * FROM TABLE1;」。
●/*+ NO_PARALLEL（TABLENAME DOP）*/相關語法如：「SELECT /*+ NO_PARALLEL（TABLE1 4）*/ * FROM TABLE1;」。
●/*+ PARALLEL_INDEX（INDEX DOP）*/相關語法如：「SELECT /*+ PARALLEL_INDEX（INDEX1 4）*/ * FROM TABLE1;」。
●/*+ NO_PARALLEL_INDEX（INDEX DOP）*/相關語法如：「SELECT /*+ NO_PARALLEL_INDEX（INDEX1 4）*/ * FROM TABLE1;」。 除了上述的這些指另外還可以在SQL*Loader與資料庫回復中使用並行處理作業。SQL*Loader語法使用並行處理作業如下：




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




Oracle並行作業處理相關設定

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

●PARALLEL_MAX_SERVERS

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


表1：PARALLEL_MAX_SERVERS

PARALLEL_MAX_SERVERS設定方式如：「PARALLEL_MAX_SERVERS=12」。如是否要設定PARALLEL_MAX_SERVERS可查詢V$PQ_SYSSTAT：




以上範例結果如圖7所示。


▲圖7：V$PQ_SYSSTAT

以上的查詢範例如果SERVER BUSY過高則需要增加PARALLEL_MAX_SERVERS此參數。主要是設定並行作業最小的並行度，也就是說當Oracle資料庫被開啟時就會維持一定的並行作業Slave Process，當並行作業的Slave Process的需求超過此參數設定時，就會動態的逐步增加，一直到達PARALLEL_MAX_SERVERS所設定的參數，當完成作業後，這些Slave Process就會逐步的縮減，設定方式如：「PARALLEL_MIN_SERVERS=4」。

如要確認Slave Process的狀況可查詢V$PX_PROCESS_SYSSTAT：「SELECT * FROM V$PX_PROCESS_SYSSTAT;」。

查詢V$PX_PROCESS_SYSSTAT主要觀察的重點依序如下所列；
●Servers In Use：目前是否有正在作業的Slave Process數量
●Servers Available：目前可用的Slave Process數量。
●Servers Started：從資料庫開啟之後啟動的Slave Process的數量。
●Servers Shutdown：從資料庫開啟之後關閉的Slave Process的數量。
●Servers Highwater：Slave Process最多曾經到達的數量。

PARALLEL_MIN_PERCENT參數主要是在強制限制Oracle的並行度維持在一定的比例，才會對相關的作業使用並行機制，這是因為當系統資源無法滿足並行度時，Oracle會自動的將並行度降低，但如果並行度降過低，甚至不使用並行作業，改由序列（Serial）作業，這可能會將整個作業的時間拖長。

如果不想讓Oracle自動的將並行度降低可設定此參數，而此參數的設定範圍是0 -100，0是預設值，代表允許Oracle自動降低並行度，100代表只有在取得所有的並行度才能執行相關的作業。此參數設定範例如：「PARALLEL_MIN_PERCENT=100;」。

此參數也可動態調整，相關語法範例如：「ALTER SYSTEM SETPARALLEL_MIN_PERCENT=100;」。

FAST_START_PARALLEL_ROLLBACK參數主要Rollback時使用並行作業加快執行的速度，而Rollback作業可從兩個方向看，分別是當資料庫不正常損毀時，Oracle會自動將未完成的作業執行Rollback，而此參數的設定方式如下。

如設定為FALSE代表ROLLBACK禁止使用並行作業，設定方式如：「FAST_START_PARALLEL_ROLLBACK=FALSE;」；如設定為HIGH代表ROLLBACK並行作業是4 x CPU_COUNT：「FAST_START_PARALLEL_ROLLBACK=HIGH;」；如設定為LOW代表ROLLBACK並行作業是2 x CPU_COUNT：「FAST_START_PARALLEL_ROLLBACK=FALSE;」。

RECOVERY_PARALLELISM參數，是設定當資料庫損毀時可使用並行作業加快回復速度，而此參數的設定值是介於2至PARALLEL_MAX_SERVERS之間。此參數設定範例如：「RECOVERY_PARALLELISM=10;」。

Oracle並行處理作業的設計就是將現有的資源在不耗費任何成本下發揮到最大的極致，進而提升資料庫的處理能力，但再次強調Oracle並行處理作業較適合資料庫倉儲系統如線上分析系統（Online Analytical Processing；OLAP），並不適合多使用者的環境如線上即時交易系統（Online Transaction Processing；OLTP），因此在使用上要反複的調整與考量。