LOAD BALANCING IN ORACLE RAC.

LOAD BALANCING IN ORACLE RAC

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@YesBolisetty1<

begin

dbms_lock.sleep(10);

end;

/

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> select inst_id, count(*) from gv$session where username is not null group by inst_id;

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> select inst_id, count(*) from gv$session where username is not null group by inst_id;

INST_ID COUNT(*)

———- ———-

1           10

2           9

SQL>

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