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.
March 3rd, 2014 at 7:22 am
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
June 13th, 2014 at 4:24 am
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
March 3rd, 2014 at 7:31 am
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
April 9th, 2014 at 9:08 pm
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.
April 28th, 2014 at 8:37 pm
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?
June 11th, 2014 at 8:32 am
Hi Ed
If you set LOAD_BALANCE option on the service at RAC side, then you will be able to use LOAD_BALANCE option
June 13th, 2014 at 5:18 am
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.
June 13th, 2014 at 5:25 am
Try it some more time and let me know count of the connections on each node each time
June 13th, 2014 at 5:48 am
Checked….
Number of sessions connected to db_01 is 27
Number of sessions connected to db_02 is 53
June 13th, 2014 at 5:49 am
Please check it a couple of time and post the result
August 31st, 2015 at 5:00 am
سلام کامران خان
بابت اشتراک اطلاعات ازتون ممنونم.
برای من بسیار مفید هستند.
سپاس
August 31st, 2015 at 5:01 am
شاد آن به شما کمک کرده دوست من