Effectice load balancing for a (web?) service in RAC 10gR2


had to review service creation with DBMS_SERVICE package for a good-feeling load balancing in Oracle RAC 10gR2 several times – now I have to write it down for myself. :) I needed it for a web service using shared server today, but the generic syntax is useful for all other purposes as well.

Basic service creation in RAC is rather simple, just use

alter system set service_name='service1','service2' scope=both sid='*';
alter system register;

But for the configuration of active RAC load balancing and for setting a special focus on a balancing behaviour, you need the DBMS_SERVICE package, as described in Oracle documentation “95 DBMS_SERVICE“.

My example syntax for a modification of a service created by init parameter service_name (as described above):

dbms_service.modify_service(service_name => 'SERVICE2',
aq_ha_notifications => TRUE,
failover_retries => 120,
failover_delay => 2,

A quick explanation:

GOAL_THROUGHPUT: Load Balancing Advisory is based on the rate that work is completed in the service plus available bandwidth to the service. Other option: GOAL_SERVICE_TIME, it does not take the real work into account, just the time you are “sitting on the node”.

aq_ha_notifications: Determines whether HA events are sent via AQ for this service. I considered Active Queuing being a good solution for me.

FAILOVER_METHOD_BASIC: You don’t have another chance than BASIC, that’s the switch for TAF on or TAF off.

FAILOVER_TYPE_SELECT: Failover works on statement basis, not session based.

RETRIES and DELAY should explain themselves.

CLB_GOAL_SHORT: Connection load balancing uses Load Balancing Advisory, when Load Balancing Advisory is enabled (either goal_service_time or goal_throughput). When GOAL=NONE (no load balancing advisory), connection load balancing uses an abridged advice based on CPU utilization.

EDIT: Just a hint: Check your results with

select * from gv$services;

Have a well balanced day now,

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Effectice load balancing for a (web?) service in RAC 10gR2”

  1. usn Says:

    Just for your information:
    This also works in 11gR1 and 11gR2, tested it today.


Leave a Reply