I am running some simple load tests and need to change my scripts a bit.
My first pass was to take a 10046 trace file and pull out the SQL and
bind values using a home grown awk script (if anyone is interested I'll
be happy to post it) and some munging. This gave me an SQL*Plus script
with the SQL using bind variables. The bind values were applied with
'exec :x1:='value' ' interspersed throughout the script. This could then
be easily looped by calling it from a shell script. Primary Keys (and
other uniqueness constraints) were dealt with by adding the looping
value in the shell to the appropriate bind value.
I started here because it was easy to get the scripts into this form
(the SQL in the trace file was identical to the SQL in the script). It
worked, but there was one problem. Most of my time was spent using CPU.
Looking at some more trace files I found that a sizable percentage of
this CPU was from my 'exec' statements. (If I only had enqueue
contention these scripts would have been more useful). So now I need to
redo the scripts a bit. I see two choices off the top of my head, but
before putting in the time, I was looking for suggestions as to the best
bet.
First is PL/SQL as I can easily do multiple loops from within the same
session. The problem with PL/SQL is that I need to rewrite the SQL to
get it to work. INSERTs, UPDATEs, and DELETEs need a USING clause to
apply the bind values. SELECT will need a SELECT ... INTO. Some of the
selects are single record and some are multiple record. As I will be
generating other scripts in the future, I want to keep the process as
automated as possible. (some manual munging will still be necessary).
The second option is to finally take Perl off of my list of 'things I
really should learn' and use it. Since I haven't done much with Perl yet
I am not sure if it is the right tool.
Which route would you take? Is there another option I am leaving out
entirely? (yeah, I know, hammerora).
Thanks.
Henry
--
//www.freelists.org/webpage/oracle-l
