loading
hello dummmy text

On Oracle RAC environment Load Balancing is something which is critical for distribution of connections between the servers we have. Load Balancing Advisory (LBA) is one of the key components for SCAN listener to decide the best instance for the new incoming connection request. I was just wondering if we can check if Load Balancing Advisory is enabled by default to all the services that run on multiple nodes then can we disable this feature? But let us understand exactly how critical is LBA for SCAN listener and its effects. The figure above explains that SCAN listener might use the data from LBA and decides the best instance to route the connection, but which type of Load Balancing this falls into? We have already understood the functionality of SCAN listener in our earlier blog “How does SCAN listener works in Oracle RAC 11gR2?

Let me quickly take you through the concepts of Load Balancing types and then we will move ahead to the activity of controlling it.

Concepts:

Typically, there are two types of load balancing:

  • Connection Load Balancing (CLB)
  • Run-time Load Balancing (RTLB)

RTLB has come into existence from 10gR2 which can be either set to “SERVICE_TIME” or “THROUGHPUT”. CLB can be configured at Client-Side or at Server-Side. Of which, Server-Side load balancing is recommended and have better functionality over Client-Side.

Is RTLB a client-side or server-side load balancing? Well, it is combination of both and majorly on the client side. Connection and performance statistics is collected on server side and transferred to the application server recursively by using ONS (Oracle Notification Service) infrastructure. It might not be applicable to all the application setups, by default JDBC based applications can use this RTLB feature.

What is the role of LBA? LBA is the key for Run-time Load Balancing. Both these configurations completely rely on the statistical data provided by LBA (Load Balancing Advisory).

Functionality of RTLB: For a fixed time period the performance statistics collected by LBA (with the help of PMON of all instances) are pushed into application server through ONS service and we call them as LBA events. Every time a new connection request triggers from application it will pass through the connection cache/pool on the client side and picks up the best instance to connect based on the service level goal configured.

Yes, now you might agree that Run-Time Load Balancing is more preferred than Connection Load Balancing as major and important decisions are taken at the client side before connection request reaches SCAN listener. In this blog, let us run through the process of creating a service, understand the weightage of LBA and if that can be disabled.

Check out how Runtime load balancing works with SCAN listener in Oracle RAC Click To Tweet

Case study:

Here is our case study, to create a default service on a 2-node RAC environment and verify the parameters of load balancing. We will take this study ahead by changing the parameters to understand the importance of LBA.

1. Creating a default service without specifying the type of load balancing.

[oracle@RAC1 ~]$ srvctl add service -s LBASRV1 -d RACDB -g RACPOOL

[oracle@RAC1 ~]$ srvctl start service -s LBASRV1 -d RACDB

[oracle@RAC1 ~]$ srvctl config service -s LBASRV1 -d RACDB

<top lines removed>

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

<next lines removed>

Our Findings: When a service is created by default Connection Load Balancing is enabled and set to “LONG” and Run-Time Load Balancing is disabled.

Does it mean that LBA is not used by SCAN at all? No, as it is server side load balancing LBA statistics are used by SCAN listener to perform the load balancing (Keep this on hand). This is the appropriate load balancing that happens on the database server at the listener.

2. Now let us edit the configuration of this service and enable Run-Time Load Balancing.

[oracle@RAC1 ~]$ srvctl modify service -d RACDB -s LBASRV1 -B SERVICE_TIME

[oracle@RAC1 ~]$ srvctl config service -d RACDB -s LBASRV1

<top lines removed>

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: SERVICE_TIME

<removed: same as above>

[oracle@RAC1 ~]$ srvctl modify service -d RACDB -s LBASRV1 -B THROUGHPUT

[oracle@RAC1 ~]$ srvctl config service -d RACDB -s LBASRV1

<removed: same as above>

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: THROUGHPUT

<next lines removed>

Our Findings: Irrespective of Connection Load Balancing Goal “SHORT” or “LONG” you can enable Runtime Load Balancing Goal to “SERVICE_TIME” or “THROUGHPUT”.

