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 10g RAC故障處理Oracle 10g
- oracle資料庫跨平臺（AIX）從RAC恢復至（linux）下的單例項Oracle資料庫AILinux單例
- Oracle 11g RAC到單例項OGG同步Oracle單例
- Oracle 10g RAC 資料儲存更換Oracle 10g
- AIX 5.3 Install Oracle 10g RAC 錯誤集錦AIOracle 10g
- ORACLE11GR2 RAC解除安裝ASM例項步驟OracleASM
- oracle rac 單個例項不能生成awr報告的問題Oracle
- ORA-29702複製RAC Oracle軟體啟動單例項Oracle單例
- 10g RAC on AIXAI
- rac恢復到單例項單例
- RAC+DG(asm單例項)ASM單例
- Oracle 例項和RAC叢集下資料庫日誌目錄合集Oracle資料庫
- 記錄一次Oracle 11.2.0.4 RAC異地恢復到單例項Oracle單例
- Oracle 資料庫巡檢指令碼 單例項 RAC 輸出HTML格式Oracle資料庫指令碼單例HTML
- 4.1. Oracle例項Oracle
- Oracle Far Sync例項Oracle
- oracle 12c RAC安裝，例項不能多節點同時啟動Oracle
- RAC+單例項DG的切換單例
- oracle資料庫與oracle例項Oracle資料庫
- 多個資料庫是否可以共有一個Oracle 11g RAC例項KG資料庫Oracle
- oracle 例項表查詢Oracle
- C＃並行，多執行緒程式設計並行集合和PLINQ的例項講解並行執行緒程式設計
- Oracle並行FAQOracle並行
- oracle RACOracle
- Oracle RAC Cache Fusion 系列十七：Oracle RAC DRMOracle
- 將RAC軟體轉換為單例項軟體單例
- 【Oracle】ASM例項安裝入門OracleASM
- oracle 10203啟動例項報警Oracle
- oracle監聽不到例項服務Oracle
- Solaris 10下遷移10G RAC (六)
- Solaris 10下遷移10G RAC (八)
- Solaris 10下遷移10G RAC (四)
- Solaris 10下遷移10G RAC (二)
- Solaris 10下遷移10G RAC (七)
- Solaris 10下遷移10G RAC (三)
- Solaris 10下遷移10G RAC (一)
- Solaris 10下遷移10G RAC (五)
- Oracle 11.2 DataGuard RAC To RAC搭建Oracle
- oracle 10g flashback databaseOracle 10gDatabase