The parallel_instance_group and instance_groups parameters of rac and their parallel settings

Parameters [, group_name]... DefaultvalueThereisnodefaultvalue. ModifiableNoRangeofvaluesOneormoreinstancegroupnames, separatedbycommasBasic

INSTANCE_GROUPS PropertyDescription Parameter typeString SyntaxINSTANCE_GROUPS = group_name [, group_name]... Default valueThere is no default value. ModifiableNo Range of valuesOne or more instance group names, separated by commas Basic

INSTANCE_GROUPS
PropertyDescription
Parameter typeString
SyntaxINSTANCE_GROUPS = group_name [, group_name ] ...
Default valueThere is no default value.
ModifiableNo
Range of valuesOne or more instance group names, separated by commas
BasicNo
Real Application ClustersMultiple instances can have different values.

INSTANCE_GROUPS is a Real Application Clusters parameter that you can specify only in parallel mode. Used in conjunction with the PARALLEL_INSTANCE_GROUPparameter, it lets you restrict parallel query operations to a limited number of instances.

This parameter specifies one or more instance groups and assigns the current instance to those groups. If one of the specified groups is also specified in thePARALLEL_INSTANCE_GROUP parameter, then Oracle allocates query processes for a parallel operation from this instance.

The instance_groups parameter is the specified parallel mode instance group. It can be used together with parallel_instance_group to specify one or more instance groups.

PARALLEL_INSTANCE_GROUP
PropertyDescription
Parameter typeString
SyntaxPARALLEL_INSTANCE_GROUP = group_name
Default valueA group consisting of all instances currently active
ModifiableALTER SESSION, ALTER SYSTEM
Range of valuesAny group name specified in the INSTANCE_GROUPS parameter of any active instance
Real Application ClustersMultiple instances can have different values.

PARALLEL_INSTANCE_GROUP is a Real Application Clusters parameter that you can specify in parallel mode only. Used in conjunction with the INSTANCE_GROUPSparameter, it lets you restrict parallel query operations to a limited number of instances.

This parameter identifies the parallel instance group Oracle will use for spawning parallel execution processes. Parallel operations will spawn parallel execution processes only on instances that specify a matching group in their INSTANCE_GROUPS parameter.

If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a group that does not exist, then the operation runs serially. No parallelism is used.

The parallel_instance_group parameter is also a parallel mode parameter. It is used in combination with instance_groups. oracle only assigns the parameter to the specified instance for parallel execution. The instance must be consistent with the instance recorded in the instance_groups parameter.
If the parameter specified by parallel_instance_group does not exist in the instance group instance_groups, oracle cannot start parallelism.
The English level of ainemo is not high, and there are some doubts about translation. In a simple summary, the instance_groups parameter records all instance groups that can be selected for parallel execution, parallel_instance_group is an instance of the specified parallel execution. We will test it manually:

The default instance_groups and parallel_instance_group parameters are not set.
SQL> show parameter instance_group;
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Instance_groups string
Parallel_instance_group string

Add parallel hint to query a view with a large data volume.
SQL> select/* + parallel (a 2) */count (*) from t01_view;
[Root @ rac01 ~] # Ps-ef | grep p0 | grep-v psp0
Oracle 10898 1 0? 00:00:00 ora_p000_xiaoyu1
Oracle 10900 1 0? 00:00:00 ora_p0020.xiaoyu1
Root 12081 7995 0 00:00:00 pts/2 grep p0
[Root @ rac02 ~] # Ps-ef | grep p0 | grep-v psp0
Oracle 3445 1 0? 00:00:00 ora_p000_xiaoyu2
Oracle 3447 1 0? 00:00:00 ora_p0020.xiaoyu2
Root 4700 9157 0 00:00:00 pts/2 grep p0
At this time, this parallel process is owned by multiple instances. This indicates that when the instance_group and parallel_instance_group parameters are not set by default, parallel queries can span instances.

