Make “n” number of connections to your DB using script in windows/unix

Recently i had to set up a db for an App and wanted to test the how better the server scaled when there are “n” number of connections at a time. I did it on a Windows 2003 server. Below is the script which i wrote for this.

On Windows

make_conn.bat
——————

FOR %%A in (1 2 3) DO start sqlplus userid/passwd@tns_entry @conn.sql

conn.sql
———–

select sysdate from dual;
exec dbms_lock.sleep(10);
exit;

Save both the files in the same folder with respective names.
Now double click the make_conn.bat and this would create 3 connections to the db and will exceute the conn.sql in each. The contents of the conn.sql are self explanatory. To make more connections all you need to do is to add more numerics to the FOR statement. Changing the make_conn.bat to (1 2 3 1 2 3 1 2 3 4 5 6) would create 12 connections and the more numbers you add, more threads(connections) are created.

If you want to trigger the connections from cmd.exe then you will need to modify the FOR statement like :

FOR %A in (1 2 3) DO start sqlplus userid/passwd@burp @conn.sql

Note that there is only one % symbol.

On Unix/Linux

make_conn.sh
—————–

#!/bin/ksh
#
#Make n number of connections to db
#
#
echo "Enter the number of connections to make :\c"
read NOF;
i=0
while [[ $i -lt $NOF ]];
do
 sqlplus -s userid/passwd @conn.sql >/dev/null &
 i=$i+1
done
exit;

conn.sql
——–

select sysdate from dual;
exec dbms_lock.sleep(10);
exit;

$chmod 755 make_conn.sh
$make_conn.sh

The “&” in the while loop will run the process in background and will direct the o/p to /dev/null, so you will not see any message in your terminal unless there is an error.

In both cases, you can test your number of connections by

select count(8) from v$session where username=’USERID’;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s