Have you ever in a situation when you wish you could do some load testing on your test database before the production release? I am sure every DBA and developer does. Here is an example to run load testing on an Oracle database server. You can add PL/SQL procedure your application will run into this script to suit your need.
#!/usr/bin/ksh
#
# file name: load_test.sh
# author: Dan Zheng April 2009
#
# This program assume following conditions:
# 1. you are running from a Unix server with Oracle client installed;
# 2. $ORACLE_HOME environmental parameter is set;
# 3. you can logon to a database as scott user.
#
# Check if file is executed with wrong number of input parameters
#
if [ $# -ne 3 ]
then
echo "Wrong number of arguments: $# "
echo "To execute this script follow the instruction:"
echo " (script filename) (ORACLEsid) (NUMofSESSION) (minutes) "
echo " for example if you want to open 300 sessions in RMDB1 database,"
echo " and leave the sessions open for 20 minutes:"
echo " load_test.sh RMDB1 300 20 > load.lst 2>&1 "
exit 1
fi
export ORACLE_SID=$1
export sessions=$2
export openmin=$3
let count=0
let totalnum=$sessions
while (($totalnum > $count )); do
$ORACLE_HOME/bin/sqlplus -s scott/tiger@ORACLE_SID << EOF
set serverout on
declare
currenttime timestamp;
endtime timestamp;
begin
currenttime := current_timestamp;
endtime := (current_timestamp + $openmin/1440);
loop
exit when currenttime > endtime;
currenttime := current_timestamp;
end loop;
dbms_output.put_line('exiting Sql*Plus at '||currenttime);
dbms_output.put_line(' ');
end;
/
exit;
EOF
let count="count + 1"
done
exit;
The above script would consume all your CPU power as it constantly checks system time. If you just want to test how many idle sessions your system can handle, use following routine inside your SQL*Plus session in your script:
exec dbms_backup_restore.sleep($openmin*60);
Reference:
Oracle Support metalink notes:
1. How to simulate a slow query. Useful for testing of timeout issues Doc ID: 357615.1
2. Instead of DBMS_LOCK.SLEEP Procedure Use SYS.DBMS_BACKUP_RESTORE.SLEEP For Time Interval > 3600 seconds Doc ID: 471246.1