If we want to control the actual execution of parallel queries, that is, do not span the instance
Parameters of the Instance xiaoyu1:
SQL> show parameter instance_group;
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Instance_groups string xiaoyu, xiaoyu1
Parallel_instance_group string xiaoyu1
Parameters of instance xiaoyu2:
SQL> show parameter instance_group;
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Instance_groups string xiaoyu, xiaoyu2
Parallel_instance_group string
Instance 1 initiates a query
SQL> select/* + parallel (a 2) */count (*) from t01_view;
[Root @ rac01 ~] # Ps-ef | grep p0 | grep-v psp0
Oracle 22975 1 1? 00:00:00 ora_p000_xiaoyu1
Oracle 22977 1 1? 00:00:00 ora_p0020.xiaoyu1
Oracle 22979 1 0? 00:00:00 ora_p002_xiaoyu1
Oracle 22981 1 0? 00:00:00 ora_p003_xiaoyu1
Root 23414 7995 0 00:00:00 pts/2 grep p0
[Root @ rac02 ~] # Ps-ef | grep p0 | grep-v psp0
Root 14032 9157 0 00:00:00 pts/2 grep p0
It can be seen that the parallel query can only be in the instance_groups instance group. Because xiaoyu1 is not set for instance_groups of instance xiaoyu2, the parallel process can only be in instance_group = 'xiaoyu ', run on the instance xiaoyu1.

Parameters of instance 1:
SQL> show parameter instance_group;
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Instance_groups string xiaoyu, xiaoyu1
Parallel_instance_group string xiaoyu
Parameters of instance 2:
SQL> show parameter instance_group;
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Instance_groups string xiaoyu, xiaoyu2
Parallel_instance_group string
Instance 1 initiates a query:
SQL> select/* + parallel (a 2) */count (*) from t01_view;
[Root @ rac01 ~] # Ps-ef | grep p0 | grep-v psp0
Oracle 5431 1 2? 00:00:00 ora_p000_xiaoyu1
Oracle 5437 1 5? 00:00:00 ora_p0020.xiaoyu1
Root 5510 7995 0 00:00:00 pts/2 grep p0
[Root @ rac02 ~] # Ps-ef | grep p0 | grep-v psp0
Oracle 28047 1 4? 00:00:00 ora_p000_xiaoyu2
Oracle 28049 1 4? 00:00:00 ora_p0020.xiaoyu2
Root 28177 9157 0 00:00:00 pts/2 grep p0

Instance 1 initiates a query, and instance 1 and instance_group of instance 2 both have the xiaoyu group. Therefore, parallel queries can be run across instances.

Parameters of instance 1:
SQL> show parameter instance_group;
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Instance_groups string xiaoyu, xiaoyu1
Parallel_instance_group string xiaoyu2
Parameters of instance 2:
SQL> show parameter instance_group;
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Instance_groups string xiaoyu, xiaoyu2
Parallel_instance_group string
[Root @ rac01 ~] # Ps-ef | grep p0 | grep-v psp0
Root 22437 7995 0 00:00:00 pts/2 grep p0
[Root @ rac02 ~] # Ps-ef | grep p0 | grep-v psp0
Oracle 12003 1 1? 00:00:00 ora_p000_xiaoyu2
Oracle 12005 1 1? 00:00:00 ora_p0020.xiaoyu2
Oracle 12007 1 2? 00:00:00 ora_p002_xiaoyu2
Oracle 12009 1 2? 00:00:00 ora_p003_xiaoyu2
Root 12183 9157 0 00:00:00 pts/2 grep p0
At this time, the session we initiate the query is in the instance xiaoyu1, but the parallel process is indeed in the instance xiaoyu2.

During the test, ainemo also found a place to note, even if the parallel_instance_group we set is inconsistent with the instance_groups of all the actual instances.
Parameters of instance 1:
SQL> show parameter instance_group;
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Instance_groups string xiaoyu, xiaoyu1
Parallel_instance_group string test
Parameters of instance 2:
SQL> show parameter instance_group;
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Instance_groups string xiaoyu, xiaoyu2
Parallel_instance_group string
Instance 1 initiates a query
SQL> select/* + parallel (a 2) */count (*) from t01_view;
[Root @ rac01 ~] # Ps-ef | grep p0 | grep-v psp0
Root 25375 7995 0 00:00:00 pts/2 grep p0
[Root @ rac02 ~] # Ps-ef | grep p0 | grep-v psp0
Root 15135 9157 0 00:00:00 pts/2 grep p0

