Unless your Perl is quite invovled, it's unlikely that anything else will have anywhere as much impact as those things.

Maybe use prepare_cached too.

Walking the road to enlightenment... I found a penguin and a camel on the way.....
Fancy a yourname@perl.me.uk? Just ask!!!

1. sql-bench
2. MySQL Super Smack
3. MyBench

I've been asked for this on Oracle 10gR2. If other databases were an option I would probably use MySQL.

As for perl/oracle interaction, a few general rules of thumb are usually sufficient. Some have been cited in previous replies; another I would add, which could have major impact, is to use the oracle-native sqlload(er) tool to do bulk insertions into tables, rather than iterating over lots of DBI "insert into..." executions -- even when you use prepared/placeholder statements, bound variables, etc, in your DBI insert calls, this is still one or two orders of magnitude slower than sqlload.

Start as many instances if you need to simulate many connections.

#!/usr/bin/perl -w
$|++;
use strict;
use Time::HiRes qw/time/;


use DBI;
use DBD::Sybase;

my ($dbi, $user, $pass, $sql, $logEvery) = @ARGV or die(q{
Syntax:
call_sp connect_string user password sql

Example:
call_sp dbi:Sybase:server=DEV_DB_01_DS;database=test_performance sa PA
+SSWORD "exec jpl_test"
});
$logEvery ||= 200;


my $dbh = DBI->connect(
    $dbi,
    $user,
    $pass,
    { RaiseError => 1, PrintError => 0 }
);

my $sth = $dbh->prepare($sql);


my @aOldAverage;
my $maxOldAverage = 10;

my $no = 0;
my $timeLatest = time();
while(1) {
    if($no++ > $logEvery) {
        my $timeNow = time();
        my $timeDuration = $timeNow - $timeLatest;
        my $freq = sprintf("%0.2d", $no / ($timeDuration || 1));


        $timeLatest = $timeNow;
        $no = 0;

        push(@aOldAverage, $freq);
        if(@aOldAverage > $maxOldAverage) {
            shift(@aOldAverage);
        }
        my $oldCount = @aOldAverage;
        my $sum = 0;
        $sum += $_ for(@aOldAverage);
        my $avg = sprintf("%0.2d", $sum / $oldCount);

        print localtime() . ": $freq / sec ($avg/sec for the last $old
+Count readings)\n";        
    }
    
    eval { $sth->execute(); };
    if($@) {
        warn("$@\n");
        $sth = $dbh->prepare($sql);
        next;
    } 
    $sth->fetchall_arrayref;
    $sth->finish;
}



__END__
[download]

If you need more complex behaviour, you may need to develop a robot (bot) user that simulates typical behaviour with a proper mix of idle time, selects, inserts, etc. If most of the time is spent idle, look into POE (or something like that) to run hundreds or thousands of users in a single process. Start more processes until the machine can't handle any more, then add extra machines.

/J