Oracle 11g RAC跨实例控制并行

在Oracle 10g RAC中的并行执行是由两个参数来控制的:instance_groups和parallel_instance_group这两个参数在Oracle 10g RAC中必须同时设置。instance_groups可以有多个值但是静态值，parallel_instance_group可以在系统级别与会话级别进行修改。在Oracle 10g RAC中，为了使用并行执行需要设置parallel_instance_group参数，并且parallel_instance_group的值必须是该实例instance_groups所指定的参数值之一。例如，有一个3节点的Oracle 10g RAC在spfile中有以下设置:
myrac1.instance_groups="rac","rac1"
myrac2.instance_groups="rac","rac2"
myrac3.instance_groups="rac","rac3"
myrac1.parallel_instance_group="rac1"#节点1的并行执行只能在节点1上运行
myrac2.parallel_instance_group="rac2"#节点2的并行执行只能在节点2上运行
myrac3.parallel_instance_group="rac3"#节点3的并行执行只能在节点3上运行在11gRAC中，因为向后兼容仍然可以使用instance_groups和parallel_instance_group参数。然而，在Oracle 11g RAC中不需要这样做，instance_groups参数已经被废弃并且保留只是为了向后兼容。在Oracle 11gRAC中并行查询子进程与服务集成在一起，因此不需要再设置instance_groups和parallel_instance_group参数。因为不需要设置instance_groups参数，而是可以直接设置服务名，
例如:alter session set parallel_instance_group=service_name。因为在11g中instance_groups参数已经被废弃，可以继续使用，但是它只是为了向后兼容。在Oracle 11g RAC中也可以不需要设置parallel_instance_groups参数来限制并行查询子进程在指定的实例上运行。如果以并行方式来执行SQL语句，那么缺省情况下并行进程只会在你通过服务名所连接到的数据库实例上运行。这不会影响其它的并行操作比如并行恢复或gv$视图的查询处理。为了覆盖这种行为，可以设置parallel_instance_group参数。可以使用srvctl add service命令来创建服务。例如，数据库名是rac且有2个实例，rac1和rac2。
对每个实例创建一个服务
[grid@rac1 ~]$ srvctl add service -d rac -s rac1 -r rac1 -a rac2
[grid@rac1 ~]$ srvctl add service -d rac -s rac2 -r rac2 -a rac1
[grid@rac1 ~]$ srvctl start service -d rac -s rac1
[grid@rac1 ~]$ srvctl start service -d rac -s rac2
上面的语句将创建两个服务，每个实例一个，-r参数指定首选实例，-a参数指定可用实例。
1.如果使用其中的一个服务连接数据库，并且没有显式地设置parallel_instance_groups参数，那么并行执行将被限制在你的连接的实例上执行例如，如果连接到rac1，因上连接到rac1实例，那么并行查询了进程将只能在rac1的r参数所指定的实例rac1上运行。
SQL> conn sys/system@rac1 as sysdbaSQL> select instance_name from v$instance;INSTANCE_NAME
----------------
rac1SQL> show parameter parallel_instance_groupNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group string
SQL> show parameter instance_groupsNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups stringSQL> show parameter parallel_degree_policyNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string auto
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ parallel（t1,60） */ count（*） from t1; COUNT（*）
----------
11121536Elapsed: 00:00:41.25Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320--------------------------------------------------------------------------------
------------------------| Id | Operation | Name | Rows | Cost （%CPU）| Time | T
Q |IN-OUT| PQ Distrib |--------------------------------------------------------------------------------
------------------------| 0 | SELECT STATEMENT | | 1 | 793 （0）| 00:00:01 |
| | || 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 | | 10M| 793 （0）| 00:00:01 | Q1,
00 | PCWC | || 6 | TABLE ACCESS FULL| T1 | 10M| 793 （0）| 00:00:01 | Q1,
00 | PCWP | |--------------------------------------------------------------------------------
------------------------
Note
-----
- dynamic sampling used for this statement （level=2）
- Degree of Parallelism is 60 because of hint
Statistics
----------------------------------------------------------
430 recursive calls
0 db block gets
281608 consistent gets
158184 physical reads
3328 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts （memory）
0 sorts （disk）
1 rows processed
在执行时查询并行子进程的是否只在rac1上运行
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 29 PX Deq: Execution Msg 54 268566527 1 1113052640 -1
1 105 PX Deq: Execution Msg 2 268566527 1 1113043256 -1
1 43 PX Deq: Execution Msg 57 268566527 1 1113057500 0
1 44 PX Deq: Execution Msg 55 268566527 1 1011415624 -1
1 50 PX Deq: Execution Msg 45 268566527 1 1113065368 -1
1 53 PX Deq: Execution Msg 64 268566527 1 1113070008 0
1 54 PX Deq: Execute Reply 8603 200 1 0 0
1 55 PX Deq: Execution Msg 59 268566527 1 1113049700 -1
1 62 PX Deq: Execution Msg 68 268566527 1 1113067520 0
1 69 PX Deq: Execution Msg 53 268566527 1 1113068028 0
1 74 PX Deq: Execution Msg 60 268566527 1 1113066504 -1 INST_ID SID EVENT SEQ# P1 P2 P3 WAIT_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
1 79 PX Deq: Execute Reply 641 200 1 0 0
1 80 PX Deq: Execution Msg 59 268566527 1 1113068568 -1
1 81 PX Deq: Execution Msg 63 268566527 1 1113069048 -1
1 88 PX Deq: Execution Msg 59 268566527 1 1113051680 0
1 97 PX Deq: Execution Msg 46 268566527 1 1011409340 0
16 rows selected.
可以看到并行子进程全都是在实例rac1上运行。2.一旦创建这些服务名之后，服务名rac1和rac2可以作为parallel_instance_groups的参数值，不管你使用那个服务名来进行数据库连接例如，如果使用通用的数据库服务名rac来进行连接，发现连接到了rac1实例，那么并行子进程会在当前所连接的实例或所有实例上运行。
SQL> conn sys/system@rac as sysdbaSQL> select instance_name from v$instance;INSTANCE_NAME
----------------
rac1SQL> show parameter parallel_instance_groupNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group string
SQL> show parameter instance_groupsNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups stringSQL> show parameter parallel_degree_policyNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string auto
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ parallel（t1,60） */ count（*） from t1; COUNT（*）
----------
11121536Elapsed: 00:00:41.25Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320--------------------------------------------------------------------------------
------------------------| Id | Operation | Name | Rows | Cost （%CPU）| Time | T
Q |IN-OUT| PQ Distrib |--------------------------------------------------------------------------------
------------------------| 0 | SELECT STATEMENT | | 1 | 793 （0）| 00:00:01 |
| | || 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 | | 10M| 793 （0）| 00:00:01 | Q1,
00 | PCWC | || 6 | TABLE ACCESS FULL| T1 | 10M| 793 （0）| 00:00:01 | Q1,
00 | PCWP | |--------------------------------------------------------------------------------
------------------------
Note
-----
- dynamic sampling used for this statement （level=2）
- Degree of Parallelism is 60 because of hint
Statistics
----------------------------------------------------------
430 recursive calls
0 db block gets
281608 consistent gets
158184 physical reads
3328 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts （memory）
0 sorts （disk）
1 rows processed
在执行时查询并行子进程的是否只在rac1上运行
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 29 PX Deq: Execution Msg 43 268566527 1 1113052640 0
1 33 PX Deq: Execute Reply 60924 200 1 0 0
1 104 PX Deq: Execution Msg 51 268566527 1 1113063216 0
1 103 PX Deq: Execution Msg 48 268566527 1 1011409820 0
1 101 PX Deq: Execution Msg 53 268566527 1 1113042688 0
1 100 PX Deq: Execution Msg 53 268566527 1 1011414604 0
1 99 PX Deq: Execution Msg 54 268566527 1 1113050660 0
1 91 PX Deq: Execution Msg 59 268566527 1 1113054620 0
1 90 PX Deq: Execution Msg 47 268566527 1 1011408860 0
1 88 PX Deq: Execution Msg 60 268566527 1 1113043256 0
1 87 PX Deq: Execution Msg 45 268566527 1 1113046760 0
1 86 PX Deq: Execution Msg 52 268566527 1 1113065936 0
1 85 PX Deq: Execution Msg 45 268566527 1 1113062708 0
1 84 PX Deq: Execution Msg 59 268566527 1 1011409340 0
1 83 PX Deq: Execution Msg 45 268566527 1 1113043764 0
1 82 PX Deq: Execution Msg 46 268566527 1 1113059480 0
1 81 PX Deq: Execution Msg 52 268566527 1 1113064800 0
1 80 PX Deq: Execution Msg 43 268566527 1 1113066504 0
1 76 PX Deq: Execution Msg 61 268566527 1 1113056540 0
1 69 PX Deq: Execution Msg 48 268566527 1 1113067012 0
1 68 PX Deq: Execution Msg 48 268566527 1 1113049700 0
1 66 PX Deq: Execution Msg 61 268566527 1 1011415624 0
1 64 PX Deq: Execution Msg 2 268566527 1 1113068028 -1
1 62 PX Deq: Execution Msg 39 268566527 1 1113067520 0
1 59 PX Deq: Execution Msg 49 268566527 1 1113044840 0
1 58 PX Deq: Execution Msg 47 268566527 1 1113048740 0
1 57 PX Deq: Execution Msg 50 268566527 1 1113064232 0
1 54 PX Deq: Execute Reply 11610 200 1 0 0
1 53 PX Deq: Execution Msg 52 268566527 1 1113065368 0
1 43 PX Deq: Execution Msg 42 268566527 1 1113047780 0
2 80 PX Deq: Execution Msg 133 268566527 2 1112541100 0
2 83 PX Deq: Execution Msg 119 268566527 2 1112529160 0
2 78 PX Deq: Execution Msg 107 268566527 2 1112534620 0
2 77 PX Deq: Execution Msg 144 268566527 2 1112535100 0
2 76 PX Deq: Execution Msg 146 268566527 2 1112540140 0
2 74 PX Deq: Execution Msg 118 268566527 2 1112530660 0
2 73 PX Deq: Execution Msg 150 268566527 2 1112536660 0
2 72 PX Deq: Execution Msg 125 268566527 2 1112531140 0
2 71 PX Deq: Execution Msg 157 268566527 2 1112533660 0
2 70 PX Deq: Execution Msg 146 268566527 2 1112530180 0
2 69 PX Deq: Execution Msg 135 268566527 2 1112532640 0
2 67 PX Deq: Execution Msg 128 268566527 2 1112556120 0
2 66 PX Deq: Execution Msg 135 268566527 2 1112532100 0
2 65 PX Deq: Execution Msg 124 268566527 2 1112555640 0
2 63 PX Deq: Execution Msg 126 268566527 2 1112528620 0
2 62 PX Deq: Execution Msg 183 268566527 2 1112537140 0
2 60 PX Deq: Execution Msg 182 268566527 2 1112541580 0
2 59 PX Deq: Execution Msg 103 268566527 2 1112535580 0
2 58 PX Deq: Execution Msg 160 268566527 2 1112539600 0
2 57 PX Deq: Execution Msg 164 268566527 2 1112538580 0
2 56 PX Deq: Execution Msg 124 268566527 2 1112527528 0
2 53 PX Deq: Execution Msg 145 268566527 2 1112539060 0
2 49 PX Deq: Execution Msg 140 268566527 2 1112538100 0
2 37 PX Deq: Execution Msg 112 268566527 2 1112537620 0
2 36 PX Deq: Execution Msg 153 268566527 1 1112536120 0
2 34 PX Deq: Execution Msg 14 268566527 2 1112531620 -1
2 81 PX Deq: Execution Msg 157 268566527 2 1112533180 0
2 79 PX Deq: Execution Msg 128 268566527 2 1112557244 058 rows selected.
从上面的信息可以看到通过通用服务名rac虽然连接到的实例是rac1,在执行并行查询时并行子进程在实例rac1与rac2上运行，并不是只在rac1实例上运行。如果你想限制你的查询只在rac1实例上执行，那么执行
alter session set parallel_instance_group=rac1命令，这将限制并行子进程只在指定服务名所相关的实例上运行，比如rac1。
SQL> alter session set parallel_instance_group="rac1";Session altered.SQL> show parameter parallel_instance_groupNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group string rac1SQL> select /*+ parallel（t1,60） */ count（*） from t1; COUNT（*）
----------
11121536Elapsed: 00:00:41.25Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320--------------------------------------------------------------------------------
------------------------| Id | Operation | Name | Rows | Cost （%CPU）| Time | T
Q |IN-OUT| PQ Distrib |--------------------------------------------------------------------------------
------------------------| 0 | SELECT STATEMENT | | 1 | 793 （0）| 00:00:01 |
| | || 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 | | 10M| 793 （0）| 00:00:01 | Q1,
00 | PCWC | || 6 | TABLE ACCESS FULL| T1 | 10M| 793 （0）| 00:00:01 | Q1,
00 | PCWP | |--------------------------------------------------------------------------------
------------------------
Note
-----
- dynamic sampling used for this statement （level=2）
- Degree of Parallelism is 60 because of hint
Statistics
----------------------------------------------------------
430 recursive calls
0 db block gets
281608 consistent gets
158184 physical reads
3328 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts （memory）
0 sorts （disk）
1 rows processedSQL> 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 43 PX Deq: Execution Msg 61 268566527 2 1113060496 0
1 53 PX Deq: Execution Msg 71 268566527 2 1011415624 0
1 54 PX Deq: Parse Reply 28202 200 1 0 -1
1 55 PX Deq: Execution Msg 64 268566527 1 1011418240 0
1 57 PX Deq: Execution Msg 88 268566527 1 1113048740 0
1 58 PX Deq: Execution Msg 53 268566527 1 1113061632 0
1 62 PX Deq: Execution Msg 66 268566527 3 1011420904 0
1 64 PX Deq: Execution Msg 58 268566527 1 1011407468 0
1 66 PX Deq: Execution Msg 75 268566527 2 1113061124 0
1 67 PX Deq: Execution Msg 2 268566527 1 1113079488 -1
1 68 PX Deq: Execution Msg 76 268566527 2 1011417732 0
1 69 PX Deq: Execution Msg 56 268566527 3 1011420424 0
1 76 PX Deq: Execution Msg 70 268566527 1 1011408860 0
1 79 PX Deq: Execute Reply 672 200 1 0 0
1 80 PX Deq: Execution Msg 75 268566527 1 1011419944 0
1 81 PX Deq: Execution Msg 75 268566527 1 1011409340 0
1 82 PX Deq: Execution Msg 63 268566527 1 1113054620 0
1 83 PX Deq: Execution Msg 62 268566527 2 1011407948 0
1 85 PX Deq: Execution Msg 65 268566527 3 1113049700 0
1 86 PX Deq: Execution Msg 54 268566527 2 1113047780 0
1 88 PX Deq: Execution Msg 65 268566527 3 1113059480 0
1 90 PX Deq: Execution Msg 83 268566527 2 1113049220 0
1 91 PX Deq: Execution Msg 65 268566527 1 1113052640 0
1 101 PX Deq: Execution Msg 69 268566527 2 1113053600 0
1 102 PX Deq: Execution Msg 56 268566527 2 1113056540 0
1 103 PX Deq: Execution Msg 70 268566527 2 1113044840 0
1 104 PX Deq: Execution Msg 70 268566527 2 1113046760 0

