SMT test script

We are comparing the performance on oracle database when the AIX is configured with SMT 4 and NO SMT, I create the following scripts to test the elapsed time when running multiple oracle queries at one time.

1. The PL/SQL code: the procedure smttestproc measures the elapsed time in milliseconds. 

create table SMTTEST (roundid int,totalprocess int, processid int,starttime timestamp,endtime timestamp,elapsedmilliseconds number(10,0),tablerows int);
create or replace procedure smttestproc(roundid in int,totalprocess in int,processid in int)
is
begintime timestamp;
endtime timestamp;
elapsedmilliseconds number(10,0);
tablerows int;
begin
select count(*) into tablerows from table_line;
select systimestamp into begintime from dual;
select count(*) into tablerows from table_line,table_line;
select systimestamp into endtime from dual;
 select sum(
    (extract(hour from endtime)-extract(hour from endtime))*3600+
    (extract(minute from endtime)-extract(minute from begintime))*60+
   extract(second from endtime)-extract(second from begintime))*1000 ms into elapsedmilliseconds from dual;
insert into smttest values (roundid,totalprocess, processid,begintime,endtime,elapsedmilliseconds,tablerows);
commit;
end;

2. Unix shell script:
2.1 the bottomline script:

$ more smttest_muser.ksh

if [ $# -ne 4 ]
then
        echo "Example: `basename $0` <SID> <roundid> <totalprocess> <processid>"
print $#
        exit 1
fi
export ORAENV_ASK=NO
typeset -u ORACLE_SID=$1
export ORACLE_SID
. oraenv
export PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin:$PATH
n=$2
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect ctvdba/chinat0wn
exec smttestproc($2,$3,$4);
EOF

2.2 the wrapper script:

$more smttest_main.ksh

#!/bin/sh

if [ $# -ne 3 ]
then
        echo "Example: `basename $0` <SID> <roundid> <NumberOfProcesses>"
        exit 1
fi
export ORAENV_ASK=NO
typeset -u ORACLE_SID=$1
export ORACLE_SID
. oraenv
export PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin:$PATH

START=$(date +%s)

echo $(date)
export NumberOfProcess=$3
#for n in {1..$3}
n=1
while [ $n -le $3 ]
do
echo $n
nohup  /home/oracle/dba/jsun/smttest_muser.ksh $ORACLE_SID $2 $3 $n &
n=`expr $n+1`
done

while :
do
rc=`ps -ef|grep smttest_muser.ksh|grep -v grep|wc -l`
if [ $rc = 0 ]
then
break
fi
done


END=$(date +%s)
DIFF=$(( $END - $START ))
echo "It took $DIFF seconds"

3. Run it:

the example below is the third round of test, and it spawn 4 parallel processes:

./smttest_main.ksh ORACLE_SID 3 4

4. Analyze result:

Query the table smttest about the elapsed time etc. and show the result in excel chat.

select * from smtest