原理：

將一個任務拆分成多個小任務同時處理，發起該sql的伺服器程式成為query coordinator程式，負責協調排程slave processes並將其結果集整合返回給客戶端；

並行操作的granule有兩種：partition granule和block range granule，後者是sql執行時動態定義的，一般更能平均的在salve processes之間分配，而並行處理的速度是由最慢的那個slave process決定的；

當一條sql執行多個操作時，例如掃描和排序，則會分配多組slave processes;

單個操作的並行化稱為intra-operation parallelism，而多組slave processes之間的互動則為inter-operation parallelism，後者則導致各組之間出現通訊；

傳送資料的程式為producer，而接受的程式則為consumer，producer透過SGA中的table queue來給consumer傳送資料，每對producer-consumer都對應一個table queue；

兩者可採用以下方式進行資料通訊：

廣播—每個producer傳送資料給所有的consumer

迴圈—producer採用輪循的方式給所有consumer傳送記錄

範圍—producer將指定範圍的記錄發給特定的consumer

Hash—producer利用hash函式決定接受資料的consumer

QC Random—每個producer都將記錄隨機發給coordinator，順序不重要

QC order--每個producer都將記錄按序發給coordinator

在並行操作的執行計劃裡可以看到如下的操作：

P -> S ---並行傳送資料到序列，例如，每個執行計劃中最後向coordinator程式傳送資料都是採用這種方式

P -> P ---一個並行操作傳送資料給另一個並行操作，當存在多組並行程式時會用到

S -> P ---序列傳送資料給並行，此操作效率比較差，應該儘量避免

PCWP---並行與父操作合併，此為同一組內的程式互動，因此沒有組間通訊

PCWC---並行與子操作合併，也為同組程式互動，沒有組間通訊

引數配置

每個instance能夠使用的並行程式數量是有限的，instance會維護一個slave process pool，類似連線池，每次coordinator會從中請求slave process執行完成後再將其返還；

parallel_min_servers：指定instance啟動時建立的slave process數量，預設為0；通常只有在sql花費過多時間建立slave process時才修改此值，此操作相關的等待事件為os thread startup；

parallel_max_servers：指定slave process的最大可用數量；

parallel_execution_message_size：前面提到的table queue存在於large pool(專門存放不可重用的資料結構)中，每個table queue有3-4個緩衝區組成，該引數用來定義該緩衝區大小；

parallel_automatic_tuning：10g開始已經不推薦使用了，設定為true時ORACLE會使用large pool處理table queue

parallel_min_percent：預設為0，可設定成0-100，為0時表明oracle將盡可能的提供足夠多的slave process，如果可分配的數量小於2則進行序列操作；若設定成非0值，則至少為sql提供指定比例的slave process數量，否則會報ora-12827，例如parallel_min_percent=25且有sql請求16個子程式，則至少提供16*25/100=4個，否則ora-12827；

parallel_adaptive_multi_user：10g預設為true；為false時，只要還有子程式，sql請求多少就分配給多少；true則oracle會根據實際情形下調sql的並行度以保證slave process不會被耗盡；

如何使用parallel

有三種方式：

1 指定table/index的並行度

2使用parallel提示

3在session級別enable parallel query/DML/DDL，其中query是預設開啟的

三者關係為hint優先順序最高，而force parallel又可以覆蓋表或索引級別定義的並行度；而要想在instance徹底禁止並行操作，可將parallel_max_servers設定為0；

何時使用

只有在滿足以下兩個條件時使用並行操作才能達到最佳效果：

1 系統有大量的閒置資源(CPU,I/O和記憶體)

2 sql序列執行時間過長，比如超過10秒，因為並行操作的初始化操作(建立slave process和table queue)也是耗費資源的，

常用檢視

v$px_process_sysstat—shows the status of query servers and provides buffer allocation statistics

V$PQ_SESSTAT—列出session級別的並行資訊

V$PQ_SYSSTAT—列出system級別的並行資訊

V$PQ_SLAVE—列出每一個active並行程式的資訊

V$PQ_TQSTAT— provides a detailed report of message traffic at the table queue，data is valid only when queried from a session that is executing parallel SQL stateme記錄當前session的並行執行的資訊

