Oracle 10g RAC跨例項控制並行
在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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11g RAC跨例項控制並行Oracle並行
- Oracle 10g RAC增加節點例項Oracle 10g
- oracle 之 控制oracle RAC 進行並行運算Oracle並行
- oracle 10g rac 單例項恢復至ORACLE10G RAC RMANOracle 10g單例
- Oracle 10g 安裝及單例項遷移到RACOracle 10g單例
- RAC中跨節點並行並行
- 【RAC】Oracle 10g RAC 重建控制檔案Oracle 10g
- RAC跨節點使用並行程式的控制並行行程
- oracle單例項轉RACOracle單例
- RAC中的跨節點並行[轉]並行
- Oracle11gRAC跨節點 並行查詢的控制Oracle並行
- rac中控制節點間並行並行
- Oracle DataBase單例項遷移到Oracle RACOracleDatabase單例
- Oracle 10G RAC中去除asm例項與vip的依賴關係Oracle 10gASM
- 10g rac asm 恢復到 單例項(二)ASM單例
- 10g rac asm 恢復到 單例項(一)ASM單例
- 判斷oracle是否是rac例項Oracle
- ORACLE 10G rac故障處理一例Oracle 10g
- 【RAC】rac中如何指定job的執行例項
- Oracle 10G windows 平臺 DataGuard 例項Oracle 10gWindows
- Oracle 10g高階複製例項Oracle 10g
- OpenMP並行化例項----Mandelbrot集合並行化計算並行
- oracle rac及單例項開啟歸檔Oracle單例
- Oracle 11g RAC 配置單例項 DataGuardOracle單例
- 單例項和RAC打造的ORACLE STREAM（完）單例Oracle
- 單例項和RAC打造的ORACLE STREAM（四）單例Oracle
- 單例項和RAC打造的ORACLE STREAM（三）單例Oracle
- 單例項和RAC打造的ORACLE STREAM（二）單例Oracle
- 單例項和RAC打造的ORACLE STREAM（一）單例Oracle
- (轉)Oracle rac環境下清除asm例項OracleASM
- Oracle單例項+ASM新增控制檔案Oracle單例ASM
- RAC環境下單例項啟動Oracle資料庫重建控制檔案案例單例Oracle資料庫
- oracle資料庫跨平臺（AIX）從RAC恢復至（linux）下的單例項Oracle資料庫AILinux單例
- Oracle 10g rac升級需要注意的事項Oracle 10g
- Oracle 10g RAC 系統 OS啟動時CRS及例項自動啟動指令碼Oracle 10g指令碼
- oracle 10g asm 例項開關機順序Oracle 10gASM
- Oracle 10g RAC NFSOracle 10gNFS
- Oracle 10g RAC TAFOracle 10g