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