28 rows selected.
可以看到在设置parallel_instance_group="rac1"后，并行子进程只能在实例rac1上运行了。3.如果连接的服务名运行在两个实例上，比如:srvctl add service -d rac -s rac -r rac1,rac2那么，并行查询的并行子进程将会运行在两个实例上，不管你所连接的是那个实例。
SQL> conn sys/system@rac as sysdbaSQL> select instance_name from v$instance;INSTANCE_NAME
----------------
rac2SQL> show parameter parallel_instance_groupNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_instance_group string
SQL> show parameter instance_groupsNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups stringSQL> show parameter parallel_degree_policyNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string auto
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ parallel（t1,60） */ count（*） from t1; COUNT（*）
----------
11121536Elapsed: 00:00:41.25Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320--------------------------------------------------------------------------------
------------------------| Id | Operation | Name | Rows | Cost （%CPU）| Time | T
Q |IN-OUT| PQ Distrib |--------------------------------------------------------------------------------
------------------------| 0 | SELECT STATEMENT | | 1 | 793 （0）| 00:00:01 |
| | || 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 | | 10M| 793 （0）| 00:00:01 | Q1,
00 | PCWC | || 6 | TABLE ACCESS FULL| T1 | 10M| 793 （0）| 00:00:01 | Q1,
00 | PCWP | |--------------------------------------------------------------------------------
------------------------
Note
-----
- dynamic sampling used for this statement （level=2）
- Degree of Parallelism is 60 because of hint
Statistics
----------------------------------------------------------
430 recursive calls
0 db block gets
281608 consistent gets
158184 physical reads
3328 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts （memory）
0 sorts （disk）
1 rows processedSQL> 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 29 PX Deq: Execution Msg 43 268566527 1 1113052640 0
1 33 PX Deq: Execute Reply 60924 200 1 0 0
1 104 PX Deq: Execution Msg 51 268566527 1 1113063216 0
1 103 PX Deq: Execution Msg 48 268566527 1 1011409820 0
1 101 PX Deq: Execution Msg 53 268566527 1 1113042688 0
1 100 PX Deq: Execution Msg 53 268566527 1 1011414604 0
1 99 PX Deq: Execution Msg 54 268566527 1 1113050660 0
1 91 PX Deq: Execution Msg 59 268566527 1 1113054620 0
1 90 PX Deq: Execution Msg 47 268566527