Oracle的並行

當我們要在操作中處理大量資料，比如全表掃描大表或者建立一個大的索引，可以將這個工作透過使用多個程式來分成多個小任務來完成，這就叫作並行執行或者並行處理。並行執行當要訪問大量資料時能夠減少響應時間，但不是任何時候並行執行都比序列執行快。並行執行在以下情況下是非常有用的:
查詢要使用大表掃描，連線或分割槽索引掃描
建立大索引和大表
批次插入，更新和刪除
聚合操作

並行執行的工作原理
一個查詢提交到Oracle伺服器並解析。在最佳化時如果考慮使用並行執行，那麼在執行時間使用者影子程式將成為查詢協調者(QC)並且根據需要為會話分配並行子程式。QC根據並行子程式的數量並使用基於rowid範圍或分割槽（從8i開始）來將工作進行分解。為了達到平均分佈工作量，一個簡單的分佈演算法被使用。生產者讀取資料並將資料儲存在表佇列中由消費者或者QC來從表佇列中讀取資料。

如果SQL語句要執行排序，那麼由消費者並行子程式從生產者相關的表佇列中讀取資料並且進行排序並將排序後的資料儲存到與消費者並行子程式相關的新表佇列中。這些佇列然後將由QC程式進行讀取。

如果SQL語句不執行排序，那麼QC將直接讀取生產者子程式相關的表佇列。

Query Coordinator(QC)查詢協調者:由會話派生出來的前臺程式，用來從查詢子程式中接收資料

Slaves:子程式從磁碟或表佇列結構（也可能是其它的子程式）中讀取資料並且將資料儲存到它們自己的表佇列中。當子程式從磁碟讀取資料時，將會執行直接I/O路麼讀。這意味著將會跨過buffer cache。事實上，子程式將會強制將已經被更新但還沒有被重新整理到磁碟的資料塊從buffer cache中重新整理到磁碟，然後使用直接路徑I/O來讀取資料。

子程式將等待資料進入佇列和離開佇列的訊息。有兩種型別的子程式
生產者和消費者:
生產者子程式根據QC所給定的rowid範圍或分割槽來檢視資料塊和檢索相關資料。然後這些資料會被儲存到表佇列中由QC或消費者子程式來進行讀取

消費者子程式當需要從由生產者子程式填充的表佇列中讀取資料時才產生。讀取資料後經過消費者子程式處理後返回給QC處理。

在一個最簡單的查詢中(資料不需要進行排序），那麼不需要產生消費者程式並且QC會直接從生產者子程式表佇列中讀取資料。

出現生產者和消費者的原因是因為當並行查詢需要排序時並行度有時要求雙倍數量的查詢子程式。

SQL> select /*+ parallel */ * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |   532 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| EMP      |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2


Statistics
----------------------------------------------------------
        130  recursive calls
          4  db block gets
        155  consistent gets
         18  physical reads
          0  redo size
       1401  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
         14  rows processed

上面的查詢沒有要求排序所以只產生的生產者，也只有一個表佇列TQ10000,一組並行子程式就是生產者子程式

SQL> select /*+ parallel */ * from scott.emp order by ename;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3979194000

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    14 |   532 |     3  (34)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)    | :TQ10001 |    14 |   532 |     3  (34)| 00:00:01 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY        |          |    14 |   532 |     3  (34)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE      | :TQ10000 |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   6 |       PX BLOCK ITERATOR |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| EMP      |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2


Statistics
----------------------------------------------------------
         19  recursive calls
          4  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
       1406  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         14  rows processed

上面的查詢要求排序，所以出現了生產者與消費者，生產者表佇列為:TQ10000，消費者表佇列為TQ10001

子程式從表佇列中等待離隊資訊時，'PX Deque wait'等待是並行查詢正常操作的一部分。子程式在工作之前必須等待離隊的訊息才能開始進行。

Table Queues(TQ)佇列它允許程式將行記錄傳送給其它程式。比如資料從生產者子程式傳送到消費者子程式。消費者子程式也可以將資料傳送給QC。

