DBD::Oracle: RowCacheSize ignored

Just spent a few hours trying to find out why the RowCacheSize attribute in DBD::Oracle does not seem to have any affect at all. The following code demonstrates the problem:

use strict;
use warnings;
use DBI;
use DBD::Oracle qw(:ora_types);

use Benchmark::Timer;

my $bm = Benchmark::Timer->new(skip => 0);

my $h = DBI->connect('dbi:Oracle:host=machine.easysoft.local;sid=devel',
                                    'name', 'pass', {PrintError => 0, RaiseError => 1});
my ($s, $field, $sth);

if (scalar(@ARGV) < 1) {
    eval {$h->do('drop table martin');};
    eval {$h->do('create table martin (a int)');};
    $h->do('delete from martin');

    $h->{AutoCommit} = 0;

    $s = $h->prepare('insert into martin values(?)');
    for (my $i = 1; $i <= 250000; $i++) {
        $s->execute($i);
    }
    $h->commit;
    $h->{AutoCommit} = 1;

    exit 0;
}

my @results;
#####row cache OCI_ATTR_PREFETCH_ROWS 858, OCI_ATTR_PREFETCH_MEMORY 0
#####rs_array_init: rs_array_on=0, rs_array_size=1
$s = $h->prepare('select a from martin');
$s->execute;
$s->bind_col(1, \$field);
$bm->start('simple');
while ($s->fetch) {
    push @results, $field;
}
$bm->stop('simple');
print scalar(@results) . " rows fetched\n";

# now repeat with RowCacheSize
#####row cache OCI_ATTR_PREFETCH_ROWS 100, OCI_ATTR_PREFETCH_MEMORY 0
#####rs_array_init: rs_array_on=0, rs_array_size=1
$h->{RowCacheSize} = 100;
@results = ();
$s = $h->prepare('select a from martin');
$s->execute;
$s->bind_col(1, \$field);
$bm->start('simple_rowcachesize');
while ($s->fetch) {
    push @results, $field;
}
$bm->stop('simple_rowcachesize');
print scalar(@results) . " rows fetched\n";

print $bm->reports;

Run once without an argument it creates the test table and inserts 250000 rows. Run again with an argument you get:

25000 rows fetched
25000 rows fetched
simple1 trial of simple (4.220s total)
simple_rowcachesize1 trial of simple_rowcachesize (5.151s total)

Strangely, setting RowCacheSize to 100 slows the fetches down instead of speeding them up. Also, a quick look at the DBI/DBD log shows lines like this:

        row cache OCI_ATTR_PREFETCH_ROWS 858, OCI_ATTR_PREFETCH_MEMORY 0
        rs_array_init: rs_array_on=0, rs_array_size=1

for the first select example and like this:

        row cache OCI_ATTR_PREFETCH_ROWS 100, OCI_ATTR_PREFETCH_MEMORY 0
        rs_array_init: rs_array_on=0, rs_array_size=1

for the second select with RowCacheSize set. I was suspicious about those rs_array_size=1 lines. Interestingly, some time ago I found a similar problem with reference cursors and looking back at it I remembered that rs_array_on was not being set on the sth so no array fetching was taking place. A similar change to dbd_st_prepare in oci8.c seems to work fine:

        imp_sth->done_desc = 0;
        imp_sth->get_oci_handle = oci_st_handle;
        imp_sth->rs_array_on = 1; /* <---- added this */

After this change I get:

# 250000 rows fetched
# 250000 rows fetched
# simple1 trial of simple (2.291s total)
# simple_rowcachesize1 trial of simple_rowcachesize (3.044s total)

Much faster. The second one is slower because once rs_array_on is set RowCacheSize defaults to 128 but in the second select I set it to 100.

Reported this on rt at RowCacheSize is being ignored slowing down fetches a lot.

Comments

DBD::Oracle is working towards fixing this now

John Scoles has now worked around this restriction in the rs_array branch of DBD::Oracle in subversion. May be it will be in the next release (1.24) of DBD::Oracle.

Fix makes it to DBD::Oracle 1.24 RC4

This change is included in DBD::Oracle 1.24 RC4 so will likely make it to the full release.

Changes made it to 1.24a release

These changes made it to 1.24a.