Oracle 11g RAC跨例項控制並行

eric0435發表於2016-04-18

在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引數來進行限制要方便很多。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26015009/viewspace-2083499/,如需轉載,請註明出處,否則將追究法律責任。

相關文章