Load Test Script for Oracle Database

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

Evaluating Execution Results of a Oracle PL/SQL Procedure in UNIX Shell Script

There are many notes with extensive review on this topic at Oracle support’s metalink website. Please see the list of references at the end of this blog. In this blog, I am only focused on how to evaluate the execution result of a PL/SQL procedure within a shell script. One practical use of this is to check the status of an application, as many of the applications primarily call PL/SQL procedures to interact with Oracle database.
I found the following two methods that are useful.
1. Use “whenever sqlerror exit 1” to check if there is a problem:
#!/usr/bin/sh
sqlplus scott/tiger@${ORACLE_SID} << EOF
whenever sqlerror exit 1
exec ‘some PLSQL procedure’
EOF
ABC=$?
if [[ $ABC = 1 ]];
then
‘do something and send out an email…’
else
‘do something else…’
fi
This method is simple. But many things could set exit status to 1. For example, you may have problem with SQL*Plus, database user account permission, etc. But if you knew the only thing that could go wrong is the execution of PLSQL procedure, this is still a viable choice.

2. Use nested block along with sql.sqlcode. The SQLCODE returns code of the most recent operation in SQL*Plus. You have a lot more control on what exit status you want to return to shell script. Here is a example:
(Note: even for the exceptions that are Oracle predefined exceptions, the following code can still capture the value of SQLCODE. Tested in 10gR2.)
#!/usr/bin/sh
sqlplus -s scott/tiger@${ORACLE_SID} << EOF
set serverout on
variable set_flag number;
DECLARE
sql_code number := 0 ;
my_errm VARCHAR2(32000);
BEGIN
BEGIN
‘some PLSQL procedure’;
EXCEPTION
WHEN OTHERS THEN
sql_code := SQLCODE;
my_errm := SQLERRM;
END;

dbms_output.put_line('Error message is: 'my_errm);

IF sqlcode !=0 THEN
:set_flag := 4;
END IF;
END;
/
exit :set_flag
EOF

#It is good idea to capture the exit status by assigning it to a variable, in case you need to use it multiple time later on in the script.
ABC=$?
if [[ $ABC = 1 ]]; then
your SQL*Plus failed to run successfully for some reason, do something such as sending an email, write to log file, etc.…
elif [[ $ABC = 4 ]]; then
the PLSQL procedure failed, do something about this, send email, write to log file, etc.….
Else
there is no problem…
fi
References:
(1) metalink note # 351015.1 How To Pass a Parameter From A UNIX Shell Script To A PLSQL Function Or Procedure.
(2) metalink note # 400195.1 How to Integrate the Shell, SQLPlus Scripts and PLSQL in any Permutation?
(3) metalink note# 73788.1 Example PL/SQL: How to Pass Status from PL/SQL Script to Calling Shell Script
(4) metalink note # 6841.1 Using a PL/SQL Variable to Set the EXIT code of a SQL script
(5) http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10807/13_elems049.htm
(6) Oracle magazine: On Avoiding Termination By Steven Feuerstein
http://www.oracle.com/technology/oramag/oracle/09-mar/o29plsql.html