與並行執行相關的資料庫引數
parallel_max_servers
一個例項可以使用的最大並行子程式數，如果為0則意味著不能並行執行語句

parallel_min_servers
例項在啟動時產生的最小並行子程式數，這些子程式在例項的生命週期內都是被保留存在的。

parallel_min_percent
如果最佳化器已經決定了查詢將以並行方式來執行，但執行時系統沒有足夠的資源來滿足所請求的並行度，那麼預設情況下，查詢將以序列方式來執行並且不會給使用者返回任何資訊。這就很容易造成一個查詢的執行時間非常長。這個引數提供了一個方法當沒有足夠資源可用時來阻查詢以序列方式來執行並顯示錯誤資訊。它定義了一個並行查詢可以執行的最小資源數。它用可以並行執行子程式的百分比
來表示。

如果這個引數沒有被設定且並行執行所需要的資源不能滿足那麼查詢將會以序列方式來執行。
如果這個引數被設定並且期望的查詢子程式的百分比不滿足，那麼將會報錯(ORA-12827)而不是序列執行語句。

如果並行執行需要的資源不可用，當parallel_min_percent=0時，語句將會序列執行而不會報錯。當parallel_min_percent=50，意味著查詢執行是最佳並行執行時間的2倍是可以接受的。當parallel_min_percent=100，意味著除非並行查詢所需要的資源完全可用才會並行執行否則會報錯ORA-12827。

parallel_adaptive_multi_user
當parallel_adaptive_multi_user設定為true時，啟用自適應演算法來使用並行執行提高多使用者環境中的效能。演算法會根據查詢開始執行時間的系統負載來自動減少所請求的並行度。實際上有效的並行度是基於預設並行度，或都表的並行度或hints除以減小因子。

例如:在一個有17個CPU的主機上預設並行度可以被設定為32。如果使用者執行一個並行查詢，得到的並行度是32，能有效的使用系統中的所有CPU和記憶體。當第二個使用者登入系統，並執行一個並行行查詢,得到的並行度將是16，當系統中的使用者增加時，演算法將繼續減小並行度直到使用者使用的並行度等於1為止，也就是系統有32個使用者登入時。

parallel_automatic_tuning
設定parallel_automatic_tuning引數將會對PX引數有影響,當parallel_automatic_tuning=false時:
parallel_executon_message_size的預設值為2Kbyte
parallel_adaptive_multi_user的預設值為false
large_pool_size不受影響
prcoesses不受影響
parallel_max_servers 5

當parallel_automatic_tuning=true時:
parallel_executon_message_size的預設值為4Kbyte
parallel_adaptive_multi_user的預設值為true
large_pool_size 將基於其它條種引數來進行復雜計算來計算出增加值
prcoesses 如果processes parallel_max_servers 如果parallel_adaptive_multi_user=true時
(cpus * parallel_threads_per_cpu * _parallel_adaptive_max_users * 5)，否則
(cpus * parallel_threads_per_cpu * _parallel_adaptive_max_users * 8)

parallel_theads_per_cpu預設值依賴於作業系統，預設值為2

parallel_force_local
parallel_force_local引數控制RAC環境中的並行執行。預設情況下，最佳化器可以從RAC中的任何節點或所有節點中選擇並行執行SQL語句的並行程式。當parallel_force_local設定為true時，那麼並行程式就只能是與查詢協調者(執行sql語句的節點)在同一個RAC節點中，也就是說並行程式是不能跨節點的.

parallel_degree_policy
parallel_degree_policy引數可以被設定為manual,auto或limited在Oracle11.1中parallel_degree_policy預設設定為manual(禁用了automatic degree of parallelism,statement queuing與in-memory parallel execution)

parallel_min_time_threshold
parallel_min_time_threshold引數用來指定SQL語句是否並行執行一個閾值，也就是當最佳化器根據統計資訊所估算的執行時間如果大於這個引數值就是使用並行，如果估算的執行時間小於這個引數值就會序列執行。這個引數值預設值是10秒。並且自動並行度只要在parallel_degree_policy引數被設定為auto或limited時才會生效。

