Quick SQL Load Testin’

Here’s a quick and simple load testing script. Add you own base.sql set of scripts to run and a sub-directory called output and you’ll be ready. More examples later.

base.sql file:

select 'x' from dual;
select count(*) from dba_objects where status != 'VALID';
select count(*) from dba_objects;

login.sql file:

set arraysize 5000
set autocommit OFF
set autoprint OFF
set autorecovery OFF
set autotrace OFF
set echo ON
set editfile "afiedt.buf"
set embedded OFF
set escape OFF
set escchar OFF
set exitcommit ON
set flagger OFF
set flush ON
set heading ON
set linesize 300
set long 300
set longchunksize 80
set newpage 1
set null ""
set numformat ""
set numwidth 40
set pagesize 0
set PAUSE OFF
set recsep WRAP
set serveroutput ON
set shiftinout INVISIBLE
set showmode OFF
set sqlblanklines OFF
set sqlcase MIXED
set sqlcontinue "> "
set sqlnumber ON
set sqlpluscompatibility 11.2.0
set sqlprompt "Default SQL> "
set suffix "sql"
set tab ON
set termout ON
set timing ON
set trimout ON
set trimspool OFF
set verify ON
set wrap off
set xmloptimizationcheck OFF
set errorlogging OFF

Exit.sql file:

exit

Korn Shell script start_load.ksh expects a sub-directory called output and testuser1 through testuser with a password you specify (or you could use the same account over and over). Permissions are up to you.

#!/usr/bin/ksh

# Environmental Variables

set -x
# Mail
RUN_MAIL_TO='david.cox@yourcompany.com'; export RUN_MAIL_TO

# Oracle
ORACLE_BASE=/u01/app/oracle

# Logging and Working Files
LOG=/home/oracle/log/load_test.$(date '+%Y%m%d.%H%M%S').log
WORKING_DIR=/home/oracle/scripts/ksh/odaperf
WORKING_DIR_OUTPUT=/home/oracle/scripts/ksh/odaperf/output
BASE_SCRIPT=$WORKING_DIR/base.sql

export LOG WORKING_DIR WORKING_DIR_OUTPUT BASE_SCRIPT

# Process
PROCESS_NAME="11g Oracle Load Test"
NO_OF_PARALLEL_SESSIONS=10 # Min is 2 or larger
SQL_TIMES=3
RAMP_UP_TIME=21
((SESSION_DELAY=NO_OF_PARALLEL_SESSIONS-1))
((SESSION_DELAY=$RAMP_UP_TIME / $SESSION_DELAY))
export PROCESS_NAME NO_OF_PARALLEL_SESSIONS SQL_TIMES RAMP_UP_TIME SESSION_DELAY

echo "Starting ${PROCESS_NAME} at `date` on `hostname` \n" >>$LOG

echo "Session Delay: ${SESSION_DELAY} /n"

# Build Script File for SQL_TIMES Iterations *********************
#
# Loop through NO_OF_PARALLEL_SESSIONS and run sql SQL_TIMES
# This means that there will be NO_OF_PARALLEL_SESSIONS to simulate sessions 
# and the base sql script will be run SQL_TIMES for each session started
#
# To do this Let's create a script from the base  that is the same as the base script run SQL_TIMES
# Let's delete copies already created in case the base script has been modified 
# Note all change should be made to the base test script

nLoopIteration=1
# Remove existing concatonates SQL SCRIPT FILE of the form base_x<no of iterations>.sql 
RUN_BASE=base_x${SQL_TIMES}
export RUN_BASE

rm ${RUN_BASE}.sql
# rebuild concatonated sql script file
while [ $nLoopIteration -le ${SQL_TIMES} ] 
do 
	cat ${BASE_SCRIPT} >> ${RUN_BASE}.sql # Append
	((nLoopIteration=nLoopIteration + 1))
done

echo "Built run script $ at `date`\n"

# Now that our special base script is created, let's start the processes with no-hup
# We can add in a ramp up delay rounded to the narest second -- ramp_up_time
# So our delay between each session start is sess_delay = ramp_up_time / (NO_OF_PARALLEL_SESSIONS-1)
# Assumption: we start a session - then sleep between sessions so the last session starts up at end of ramp_up_time period roughly
# zero time for kick off on each session is assumed (not completely true) and no program time - 
# So this means ramp up time is a rough estimate

nLoopIterations=1
while [ $nLoopIterations -le $NO_OF_PARALLEL_SESSIONS ]
do
        # Create spool command
	echo "spool ${WORKING_DIR_OUTPUT}/$(date '+%Y%m%d.%H%M%S')_${RUN_BASE}_p${nLoopIterations}.out" > ${WORKING_DIR}/${RUN_BASE}_p${nLoopIterations}.spooltext # overwrite spool text file
	# Create full sql file
	cat ${WORKING_DIR}/${RUN_BASE}_p${nLoopIterations}.spooltext ${WORKING_DIR}/login.sql ${WORKING_DIR}/${RUN_BASE}.sql exit.sql > ${WORKING_DIR}/${RUN_BASE}_p${nLoopIterations}.sql
	nohup $ORACLE_HOME/bin/sqlplus -s testuser${nLoopIterations}/PasswordGoesHere @${WORKING_DIR}/${RUN_BASE}_p${nLoopIterations}.sql  > ${WORKING_DIR_OUTPUT}/${RUN_BASE}_p${nLoopIterations}.nohupout 2>&1 &
        ((nLoopIterations=nLoopIterations + 1))
        sleep $SESSION_DELAY
done

echo "All nohup job started at `date`/n"


echo "Startup Initiated of ${PROCESS_NAME} at `date` on `hostname` /n" >>$LOG
mailx -s "${PROCESS_NAME} at `date` on `hostname` STARTED" ${RUN_MAIL_TO} < $LOG

exit 0

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