SERVICE_TIME: Attempts to direct work requests to instances according to response time. Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service.

THROUGHPUT: Attempts to direct work requests according to throughput. The load balancing advisory is based on the rate that work is completed in the service plus available bandwidth to the service.

Key point: With Connection Load Balancing goal set to LONG, do not configure Run-time Load Balancing as it is only applicable to applications where next session or job starts only after the current one ends which is not practical. This is the reason you must have read that Run-time Load Balancing must be enabled with CLB goal set to SHORT.

Is there a way to disable Load Balancing Advisory at all in Oracle RAC? Click To Tweet

Can we disable CLB?

Disabling CLB on server-side is equivalent to disabling LBA (Load Balancing Advisory). You will not find an option to do it if you search in “srvctl” help.

Oracle document says that configuring GOAL to NONE will disable Load Balancing Advisory (LBA) on the service. Let us try doing it and see what exactly it is.

3. I will try to use DBMS_SERVICE package to modify this service and to disable LBA.

SQL> EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => ‘LBASRV1’, goal => DBMS_SERVICE.GOAL_NONE);

SQL> select GOAL,CLB_GOAL from dba_services where name=’LBASRV1′;

GOAL         CLB_G

———— —–

NONE         LONG

But as soon as we restart the service using srvctl GOAL has come back to THROUGHPUT.

[oracle@RAC1 ~]$ srvctl stop service -s LBASRV1 -d RACDB

[oracle@RAC1 ~]$ srvctl start service -s LBASRV1 -d RACDB

SQL> select GOAL,CLB_GOAL from dba_services where name=’LBASRV1′;

GOAL         CLB_G

———— —–

THROUGHPUT   LONG

You can call this as bug as the configuration changes done in SQL prompt will not be updated in OCR. So let us try doing it using srvctl.

[oracle@RAC1 ~]$ srvctl stop service -s LBASRV1 -d RACDB

[oracle@RAC1 ~]$ srvctl modify service -s LBASRV1 -d RACDB -B NONE

[oracle@RAC1 ~]$ srvctl config service -s LBASRV1 -d RACDB

<top lines removed>

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

<next lines removed>

This has come back to the default configuration when we created this service. Yes, RTLB is now disabled but will it not use LBA according to the Oracle document?

4. Let us connect to the database now using this service from the client server and check if LBA is used by SCAN listener.

Client server: C:\Users\Administrator>sqlplus system/########@//192.168.122.#/LBASRV1

Database server:

SQL> select user_data from sys.sys$service_metrics_tab order by enq_time;

Last row of the output:

SYS$RLBTYP(‘LBASRV1’, ‘VERSION=1.0 database=RACDB service=LB

ASRV1 { {instance=RACDB_2 percent=50 flag=UNKNOWN aff=FALSE}

{instance=RACDB_1 percent=50 flag=UNKNOWN aff=FALSE} } times

tamp=2016-03-17 16:38:38′) Current system time:

SQL> !date

Thu Mar 17 16:49:46 IST 2016

This means that LBA events are not generated when new connection request was handled by SCAN listener.

5. Now let us change the Connection Load Balancing to “SHORT”, reconnect from client server and see if LBA is used by SCAN listener.

[oracle@RAC1 ~]$ srvctl modify service -s LBASRV1 -d RACDB -j SHORT

[oracle@RAC1 ~]$ srvctl stop service -s LBASRV1 -d RACDB

[oracle@RAC1 ~]$ srvctl start service -s LBASRV1 -d RACDB

[oracle@RAC1 ~]$ srvctl config service -s LBASRV1 -d RACDB

<top lines removed>

Connection Load Balancing Goal: SHORT

Runtime Load Balancing Goal: NONE

<next lines removed>

Client server:  C:\Users\Administrator>sqlplus system/########@//192.168.122.#/LBASRV1

Database server:

SQL> SELECT user_data  FROM sys.sys$service_metrics_tab  ORDER BY 1 ;

Last row of the output:

SYS$RLBTYP(‘LBASRV1’, ‘VERSION=1.0 database=RACDB service=LB

ASRV1 { {instance=RACDB_2 percent=50 flag=UNKNOWN aff=FALSE}

{instance=RACDB_1 percent=50 flag=UNKNOWN aff=FALSE} } times

tamp=2016-03-17 16:38:38′) Current system time:

SQL> !date

Thu Mar 17 16:54:46 IST 2016

Even with “SHORT” goal we don’t see any LBA events generated by Oracle RAC. Does it mean that LBA is not used by SCAN listener when only CLB is configured? Hold on!!! Let us clear this road block.

6. Let me try to connect from multiple client sessions and see if load is distributed across RAC instances.

SQL> select inst_id,username from gv$session where username=’SYSTEM’;

INST_ID USERNAME

———- ——————————

1 SYSTEM

2 SYSTEM

2 SYSTEM

Woooh!!!, there is load balancing on server side. Is it with the help of LBA? We have disabled it before this step, isn’t it?

One of the Oracle documents shown above says that LBA can be disabled by setting GOAL to NONE and this document says that server-side load balancing uses LBA even when Run-time Load Balancing is disabled. It is not surprising if you have got confused now 🙂

Summary of our case study:

  • Server-side CLB is better over Client-side CLB and RTLB is best over Server-side CLB.
  • Application should be able to read LBA events published by RAC to make the most out of RTLB. Mostly JBDC, ODBC, Oracle Data Provider for .NET (ODP.NET) can be configured to handle RTLB.
  • We can enable (SERVICE_TIME/THROUGHPUT) or disable(NONE) RTLB by using srvctl command line utility for a service.
  • When RTLB is disabled Load Balancing Advisory EVENTS are disabled, which means that no notifications are published through ONS service – This is what we have observed by running a select query on sys$service_metrics view.
  • You cannot disable CLB – it has to be either SHORT/LONG. This also means that it uses Load Balancing Advisory(LBA) data collected by PMON of all the instances without which service goal metrics cannot be met.
  • Now, I understand that LBA is definitely mandatory for SCAN listener in Oracle RAC.

I love to see your comments!

Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.

20 Comment

  1. Abdul

    Very good…

    1. Pawan Kumar Yaddanapudi

      Thanks

  2. Foued

    Thanks for sharing this post.
    Foued

    1. Pawan Kumar Yaddanapudi

      Thank you 🙂

  3. Isidro

    A very good article.
    Thank you.

    1. Pawan Kumar Yaddanapudi

      Thanks for liking my article.

      1. Rakesh kumar

        Thanks nice blog

        1. Pawan Kumar Yaddanapudi

          Thank you Rakesh

  4. Vinay

    Very nice blog, thanks

    1. Pawan Kumar Yaddanapudi

      Cheers Vinay !!!

  5. Wandi

    Thank you very much sir, your explanation as clear as a crystal.. :).

    1. Pawan Kumar Yaddanapudi

      Cheers Wandi !!!

  6. Jorge Lallemand

    I really like the detail of your findings.

    This is a topic that is neglected between IT Prod Staff and Development Leads.

  7. Sherry

    I was confused by Oracle Documentation too. Nice explanation

  8. Abadhesh

    Very good article

  9. Sudarsan Sahu

    Liked your investigation on LPA .

    1. Pawan Kumar Yaddanapudi

      Thank you Sudarsan !!!

  10. nagarjuna

    Very nice posting , you have cleared me the LBA concept clearly. thank you very much.

    1. Pawan Kumar Yaddanapudi

      Cheers !!!

  11. javeed

    Hi,
    I need the training on oracle rac. Kindly can you please let me know the date.
    My mobile no: +91-9160266053

Write a Reply or Comment

Your email address will not be published. Required fields are marked *

Knowledge That Can Always Keep Your Inbox Informed