parallel_degree_limit
使用自動並行度時，Oracle會自動決定是否以並行方式來執行SQL語句以及所使用的並行度。最佳化根據語句所請求的資源來決定一個語句的並行度。然而最佳化器所使用的並行度是受限制的以防止並行程式擊垮系統。也就是是系統中所能使用的並行度的上限為parallel_degree_limit引數值。它有三個引數值可以選擇：
CPU
最大並行度由系統中的CPU數量來限制。其計算公式為
parallel_degree_limit=parallel_thread_per_cpu*cpu_count當然，你也可以將parallel_degree_limit的值設定為一個具體的值，以達到明確控制實際並行度的目的。

IO
最佳化器能使用的最大並行度由系統的I/O能力來限制。這個值等於系統總吞吐量除以每個程式的最大I/O頻寬。但在Oracle 11.2中為了將parallel_degree_limit設定為IO必須執行dbms_resource_manager.calibrate_io過程來收集系統的I/O統計資訊。這個過程將會計算系統的總吞吐量與每個程式的最大IO頻寬。

具體數字
當自動並行度被啟用時，指定一個SQL語句所能使用的最大並行度。這個引數只有當parallel_degree_policy設定為auto或limited時才生效。

parallel_servers_target
Parallel_servers_target用於控制在自動並行被完全開啟的情況下，待並行執行的目標SQL是立即被執行還是進入並行執行佇列，它的預設值為4*cpu_count*parallel_threads_per_cpu（在Oracle 11gr2中，如果你開啟了SGA和PGA的自動調整，則parallel_servers_target的預設值為8*cpu_count*parallel_threads_per_cpu）。

最佳化器
並行執行只能用於CBO。在有些情況下,當表或索引有非0並行度時將會強制使用CBO。有以下hint影響並行執行:
.parallel
.noparallel
.pq_distribute
.parallel_index
.noparallel_index

如果使用RBO，那麼任何並行hints都會被忽略。

如何判斷語句是否使用並行執行
1.檢查執行計劃
如果沒有並行的相關資訊，那麼沒有使用並行執行。檢查plan table中由並行子程式所使用的其它列。如果SQL包含hints比如/*+ rowid(a1) */並且這些hints沒有出現在原始碼中，那麼可能是並行查詢所使用的。ROWID hint是一種內部方法用於處理並行查詢。注意ROWID hint有特定的意思，意味首跨過了buffer cache。事實上它會造成buffer被重新整理到磁碟因此對基本的資料檔案可以執行直接
I/O。

有兩個指令碼用來格式化plan_table的查詢。一個用來格式化序列執行計劃的輸出(utlxpls.sql)，這些指令碼都可以到$ORACLE_HOME/rdbms/admin/目錄下找到。為了避免截斷輸出執行以下設定
'set charwidth 108' in svrmgrl
'set linesize 108' in SQL*Plus

2.執行查詢

SQL> select * from v$pq_sesstat; 

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               0             0
Server Threads                          0             0
Allocation Height                       0             0
Allocation Width                        0             0
Local Msgs Sent                         0             0
Distr Msgs Sent                         0             0
Local Msgs Recv'd                       0             0
Distr Msgs Recv'd                       0             0

11 rows selected.

SQL> select count(*) from t1;

  COUNT(*)
----------
  22040576

SQL> select * from v$pq_sesstat; 

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          2             0
Allocation Height                       1             0
Allocation Width                        2             0
Local Msgs Sent                        29            29
Distr Msgs Sent                        29            29
Local Msgs Recv'd                      29            29
Distr Msgs Recv'd                      29            29

11 rows selected.

上面查詢資訊的第一行可以看到這個會話的最後一個查詢是並行執行。

3.檢查子程式活動檢視
查詢v$pq_slave兩次

SQL> SELECT slave_name,status, cpu_secs_total FROM v$pq_slave;

SLAV STAT CPU_SECS_TOTAL
---- ---- --------------
PZ99 IDLE              0

