Oracle 並行操作

標籤：

SQL 陳述式預設是在一個 CPU上串列（Serial）執行的，即便系統中有多個 CPU存在，一個 SQL 陳述式也無法利用它們。這樣一來一個 CPU 所能使用的資源就決定了 SQL 陳述式的效能。這 時提升效能的另一種方法就應運而生，也就是平行處理——同時利用多個 CPU進行工作。

正統的理論界認為，企業的資料庫有兩種類型，OLTP 和 OLAP，前者用於業務處理，其 特點是大量的已耗用時間很短的事務型 SQL 陳述式，並行操作在這種系統中作用有限。後一種用於 資料分析，其特點是大量的查詢型 SQL 陳述式，這裡才是並行操作大展身手的舞台。但在實際的 企業環境中，由於成本的制約，可能無法部署一台單獨的 OLAP 資料倉儲。功能界限的也就無 法絕對切割，很多 7×24 的業務資料庫也要支援資料統計分析功能，雖然理論上這些功能應該 是在一個獨立的資料倉儲中完成。因此無論對於資料倉儲還是 OLTP 系統，平行處理是一個非 常有用的工具。

本文地址：http://blog.csdn.net/sunansheng/article/details/51219039

並行架構

Oracle 的平行處理是從 1996 年的 Oracle 7.3 版本開始出現的，隨著 Oracle 資料庫的發展， 這個功能也不斷得到發展和提升。習慣上使用者都覺得似乎只有資料倉儲才能從並行能力中獲益， 其實這中理解並不全面。Oracle 的平行處理並不局限於查詢功能（SELECT），DML 操作 （INSERT、UPDATE、DELETE）、DDL 操作（CREATE TABLE、CREATE INDEX），收集統計 資料、載入資料、備份恢複等功能都已經獲得了並行支援。因為平行處理可以充分利用多個硬 件資源，利用率越高，處理得效率也會更高。有了平行處理後，TB 級的資料可以在幾十分鐘 內完成，而不必等待一天或更長的時間，但是處理能力的提升並不是線形關係， 7-1 所示， 這是因為資料庫處理涉及多個組件，包括 CPU、記憶體、磁碟 IO，這些組件如果不能同步地擴充， 那麼總的效能提升能力會受限於最弱的一環，也就是短板理論。

Oracle 所謂的並行操作通過把一個任務分解成許多小的、獨立的部分，使得原來只能由一 個進程完成的一個大任務變成了可以由多個進程同時完成的若干個小任務，這樣就能充分利用 系統資源套件括多 CPU、多 IO 通道。可以用一個現實生活中的例子來解釋什麼是平行處理：計 算一條街道上有多少輛汽車，如果讓你一個人完成這個任務，那你就需要從街頭走到街尾才能 數清楚有多少輛車。如果有兩個人一起完成這個任務，那就可以一個人從街頭走向街尾、另一 個人從街尾走向街頭，如果兩個人的能力一樣，理論上只需要原來的一半時間就可以完成這個 任務。

注意：只有Oracle企業版才支援平行處理。

Oracle 的並行架構涉及進程、記憶體和參數，下面將分別詳細介紹。

進程

並行操作會用到兩類進程，分別叫做 Slave Process 和 Query Coodinator。前者是由若干個 進程組成，這些進程的地位相同，每個進程都負責一部分工作或者一個工作單元。比如全表掃 描，每個 Slave Process負責掃描 Segment 的一部分，這樣的一組進程也叫做一個 PS Set（Parallel Server Set）。

Query Coodinator進程通常是發出 SQL 陳述式的那個進程，這個進程負責把一個任務分解成 若干個任務片，並申請 PS Set，為每個 Slave Process 指定工作片段，並收集每個 Slave Process 返回的處理結果並匯總成最終的結果返回給使用者。總的來說可以這樣理解兩種進程的角色， Slave Process是真正幹活的進程，而 Query Coordinator進程只是指手畫腳，負責協調，當然偶 爾也會幹活。

在並行架構中，Query Coornidator進程會把整個工作會按照一定粒度（Granules）劃分成若 幹個單元，每個 Slave Process 會分配一個工作單元，Slave Process 完成分配給它的單元後，如 果還有剩餘的工作單元，則繼續分配下一個單元，直到所有單元都完成。 Query Coornidator進程劃分工作單元的粒度有兩種。

• 分區：如果對象是分區的，則工作單元可以以分區為單位進行劃分。
• 資料區塊範圍：每個工作單位是一個 Segment 中的若干個資料區塊。

注意：對於工作單元的劃分是在語句的執行期間進行的，而不是在Parsing階段進行。

相關參數

Oracle 資料庫中與並行有關的參數有多個，並且隨著版本的不同這些參數變化也很大，讀 者可以用 SHOW PARAMETER 命令查看這些參數，比如，以下是 Oracle 10.2.0.1 中的查詢結果：

SQL> show parameter parallel NAME     TYPE     VALUE fast_start_parallel_rollback     string     LOW parallel_adaptive_multi_user     boolean     TRUE parallel_automatic_tuning     boolean     FALSE parallel_execution_message_size     integer     2152 parallel_instance_group     string parallel_max_servers         integer     160 parallel_min_percent         integer     0 parallel_min_servers         integer     0 parallel_server boolean     FALSE parallel_server_instances         integer 1 parallel_threads_per_cpu     integer 2 recovery_parallelism     integer 0

以上這些參數是與並行功能直接相關的，除此之外還有許多參數會間接地影響並行能力， 下面就介紹一些最重要的參數。

1.PARALLE_MAX_SERVERS

