Thursday, January 6, 2011

Disabling Parallel Load Balancing

Normally, parallel load balancing is a good feature which distributes parallel sessions among rac nodes depending on node workloads. However there is a little problem with this algorithm. Node load statistics are calculated on very little time intervals (i don't remember now, let say in a few milliseconds). What happens if a big batch allocates large number of parallel sessions within this time period? All parallel sessions are allocated depending on this constant statistic which may lead one node to be overloaded.

Let me explain with an example. Let say there is 3-node rac cluster and we will run a big batch which will open 128 parallel sessions. Let assume before running batch 2 nodes are 50% loaded, 1 is idle (<3%). When we run batch, oracle starts distributing parallel sessions to nodes depending on their workload and he may decided to open all parallel sessions on idle node. This is because all parallel sessions are opened in a very short time interval in which node load statistics are not updated so oracle decides to open every connection in idle node which seems best suitable. The result 2 nodes with 50% load and 1 with 100%.

In order to eliminate this, you may set following variable on batch session:

alter session set "_parallel_load_balancing"=FALSE;

which disables load balancing and distributes all sessions one by one to all nodes ignoring workload.

However, you should be very careful while using this parameter. Because if one node is already 100% loaded and if you set this parameter, oracle will try to open 1/3 of the 128 parallel session on this already 100% loaded node which may kill node. However load balancing would prevent opening new sessions on loaded node.