国内的用户对INSTANCE_GROUPS和PARALLEL_INSTANCE_GROUP参数比较陌生,因为在国内多节点的RAC比较少。要说这两个参数,我们必须说说并行查询(PQ,PARALLEL QUERY),并行查询是Oracle为了提高长时间运行的查询操作的性能而提供的功能,可以把一个QUERY分解为多个TASK,然后启动PX进程,由多个进程共同完成一个QUERY。
在RAC环境下,PQ是可以跨实例的,也就是说可以由一个CLUSTER数据库的多个实例协同来完成一个QUERY。在这个情况下,就需要使用开头我们所说的那两个参数了。首先
INSTANCE_GROUPS指出了本实例属于的GROUP的名字。这个参数可以指定多个值,用“,”分开。也就是说一个实例可以属于多个实例组。实例组通过名字来区分
PARALLEL_INSTANCE_GROUP是并行查询使用的组的名字,如果这个参数是空的,那么说明PQ可以使用数据库的所有实例。如果指定了某个名字,那么说明PQ只能在指定的INSTANCE GROUP里进行。
PARALLEL_INSTANCE_GROUP参数是可以在会话级动态修改的,因此通过调整这个参数,可以控制并行查询的范围。这个特性对于双节点RAC和多节点RAC都十分有用。
要注意的是在不同的ORACLE版本中,这2个参数的设置是不同的,因此要了解详细的信息,请参考相关手册。比如在很多版本中,如果设置了一个不存在的GOUP,那么该SQL会使用串行方式执行,而不使用PQ,在有些版本中,错误的PARALLEL_INSTANCE_GROUP会报错。
参数参考:
INSTANCE_GROUPS
Property Description
Parameter type String
Syntax INSTANCE_GROUPS = group_name [, group_name ] ...
Default value There is no default value.
Modifiable No
Range of values One or more instance group names, separated by commas
Basic No
Real Application Clusters Multiple 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_GROUP parameter, 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 the PARALLEL_INSTANCE_GROUP parameter, then Oracle allocates query processes for a parallel operation from this instance.
PARALLEL_INSTANCE_GROUP
Property Description
Parameter type String
Syntax PARALLEL_INSTANCE_GROUP = group_name
Default value A group consisting of all instances currently active
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values Any group name specified in the INSTANCE_GROUPS parameter of any active instance
Real Application Clusters Multiple 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_GROUPS parameter, 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.