Kamran Agayev's Oracle Blog

Oracle Certified Master

Testing Load balancing in Oracle RAC

Posted by Kamran Agayev A. on January 14th, 2013

If you’ve installed Oracle RAC (Real Application Clusters) and want to test how Load Balancing works, you can run the following shell script and check GV$SESSION view:

#!/bin/bash
. /home/oracle/.bash_profile
for ((i=1; i <= 50 ; i++))
do
nohup sqlplus -S system/oracle@racdb<<eof &
begin
dbms_lock.sleep(10);
end;
/

eof
done

This will open 50 sessions in the background. Check GV$SESSION view before and after  running this query:

SQL> select inst_id,count(*) from gv$session where username is not null group by inst_id;

INST_ID COUNT(*)
———- ———-
1           10
2          9

Run the following command from the different session:

[oracle@node1 ~] ./check_load_balancing.sh

SQL> /

INST_ID COUNT(*)
———- ———-
1           33
2           36

Wait for 10 seconds (as we’ve defined “10” seconds at DBMS_LOCK.SLEEP procedure) and run the query again :

SQL> /

INST_ID COUNT(*)
———- ———-
1           10
2           9

SQL>

From the output you can see that all new sessions were distributed between two nodes appropriately.

12 Responses to “Testing Load balancing in Oracle RAC”

  1. Preethi Says:

    These is not happening (Sessions distribution is not happening)

    We have 2Node RAC envinorment.
    The local and remote listener’s are set properly.. but eventhough always no.of sessions on node is morethan other node.

    Number of sessions connected to db_01 is 47 ( Node 1 always less sessions)

    Number of sessions connected to db_02 is 79 (node 2 always more sessions)

    Could you help on these.

    Regards,
    Preethi

  2. Kamran Agayev A. Says:

    Hi Preethi.

    Sessions will never 100% distributed among the instances, but will be mostly distributed in a parallel. Actually, it’s a PMON process that updates listener with the load information of the system. Please check if the RAM or CPU of the two servers are different

  3. Preethi Says:

    SQL> select inst_id,count(*) from gv$session where username is not null group by inst_id;

    INST_ID COUNT(*)
    ———- ———-
    1 49
    2 89

    SQL> /

    INST_ID COUNT(*)
    ———- ———-
    1 49
    2 88

    SQL>
    SQL> /

    INST_ID COUNT(*)
    ———- ———-
    1 49
    2 82

    SQL> /

    INST_ID COUNT(*)
    ———- ———-
    1 49
    2 82

    SQL> !cat /tmp/session_test.sh
    #!/bin/bash
    for ((i=1; i <= 50 ; i++))
    do
    nohup sqlplus -S system/xxxx@xxx<<eof & —– xxxx because dont want to keep organisation data..
    begin
    dbms_lock.sleep(10);
    end;
    /

    eof
    done

  4. Muhammad Atif Says:

    Thanks for this but, I want to know how to test ORACLE RAC 10g Load Balancing in Windows 2003 Environment. Please tell me if you know or share any link related this query. I’ll be waiting for your response.

  5. Ed Says:

    Hi,

    In its simplest form, for example one app service 2 node RAC, if the service is ever active in only one node, does that mean I will never have load balancing in place regardless of whether I have LOAD_BALANCE ON or not in the tnsnames.ora?

  6. Kamran Agayev A. Says:

    Hi Ed
    If you set LOAD_BALANCE option on the service at RAC side, then you will be able to use LOAD_BALANCE option

  7. preethi Says:

    Hi Kamran,

    Both the Nodes Hardware is same…

    The CPU and RAM is same for both the Nodes.

    RAM is 24GB and CPU processors are 15.

  8. Kamran Agayev A. Says:

    Try it some more time and let me know count of the connections on each node each time

  9. preethi Says:

    Checked….

    Number of sessions connected to db_01 is 27

    Number of sessions connected to db_02 is 53

  10. Kamran Agayev A. Says:

    Please check it a couple of time and post the result

  11. sahar Says:

    سلام کامران خان
    بابت اشتراک اطلاعات ازتون ممنونم.
    برای من بسیار مفید هستند.
    سپاس

  12. Kamran Agayev A. Says:

    شاد آن به شما کمک کرده دوست من

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>