Oracle RAC load balancing tips Oracle Tips by Burleson Consulting

 

 

Question: Is it possible in a RAC environment to force the database node that you want to connect too?

Answer: Yes, and intelligent load balancing is an important tuning technique. You use the tnsnames.ora file to direct "like-minded" end-users to specific nodes, and you can use TAF to specify failover nodes.

 

Again, in my years as a RAC DBA, I recommend functional load balancing, whereby like-minded transactions are grouped together on each node (e.g. one node for order queries, one node for customer queries).  Using such a "functional" load balancing scheme, cache fusion pinging is greatly reduced and performance is improved.

One major RAC tuning issue is minimizing pinging access the cache fusion layer, and smart DBA's will segregate users by node (i.e. customer queries on node 1, product queries on node 2, etc).

 

I don't recommend that "automatic load balancing" for all RAC databases, and it's smart to group your end-users to nodes based on their query types.

 

Load-balancing for RAC involves extensive manual configuration to use a round-robin configuration to distribute the load among the instances. Starting in Oracle 10g release 2, we have a brand-new load-balancing advisory that promises to cut-down the manual effort for RAC load balancing between instances, but this does not take a functional approach to RAC load balancing, load balancing by the "type" of data being requested.

Again, a "functional" load balancing scheme is best, especially since pinging can become a major RAC bottleneck.

 

Here are some notes on RAC load balancing:

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.