Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> load test

load test

From: Henry Poras <>
Date: Mon, 30 Oct 2006 10:00:43 -0500
Message-ID: <>

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).



Received on Mon Oct 30 2006 - 09:00:43 CST

Original text of this message