script: parallel cpu intensive sql

#!/bin/ksh
#
# cpu_throughput_test_v4_sql.sh
#
# this script runs CPU-intensive sql in N parallel sqlplus sessions
#
# Parameters: $1 - how many parallel sqlplus sessions to run
#
# Usage example: cpu_throughput_test_v4_sql.sh 8 | tee 8_sessions.log
#
# Script Steps:
#    1. create N named pipes
#    2. start N sqlplus processes in background, connect to db and start reading from named pipes
#    3. push into pipes:
#       3.1 "sleep 15" to let sessions establish
#       3.2 for debugging: set time on sqlprompt, set timing
#       3.4 push test SQL into pipes
#       3.5 for debugging: print last known CPU number
#    4. wait for all background sqlplus sessions to finish and measure elapsed time
#    5. repeat above process 100 times for given parallel=N
#
# change log:
#    012017 - A. Balbekov, initial creation
#

PARALLEL=$1
#CONN=username/psw@//host:port/sid
CONN="/ as sysdba"

trap 'kill -KILL `jobs -p` ; exit' INT
trap 'rm -f inputpipe_*; exit' EXIT

rm output_* 2>/dev/null
# start PARALLEL sqlplus processes in background having them wait for input in inputpipes
# the processes will immediately block until there is something in pipes (i.e. will not immediately connect to database)
echo "Parallel Sessions:  $PARALLEL"
i=0
while [ $i -lt $PARALLEL ] ; do
        rm -f inputpipe_$i
        mkfifo inputpipe_$i
        sqlplus $CONN < inputpipe_$i 1>output_$i 2>&1 &
        (( i=$i+1 ))
done

# push commands into inputpipes to wake up blocked sqlplus processes
# do this in background subshells to make it as parallel as possible
i=0
while [ $i -lt $PARALLEL ] ; do
		# output of folowing block goes into named pipes, so the pipes initiate
		# this wakes up blocked sqlplus processes that are waiting to read from the pipes
		# let the first command be "sleep" to give some time for sqlplus db connections to establish
        (
		sleep 10
		# set time on sqlprompt for debugging, set timing for debugging
		echo "set time on timing on"
		# run test sql
        echo "with t as ( SELECT rownum FROM dual CONNECT BY LEVEL <= 100 )"
        echo "select /*+ ALL_ROWS */ count(*) as cnt from t,t,t,t ;"
        echo exit
        ) > inputpipe_$i &
        (( i=$i+1 ))
done

# wait until all background processes finish
wait

# collect and print some per-process diagnostics output
i=0
while [ $i -lt $PARALLEL ] ; do
	# collect elapsed times; careful! - this gets only min:sec portion in elapsed time 
	echo `tail -3 output_$i | head -1 | awk '{print $2}' | awk -F ":" '{print $2":"$3}'`
	(( i=$i+1 ))
done


%d bloggers like this: