Hi,
I was attempting to check DBD::Oracle 1.24 rc1 fixed the issue
"RowCacheSize is being ignored slowing down fetches a lot",
http://rt.cpan.org/Public/Bug/Display.html?id=46763 and came across a
possible bug/inconsistency in DBI or DBD::Oracle.
DBI says RowCacheSize is a connection attribute and RowsInCache is a
statement attribute - this seems to imply to me there can only be a
single cache per connection OR you cannot have different cache sizes per
statement AND the cache size must be set before creating a statement.
Was is the intention of the DBI spec to disallow setting the cache size
per statement?
In DBD::Oracle setting and retrieving RowCacheSize on a connection
handle works (as per DBI spec) but retrieving the RowsInCache only works
from a connection handle and not as documented from the statement handle
e.g.,
use 5.010;
use strict;
use warnings;
use DBI;
my $h = DBI->connect('dbi:Oracle:host=xxx;sid=xxx', 'xxx', 'xxx',
{PrintError => 0, RaiseError => 1});
my ($s, $field);
eval {$h->do('drop table martin');};
eval {$h->do('create table martin (a int)');};
$h->{AutoCommit} = 0;
$s = $h->prepare('insert into martin values(?)');
for (my $i = 1; $i <= 6; $i++) {
$s->execute($i);
}
$h->commit;
$h->{AutoCommit} = 1;
$h->{RowCacheSize} = 3;
my @results;
$s = $h->prepare('select a from martin');
$s->execute;
$s->bind_col(1, \$field);
while ($s->fetch) {
push @results, $field;
say "dbh/rcs ",
($h->{RowCacheSize} // 'undef'), ', ',
"dbh/ric ", ($h->{RowsInCache} // 'undef'), ', ',
"sth/rcs ", ($s->{RowCacheSize} // 'undef'), ', ',
"sth/ric ", ($s->{RowsInCache} // 'undef');
}
print scalar(@results) . " rows fetched\n";
produces:
dbh/rcs 3, dbh/ric 3, sth/rcs undef, sth/ric undef
dbh/rcs 3, dbh/ric 2, sth/rcs undef, sth/ric undef
dbh/rcs 3, dbh/ric 1, sth/rcs undef, sth/ric undef
dbh/rcs 3, dbh/ric 3, sth/rcs undef, sth/ric undef
dbh/rcs 3, dbh/ric 2, sth/rcs undef, sth/ric undef
dbh/rcs 3, dbh/ric 1, sth/rcs undef, sth/ric undef
in otherwords the RowsInCache is only retrievable from the connection
handle (contrary to DBI docs) thus re-enforcing my guess the cache is
per connection and not what I expected which was per stmt.
Also, DBI says for RowsInCache:
"If the driver supports a local row cache for SELECT statements, then
this attribute holds the number of un-fetched rows in the cache"
and it looks like DBD::Oracle is reporting 1 more than it should i.e.,
before the fetch instead of after it.
Any ideas/opinions on what it right/wrong here and what should happen?
I'll rt it when I have a better idea of what is at fault - if anything.
BTW, the links on CPAN in the DBI docs for RowCacheSize/RowsInCache and
a number of others do not work as the title of the sections is
"RowsInCache (integer)" and not "RowsInCache" as used in the L<>
references. I am prepared to fix that Tim if you just confirm you want
the section to stay as "XXX (integer)".
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