Oracle 的平行處理是通過專門進程完成的，這些就是前面說過的 Slave Process。並行進程是在資料庫範圍共用使用的，資料庫裡所有的並行進程組成一個進程池（PX Server Pool），每 當需要並行操作時，就會從這個池中取得足夠數量的並行進程，當操作執行結束後，還需要把 並行進程返回這個池中。PARALLE_MAX_SERVERS 參數就是控制資料庫範圍內並行進程的最 大數量。這也是一種自我保護機制，如果不控制並行進程的數量很容易消耗光系統資源。

參數的預設值=CPU 數量×10。如果把這個參數設定成 0，就相當於在資料庫範圍內禁用了 並行。

2.PARALLEL_MIN_SERVERS

PARALLEL_MIN_SERVERS 參數定義進程池中最少要保有的並行進程數量，在資料庫啟 動時，這些進程就要被啟動，並且在資料庫生命期內要一直保持運行狀態，即使沒有用到並行 操作。

如果需要的 Slave Process超過了這個數量，Oracle 就會動態地增加 Slave Process，這些動 態增加的 Slave Process 完成任務後會等待 5 分鐘，這 5 分鐘內如果沒有新的任務分配，Oracle 就結束這些進程。

PARALLEL_MIN_SERVERS參數的預設值是 0，因此在測試並行時可以觀察到，當發出一 個SQL語句後，通常要經過一段時間後才能看到P00n進程產生。這時會話表現等待在“os thread startup”事件上的。如果把這個參數設定成非 0 值，資料庫執行個體啟動時就會同時啟動若干並行 進程，就可以減少啟動新的並行進程的延遲時間。

查看 PS Server 的使用方式：

SQL> select * from v$px_process_sysstat  where statistic like ‘Server%‘; STATISTIC     VALUE ------------------------------------------------------------ ---------- Servers     In Use     0 Servers     Available     20 Servers     Started 1924 Servers     Shutdown     1904 Servers     Highwater     20 Servers     Cleaned Up     0 Server     Sessions     9488 

• Server In Use：當前正在工作的有 0 個。
• Servers Available：當前可用的進程是 20，這是因為 parallel_min_servers 參數定義為 20，所以總是有 20 個進程可以使用。
• Server Started：資料庫啟動以來啟動的進程數量。
• Server Shutdown：關閉的進程數量。

3.PARALLEL_EXECUTION_MESSAGE_SIZE

並行操作中必須的處理序間通訊（包括 Slave Process 和 Query Coordinator 之間，以及 Producer/Consumer模式的 Slave Process 之間）都是通過 Table Queue 實現的，所謂 Table Queue 實際就是共用記憶體，這些記憶體可以在 SGA的 Large Pool 中，也可以在 SGA的 Shared Pool 中。 Oracle 推薦應該把它放在 Larege Pool 中，以避免造成 Shared Pool 片段。

利用下面語句可以判斷 Table Queue 是在哪個 Pool中分配的，可以通過以下語句查詢：

SQL> select * from v$sgastat  2 where name =‘PX msg pool‘; POOL NAME BYTES ------------ -------------------------- ---------- shared pool PX msg pool 774720 

這個查詢結果顯示 Table Queue 是在 Shared Pool 中分配的，這是應該避免的情況。如果是 在 Large Pool 中分配，查詢結果就如下所示：

POOL NAME BYTES ------------ -------------------------- ---------- large pool PX msg pool 1076024 

對於 Oracle 10g，有兩種途徑把 Table Queue 放在 Large Pool 中：

• 使用 SGA 的自動調整，也就是設定 SGA_TARGET 參數為非 0 值；
• 如果沒有使用 SGA 的自動管理，通過把參數 parallel_automatic_tuning 設定成 TRUE 也可以實現，這個參數預設是 FALSE。

每個 Table Queue 是由3個（在 RAC環境下是 4個）Buffer組成的，這個參數定義的就是 每個 Buffer的大小，單位是 Bytes，預設值是 2152Bytes。預設值在大多數場合下都顯得偏小， 可以把它調整成 16KB、32KB、64KB 等。

在調整這個參數前，需要考慮對記憶體的消耗情況，可以使用下面的公式來估計：

memory≥ parallel_max_servers×parallel_max_servers×parallel_execution_message_size×3 or 4 

其中PARALLEL_MAX_SERVERS×PARALLE_MAX_SERVERS代表Table Queue最多時的 數量，因為在 Producer/Consumer模式下，Slave Process 是配對分配的。

4.PARALLEL_AUTOMATIC_TUNNING

PARALLEL_AUTOMATIC_TUNNING參數如果設定成 TRUE，Oracle 會做兩件事：首先是 調整若干 PARALLEL參數值；其次，就是把 Table Qieue 放在 Large Pool 中。雖然在Oracle 10g中，這已經是一個廢棄的參數了。不過仍然可以利用這個參數的後一種功能，達到強迫Oracle 把 Table Queue 放在 Large Pool 中的效果。

5．PARALLEL _ADAPTIVE_MULTI_USER

平行處理功能是一個效能加速器，但如果使用不當，又會成為效能殺手。這一點也很好理 解，如果所有的操作都使用平行處理，很快系統資源就會被消耗盡。比如表 A 的並行度是 4，那麼所有這對這個表的訪問都會使用 4 個 Slave Process，如果 PARALLEL_MAX_SERVERS 是 40，只要有10 個使用者就可以耗盡整個 PX Pool。不僅如此，大量的並行進程會長時間佔用 CPU、 磁碟資源，使得其他使用者的操作根本沒有執行的機會。因此需要對平行處理作控制。Oracle 10g 有兩個參數可以調節並行，這個參數就是其中之一。

Oracle 並行操作