V$PX_BUFFER_ADVICE-- provides statistics on historical and projected maximum buffer usage by all parallel queries. You can consult this view to reconfigure SGA size in response to insufficient memory problems for parallel queries

V$PX_PROCESS-- contains information about the parallel processes, including status, session ID, process ID, and other information

V$PX_PROCESS_SYSSTAT-- shows the status of query servers and provides buffer allocation statistics

V$PX_SESSION-- shows data about query server sessions, groups, sets, and server numbers. It also displays real-time data about the processes working on behalf of parallel execution. This table includes information about the requested degree of parallelism (DOP) and the actual DOP granted to the operation.

V$PX_SESSTAT-- provides a join of the session information from V$PX_SESSION and the V$SESSTAT table.

檢視並行程式的coordinator資訊，

可以使用如下兩個SQL查詢，其中第一個來源

第二個則是

col username for a12

col "QC SID" for A6

col "SID" for A6

col "QC/Slave" for A8

col "Req. DOP" for 9999

col "Actual DOP" for 9999

col "Slaveset" for A8

col "Slave INST" for A9

col "QC INST" for A6

set pages 300 lines 300

col wait_event format a30

select

decode(px.qcinst_id,NULL,username,' - '||lower(substr(pp.SERVER_NAME,

length(pp.SERVER_NAME)-4,4) ) )"Username",

decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

to_char( px.server_set) "SlaveSet",

to_char(s.sid) "SID",

to_char(px.inst_id) "Slave INST",

decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,

case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,

decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

to_char(px.qcinst_id) "QC INST",

px.req_degree "Req. DOP",

px.degree "Actual DOP"

from gv$px_session px,

gv$session s ,

gv$px_process pp,

gv$session_wait sw

where px.sid=s.sid (+)

and px.serial#=s.serial#(+)

and px.inst_id = s.inst_id(+)

and px.sid = pp.sid (+)

and px.serial#=pp.serial#(+)

and sw.sid = s.sid

and sw.inst_id = s.inst_id

order by

decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),

px.QCSID,

decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),

px.SERVER_SET,

px.INST_ID；

SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",

px.SERVER_GROUP "Group", px.SERVER_SET "Set",

px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"

FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w

WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND

s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND

s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+)

ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID,

DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID

檢視並行程式的物理讀資訊

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",

NAME "Stat Name", VALUE

FROM GV$PX_SESSTAT A, V$STATNAME B

WHERE A.STATISTIC# = B.STATISTIC# AND NAME LIKE 'PHYSICAL READS'

AND VALUE > 0 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

QCSID SID Inst Group Set Stat Name VALUE

------ ----- ------ ------ ------ ------------------ ----------

9 9 1 physical reads 3863

9 7 1 1 1 physical reads 2

9 21 1 1 1 physical reads 2

9 18 1 1 2 physical reads 2

9 20 1 1 2 physical reads 2

檢視系統中與parallel有關的資訊

SELECT NAME, VALUE FROM GV$SYSSTAT

WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'

OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

NAME VALUE

-------------------------------------------------- ----------

queries parallelized 347

DML statements parallelized 0

DDL statements parallelized 0

DFO trees parallelized 463

Parallel operations not downgraded 28

Parallel operations downgraded to serial 31

Parallel operations downgraded 75 to 99 pct 252

Parallel operations downgraded 50 to 75 pct 128

Parallel operations downgraded 25 to 50 pct 43

Parallel operations downgraded 1 to 25 pct 12

PX local messages sent 74548

PX local messages recv'd 74128

PX remote messages sent 0

PX remote messages recv'd 0

檢視px佔用的記憶體資訊—該instance沒有分配large pool

SQL> select * from v$sgastat where name like 'PX%';

POOL NAME BYTES

------------ -------------------------- ----------

shared pool PX subheap 11651336

shared pool PX msg pool 103310848

shared pool PX QC deq stats 1480

shared pool PX QC msg stats 2288

shared pool PX subheap desc 256

shared pool PX msg pool struct 1088

shared pool PX server deq stats 1480

shared pool PX server msg stats 2288

案例

create table t as select owner, object_name name from dba_objects where owner in ('SYSMAN','ORDSYS','PUBLIC','SYS');

create table m(owner varchar2(20));

