Oracle RAC: Load Balancing advisory concepts

Oracle 10gR2 provides an advanced load balancing method to overcome the imbalance of RAC logon’s , especially when there is logon storms, the listeners on the two nodes will simply distribute them evenly across both the nodes. Some case one of the node can be busier than the other . The LBA will calculate how much work each instance in the clustered database has been asked to do. The LBA will then make sure that any new incoming work is routed to the instance(s) that are least busy. This determination takes place on a service-by-service basis across all instances that are providing resources to each service.

The LBA considers the following factors when determining how to perform this “balancing act”:

• Are there any differences in processing power between nodes?
• Are there any sessions that are currently blocked from execution because of waits?
• Have any failures occurred on a node that might block processing from continuing?
• Are there any services that are competing for resources, and have those services been granted a different priority to complete their tasks at hand?

Not only does this help to balance out the total workload across all instances in the cluster, it also insures that one node won’t be overwhelmed by requests for service by its primary application(s).

As part of its Fast Application Notification (FAN) event transmission features, Oracle 10g RAC uses Oracle Notification Services (ONS) to communicate the status of cluster resources to all participating node applications in the cluster. In Oracle 10gR2, FAN added a new event type, SERVICE_METRIC, whose event payload contains

information about the relative workload of each node in the RAC cluster, and it’s this information that the Load Balancing Advisory uses to determine how to route new connections throughout the clustered database’s instances.

See sample PMON Trace file here for Metrics

Oracle 10gR2 supplies an upgraded DBMS_SERVICE.MODIFY_SERVICE procedure that modifies an existing RAC service so that it can utilize the Load Balance Advisory for service-by-service load balancing. This procedure provides two crucial parameters, GOAL and CLB_GOAL

Command Reference:-

BEGIN
    DBMS_SERVICE.MODIFY_SERVICE(
        service_name => 'ADHOC'
       ,aq_ha_notifications => TRUE
       ,goal => DBMS_SERVICE.GOAL_NONE
       ,clb_goal => DBMS_SERVICE.CLB_GOAL_LONG
    );
    DBMS_SERVICE.MODIFY_SERVICE(
        service_name => 'DSS'
       ,aq_ha_notifications => TRUE
       ,goal => DBMS_SERVICE.GOAL_SERVICE_TIME
       ,clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT
    );
    DBMS_SERVICE.MODIFY_SERVICE(
        service_name => 'OLTP'
       ,aq_ha_notifications => TRUE
       ,goal => DBMS_SERVICE.GOAL_THROUGHPUT
       ,clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT
    );
END;
/

Now look at each option,

GOAL

In addition, the CLB_GOAL parameter provides yet another load balancing method: It tells the LBA how to perform connection load balancing. See Table 2 for a list of its acceptable enumerated constant values and the impact of setting either.

Depending on the settings for these two parameters, the LBA uses the METRICS shown in Table 3 to make its determination of which instance should receive the next set of incoming connections:

Further, the LONG goals can be called as connect time load balancing and SHORT goals called as Runtime load balancing.

Scripts:-

Script to Verify the services and load balancing metrics details

SET PAGESIZE 50
SET LINESIZE 11O
TTITLE 'Services Configured to Use Load Balancing Advisory (LBA) Features|
(From DBA_SERVICES)'
COL name            FORMAT A16      HEADING 'Service Name' WRAP
COL created_on      FORMAT A20      HEADING 'Created On' WRAP
COL goal            FORMAT A12      HEADING 'Service|Workload|Management|Goal'
COL clb_goal        FORMAT A12      HEADING 'Connection|Load|Balancing|Goal'
COL aq_ha_notifications FORMAT A16  HEADING 'Advanced|Queueing|High-|Availability|Notification'
SELECT
     name
    ,TO_CHAR(creation_date, 'mm-dd-yyyy hh24:mi:ss') created_on
    ,goal
    ,clb_goal
    ,aq_ha_notifications
  FROM dba_services
WHERE goal IS NOT NULL
   AND name NOT LIKE 'SYS%'
ORDER BY name
;

 

 

TTITLE 'Current Service-Level Metrics|(From GV$SERVICEMETRIC)'
BREAK ON service_name NODUPLICATES
COL service_name    FORMAT A08          HEADING 'Service|Name' WRAP
COL inst_id         FORMAT 9999         HEADING 'Inst|ID'
COL beg_hist        FORMAT A10          HEADING 'Start Time' WRAP
COL end_hist        FORMAT A10          HEADING 'End Time' WRAP
COL intsize_csec    FORMAT 9999         HEADING 'Intvl|Size|(cs)'
COL goodness        FORMAT 999999       HEADING 'Good|ness'
COL delta           FORMAT 999999       HEADING 'Pred-|icted|Good-|ness|Incr'
COL cpupercall      FORMAT 99999999     HEADING 'CPU|Time|Per|Call|(mus)'
COL dbtimepercall   FORMAT 99999999     HEADING 'Elpsd|Time|Per|Call|(mus)'
COL callspersec     FORMAT 99999999     HEADING '# 0f|User|Calls|Per|Second'
COL dbtimepersec    FORMAT 99999999     HEADING 'DBTime|Per|Second'
COL flags           FORMAT 999999       HEADING 'Flags'
SELECT
     service_name
    ,TO_CHAR(begin_time,'hh24:mi:ss') beg_hist
    ,TO_CHAR(end_time,'hh24:mi:ss') end_hist
    ,inst_id
    ,goodness
    ,delta
    ,flags
    ,cpupercall
    ,dbtimepercall
    ,callspersec
    ,dbtimepersec
  FROM gv$servicemetric
WHERE service_name IN ('OLTP','DSS','ADHOC')
ORDER BY service_name, begin_time DESC, inst_id
;

Thanks

Geek DBA