Restrict RAC parallel operations in each instance node
                                                 Last update (2010-07-25 21:05:55)
                                                                                                                   Date added (2008-04-03 11:36:31)

Oracle RAC harnesses 3 things.
1. Availability
2. Scalability
3. Performance
But every step forward to this directions doesn't come without some black spots. As far it concerns Performance is not so clear that RAC boosts performance. The key concept is that when you start a session the process starts at the server with the minimum load (load balancing is a good thing).

If this session was a parallel operation after using all the available parallel slaves at one instance will spawn new parallel slaves to the next available instance. This is great as an idea! But it doesn't work so well. You need a very fast interconnect speed to have the desired performance at the spawn parallel processes. Most configurations use 1 Gigabit interconnects, but this is not enough. Usually you need at least 4 Gigabit interconnect to be able to spawn parallel processing to all nodes.

Follow the example. Imagine that you have a RAC database with two instances (OTE1 and OTE2), each instance runs on a HP-UX machine with 16CPUs and you want to make a Full Table Scan to a 100GB table. Well you say, Instead of doing it serial, I'll do it in parallel, and I'll use all the CPUs on every available node! Assume that you init parameters are.
And you execute
select /*+ parallel(A 80) */ * from TABLE_NAME A;
Then in an ideal idle environment will happen the following:
The query will use all the available parallel server processes to the instance connected, lets say OTE1, which is 64 and will open another 16 to the OTE2. You will expect to go faster, but you will not.

You can have better results in RAC with parallel if you restrict parallel operations to only in the instance connected when your interconnect speed is 1 Gigabit (which is in most oracle9i - oracle10g RAC installations)

How to
Use the parameters parallel_instance_group and instance_groups. In the previous example you must setup
Parallel_instance_group 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.

Tip: You can monitor how parallel processes are spawned to the instances with the script:
SELECT inst_id, statistic,VALUE
  FROM gv$pq_sysstat
AND statistic LIKE 'Server%'
ORDER BY 1, 2;
Other links
Monitor parallel sessions
How to setup parallel in an SMP Server