| Subject: | RowCacheSize is being ignored slowing down fetches a lot |
See attached script demonstrating that RowCacheSize is ignored and in
fact, the default RowCacheSize is not acted upon. Attached script
contains a solution but I'm unsure if it is the right one - seems to
work nicely here.
Martin
--
Martin J. Evans
Wetherby, UK
| Subject: | row_cache_size_simple.pl |
#
# $Id: row_cache_size_simple.pl 3167 2009-06-09 10:03:57Z martin $
#
# Script demonstrating that setting RowCacheSize for a simple select in a
# prepare/execute/fetch is ignored. Evidence for the latter is the time
# taken for one with RowCacheSize not set and set is the same and
# if the a trace is examined rs_array_on is not set and every fetch calls
# OCIStmtFetch!
#
# Run once with no args to create table, rows and function
# e.g., perl x.pl
# Run again with any arg to fetch rows
# e.g., perl x.pl 1
# Change dbd_st_prepare as below and rerun - faster :-)
#
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=betoracle.easysoft.local;sid=devel', 'bet', 'b3t', {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;
#
# produces for me here:
#
# 25000 rows fetched
# 25000 rows fetched
# simple1 trial of simple (4.220s total)
# simple_rowcachesize1 trial of simple_rowcachesize (5.151s total)
#
# in other words setting RowCacheSize has slowed down fetching the 250000 rows
# Looking at the DBD log I see:
#
# 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 example without RowCacheSize and the following when RowCacheSize
# is set:
# row cache OCI_ATTR_PREFETCH_ROWS 100, OCI_ATTR_PREFETCH_MEMORY 0
# rs_array_init: rs_array_on=0, rs_array_size=1
#
# In neither case is rs_array_size set to anything greater than 1 and I
# believe this is because rs_array_on is never set on the sth. If you look
# at reference cursors, rs_array_on is set as per the bug I reported to
# you some time ago which was fixed by setting rs_array_on in the child
# sth.
#
# If you change dbd_st_prepare to set rs_array_on:
#
# imp_sth->done_desc = 0;
# imp_sth->get_oci_handle = oci_st_handle;
# imp_sth->rs_array_on = 1; /* <--- line added */
#
# and rerun you will get:
#
# 250000 rows fetched
# 250000 rows fetched
# simple1 trial of simple (2.291s total)
# simple_rowcachesize1 trial of simple_rowcachesize (3.044s total)
#
# a load faster. The reason the first one is faster is actually because if
# rs_array_on is set the RowCacheSize defaults to 128 but in the second
# case it is set at 100 specifically in my example.
#
# I'm not saying this is the right change to make but clearly row cache size
# is not working at the moment for normal selects and yet works nicely in
# reference cursors.
#
#
# BTW, why 128:
#
# if (imp_sth->rs_array_on && cache_rows>0)
# imp_sth->rs_array_size=cache_rows>128?128:cache_rows;
# /* restrict to 128 for now */
#
# Why "restrict to 128 for now"?
#