Although parallel processes are not visible here, parallel execution plans are seen in the 10046 trace file:
Select/* + parallel (a 2) */count (*)
From
T01_view

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.23 12.76 48196 87280 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.24 12.77 48196 87280 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=87280 pr=48196 pw=0 time=12766605 us)
1 PX COORDINATOR (cr=87280 pr=48196 pw=0 time=12766580 us)
1 PX SEND QC (RANDOM) :TQ10001 (cr=87280 pr=48196 pw=0 time=12765838 us)
1 SORT AGGREGATE (cr=87280 pr=48196 pw=0 time=12765825 us)
14487552 HASH JOIN (cr=87280 pr=48196 pw=0 time=396515809 us)
234304 PX RECEIVE (cr=43640 pr=24382 pw=0 time=13355332 us)
234304 PX SEND BROADCAST :TQ10000 (cr=43640 pr=24382 pw=0 time=13121027 us)
234304 PX BLOCK ITERATOR (cr=43640 pr=24382 pw=0 time=12652420 us)
234304 TABLE ACCESS FULL T01 (cr=43640 pr=24382 pw=0 time=12418113 us)
3186304 PX BLOCK ITERATOR (cr=43640 pr=23814 pw=0 time=12745290 us)
3186304 TABLE ACCESS FULL T01 (cr=43640 pr=23814 pw=0 time=6372668 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ----------------------
SQL * Net message to client 2 0.00 0.00
Db file scattered read 3116 0.33 7.99
Db file sequential read 5 0.00 0.00
SQL * Net message from client 2 0.00 0.00
It is difficult to explain this phenomenon. The official prompt does mention instance_groups. If parallel_instance_group is not available, the parallel execution will not take effect, but the 10046 trace shows the parallel execution, ainemo tracked the parallel execution:
SQL> alter session set "_ px_trace" = "all ";
Session altered.
SQL> select/* + parallel (a 2) */count (*) from t01_view;

COUNT(*)
----------
14487552
SQL> alter session set "_px_trace"="none";
Session altered.

Let's check the trace file to see if the preceding SQL has been executed in parallel:
Kkfdapdml
Pgadep: 2 pdml mode: 0 PQ allowed DML allowed not autonomous => not allowe
D
Kkfdapdml
Pgadep: 0 pdml mode: 0 PQ allowed DML allowed not autonomous => not allowe
D
Kxfplist
Getting instance info for open group
Kxfralo
Serial-instance group has no open members
QerpxStart
Rpa: 0 x 6d923dd 0 RPM: 0x (nil)
Current SQL statement for this session:
Select/* + parallel (a 2) */count (*) from t01_view
START no parallel resources
Qbas: 4971
Pgakid: 2 pgadep: 0
QertqiStart
Dfo: 0 passthru
QertqiFetch
Tqhdl = 0x (nil) dfo = 0 rows = 32767 passthru
Kkfdapdml
Pgadep: 0 pdml mode: 0 PQ allowed DML allowed not autonomous => not allowe
D

Here we can see Current SQL statement for this session:
Select/* + parallel (a 2) */count (*) from t01_view
START no parallel resources
It indicates that the parallel query has not taken effect. It seems that the execution plan obtained by 10046 trace does not match the trace file of the parallel trace, but this problem is indeed quite confusing, here, the crs and database versions of ainemo are both 10.2.0.1, and no suitable information is found to verify whether a bug exists, for the trace file analysis of parallel execution, can you refer to an article by a master in the circle http://www.hellodba.com/reader.php? ID = 74 & lang = CN
In general, in the rac environment, we should try to avoid cross-parallel execution between instances, mainly to reduce the problems caused by cache fusion, especially if the SQL performance is not good, it is expected that the parallel between instances, this may be counterproductive. If the cpu capacity of a single server is still mixed, ainemo users generally prefer to control parallel execution on a local instance.

Original article address: The parallel_instance_group and instance_groups parameters of rac are related to parallelism. Thank you for sharing them with me.