在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上运行
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-2083481/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26015009/viewspace-2083481/