共计 43489 个字符，预计需要花费 109 分钟才能阅读完成。
在 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 sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
rac1
SQL> show parameter parallel_instance_group
NAME TYPE VALUE
———————————— ———– ——————————
parallel_instance_group string
SQL> show parameter instance_groups
NAME TYPE VALUE
———————————— ———– ——————————
instance_groups string
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
———————————— ———– ——————————
parallel_degree_policy string auto
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ parallel(t1,60) */ count(*) from t1;
COUNT(*)
———-
11121536
Elapsed: 00:00:41.25
Execution 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 sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
rac1
SQL> show parameter parallel_instance_group
NAME TYPE VALUE
———————————— ———– ——————————
parallel_instance_group string
SQL> show parameter instance_groups
NAME TYPE VALUE
———————————— ———– ——————————
instance_groups string
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
———————————— ———– ——————————
parallel_degree_policy string auto
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ parallel(t1,60) */ count(*) from t1;
COUNT(*)
———-
11121536
Elapsed: 00:00:41.25
Execution 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 0
58 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_group
NAME TYPE VALUE
———————————— ———– ——————————
parallel_instance_group string rac1
SQL> select /*+ parallel(t1,60) */ count(*) from t1;
COUNT(*)
———-
11121536
Elapsed: 00:00:41.25
Execution 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
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 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 sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
rac2
SQL> show parameter parallel_instance_group
NAME TYPE VALUE
———————————— ———– ——————————
parallel_instance_group string
SQL> show parameter instance_groups
NAME TYPE VALUE
———————————— ———– ——————————
instance_groups string
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
———————————— ———– ——————————
parallel_degree_policy string auto
SQL> set timing on
SQL> set autotrace on
SQL> select /*+ parallel(t1,60) */ count(*) from t1;
COUNT(*)
———-
11121536
Elapsed: 00:00:41.25
Execution 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
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 0
58 rows selected.
从上面的信息可以看到通过通用服务名 rac 虽然连接到的实例是 rac2, 在执行并行查询时并行子进程在实例 rac1 与 rac2 上运行，并不是只在 rac2 实例上运行。
4. 如果所连接的实例已经设置了 instance_groups，那么也可以设置 parallel_instance_group 来进行限制，就像 oracle 10g rac 一样。
实例 rac1:
SQL> alter system set instance_groups=’rac’,’rac1′ scope=spfile sid=’rac1′;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 669581312 bytes
Fixed Size 1366724 bytes
Variable Size 306185532 bytes
Database Buffers 356515840 bytes
Redo Buffers 5513216 bytes
Database mounted.
Database opened.
SQL> show parameter instance_groups
NAME TYPE VALUE
———————————— ———– ——————————
instance_groups string rac, rac1
SQL> show parameter parallel_instance_group
NAME TYPE VALUE
———————————— ———– ——————————
parallel_instance_group string
将 parallel_instance_group 设置为 rac，并行子进程可能会在实例 rac1 与 rac2 上运行
SQL> alter session set parallel_instance_group=’rac’;
Session altered.
SQL> show parameter parallel_instance_group
NAME TYPE VALUE
———————————— ———– ——————————
parallel_instance_group string rac
SQL> set autotrace on
SQL> select /*+ parallel(t1,60) */ count(*) from t1;
COUNT(*)
———-
11121536
Execution 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=5)
– Degree of Parallelism is 60 because of hint
Statistics
———————————————————-
250 recursive calls
4 db block gets
162870 consistent gets
158172 physical reads
0 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
0 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 1 PX Deq: Execute Reply 11283 200 1 0 0
1 35 PX Deq: Execution Msg 45 268566527 1 1113085912 0
1 86 PX Deq: Execution Msg 36 268566527 1 1039760268 0
1 85 PX Deq: Execution Msg 42 268566527 1 1039763628 0
1 84 PX Deq: Execution Msg 40 268566527 1 1039764108 0
1 83 PX Deq: Execution Msg 49 268566527 2 1039764588 0
1 81 PX Deq: Execution Msg 42 268566527 1 1113034840 0
1 80 PX Deq: Execution Msg 33 268566527 1 1113035320 0
1 78 PX Deq: Execution Msg 37 268566527 1 1113036280 0
1 77 PX Deq: Execution Msg 54 268566527 1 1113058080 0
1 75 PX Deq: Execution Msg 2 268566527 1 1039758828 -1
1 74 PX Deq: Execution Msg 50 268566527 2 1113088316 0
1 73 PX Deq: Execution Msg 54 268566527 1 1113037780 0
1 72 PX Deq: Execution Msg 50 268566527 1 1113062540 0
1 70 PX Deq: Execution Msg 46 268566527 1 1113063500 0
1 69 PX Deq: Execution Msg 54 268566527 1 1113038740 0
1 68 PX Deq: Execution Msg 60 268566527 1 1113060560 0
1 66 PX Deq: Execution Msg 43 268566527 2 1113061520 0
1 65 PX Deq: Execution Msg 56 268566527 1 1113039760 0
1 63 PX Deq: Execution Msg 64 268566527 1 1113040780 0
1 62 PX Deq: Execution Msg 59 268566527 1 1113040240 0
1 61 PX Deq: Execution Msg 69 268566527 1 1113059600 0
1 60 PX Deq: Execution Msg 70 268566527 2 1113057120 0
1 58 PX Deq: Execution Msg 46 268566527 2 1113041260 0
1 50 PX Deq: Execute Reply 5402 200 1 0 0
1 36 PX Deq: Execution Msg 41 268566527 1 1113037300 0
2 29 PX Deq: Execution Msg 175 268566527 2 1113084304 0
2 79 PX Deq: Execution Msg 99 268566527 2 1113067788 0
2 77 PX Deq: Execution Msg 123 268566527 2 1113068868 0
2 76 PX Deq: Execution Msg 119 268566527 2 1113069348 0
2 72 PX Deq: Execution Msg 146 268566527 2 1113071268 0
2 71 PX Deq: Execution Msg 131 268566527 2 1113071868 0
2 70 PX Deq: Execution Msg 105 268566527 2 1113072348 0
2 69 PX Deq: Execution Msg 147 268566527 2 1113072828 0
2 68 PX Deq: Execution Msg 109 268566527 2 1113073308 0
2 63 PX Deq: Execution Msg 155 268566527 2 1113087304 0
2 62 PX Deq: Execution Msg 129 268566527 2 1113074268 0
2 61 PX Deq: Execution Msg 113 268566527 2 1113096428 0
2 59 PX Deq: Execution Msg 149 268566527 2 1113087784 0
2 58 PX Deq: Execution Msg 134 268566527 2 1113085264 0
2 57 PX Deq: Execution Msg 155 268566527 2 1113084784 0
2 56 PX Deq: Execution Msg 156 268566527 2 1113082384 0
2 54 PX Deq: Execution Msg 167 268566527 2 1113081784 0
2 53 PX Deq: Execution Msg 147 268566527 2 1113085864 0
2 49 PX Deq: Execution Msg 144 268566527 2 1113083344 0
2 48 PX Deq: Execution Msg 167 268566527 2 1113095304 0
2 47 PX Deq: Execution Msg 128 268566527 2 1113083824 0
2 44 PX Deq: Execution Msg 14 268566527 2 1113067308 -1
2 37 PX Deq: Execution Msg 151 268566527 2 1113086344 0
2 36 PX Deq: Execution Msg 148 268566527 2 1113082864 0
2 35 PX Deq: Execution Msg 133 268566527 2 1113094824 0
2 78 PX Deq: Execution Msg 102 268566527 2 1113068388 0
52 rows selected.
从上面的信息可以看到当 instance_groups 设置为 ’rac’,’rac1’，且 parallel_instance_group 设置为 ’rac’ 时，在实例 rac1 上执行并行查询时并行子进程能在 rac1 与 rac2 实例上运行。
将 parallel_instance_group 设置为 rac1 时，并行子进程就只能在 rac1 实例上运行
SQL> alter session set parallel_instance_group=’rac1′;
Session altered.
SQL> show parameter parallel_instance_group
NAME TYPE VALUE
———————————— ———– ——————————
parallel_instance_group string rac1
SQL> select /*+ parallel(t1,60) */ count(*) from t1;
COUNT(*)
———-
11121536
Execution 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=5)
– Degree of Parallelism is 60 because of hint
Statistics
———————————————————-
140 recursive calls
0 db block gets
161150 consistent gets
158026 physical reads
0 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
0 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 1 PX Deq: Execute Reply 12755 200 1 0 0
1 34 PX Deq: Execution Msg 61 268566527 1 1113057120 0
1 35 PX Deq: Execution Msg 75 268566527 1 1113075080 0
1 36 PX Deq: Execution Msg 56 268566527 1 1113058640 0
1 47 PX Deq: Execution Msg 39 268566527 1 1113071544 0
1 50 PX Deq: Execute Reply 8426 200 1 0 0
1 58 PX Deq: Execution Msg 63 268566527 1 1113036280 0
1 60 PX Deq: Execution Msg 67 268566527 1 1113087836 0
1 61 PX Deq: Execution Msg 70 268566527 1 1113078440 0
1 62 PX Deq: Execution Msg 51 268566527 1 1113085912 0
1 63 PX Deq: Execution Msg 64 268566527 1 1039759788 0
1 64 PX Deq: Execution Msg 87 268566527 1 1113077960 0
1 65 PX Deq: Execution Msg 51 268566527 1 1113088316 0
1 67 PX Deq: Execution Msg 58 268566527 1 1113062540 0
1 68 PX Deq: Execution Msg 67 268566527 1 1113077000 0
1 69 PX Deq: Execution Msg 50 268566527 1 1113063500 0
1 70 PX Deq: Execution Msg 55 268566527 1 1113076520 0
1 72 PX Deq: Execution Msg 63 268566527 1 1113076040 0
1 73 PX Deq: Execution Msg 60 268566527 1 1113061520 0
1 74 PX Deq: Execution Msg 64 268566527 1 1113075560 0
1 75 PX Deq: Execution Msg 75 268566527 1 1113072052 0
1 76 PX Deq: Execution Msg 45 268566527 1 1039764108 0
1 78 PX Deq: Execution Msg 48 268566527 1 1113074060 0
1 79 PX Deq: Execution Msg 53 268566527 1 1113073520 0
1 80 PX Deq: Execution Msg 73 268566527 1 1113058080 0
1 81 PX Deq: Execution Msg 55 268566527 1 1113073040 0
1 82 PX Deq: Execution Msg 64 268566527 1 1039759308 0
1 83 PX Deq: Execution Msg 63 268566527 1 1113072560 0
1 84 PX Deq: Execution Msg 58 268566527 1 1039758348 0
1 86 PX Deq: Execution Msg 55 268566527 1 1113059600 0
1 87 PX Deq: Execution Msg 79 268566527 1 1113074540 0
1 88 PX Deq: Execution Msg 35 268566527 1 1113035320 0
1 89 PX Deq: Execution Msg 38 268566527 1 1113070976 0
1 92 PX Deq: Execution Msg 38 268566527 1 1039753980 0
1 93 PX Deq: Execution Msg 38 268566527 1 1113069840 0
1 94 PX Deq: Execution Msg 54 268566527 1 1039753500 0
1 95 PX Deq: Execution Msg 51 268566527 1 1113069332 0
1 96 PX Deq: Execution Msg 49 268566527 1 1039753020 0
1 98 PX Deq: Execution Msg 2 268566527 1 1113063980 -1
40 rows selected.
从上面的信息可以看到当 instance_groups 设置为 ’rac’,’rac1’，且 parallel_instance_group 设置为 ’rac1’ 时，在实例 rac1 上执行并行查询时并行子进程只能在 rac1 上运行。
parallel_instance_group
在 Oracle 10g 中 parallel_instance_group 的参数值必须是 instance_groups 的参数列表之一。在 11g 中这个列表已经扩展包含了所有当前被启用的服务。因此，parallel_instance_group 在 11g 中它的值可以在 instance_groups 的参数值中找到数据库当前已经启用的服务名之一
parallel_force_local
在 11gr2 中，parallel_force_local 控制着 Oracle RAC 环境中的并行执行。缺省情况下，并行执行的并行子进程可以在任何 RAC 节点或所有节点上运行。通过设置 parallel_force_lcoal 为 true，并行子进程将会受限制，因此它们只能在与查询协调者 (QC) 所在实例上执行。
SQL> alter session set parallel_force_local=true;
Session altered.
SQL> show parameter parallel_force_local
NAME TYPE VALUE
———————————— ———– ——————————
parallel_force_local boolean TRUE
SQL> select /*+ parallel(t1,60) */ count(*) from t1;
COUNT(*)
———-
11121536
Execution 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=5)
– Degree of Parallelism is 60 because of hint
Statistics
———————————————————-
140 recursive calls
0 db block gets
161150 consistent gets
158026 physical reads
0 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
0 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 1 PX Deq: Execute Reply 12755 200 1 0 0
1 34 PX Deq: Execution Msg 61 268566527 1 1113057120 0
1 35 PX Deq: Execution Msg 75 268566527 1 1113075080 0
1 36 PX Deq: Execution Msg 56 268566527 1 1113058640 0
1 47 PX Deq: Execution Msg 39 268566527 1 1113071544 0
1 50 PX Deq: Execute Reply 8426 200 1 0 0
1 58 PX Deq: Execution Msg 63 268566527 1 1113036280 0
1 60 PX Deq: Execution Msg 67 268566527 1 1113087836 0
1 61 PX Deq: Execution Msg 70 268566527 1 1113078440 0
1 62 PX Deq: Execution Msg 51 268566527 1 1113085912 0
1 63 PX Deq: Execution Msg 64 268566527 1 1039759788 0
1 64 PX Deq: Execution Msg 87 268566527 1 1113077960 0
1 65 PX Deq: Execution Msg 51 268566527 1 1113088316 0
1 67 PX Deq: Execution Msg 58 268566527 1 1113062540 0
1 68 PX Deq: Execution Msg 67 268566527 1 1113077000 0
1 69 PX Deq: Execution Msg 50 268566527 1 1113063500 0
1 70 PX Deq: Execution Msg 55 268566527 1 1113076520 0
1 72 PX Deq: Execution Msg 63 268566527 1 1113076040 0
1 73 PX Deq: Execution Msg 60 268566527 1 1113061520 0
1 74 PX Deq: Execution Msg 64 268566527 1 1113075560 0
1 75 PX Deq: Execution Msg 75 268566527 1 1113072052 0
1 76 PX Deq: Execution Msg 45 268566527 1 1039764108 0
1 78 PX Deq: Execution Msg 48 268566527 1 1113074060 0
1 79 PX Deq: Execution Msg 53 268566527 1 1113073520 0
1 80 PX Deq: Execution Msg 73 268566527 1 1113058080 0
1 81 PX Deq: Execution Msg 55 268566527 1 1113073040 0
1 82 PX Deq: Execution Msg 64 268566527 1 1039759308 0
1 83 PX Deq: Execution Msg 63 268566527 1 1113072560 0
1 84 PX Deq: Execution Msg 58 268566527 1 1039758348 0
1 86 PX Deq: Execution Msg 55 268566527 1 1113059600 0
1 87 PX Deq: Execution Msg 79 268566527 1 1113074540 0
1 88 PX Deq: Execution Msg 35 268566527 1 1113035320 0
1 89 PX Deq: Execution Msg 38 268566527 1 1113070976 0
1 92 PX Deq: Execution Msg 38 268566527 1 1039753980 0
1 93 PX Deq: Execution Msg 38 268566527 1 1113069840 0
1 94 PX Deq: Execution Msg 54 268566527 1 1039753500 0
1 95 PX Deq: Execution Msg 51 268566527 1 1113069332 0
1 96 PX Deq: Execution Msg 49 268566527 1 1039753020 0
1 98 PX Deq: Execution Msg 2 268566527 1 1113063980 -1
使用 parallel_force_local 要并使用 instance_groups 与 parallel_instance_group 参数来进行限制要方便很多。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-04/130544.htm
