在Oracle 10g RAC中的并行执行由两个参数:instance_groups和parallel_instance_group来控制。并且这两个参数必须同时使用才能生效。
instance_groups可以设置多个参数值，但它是静态参数，parallel_instance_groups可以在系统级别和会话级别运态进行修改。
在Oracle 10g RAC中，为了让并行执行可以在设置了parallel_instance_group参数的会话中执行，那么parallel_instance_group的参数值必须是该实例instance_groups参数列表中的一个。
例如，两节点的Oracle 10g RAC，在spfile文件中有以下设置
myrac1.instance_groups=’jyrac’,’jyrac1′
myrac2.instance_groups=’jyrac’,’jyrac2′
myrac1.parallel_instance_group=’jyrac1’#节点1的并行执行只能在节点1上运行
myrac2.parallel_instance_group=’jyrac2’#节点2的并行执行只能在节点2上运行
SQL> alter system set instance_groups='jyrac','jyrac1' scope=spfile sid='jyrac1'; System altered. SQL> alter system set instance_groups='jyrac','jyrac2' scope=spfile sid='jyrac2'; SQL> alter system set parallel_instance_group='jyrac1' scope=spfile sid='jyrac1'; System altered. SQL> alter system set parallel_instance_group='jyrac2' scope=spfile sid='jyrac2'; System altered.
上面的设置意味着在任何节点上启动的并行只能在该节点上执行。
SQL> show parameter instance_group
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups string jyrac, jyrac1
parallel_instance_group string jyrac1
SQL>set autotrace on
SQL> select /*+ parallel(t1,20) */ count(*) from t1;
COUNT(*)
----------
25962496
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4320 (1)| 00:00:52 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
45 recursive calls
0 db block gets
532872 consistent gets
355028 physical reads
2132 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
INST_ID SID EVENT SEQ# P1 P2 P3 WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
1 124 PX Deq: Execution Msg 1 268566527 1 0 -1
1 126 PX Deq: Execution Msg 8409 268566527 1 0 11
1 127 PX Deq: Execution Msg 7775 268566527 1 0 0
1 128 PX Deq: Execution Msg 8462 268566527 1 0 0
1 129 PX Deq: Execute Reply 3151 200 1 0 0
1 134 PX Deq: Execution Msg 9208 268566527 1 0 0
1 136 PX Deq: Execution Msg 9136 268566527 1 0 20
1 140 PX Deq: Execution Msg 8348 268566527 1 0 0
1 153 PX Deq: Signal ACK 59131 10 3 0 0
从上面的信息可以看到确实在实例jyrac1上执行的并行查询并行子进程只在jyrac1实例上运行
SQL> show parameter instance_group
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups string jyrac, jyrac2
parallel_instance_group string jyrac2
SQL> select /*+ parallel(t1,20) */ count(*) from t1;
COUNT(*)
----------
25962496
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4320 (1)| 00:00:52 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
60 recursive calls
0 db block gets
533765 consistent gets
354821 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
INST_ID SID EVENT SEQ# P1 P2 P3 WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
2 120 PX Deq: Execution Msg 1 268632063 1 0 0
2 121 PX Deq: reap credit 13 0 0 0 -1
2 122 PX Deq: Execution Msg 1 268632063 1 0 0
2 129 PX Deq: Execution Msg 1 268632063 1 0 0
2 153 PX Deq: Join ACK 3664 268566529 8 0 0
2 131 PX Deq: Execution Msg 1 268632063 1 0 0
2 132 PX Deq: Execution Msg 1 268632063 1 0 0
2 133 PX Deq: Execution Msg 1 268632063 1 0 0
2 135 PX Deq: Execution Msg 1 268632063 1 0 0
2 139 PX Deq: Execution Msg 1 268632063 1 0 0
2 140 PX Deq: Execution Msg 1 268632063 1 0 0
2 141 PX Deq: Execution Msg 1 268632063 1 0 0
2 147 PX Deq: Execution Msg 1 268632063 1 0 0
2 119 PX Deq: Execution Msg 1 268632063 1 0 0
2 118 PX Deq: Execution Msg 1 268632063 1 0 0
2 130 PX Deq: Execution Msg 1 268632063 1 0 0
从上面的信息可以看到确实在实例jyrac2上执行的并行查询并行子进程只在jyrac2实例上运行
可以根据需要使用相同的实例组来完成不同的需求
myrac1.instance_groups=’jyrac’,’jyrac1′
myrac2.instance_groups=’jyrac’,’jyrac2′
myrac1.parallel_instance_group=’jyrac1’#节点1的并行执行只能在节点1上运行
myrac2.parallel_instance_group=’jyrac1’#节点2的并行执行只能在节点1上运行
myrac1.parallel_instance_group=’jyrac’#节点1的并行执行能在所有节点上运行
将实例jyrac1的parallel_instance_group设置为’jyrac1’来测试节点1的并行执行是否只能在节点1上运行
SQL> show parameter instance_group
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups string jyrac, jyrac1
parallel_instance_group string jyrac1
SQL>set autotrace on
SQL> select /*+ parallel(t1,20) */ count(*) from t1;
COUNT(*)
----------
25962496
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4320 (1)| 00:00:52 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
45 recursive calls
0 db block gets
532872 consistent gets
355028 physical reads
2132 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
INST_ID SID EVENT SEQ# P1 P2 P3 WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
1 124 PX Deq: Execution Msg 1 268566527 1 0 -1
1 126 PX Deq: Execution Msg 8409 268566527 1 0 11
1 127 PX Deq: Execution Msg 7775 268566527 1 0 0
1 128 PX Deq: Execution Msg 8462 268566527 1 0 0
1 129 PX Deq: Execute Reply 3151 200 1 0 0
1 134 PX Deq: Execution Msg 9208 268566527 1 0 0
1 136 PX Deq: Execution Msg 9136 268566527 1 0 20
1 140 PX Deq: Execution Msg 8348 268566527 1 0 0
1 153 PX Deq: Signal ACK 59131 10 3 0 0
从上面的信息可以看到确实在实例jyrac1上执行的并行查询并行子进程只在jyrac1实例上运行将实例jyrac2的parallel_instance_group设置为’jyrac1’来测试节点2的并行执行是否只能在节点1上运行
SQL> alter session set parallel_instance_group='jyrac1';
Session altered.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
jyrac2
SQL> show parameter instance_group
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups string jyrac, jyrac2
parallel_instance_group string jyrac1
SQL> select /*+ parallel(t1,20) */ count(*) from t1;
COUNT(*)
----------
25962496
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4320 (1)| 00:00:52 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
21 recursive calls
0 db block gets
532751 consistent gets
354823 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
INST_ID SID EVENT SEQ# P1 P2 P3 WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
1 125 PX Deq: Execution Msg 8662 268632063 2 699436536 0
1 126 PX Deq: Execution Msg 9290 268632063 2 699434348 0
1 127 PX Deq: Execution Msg 8000 268632063 2 699433976 0
1 129 PX Deq: Execute Reply 7722 200 1 0 0
1 136 PX Deq: Execution Msg 1 268566527 1 0 -1
1 148 PX Deq: Execution Msg 8697 268632063 2 699439808 0
1 137 PX Deq: Execution Msg 8719 268632063 3 699432860 0
1 140 PX Deq: Execution Msg 8660 268632063 2 699433604 0
从上面的信息可以看到在实例jyrac2执行并行查询的并行子进程确实在jyrac1实例上运行的。
将实例jyrac1的parallel_instance_group设置为jyrac，来测试在jyrac1实例上执行并行查询的并行子进程将会在实例jyrac1,jyrac2上运行
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
jyrac1
SQL> alter session set parallel_instance_group='jyrac';
Session altered.
SQL> show parameter instance_group
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups string jyrac, jyrac1
parallel_instance_group string jyrac
SQL> select /*+ parallel(t1,20) */ count(*) from t1;
COUNT(*)
----------
25962496
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4320 (1)| 00:00:52 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 27M| 4320 (1)| 00:00:52 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
59 recursive calls
0 db block gets
533297 consistent gets
354823 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT inst_id,sid, event, seq#,p1,p2,p3, wait_time FROM gv$session_wait WHERE upper(event) like ('PX%') ORDER BY 1;
INST_ID SID EVENT SEQ# P1 P2 P3 WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
1 125 PX Deq: Execution Msg 4678 268566527 1 0 0
1 126 PX Deq: Execution Msg 4637 268566527 1 0 0
1 128 PX Deq: Execution Msg 4658 268566527 1 0 0
1 129 PX Deq: Execute Reply 10795 200 1 0 0
1 130 PX Deq: Execution Msg 1 268566527 1 0 -1
1 140 PX Deq: Execution Msg 4627 268566527 1 0 0
1 148 PX Deq: Execution Msg 4653 268566527 1 0 0
1 153 PX Deq: Execute Reply 59889 200 1 0 0
2 122 PX Deq: Execution Msg 3675 268566527 2 699422552 0
2 124 PX Deq: Execution Msg 3709 268566527 2 699424100 0
2 126 PX Deq: Execution Msg 4066 268566527 2 699426332 0
2 135 PX Deq: reap credit 13 0 0 0 -1
2 139 PX Deq: Execution Msg 4375 268566527 2 699420320 0
2 141 PX Deq: Execution Msg 4004 268566527 2 699423356 0
从上面的信息可以看到在jyrac1实例上执行并行查询的并行子进程确实在实例jyrac1,jyrac2上运行