DBD::Oracle: RowCacheSize ignored

9 June, 2009 - 10:36 — martinjevans

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.

Trackback URL for this post:

http://www.martin-evans.me.uk/trackback/24

Comments

26 July, 2009 - 09:34 — martinjevans

Syndicate

User login

Links

My professional profile

My picasa photos
Terms of Use

martin-evans.me.uk Always finish the day on a success

Navigation

New forum topics

Active forum topics