insert into m values('SYS');

--收集統計資訊

執行select * from t, m where t.owner=m.owner and m.owner='SYS';

當兩個表都不開啟並行時

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 23736 | 2202K| 55 (4)| 00:00:01 |

|* 1 | HASH JOIN | | 23736 | 2202K| 55 (4)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| M | 1 | 12 | 2 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T | 23736 | 1923K| 52 (2)| 00:00:01 |

---------------------------------------------------------------------------

只為表T開啟並行alter table t parallel 4;

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 23213 | 770K| 17 (6)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10001 | 23213 | 770K| 17 (6)| 00:00:01 | Q1,01 | P->S | QC (RAND) |

|* 3 | HASH JOIN | | 23213 | 770K| 17 (6)| 00:00:01 | Q1,01 | PCWP | |

| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |

| 5 | PX RECEIVE | | 1 | 4 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |

| 6 | PX SEND BROADCAST | :TQ10000 | 1 | 4 | 2 (0)| 00:00:01 | | S->P | BROADCAST |

|* 7 | TABLE ACCESS FULL| M | 1 | 4 | 2 (0)| 00:00:01 | | | |

| 8 | PX BLOCK ITERATOR | | 23213 | 680K| 14 (0)| 00:00:01 | Q1,01 | PCWC | |

|* 9 | TABLE ACCESS FULL | T | 23213 | 680K| 14 (0)| 00:00:01 | Q1,01 | PCWP | |

-----------------------------------------------------------------------------------------------------------------

--訪問表T用到了並行，操作粒度為block，而M依舊是序列訪問，第6步出現了S->P，並且是透過廣播的方式向並行程式傳送資訊；

為表T開啟並行alter table m parallel 4;

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 23213 | 770K| 17 (6)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10001 | 23213 | 770K| 17 (6)| 00:00:01 | Q1,01 | P->S | QC (RAND) |

|* 3 | HASH JOIN | | 23213 | 770K| 17 (6)| 00:00:01 | Q1,01 | PCWP | |

| 4 | PX RECEIVE | | 1 | 4 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |

| 5 | PX SEND BROADCAST | :TQ10000 | 1 | 4 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |

| 6 | PX BLOCK ITERATOR | | 1 | 4 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |

|* 7 | TABLE ACCESS FULL| M | 1 | 4 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |

| 8 | PX BLOCK ITERATOR | | 23213 | 680K| 14 (0)| 00:00:01 | Q1,01 | PCWC | |

|* 9 | TABLE ACCESS FULL | T | 23213 | 680K| 14 (0)| 00:00:01 | Q1,01 | PCWP | |

-----------------------------------------------------------------------------------------------------------------

--此時M也使用並行訪問，操作粒度為block；因為sql沒有要求排序，最後向coordinator使用RAND方式傳送資料

--M的結果集使用廣播的方式傳送資料，使用hint /*+ pq_distribute(t,hash,hash) */可以將其改為Hash

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 23213 | 770K| 17 (6)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10002 | 23213 | 770K| 17 (6)| 00:00:01 | Q1,02 | P->S | QC (RAND) |

|* 3 | HASH JOIN BUFFERED | | 23213 | 770K| 17 (6)| 00:00:01 | Q1,02 | PCWP | |

| 4 | PX RECEIVE | | 1 | 4 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |

| 5 | PX SEND HASH | :TQ10000 | 1 | 4 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |

| 6 | PX BLOCK ITERATOR | | 1 | 4 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |

|* 7 | TABLE ACCESS FULL| M | 1 | 4 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |

| 8 | PX RECEIVE | | 23213 | 680K| 14 (0)| 00:00:01 | Q1,02 | PCWP | |

| 9 | PX SEND HASH | :TQ10001 | 23213 | 680K| 14 (0)| 00:00:01 | Q1,01 | P->P | HASH |

| 10 | PX BLOCK ITERATOR | | 23213 | 680K| 14 (0)| 00:00:01 | Q1,01 | PCWC | |

|* 11 | TABLE ACCESS FULL| T | 23213 | 680K| 14 (0)| 00:00:01 | Q1,01 | PCWP | |

-----------------------------------------------------------------------------------------------------------------

oracle並行程式小結

相關文章