【原創】Oracle 並行原理與示例總結
《Oracle 並行原理與示例總結》
並行：把一個工作分成幾份，分給不同程式同時進行處理。 程式層面
併發：多個會話同時進行訪問，就是通常所說併發數。會話層面
資料庫版本
LEO1@LEO1> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
一 給出一個2表關聯的並行查詢執行計劃，並畫出並行資料流圖
（1）並行應用場合：
1.OLAP 業務模式
首先說明一個我們經常混洗的概念OLAP和資料倉儲是不是一個東西。
回答：NO
OLAP是一種業務模式，是一種批次載入批次檢索的業務模式，例如 線上資料分析系統。
資料倉儲：是支撐這種業務模式的底層資料庫。
2.系統資源相對空閒
當系統的CPU較多 IO負載不高 記憶體夠大的時候，可以使用並行操作，需要考慮資源平衡。
3.資料符合並行特點
這個說明是什麼呢，就是你所操作的資料沒有序列化，可以拆分，同時對不同部分資料進行操作，
如果是那種只有計算出前者才能統計後者的流程就不適合使用並行操作了。
例如統計行數就可以使用並行，讓10個程式分別統計不同區域的行數最後把結果合併起來返回給使用者。
（2）首先明示一下使用並行的3種方法
1.hints 方式 臨時有效
select /*+ parallel(leo1 2) */ count(*) from leo1;
2.alter table 物件級別定義並行 直接修改物件屬性 長期有效
alter table leo1 parallel 4; 定義leo1表並行度為4
3.alter session force parallel 會話級別定義並行 會話生命期中有效
alter session force parallel query parallel 4; 強制定義並行度為4
通常/*+ parallel(leo1 4) */
4.並行查詢和並行DDL可以無障礙使用並行，如果想使用並行DML，就需要啟動會話DML並行功能
alter session enable parallel dml;
（3）實驗
現在我們做一個並行操作，分析一下並行執行計劃流程，感性的感受一下並行的魅力
LEO1@LEO1> drop table leo1 purge; 清理環境
Table dropped.
LEO1@LEO1> drop table leo2 purge;
Table dropped.
LEO1@LEO1> create table leo1 as select * from dba_objects; 建立leo1表
Table created.
LEO1@LEO1> create table leo2 as select * from leo1; 模擬leo1建立leo2表
Table created.
LEO1@LEO1> execute dbms_stats.gather_table_stats(ownname=>'LEO1',tabname=>'LEO1',
method_opt=>'for all indexed columns size 254');
PL/SQL procedure successfully completed.
LEO1@LEO1> execute dbms_stats.gather_table_stats(ownname=>'LEO1',tabname=>'LEO2',
method_opt=>'for all indexed columns size 254');
PL/SQL procedure successfully completed.
對leo1和leo2進行全表整體分析包括資料分佈情況分析（資料傾斜程度，即直方圖）
Tips
如果想讓n個表關聯查詢並且都啟並行的話，hints如何寫呢，請看下面
/*+ parallel(leo1,leo2,2) */ 對嗎？ 當然不對
/*+ parallel(leo1 leo2,2) */ 對嗎？ 當然也不對
/*+ parallel(leo1,2) parallel(leo2,2) */ 對嗎？ Yes 親們要看好哦
/*+ parallel(leo1,2) parallel(leo2,2) parallel(leo3,2) ……*/ 後面同理延續
LEO1@LEO1> select /*+ parallel(leo1,2) parallel(leo2,2) */ count(*) from leo1,leo2
where leo1.object_id=leo2.object_id;
Execution Plan 並行度為2 執行計劃
----------------------------------------------------------
Plan hash value: 2718975204
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 320 (1)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 26 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | Q1,01 | PCWP | |
|* 5 | HASH JOIN | | 72017 | 1828K| 320 (1)| 00:00:04 | Q1,01 | PCWP | |
| 6 | PX BLOCK ITERATOR | | 72017 | 914K| 160 (1)| 00:00:02 | Q1,01 | PCWC | |
| 7 | TABLE ACCESS FULL | LEO1 | 72017 | 914K| 160 (1)| 00:00:02 | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 72017 | 914K| 160 (1)| 00:00:02 | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 72017 | 914K| 160 (1)| 00:00:02 | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 72017 | 914K| 160 (1)| 00:00:02 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| LEO2 | 72017 | 914K| 160 (1)| 00:00:02 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("LEO1"."OBJECT_ID"="LEO2"."OBJECT_ID")
解釋一下並行執行計劃步驟，並行執行計劃應該從下往上讀，當看見PX（parallel execution）關鍵字說明使用了並行技術
1. | 7 | TABLE ACCESS FULL | LEO1 | 首先對leo1表進行全表掃描
2. | 6 | PX BLOCK ITERATOR | 並行程式以迭代iterator的方式分割資料塊
3. | 11 | TABLE ACCESS FULL| LEO2 | 然後對leo2表進行全表掃描
4. | 10 | PX BLOCK ITERATOR |並行程式以迭代iterator的方式分割資料塊
5. | 9 | PX SEND BROADCAST | :TQ10000 |並行程式以廣播方式傳送掃描結果
6. | 8 | PX RECEIVE |並行程式接收傳送過來的結果
7. |* 5 | HASH JOIN |在這裡把leo1 leo2表掃描結果做雜湊關聯
8. | 4 | SORT AGGREGATE|將雜湊關聯結果做count統計操作
9. | 3 | PX SEND QC (RANDOM)| :TQ10001 |按照隨機順序傳送給並行協調程式QC（query coordinator）整合結果
10. | 2 | PX COORDINATOR |接收資料
11. | 1 | SORT AGGREGATE |整合結果
12. | 0 | SELECT STATEMENT |完畢後QC將最終結果返回給使用者
說明並行執行計劃中特有的IN-OUT列的含義（指明瞭操作中資料流的方向）
Parallel to Serial（P->S）: 表示一個並行操作向一個序列操作傳送資料，通常是將並行結果傳送給並行
排程程式QC進行彙總 例如 PX SEND QC (RANDOM)
Serial to Parallel（S->P）: 表示一個序列操作向一個並行操作傳送資料，如果select部分是序列操作，就會出現這個情況
Parallel to Parallel（P->P）：表示一個並行操作向另一個並行操作傳送資料，一般是並行父程式與並行子程式之間的資料交流
Parallel Combined with parent(PCWP): 資料在同一組的並行程式之間傳遞
Parallel Combined with Child(PCWC): 資料在不同組的並行程式之間傳遞
下面我們從v$pq_tqstat動態效能檢視中檢視一下並行程式的工作量平衡情況
LEO1@LEO1> select dfo_number,tq_id,server_type,num_rows,bytes,process from v$pq_tqstat;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES PROCESS
---------- ---------- -------------------- ---------- ---------- ----------------------------------------------------------
1 0 Consumer 72017 422219 P000
1 1 Producer 1 36 P000
1 0 Consumer 72017 422219 P001
1 1 Producer 1 36 P001
PROCESS：P000 P001 是2個程式號，說明我們啟動了2個並行程式和我們設定的並行度一致
SERVER_TYPE：程式在處理中扮演的角色
Producer 扮演並行服務角色，幹活的（具體處理操作）
Consumer 扮演並行協調角色，分配任務和結果合併
NUM_ROWS：上面角色處理的行數
BYTES：行數所佔的空間位元組
TQ_ID：table queue ID 區分同一個程式扮演2個角色時的不同操作（來自官方文件）和執行計劃中TQ同理
DFO_NUMBER：1 = 第一次並行操作 2 = 第二次並行操作 3 = 第三次並行操作
第一次並行操作牽涉到兩個並行程式 P000 P001
DFO （data flow operator）操作流程
Tips
LEO1@LEO1> select distinct sid from v$mystat;
SID
------------------
133
LEO1@LEO1> select sid,PDML_STATUS,PDDL_STATUS,PQ_STATUS from v$session;
SID PDML_STA PDDL_STA PQ_STATU
---------- -------- -------- ---------------- -------- ----
133 DISABLED ENABLED ENABLED
我們可以利用v$session中的PDML_STATUS,PDDL_STATUS,PQ_STATUS檢視當前會話的預設並行狀態
二 就自己本機的硬體情況，透過SQL示例，來找到最優的並行度
（1）並行度：就是oracle在進行並行處理時，會啟動幾個並行服務程式來同時處理資料，注意看看資料需要幾步處理，每一步都啟n個程式而不是隻啟n個程式
並行度設定：一般來講一個CPU核心可以支撐一個並行度，一臺多核伺服器中通常採用CPU核數50%來設定並行度，餘下的CPU處理其他程式
並行度與硬體關係密切，同樣並行度在不同硬體上體現的效果是截然不同的
並行度與併發數關係：總並行數=並行度*併發數，當並行度確立後，併發數越多總並行數越高
（2）實驗
我使用的是me的ACER小本本，例舉一下配置
英特爾@酷睿 i3-370M 雙核四執行緒
14LED螢幕
NVIDIA GeForce@GT 520M
4G DDR3
500GB
DVD-SuperMulti燒錄
Acer Nplify 802.11b/g/n
6芯鋰離子電池
為了對比方便，我還是使用剛才SQL語句只是變化不同的並行度，來對比執行計劃找到最優的並行度
並行度設定成2
LEO1@LEO1> select /*+ parallel(leo1,2) parallel(leo2,2) */ count(*) from leo1,leo2
where leo1.object_id=leo2.object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2718975204
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 320 (1)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 26 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | Q1,01 | PCWP | |
|* 5 | HASH JOIN | | 72017 | 1828K| 320 (1)| 00:00:04 | Q1,01 | PCWP | |
| 6 | PX BLOCK ITERATOR | | 72017 | 914K| 160 (1)| 00:00:02 | Q1,01 | PCWC | |
| 7 | TABLE ACCESS FULL | LEO1 | 72017 | 914K| 160 (1)| 00:00:02 | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 72017 | 914K| 160 (1)| 00:00:02 | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 72017 | 914K| 160 (1)| 00:00:02 | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 72017 | 914K| 160 (1)| 00:00:02 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| LEO2 | 72017 | 914K| 160 (1)| 00:00:02 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
效能指標Cost=160 Time=00:00:02
並行度設定成4
LEO1@LEO1> select /*+ parallel(leo1,4) parallel(leo2,4) */ count(*) from leo1,leo2
where leo1.object_id=leo2.object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 589148148
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 160 (1)| 00:00:02 | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 26 | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | 72017 | 1828K| 160 (1)| 00:00:02 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 72017 | 914K| 80 (0)| 00:00:01 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 72017 | 914K| 80 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 72017 | 914K| 80 (0)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| LEO1 | 72017 | 914K| 80 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 72017 | 914K| 80 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 72017 | 914K| 80 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 72017 | 914K| 80 (0)| 00:00:01 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| LEO2 | 72017 | 914K| 80 (0)| 00:00:01 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
效能指標Cost=80 Time=00:00:01
LEO1@LEO1> select dfo_number,tq_id,server_type,num_rows,bytes,process from v$pq_tqstat;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES PROCESS
---------- ---------- -------------------- ---------- ---------- ----------------------------------------------------------
1 0 Consumer 72017 422219 P000
1 1 Producer 1 36 P000
1 0 Consumer 72017 422219 P001
1 1 Producer 1 36 P001
1 0 Producer 71650 418494 P002
1 0 Producer 72384 425944 P003
1 1 Consumer 2 72 QC
PROCESS：P000 P001 P002 P003 說明我們啟動了4個並行程式
就Cost和Time指標而言4比2個並行度效率要高一倍
並行度設定成8
LEO1@LEO1> select /*+ parallel(leo1,8) parallel(leo2,8) */ count(*) from leo1,leo2
where leo1.object_id=leo2.object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 589148148
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 80 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 26 | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | 72017 | 1828K| 80 (0)| 00:00:01 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 72017 | 914K| 40 (0)| 00:00:01 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 72017 | 914K| 40 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 72017 | 914K| 40 (0)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| LEO1 | 72017 | 914K| 40 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 72017 | 914K| 40 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 72017 | 914K| 40 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 72017 | 914K| 40 (0)| 00:00:01 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| LEO2 | 72017 | 914K| 40 (0)| 00:00:01 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------效能指標Cost=53
效能指標Cost=40 代價又減少了一半
並行度設定成16
LEO1@LEO1> select /*+ parallel(leo1,16) parallel(leo2,16) */ count(*) from leo1,leo2
where leo1.object_id=leo2.object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 589148148
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 40 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 26 | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | 72017 | 1828K| 40 (0)| 00:00:01 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 72017 | 914K| 20 (0)| 00:00:01 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 72017 | 914K| 20 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 72017 | 914K| 20 (0)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| LEO1 | 72017 | 914K| 20 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 72017 | 914K| 20 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 72017 | 914K| 20 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 72017 | 914K| 20 (0)| 00:00:01 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| LEO2 | 72017 | 914K| 20 (0)| 00:00:01 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
效能指標Cost=20 代價又減少了一半
並行度設定成32
LEO1@LEO1> select /*+ parallel(leo1,32) parallel(leo2,32) */ count(*) from leo1,leo2
where leo1.object_id=leo2.object_id;
效能指標Cost=10 代價又減少了一半
並行度設定成64
LEO1@LEO1> select /*+ parallel(leo1,64) parallel(leo2,64) */ count(*) from leo1,leo2
where leo1.object_id=leo2.object_id;
效能指標Cost=5 代價又減少了一半
並行度設定成128
LEO1@LEO1> select /*+ parallel(leo1,128) parallel(leo2,128) */ count(*) from leo1,leo2
where leo1.object_id=leo2.object_id;
效能指標Cost=2 代價又減少了一半
並行度設定成256
LEO1@LEO1> select /*+ parallel(leo1,128) parallel(leo2,128) */ count(*) from leo1,leo2
where leo1.object_id=leo2.object_id;
效能指標Cost=2 好一致了
小結：當並行度設定成256時Cost不在減少了，說明此時並行度最優。說明一下我的這張表只有7w多條資料，有可能資料量比較少CPU可以很快的執行完畢，所以Cost也比較小。
三 針對PARALLEL_DEGREE_POLICY的三個值，分別演示它們的效果
（1）自動並行度策略PARALLEL_DEGREE_POLICY是ORACLE 11G新特性，透過它可以設定並行度策略
這個引數有三個值：manual 手動方式，也是預設方式，oracle不會擅自調整並行度
limited 限制方式
auto 自動方式
（2）實驗
LEO1@LEO1> drop table leo3 purge; 清理環境
Table dropped.
LEO1@LEO1> drop table leo4 purge;
Table dropped.
LEO1@LEO1> create table leo3 as select * from dba_objects; 建立leo3表
Table created.
LEO1@LEO1> create table leo4 as select * from dba_objects; 建立leo4表
Table created.
parallel_degree_policy=manual
如果我們設定manual，oracle不會參與調整並行度，不管資源負載如何，並行度是多少就用多少
第一種設定方式
LEO1@LEO1> alter table leo3 parallel 4; 直接指定leo3表並行度為4
Table altered.
物件級別定義並行，直接修改物件屬性，長期有效
LEO1@LEO1> select table_name,degree from user_tables where table_name in ('LEO3'); 並行度已經調整完畢
TABLE_NAME DEGREE
------------------------------ -------------------------------
LEO3 4
第二種設定方式
LEO1@LEO1> alter table leo3 parallel(degree 4); 這種方法和上面方法同理
第一種設定方式
LEO1@LEO1> alter table leo4 parallel; 設定leo4表的並行度為default
Table altered.
第二種設定方式
LEO1@LEO1> alter table leo4 parallel(degree default); 同理也是設定為default
Table altered.
第三種設定方式 hint
select /*+ parallel */ * from leo4; 不寫並行度就是使用oracle預設並行度（臨時有效）
這三種方式我們選擇其一即可
LEO1@LEO1> select table_name,degree from user_tables where table_name in ('LEO3','LEO4');
TABLE_NAME DEGREE
------------------------------ -------------------- --------------------
LEO3 4
LEO4 DEFAULT 預設並行度
設定自動並行度為manual
LEO1@LEO1> alter session set parallel_degree_policy=manual;
Session altered.
LEO1@LEO1> select count(*) from leo3; 統計leo3表使用並行操作
COUNT(*)
------------------
72017
LEO1@LEO1> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- ------------------------------ ---------- ----
Allocation Height 4 0
使用並行度是4，oracle沒有改變並行度
LEO1@LEO1> select count(*) from leo4; 這張表是使用oracle預設並行度
COUNT(*)
----------
72018
LEO1@LEO1> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Allocation Height 4 0
並行度是4，oracle還是使用預設並行度
parallel_degree_policy=limited
如果我們設定limited
當有直接指定並行度時繼續使用
當表是使用oracle預設並行度時，oracle會根據資源負載自動評估調整並行度
LEO1@LEO1> alter table leo3 parallel(degree 6); 我把leo3表並行度調整為6
Table altered.
LEO1@LEO1> alter table leo4 parallel(degree default); leo4還是使用oracle預設並行度
Table altered.
LEO1@LEO1> select table_name,degree from user_tables where table_name in ('LEO3','LEO4');
TABLE_NAME DEGREE 進行檢查
---------- --------------------
LEO3 6
LEO4 DEFAULT
設定自動並行度為limited
LEO1@LEO1> alter session set parallel_degree_policy=limited;
Session altered.
LEO1@LEO1> select count(*) from leo3; 統計leo3表使用並行操作
COUNT(*)
----------
72017
LEO1@LEO1> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- ------------------------ ---------- ---------
Allocation Height 6 0
使用並行度是6，oracle沒有改變並行度
LEO1@LEO1> select count(*) from leo4; 使用oracle預設並行度
COUNT(*)
----------------
72018
LEO1@LEO1> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- ------------------ ---------- -------------
Allocation Height 0 0
這回oracle改變了並行度=0，因為oracle覺得不使用並行也能有很好的效果
parallel_degree_policy=auto
如果我們設定auto，不論是直接指定的並行度還是使用oracle預設並行度，oracle都會進行干預調整，oracle會根據資源負載情況來動態調整並行度。
設定自動並行度為auto
LEO1@LEO1> alter session set parallel_degree_policy=auto;
Session altered.
LEO1@LEO1> select table_name,degree from user_tables where table_name in ('LEO3','LEO4');
TABLE_NAME DEGREE
---------- --------------------
LEO3 6
LEO4 DEFAULT
LEO1@LEO1> select count(*) from leo3;
COUNT(*)
----------
72017
LEO1@LEO1> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Allocation Height 0 0
LEO1@LEO1> select count(*) from leo4;
COUNT(*)
----------
72018
LEO1@LEO1> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Allocation Height 0 0
小結：無論是直接指定並行度還是使用預設並行度，oracle都調整為0，這是因為oracle認為資料量較小（7w）沒有必要使用並行技術。
四 用一個並行的SQL示例，比較10391事件和 V$PQ_TQSTAT結果的異同
10391事件可以跟蹤整個並行流程，我們檢視10391事件trace檔案就可以知道sql語句並行執行情況
第一步 第二步 第三步 第四步
LEO1@LEO1> drop table leo5 purge; 清理環境
Table dropped.
LEO1@LEO1> create table leo5 as select * from dba_objects; 建立leo5表
Table created.
LEO1@LEO1> execute dbms_stats.gather_table_stats(ownname=>'LEO1',tabname=>'LEO5') 表分析
PL/SQL procedure successfully completed.
LEO1@LEO1> alter session set events '10391 trace name context forever,level 12'; 啟動10391事件
Session altered.
在執行下面3條語句的時候記下當前時間，幹什麼呢後面會揭曉（非常重要哦）
2013-01-25 15:25 這是我的時間
LEO1@LEO1> select /*+ parallel(leo5,4) */ count(*) from leo5; 執行3次sql語句
COUNT(*)
----------
72011
LEO1@LEO1> select /*+ parallel(leo5,4) */ count(*) from leo5;好在10391事件trace檔案中找到並行資訊
COUNT(*)
----------
72011
LEO1@LEO1> select /*+ parallel(leo5,4) */ count(*) from leo5;我們執行幾次就要在trace檔案中找到幾段
COUNT(*)
----------
72011
下面還可以執行一些其他SQL語句用於刷trace，後面有解釋為什麼
LEO1@LEO1> alter session set events '10391 trace name context off'; 關閉10391事件
Session altered.
LEO1@LEO1> select name,value from v$diag_info where name='Default Trace File'; 當前會話寫入trace檔名
NAME VALUE
-------------------------------------------------- -------------------------------------------------------------------------------
Default Trace File /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_16433.trc
[oracle@leonarding1 trace]$ pwd
/u01/app/oracle/diag/rdbms/leo1/LEO1/trace oracle 11g和oracle 10g trace檔案所在目錄不同請注意
LEO1@LEO1> !vim /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_16433.trc 開啟trace檔案選取有用資訊，真的有可用資訊嗎？真的嘛？
這時你可能會發現從上找到下，翻來覆去的找啊找，就是沒有並行salve程式資訊，難道oracle 11g不把並行資訊寫入trace檔案嘛，這是真的嘛？=> NO不要相信你看到的，真理往往站在少數人身邊。那大家會說並行資訊到底在哪裡啊在哪裡？其實oracle跟我們玩了一次躲貓貓，它把資訊藏在了log buffer cache 中，因為還沒有刷到trace檔案裡，所以我們才沒看到，那大家進一步會問oracle多長時間清理一次緩衝區髒資料呢，反正我的oracle 11g資料庫大約每11分鐘刷一次（oracle 10g相對較短所以大家在10g上會立刻看到），這也和緩衝區中資料量有關，如果在生產環境中不停的產生trace log可能時間間隔會縮短，只有把並行資訊刷到trace檔案中，我們才能看到，這就是原因所在！
ORACLE 11G版
2013-01-25 15:25
LEO1@LEO1> select /*+ parallel(leo5,4) */ count(*) from leo5;
*** 2013-01-25 15:25:32.042
kxfrDmpUpdSys
allocated slave set: nsset:1 nbslv:4 tinst=1 ipq=0
Slave set 0: #nodes:1
Min # slaves 4: Max # slaves:4
List of Slaves:
slv: 0 nid:0 instid:1 sid:00
slv: 1 nid:0 instid:1 sid:01
slv: 2 nid:0 instid:1 sid:02
slv: 3 nid:0 instid:1 sid:03
List of Nodes:
node 0
這是第一條語句的並行資訊，對著時間找比較容易，使用了4個slave程式
ORACLE 10G版
kxfrDmpUpdSys
allocated slave set: nsset:1 nbslv:4
Slave set 0: #nodes:1
Min # slaves 4: Max # slaves:4
List of Slaves:
slv 0 nid:0
slv 1 nid:0
slv 2 nid:0
slv 3 nid:0
List of Nodes:
node 0
11G要比10G的多些資訊，包括例項資訊 會話資訊等
*** 2013-01-25 15:25:34.115
dumping system information
arch:255 (unknown)
sess:133 myiid:1 mynid:1 ninst:1 maxiid:1, mnodes:0
Instances running on that system:
inum:0 iid:1
kxfrDmpUpdSys
allocated slave set: nsset:1 nbslv:4 tinst=1 ipq=0
Slave set 0: #nodes:1
Min # slaves 4: Max # slaves:4
List of Slaves:
slv: 0 nid:0 instid:1 sid:00
slv: 1 nid:0 instid:1 sid:01
slv: 2 nid:0 instid:1 sid:02
slv: 3 nid:0 instid:1 sid:03
List of Nodes:
node 0
這是第二條語句的並行資訊
*** 2013-01-25 15:25:37.975
dumping system information
arch:255 (unknown)
sess:133 myiid:1 mynid:1 ninst:1 maxiid:1, mnodes:0
Instances running on that system:
inum:0 iid:1
kxfrDmpUpdSys
allocated slave set: nsset:1 nbslv:4 tinst=1 ipq=0
Slave set 0: #nodes:1
Min # slaves 4: Max # slaves:4
List of Slaves:
slv: 0 nid:0 instid:1 sid:00
slv: 1 nid:0 instid:1 sid:01
slv: 2 nid:0 instid:1 sid:02
slv: 3 nid:0 instid:1 sid:03
List of Nodes:
node 0
這是第三條語句的並行資訊
比較10391事件和 V$PQ_TQSTAT檢視
LEO1@LEO1> select dfo_number,tq_id,server_type,num_rows,bytes,process from v$pq_tqstat;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES PROCESS
---------- ---------- -------------------- ---------- ---------- --------------------
1 0 Producer 1 36 P003
1 0 Producer 1 36 P001
1 0 Producer 1 36 P002
1 0 Producer 1 36 P000
1 0 Consumer 4 144 QC
都顯示使用了4個並行服務程式
小結：本小題做起來並不難，但往往得不到自己想要的結果，做了一個下午終於撥雲見日感慨萬千，賦詩一首，留念於此！走起
遇到問題誤急躁
想想這是為何因
多問朋友多交流
答案自然來敲門
五 分別演示一個DDL和DML操作的並行執行示例
（1） 並行常用於：1.FTS_Full Table Scan
2.IFFS_Index Fast Full Scan
3.分割槽表
（2）這個小題在我的一篇blog中有非常詳細闡述和實驗，感興趣的朋友可以去看一看
《oracle 並行原理深入解析及案例精粹》
（3）這裡我把需要注意的地方和一些應用場合跟大家一起交流交流
並行查詢和並行DDL可以無障礙使用並行，如果想使用並行DML，就需要啟動會話DML並行功能
alter session enable parallel dml;
（4）實驗
DDL並行測試
如果想捕捉DDL並行資訊，我們可以採用10046事件來過濾並行資訊，因為10046事件記錄了sql解析、執行、取操作的效能指標和等待事件，在等待事件中我們就可以看到PX事件來說明DDL的並行操作
LEO1@LEO1> drop table leo6 purge; 清理環境
Table dropped.
LEO1@LEO1> alter session set events '10046 trace name context forever,level 12'; 啟動10046事件
Session altered.
LEO1@LEO1> create table leo6 parallel 4 as select * from dba_objects; 使用4個並行程式來建立表
Table created.
LEO1@LEO1> create index leo6_index on leo6(object_id) parallel 4; 使用4個並行程式來建立索引
Index created.
LEO1@LEO1> alter index leo6_index rebuild parallel 4; 重建索引可以這種寫法
Index altered.
LEO1@LEO1> alter session set events '10046 trace name context off'; 關閉10046事件
Session altered.
LEO1@LEO1> select name,value from v$diag_info where name='Default Trace File'; 當前會話寫入trace檔名
NAME VALUE
-------------------------------------------------- -------------------------------------------------------------------------------
Default Trace File /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_16433.trc
注：oracle提供了一個tkprof工具來對trace檔案進行格式化翻譯，過濾出有用的資訊
LEO1@LEO1> !tkprof /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_16433.trc
/u01/app/oracle/diag/rdbms/leo1/LEO1/trace/10046.txt sys=no
TKPROF: Release 11.2.0.1.0 - Development on Fri Jan 25 17:21:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
LEO1@LEO1> !vim /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/10046.txt 檢視日誌
create table leo6 parallel 4 as select * from dba_objects 我只把並行資訊擷取出來
Rows Row Source Operation
------- ---------------------------------------------------
4 PX COORDINATOR (cr=1347 pr=0 pw=0 time=365787 us)
0 PX SEND QC (RANDOM) :TQ20001 (cr=0 pr=0 pw=0 time=0 us cost=132 size=14151348 card=68364)
0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=0 us)
0 VIEW DBA_OBJECTS (cr=0 pr=0 pw=0 time=0 us cost=132 size=14151348 card=68364)
0 UNION-ALL (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID SUM$ (cr=2 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 956)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=30 card=1)
0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 36)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND ROUND-ROBIN :TQ20000 (cr=0 pr=0 pw=0 time=0 us)
72011 FILTER (cr=1342 pr=0 pw=0 time=1481001 us)
72877 PX COORDINATOR (cr=9 pr=0 pw=0 time=1519912 us)
0 PX SEND QC (RANDOM) :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=73 size=9030052 card=72823)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=73 size=9030052 card=72823)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=2 size=1584 card=88)
0 PX SEND BROADCAST :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=2 size=1584 card=88)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2 size=1584 card=88)
0 TABLE ACCESS FULL USER$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=1584 card=88)
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
os thread startup 12 0.03 0.28
PX Deq: Join ACK 12 0.00 0.00
PX Deq: Parse Reply 12 0.03 0.06
reliable message 5 0.00 0.00
PX Deq: Execute Reply 90 0.33 1.18
PX Deq: Table Q Normal 3 0.00 0.00
PX Deq: Signal ACK RSG 1 0.00 0.00
enq: CR - block range reuse ckpt 3 0.01 0.03
log file sync 1 0.00 0.00
PX Deq: Signal ACK EXT 7 0.00 0.00
PX Deq: Slave Session Stats 8 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 7.71 7.71
create index leo6_index on leo6(object_id) parallel 4
Rows Row Source Operation
------- ---------------------------------------------------
3 PX COORDINATOR (cr=5 pr=0 pw=0 time=82 us)
0 PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 INDEX BUILD NON UNIQUE LEO6_INDEX (cr=0 pr=0 pw=0 time=0 us)(object id 0)
0 SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=85 size=1189266 card=91482)
0 PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=85 size=1189266 card=91482)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=85 size=1189266 card=91482)
0 TABLE ACCESS FULL LEO6 (cr=0 pr=0 pw=0 time=0 us cost=85 size=1189266 card=91482)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
reliable message 4 0.00 0.00
enq: KO - fast object checkpoint 1 0.01 0.01
PX Deq: Join ACK 1 0.00 0.00
PX Deq: Parse Reply 2 0.00 0.00
PX Deq: Execute Reply 58 0.16 0.50
PX Deq: Table Q qref 2 0.00 0.00
db file sequential read 4 0.00 0.00
enq: CR - block range reuse ckpt 2 0.09 0.14
log file sync 1 0.02 0.02
PX Deq: Signal ACK EXT 7 0.06 0.08
PX Deq: Slave Session Stats 3 0.00 0.00
alter index leo6_index rebuild parallel 4
Rows Row Source Operation
------- ---------------------------------------------------
3 PX COORDINATOR (cr=5 pr=0 pw=0 time=52 us)
0 PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 INDEX BUILD NON UNIQUE LEO6_INDEX (cr=0 pr=0 pw=0 time=0 us)(object id 0)
0 SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 INDEX FAST FULL SCAN LEO6_INDEX (cr=0 pr=0 pw=0 time=0 us)(object id 74027)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 5 0.00 0.00
PX Deq: Parse Reply 4 0.00 0.00
PX Deq: Execute Reply 50 0.04 0.19
PX Deq: Table Q qref 3 0.00 0.00
db file sequential read 5 0.00 0.00
reliable message 3 0.00 0.00
enq: CR - block range reuse ckpt 3 0.01 0.02
PX Deq: Signal ACK EXT 4 0.03 0.03
PX Deq: Slave Session Stats 3 0.00 0.00
小結：上面的PX資訊充分說明了我們在進行DDL操作時使用了並行技術
DML並行測試
前提：首先說明oracle對DML並行操作是有限制的，必須設定啟用會話並行度，否則即使SQL指定了並行，oracle也不會執行DML並行操作
其次oracle只對partition table分割槽表做並行處理（有幾個分割槽就開幾個並行），普通表oracle不做並行處理（即使設定了並行度oracle也不做並行處理），只限delete update merge操作
我們做個insert並行測試吧
在insert測試中只有insert into ...... select ......做並行才有意義，insert into ......values ......單條插入沒有意義
LEO1@LEO1> explain plan for insert /*+ parallel(leo5 4) */ into leo5 select /*+ parallel(leo6 2) */ * from leo6;
Explained. 這種寫法只生成執行計劃不做實際插入操作，下面我們來看看執行計劃情況
LEO1@LEO1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 987566897
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 60249 | 11M| 170 (1)| 00:00:03 | | | |
| 1 | LOAD TABLE CONVENTIONAL | LEO5 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 60249 | 11M| 170 (1)| 00:00:03 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 60249 | 11M| 170 (1)| 00:00:03 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | LEO6 | 60249 | 11M| 170 (1)| 00:00:03 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
小結：執行計劃 insert和select操作別分使用了並行，它們是相互獨立的互不干涉
並行 執行計劃 PARALLEL_DEGREE_POLICY 10391 V$PQ_TQSTAT DDL DML
2013.1.25
天津&winter
分享技術~成就夢想
Blog：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-753355/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 原創：oracle 事務總結Oracle
- oracle 啟動篇總結(原創)Oracle
- 原創 oracle 資料完整性總結Oracle
- 【原創】Oracle execute plan 原理分析與例項分享Oracle
- 例項恢復相關原理精簡總結（原創）
- 【原創】Oracle RAC原理和安裝Oracle
- 【原創】ORACLE 分割槽與索引Oracle索引
- 【原創】Oracle 變數繫結與變數窺視合集Oracle變數
- 【原創】oracle spfile和pfile小結Oracle
- Oracle Buffer Cache原理總結(一)Oracle
- Oracle Buffer Cache原理總結(二)Oracle
- [原創]編寫反連ShellCode遇到的難點並解決之總結
- 原創：oracle DML介紹與使用Oracle
- zt_例項恢復instance recovery相關原理精簡總結（原創）
- 【原創】Oracle ASM發展與實踐OracleASM
- 【原創】Oracle RAC故障分析與處理Oracle
- oracle 10g資料庫備份與還原總結Oracle 10g資料庫
- 原創：ServletContext應用介紹總結ServletContext
- 滴滴順風車設計總結(原創文章)
- 原創：oracle data block 內部結構分析OracleBloC
- 【原創】Oracle 資料結構知多少（二）Oracle資料結構
- 【原創】Oracle 資料結構知多少（一）Oracle資料結構
- oracle統計表的所有行數(原創)Oracle
- 【原創】演示一個VPD進行資料訪問控制的示例
- 【原創】Oracle 事務探索與例項(二)Oracle
- 【原創】 Oracle 事務探索與例項(一)Oracle
- 【筆記】oracle 並行原理深入解析及案例精粹筆記Oracle並行
- [原創]總結：實施goldengate director的步驟Go
- 並查集的理解與實現總結並查集
- 多執行緒並行執行，然後彙總結果執行緒並行
- 【原創】Linux版 對普通使用者進行審計的示例Linux
- 【原創】Linux版 對sys使用者進行審計的示例Linux
- 【原創】 演示一個通過觸發器進行審計的示例觸發器
- [原創] Oracle資料庫聯機日誌檔案丟失處理方法（總結）！Oracle資料庫
- 資料結構 — 並查集的原理與應用資料結構並查集
- Java基礎 | Stream流原理與用法總結Java
- session原理總結Session
- 【原創】總結這個專案可複用的功能