Internode/Intranode Parallelism in RAC

Case 1: Running Query in Serial Mode from Instance 1 - Unbounded

Parallel_max_servers = 5
Parallel_min_servers = 0

select sum(bytes),sum(blocks),sum(blocks/1024), sum(block_id/12) from xyz;

SUM(BYTES) SUM(BLOCKS) SUM(BLOCKS/1024) SUM(BLOCK_ID/12)
---------- ----------- ---------------- ----------------
1.4239E+12 173813760 169740 1.3930E+10

Elapsed: 00:01:00.86

Instance 1



Instance 2



Case 2: Running Query in Parallel mode (Degree 6) from Instance 1 - unbounded

Parallel_max_servers = 5
Parallel_min_servers = 0

select /*+ full(xyz) parallel(xyz,6) */ sum(bytes),sum(blocks),
sum(blocks/1024), sum(block_id/12) from xyz;

SUM(BYTES) SUM(BLOCKS) SUM(BLOCKS/1024) SUM(BLOCK_ID/12)
---------- ----------- ---------------- ----------------
1.4239E+12 173813760 169740 1.3930E+10

Elapsed: 00:01:02.82

oracle 20394 1 4 16:23 ? 00:00:02 ora_p000_sweety1
oracle 20400 1 4 16:23 ? 00:00:03 ora_p001_sweety1
oracle 20402 1 4 16:23 ? 00:00:02 ora_p002_sweety1

oracle 17248 1 3 16:23 ? 00:00:02 ora_p000_sweety2
oracle 17252 1 2 16:23 ? 00:00:02 ora_p001_sweety2
oracle 17259 1 3 16:23 ? 00:00:02 ora_p002_sweety2

Instance 1



Instance 2



Case 3: Running Query in Parallel (Degree 6) mode from Instance 1 - bounded

To restrict parallel processing to specific node, instance groups can be used.

SQL> select inst_id,name,value from gv$parameter where
name like '%instance%grou%';

INST_ID NAME VALUE
---------- ------------------------------ ------------------------------
1 instance_groups s1
1 parallel_instance_group
2 instance_groups s2
2 parallel_instance_group


SQL> select inst_id,name,value from gv$parameter where name
like '%instance%grou%';

INST_ID NAME VALUE
---------- ------------------------------ ------------------------------
1 instance_groups s1
1 parallel_instance_group
2 instance_groups s2
2 parallel_instance_group

SQL> alter session set parallel_instance_group='s1';

Session altered.

SQL> select /*+ full(xyz) parallel(xyz,8) */ sum(bytes),sum(blocks),
sum(blocks/1024), sum(block_id/12) from xyz;

SUM(BYTES) SUM(BLOCKS) SUM(BLOCKS/1024) SUM(BLOCK_ID/12)
---------- ----------- ---------------- ----------------
1.4239E+12 173813760 169740 1.3930E+10


oracle 32285 1 6 17:36 ? 00:00:02 ora_p000_sweety1
oracle 32293 1 6 17:36 ? 00:00:02 ora_p001_sweety1
oracle 32301 1 6 17:36 ? 00:00:02 ora_p002_sweety1
oracle 32304 1 6 17:36 ? 00:00:02 ora_p003_sweety1
oracle 32340 1 6 17:36 ? 00:00:02 ora_p004_sweety1

No parallel processes spawned on instance 2.

All the parallel workers run only on instance1 node.
Why only 5 servers and not 6? Remember parallel_max_servers
is set to 5.

Observation:

Running couple of sessions in parallel unbounded mode can slow down interconnect.
Similar test should be performed on production system before going for cross instance parallelism.

Test was performed using single session on:
2 Node Oracle 11g RAC
Enterprise Linux 5
NFS