SQL> select count(*) from t1;

  COUNT(*)
----------
  22040576
SQL> SELECT slave_name,status, cpu_secs_total FROM v$pq_slave;

SLAV STAT CPU_SECS_TOTAL
---- ---- --------------
P000 BUSY              0
PZ99 IDLE              0

如果沒有返回記錄，說明沒有並行子程式執行。如果在2次執行之間CPU利用率沒有差異，那麼在這期間沒有CPU活動。

可以透過v$session_wait來查詢PQ活動,對於8以下的版本可以執行以下查詢

SQL> SELECT sid, event, seq#,p1,p2,p3, wait_time FROM v$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
       SID EVENT
---------- ----------------------------------------------------------------
      SEQ#         P1         P2         P3  WAIT_TIME
---------- ---------- ---------- ---------- ----------
        33 PX Deq: Execute Reply
     31451        200          1          0          0

4.使用event 10046 level 12診斷事件來跟蹤查詢協調者QC
並行執行的效能
以並行方式來執行查詢而不是序列，可以提高效能。然而，並行查詢是否最有效，有許多因素需要考慮。多個並行子程式會比單個程式消耗更多的CPU，每個並行子程式都有它自己的地址空間(記憶體分配)

對於讀取大量資料除了使用並行執行來減少執行時間之外沒有其它選擇。透過將讀取工作負載分解到多個處理器或並行子程式來執行最大化了資料吞吐量。排序操作也使用子程式的'table queue structures'來進行管理。這將有效地增加整個系統的CPU負載。如果系統當前CPU利用率已經最大化（或接近最大化），並行查詢不會得到任何好處。如果沒有更多的CPU可以使用，那麼並行查詢操作將以序列方式來執行。

另外並行子程式的讀取資料的額外IO請求也會對I/O子系統的負載產生壓力。在有些情況下磁碟I/O可能會成為瓶頸。跨多個磁碟來分佈資料會所有幫助。

另外對於適用索引查詢而不是並行查詢的語句序列方式執行可能會執行地更快
.nested loops vs hash/sort merge join
通常來說並行查詢是使用快速表掃描來訪問資料並且基於rowid範圍來分給並行子程式。通常nestedloop join對於快速表掃描不是很有效（除非驅動表輸入的記錄數很小）。Hash Joins與Sort Merge在處理大理資料時會更有效。然而，對於HJ與SMJ不會對來自驅動表(也就是驅動行記錄）進行行消除。這種行消除會減小被呼叫資料集的大小。因為被消除的資料量這就可能意味著使用索引訪問資料的串
行執行會比並行執行更快。

.建立並行子程式，平均分解資料並將資料傳給多個程式和合並結果的總成本可能會比序列執行的成本更高

.資料傾斜
並行執行是基於rowid範圍來將資料平均分給並行子程式。因為分給每個並行子程式的相同資料塊包含首相同記錄。事實上，有些資料塊完全是空的。這種問題在大量資料被歸檔或被刪除之後更為突出，因此造成了許多空塊或稀疏的資料塊。這會造成資料的不均勻分佈進而造成並行查詢比序列查詢執行的更慢，因為一個並行子程式執行工作（例如一個並行子程式的對分佈的資料序列訪問會引起瓶頸）。
對於這種情況除了重新組織資料幾乎沒有方法可以解決。

效能概述
並不是所有的並行查詢都比序列查詢執行快。有些查詢適合並行查詢，有些適合序列查詢。如果使用並行執行，那麼應該儘量最大化磁碟I/O的吞吐量。確保:
.有足夠的並行子程式來有效檢索資料
.不能有太多的並行子程式(避免超過CPU)
.設定記憶體引數(sort_area_size等）因此不會記憶體溢位與引起記憶體交換
.資料均勻分佈給多個磁碟，因此並行子程式沒有I/O競爭
.需要並行執行的查詢型別在適合並行執行
.檢視並行子程式是否出現資料的不均勻分佈這就說明了資料傾斜