BASH commands inbetween SQLPlus code or how to count the time used to run different SQLplus commands?

Question

For performance testing purposes - I need to count duration of SQLPlus query. Its not so simple as it seems to be.

I have the code here:

VARIABLE RC REFCURSOR;
EXEC :RC := $aktuellesBericht;
SPOOL ${BERICHT}_${configArray[0]}.DATA
PRINT RC;
SPOOL OFF

It's just a part of the whole command. The whole command looks like this:

sqlplus -s /nolog > /dev/null 2>&1 <<EOF
CONNECT USER/PASSWORD@DATABASE
#A ton of unimportant SET and CLEAR commands goes here#
VARIABLE RC REFCURSOR;
EXEC :RC := $aktuellesBericht;
SPOOL ${BERICHT}_${configArray[0]}.DATA
PRINT RC;
SPOOL OFF
QUIT
EOF

I need to count time of EXEC :RC := $aktuellesBericht; and PRINT RC; separately. Is this even possibe? Can I count it somehow? Let me use some pseudocode showing what I want to do:

execstart = date +%s%N
EXEC :RC := $aktuellesBericht;
execend = date +%s%N

SPOOL ${BERICHT}_${configArray[0]}.DATA

printstart = date +%s%N
PRINT RC;
printend = date +%s%N
SPOOL OFF

exectime=$(( execend - execstart ))
printtime=$(( printend - printstart ))

echo $exectime;
echo $printtime;

possible duplicate of Get execution time of sql script in oracle sqlplus — Etan Reisner
– Etan Reisner, Commented Nov 26, 2014 at 12:15

Philippe Banwarth · Accepted Answer · 2014-11-26 12:35:21Z

0

bash has a builtin time command which executes a command then print various times :

If the time reserved word precedes a pipeline, the elapsed as well as user and system time consumed by its execution are reported when the pipeline terminates. The TIMEFORMAT variable may be set to a format string that specifies how the timing information should be displayed;

# only the real time
TIMEFORMAT="This was done in : %R seconds"

time EXEC :RC := $aktuellesBericht;

answered Nov 26, 2014 at 12:35

Philippe Banwarth

17.8k2 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Philippe Banwarth Over a year ago

I thought EXEC was some alias, I was misled by the bash tag and the bash-like pseudo code, sorry

Collectives™ on Stack Overflow

BASH commands inbetween SQLPlus code or how to count the time used to run different SQLplus commands?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related