Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 46763
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: bohica [...] ntlworld.com
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 1.23
Fixed in:
  • 1.24
  • 1.24a
  • 1.24b



Subject: RowCacheSize is being ignored slowing down fetches a lot
Download (untitled) / with headers
text/plain 263b
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"? #
Download (untitled) / with headers
text/plain 377b
seems the row cache size was added a while ago in 1.21 to make reff cursors faster. So it I am going to look into it a little deeper and I think the 128 is ratgher arbiratyr as well I might just open it up more and just let the user set it as they like as there is no real reason for the older system that was for the older 7 and 8 oracles that where more memory dependand
Download (untitled) / with headers
text/plain 999b
On Fri Jul 03 17:06:40 2009, PYTHIAN wrote: Show quoted text
> seems the row cache size was added a while ago in 1.21 to make reff > cursors faster. So it I am going to look into it a little deeper and I > think the 128 is ratgher arbiratyr as well > > I might just open it up more and just let the user set it as they like > as there is no real reason for the older system that was for the older > 7 and 8 oracles that where more memory dependand
row cache size limited to 128 is really the following issue I reported: http://rt.cpan.org/Public/Bug/Display.html?id=46998 This one is more serious as setting RowCacheSize actually slows the fetch down because something in DBD::Oracle is broken when it is set. Basically rs_array_on is never set on the sth. The workaround I demonstrate above appears to work (we've been running with this change and the limit of 128 removed for a while) but I'd suggest someone with better knowledge of DBD::Oracle checks it out first. Martin -- Martin J. Evans Wetherby, UK
Well this is one of the most major revisions of DBD:Oracle since I took it over Seems we were way back in the 1990s when it came to how DBD::Oracle was using OCI to fetch Rows from a record set. Seems we were only fetching 1 row at a time when in fact we could get more than 1 row per fetch. So DBD::Oracle was using RowCacheSize incorrectly. Internally it was using this to set the OCI_ATTR_PREFETCH_ROWS, (which like OCI_ATTR_PREFETCH_MEMORY) does not cache any rows but buffers them up for the next fetch. They does speed thing up but not very much and in some cases it slows things down. The link below is a new Test branch of DBD::Oracle what I hope implements all things correctly http://svn.perl.org/modules/dbd-oracle/branches/rs_array 1) RowCacheSize Now selects that many rows on each fetch 2) OCI_ATTR_PREFETCH_ROWS, OCI_ATTR_PREFETCH_MEMORY Work correctly as well by prefetching rows before each fetch 3) Some types (blobs,clobs,Nclobs) may not work with multiple rows per fetch these have been accounted for 4) RowsInCache is also enabled so you can see how many records you have to go before you next fetch from the server 5) you can turn RowCacheSize off by setting ora_row_cache_off=>1 on the prepare or setting RowCacheSize=1 which has the same effect 6) RowCacheSize does not work for scrollable cursors You should see some great increases in speed as you will no longer have to go to server to fetch each row. Some Tweaking of the setting might be required and you can do this by setting ora_oci_success_warn=>1 which will display some tweaking info. As well I have set up DBD::Oracle to fetch rows on execute so in cases where you have very few rows (or a large RowCacheSize ) you could do the whole select without ever having to go back to the server to fetch a row I have retweaked the row caching function so it now selects a better size for RowCacheSize and OCI_ATTR_PREFETCH_ROWS so now by default you will see a great increase in speed. Hopefully this fixes the speed issue. I still have to update the pod with all the details and I ams still working them out
Download (untitled) / with headers
text/plain 318b
I have now tested this code inside and out and it is ready to put into trunk. You will find that you selects will work much faster that before. At least a 30% or even more speed increase you can get the code in Trunk http://svn.perl.org/modules/dbd-oracle/trunk it will be release with 1.24 version of the code


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.