Hello,
I am listing the patch (diff file).
The REF CURSOR array-fetch can be turned on using something like
"$dbh->{RefCursorCacheSize}=16;".
Array fetch remains off by default (single row fetch) so that it will not
affect users who dont care about it.
I have added tests to the end of "50cursor.t" that you may execute to view
performance improvements.
I hope you will consider it for the next version.
Thank You.
Biswa
============================== patch file start
==============================
diff -r -u DBD-Oracle-1.19/dbdimp.c DBD-Oracle-1.19.refcursor/dbdimp.c
--- DBD-Oracle-1.19/dbdimp.c 2006-11-03 06:05:46.000000000 -0800
+++ DBD-Oracle-1.19.refcursor/dbdimp.c 2007-09-13 14:02:31.000000000 -0700
@@ -837,6 +837,15 @@
else if (kl==12 && strEQ(key, "RowCacheSize")) {
imp_dbh->RowCacheSize = SvIV(valuesv);
}
+/************************************************************
+* rs_batch: save cache parameter - START
+************************************************************/
+ else if (kl==18 && strEQ(key, "RefCursorCacheSize")) {
+ imp_dbh->RefCursorCacheSize = SvIV(valuesv);
+ }
+/************************************************************
+* rs_batch: save cache parameter - END
+************************************************************/
else if (kl==22 && strEQ(key, "ora_max_nested_cursors")) {
imp_dbh->max_nested_cursors = SvIV(valuesv);
}
@@ -884,6 +893,15 @@
else if (kl==12 && strEQ(key, "RowCacheSize")) {
retsv = newSViv(imp_dbh->RowCacheSize);
}
+/************************************************************
+* rs_batch: get cache parameter - START
+************************************************************/
+ else if (kl==18 && strEQ(key, "RefCursorCacheSize")) {
+ retsv = newSViv(imp_dbh->RefCursorCacheSize);
+ }
+/************************************************************
+* rs_batch: get cache parameter - END
+************************************************************/
else if (kl==22 && strEQ(key, "ora_max_nested_cursors")) {
retsv = newSViv(imp_dbh->max_nested_cursors);
}
@@ -1298,6 +1316,13 @@
/* set ACTIVE so dbd_describe doesn't do explicit OCI describe */
DBIc_ACTIVE_on(imp_sth_csr);
+/**********************************************************************
+* rs_batch: mark as ref cursor - START
+**********************************************************************/
+ imp_sth_csr->rs_batch_ref_cursor=1;
+/**********************************************************************
+* rs_batch: mark as ref cursor - END
+**********************************************************************/
if (!dbd_describe(sth_csr, imp_sth_csr)) {
return 0;
}
diff -r -u DBD-Oracle-1.19/dbdimp.h DBD-Oracle-1.19.refcursor/dbdimp.h
--- DBD-Oracle-1.19/dbdimp.h 2006-10-27 08:50:52.000000000 -0700
+++ DBD-Oracle-1.19.refcursor/dbdimp.h 2007-09-13 13:51:55.000000000 -0700
@@ -80,6 +80,13 @@
int proc_handles; /* If true, srvhp, svchp, and authp handles
are owned by ProC and must not be freed.
*/
int RowCacheSize;
+/************************************************************
+* rs_batch: cache parameter - START
+************************************************************/
+ int RefCursorCacheSize; /* ref cursor cache parameter */
+/************************************************************
+* rs_batch: cache parameter - END
+************************************************************/
int ph_type; /* default oratype for placeholders */
ub1 ph_csform; /* default charset for placeholders */
int parse_error_offset; /* position in statement of last error */
@@ -134,6 +141,17 @@
/* (In/)Out Parameter Details */
bool has_inout_params;
+/************************************************************
+* rs_batch: state variables - START
+************************************************************/
+ bool rs_batch_ref_cursor; /* indicates ref cursor */
+ int rs_batch_size; /* prefetch cache size */
+ int rs_batch_num_rows; /* num rows in last fetch */
+ int rs_batch_idx; /* index of current row */
+ sword rs_batch_status; /* status of last fetch */
+/************************************************************
+* rs_batch: state variables - END
+************************************************************/
};
#define IMP_STH_EXECUTING 0x0001
diff -r -u DBD-Oracle-1.19/oci8.c DBD-Oracle-1.19.refcursor/oci8.c
--- DBD-Oracle-1.19/oci8.c 2006-10-13 05:12:20.000000000 -0700
+++ DBD-Oracle-1.19.refcursor/oci8.c 2007-09-13 14:10:17.000000000 -0700
@@ -1572,6 +1572,36 @@
imp_sth->in_cache = 0;
imp_sth->eod_errno = 0;
+/**********************************************************************
+* rs_batch: initialize cache size - START
+**********************************************************************/
+/**********************************************************************
+* rs_batch: initialize cache size - START
+**********************************************************************/
+ if (imp_sth->rs_batch_ref_cursor)
+ {
+ if (imp_dbh->RefCursorCacheSize>64) /* restrict to 64 max for
now */
+ {
+ imp_dbh->RefCursorCacheSize=64;
+ } else {
+ if (imp_dbh->RefCursorCacheSize>0)
+ imp_sth->rs_batch_size=imp_dbh->RefCursorCacheSize;
+ else
+ imp_sth->rs_batch_size=1; /* default no array
fetch for now */
+ }
+ }
+ else /* do not use internal prefetch */
+ {
+ imp_sth->rs_batch_size=1;
+ }
+ imp_sth->rs_batch_num_rows=0;
+ imp_sth->rs_batch_idx=0;
+ imp_sth->rs_batch_status=OCI_SUCCESS;
+ if (DBIS->debug >= 3)
+ PerlIO_printf(DBILOGFP, " dbd_describe: rs_batch_size set to
%d\n",imp_sth->rs_batch_size);
+/**********************************************************************
+* rs_batch: initialize cache size - END
+**********************************************************************/
for(i=1; i <= num_fields; ++i) {
imp_fbh_t *fbh = &imp_sth->fbh[i-1];
int ftype = fbh->ftype;
@@ -1579,7 +1609,13 @@
sb4 define_len = (ftype==94||ftype==95) ? fbh->disize+4 :
fbh->disize;
fb_ary_t *fb_ary;
- fbh->fb_ary = fb_ary_alloc(define_len, 1);
+/**********************************************************************
+* rs_batch: allocate field buffer array - START
+**********************************************************************/
+ fbh->fb_ary = fb_ary_alloc(define_len, imp_sth->rs_batch_size);
+/**********************************************************************
+* rs_batch: allocate field buffer array - END
+**********************************************************************/
fb_ary = fbh->fb_ary;
if (fbh->ftype == 116) { /* RSET */
@@ -1663,8 +1699,26 @@
else {
if (DBIS->debug >= 3)
PerlIO_printf(DBILOGFP, " dbd_st_fetch %d fields...\n",
DBIc_NUM_FIELDS(imp_sth));
- OCIStmtFetch_log_stat(imp_sth->stmhp, imp_sth->errhp,
- 1, (ub2)OCI_FETCH_NEXT, OCI_DEFAULT, status);
+/**********************************************************************
+* rs_batch: iteration - START
+**********************************************************************/
+ imp_sth->rs_batch_idx++;
+ if (imp_sth->rs_batch_status==OCI_SUCCESS &&
imp_sth->rs_batch_num_rows<=imp_sth->rs_batch_idx)
+ {
+ OCIStmtFetch_log_stat(imp_sth->stmhp, imp_sth->errhp,
+ imp_sth->rs_batch_size, (ub2)OCI_FETCH_NEXT, OCI_DEFAULT,
status);
+ imp_sth->rs_batch_status=status;
+ OCIAttrGet_stmhp_stat(imp_sth, &imp_sth->rs_batch_num_rows,0,
+ OCI_ATTR_ROWS_FETCHED, status);
+ imp_sth->rs_batch_idx=0;
+ }
+ if (imp_sth->rs_batch_num_rows>imp_sth->rs_batch_idx)
+ status=OCI_SUCCESS;
+ else
+ status=imp_sth->rs_batch_status;
+/**********************************************************************
+* rs_batch: iteration - END
+**********************************************************************/
}
if (status != OCI_SUCCESS) {
@@ -1698,7 +1752,15 @@
for(i=0; i < num_fields; ++i) {
imp_fbh_t *fbh = &imp_sth->fbh[i];
fb_ary_t *fb_ary = fbh->fb_ary;
- int rc = fb_ary->arcode[0];
+/**********************************************************************
+* rs_batch: offset values - START
+**********************************************************************/
+ int row_idx=imp_sth->rs_batch_idx;
+ int rc = fb_ary->arcode[row_idx];
+ ub1* row_data=&fb_ary->abuf[0]+(fb_ary->bufl*row_idx);
+/**********************************************************************
+* rs_batch: offset values - END
+**********************************************************************/
SV *sv = AvARRAY(av)[i]; /* Note: we (re)use the SV in the AV */
if (rc == 1406 /* field was truncated */
@@ -1724,8 +1786,14 @@
++err; /* fetch_func already called oci_error */
}
else {
- int datalen = fb_ary->arlen[0];
- char *p = (char*)&fb_ary->abuf[0];
+/**********************************************************************
+* rs_batch: use offset data - START
+**********************************************************************/
+ int datalen = fb_ary->arlen[row_idx];
+ char *p = (char*)row_data;
+/**********************************************************************
+* rs_batch: use offset data - END
+**********************************************************************/
/* if ChopBlanks check for Oracle CHAR type (blank padded)
*/
if (ChopBlanks && fbh->dbtype == 96) {
while(datalen && p[datalen - 1]==' ')
@@ -1747,8 +1815,14 @@
if (!fbh->fetch_func) {
/* Copy the truncated value anyway, it may be of use,
*/
/* but it'll only be accessible via prior bind_column()
*/
- sv_setpvn(sv, (char*)&fb_ary->abuf[0],
- fb_ary->arlen[0]);
+/**********************************************************************
+* rs_batch: use offset data/len - START
+**********************************************************************/
+ sv_setpvn(sv, (char*)row_data,
+ fb_ary->arlen[row_idx]);
+/**********************************************************************
+* rs_batch: use offset data/len - END
+**********************************************************************/
if (CSFORM_IMPLIES_UTF8(fbh->csform))
SvUTF8_on(sv);
}
diff -r -u DBD-Oracle-1.19/t/50cursor.t DBD-Oracle-1.19.refcursor
/t/50cursor.t
--- DBD-Oracle-1.19/t/50cursor.t 2006-07-07 05:17:29.000000000 -0700
+++ DBD-Oracle-1.19.refcursor/t/50cursor.t 2007-09-13 14:32:
32.000000000 -0700
@@ -96,6 +96,59 @@
ok(0, $close_cursor->execute);
}
+##################################################
+# Row fetch timing: START
+##################################################
+use Time::HiRes qw(gettimeofday tv_interval);
+
+sub timed_fetch {
+ my($rs,$caption) = @_;
+ my $row_count=0;
+ my $tm_start=[gettimeofday];
+ while (my $array_ref = $rs->fetchrow_arrayref()) {
+ $row_count++;
+ }
+ my $elapsed=tv_interval($tm_start);
+ print "Fetched $row_count rows ($caption): [$elapsed] secs.\n";
+}
+
+##################################################
+# execute (select)
+##################################################
+my $sth = $dbh->prepare(
+ 'SELECT object_name FROM (SELECT object_name '
+ .'FROM all_objects WHERE ROWNUM<=70),(SELECT 1 FROM all_objects '
+ .'WHERE ROWNUM<=70)'
+);
+$sth->execute();
+timed_fetch($sth,'select');
+
+##################################################
+# execute (refcursor)
+##################################################
+$sth = $dbh->prepare(
+ 'BEGIN OPEN ? FOR '
+ .'SELECT object_name FROM (SELECT object_name '
+ .'FROM all_objects WHERE ROWNUM<=70),(SELECT 1 FROM all_objects '
+ .'WHERE ROWNUM<=70); '
+ .'END;'
+);
+my $rs;
+$sth->bind_param_inout(1,\$rs,0,{ ora_type => ORA_RSET });
+$sth->execute();
+timed_fetch($rs,'ref cursor');
+
+##################################################
+# execute (refcursor array-fetch)
+##################################################
+$dbh->{RefCursorCacheSize}=16;
+$sth->execute();
+timed_fetch($rs,'ref cursor array-fetch');
+
+##################################################
+# Row fetch timing: END
+##################################################
+
$dbh->disconnect;
exit 0;
============================== patch file end ==============================
---------- Forwarded message ----------
From: Biswadeep Chowdhury <[EMAIL PROTECTED]>
Date: Sep 14, 2007 2:09 AM
Subject: "speak before you patch": intention to patch DBD-Oracle to allow
faster fetch from REF CURSORs
To: [email protected]
Hello,
As the DBD-Oracle documentation mentions us to "speak before you patch", I
would like to know if there are any objections to patching DBD-Oracle to
improve data fetch from REF CURSORs.
Prefetch does not work for REF CURSORs in OCI. Since this is the underlying
library for DBD-Oracle, fetching a large number of rows from a REF CURSOR is
very slow as a roundtrip to the DB is done for each row. (See
http://www.cpanforum.com/posts/1567)
I would like to patch DBD-Oracle to allow faster retrieval of records from
REF CURSORs. I intend to do this by calling OCIStmtFetch to fetch more than
one row at a time (array fetch). I believe the improvements are significant.
I had discussed this with Mr. Tim Bunce quite a while ago and am listing the
last email for reference.
Thanks.
Biswa
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
On Fri, Jan 20, 2006 at 10:37:06AM -0500, Biswa Chowdhury wrote:
> -----Original Message-----
> From: Tim Bunce [mailto: [EMAIL PROTECTED]
> Sent: Friday, January 20, 2006 5:56 AM
> To: Biswa Chowdhury
> Cc: Tim Bunce
> Subject: Re: DBD-Oracle: implementing prefetch for REF CURSORs
>
> >But wouldn't any prefetch implemented outside if Oracles own libs will
> >require a round-trip to the server for each row, so you'll see little
> >gain over using $sth->fetchall_arrayref(undef, $max_rows)?
>
> No, if we implement prefetch, we will call to OCIStmtFetch in
> dbd_st_fetch (oci8.c)
> to return (say) 64 rows instead of 1 row.
> That lessens the roundtrips and speeds things up significantly.
Ah. I'd forgotten that OCIStmtFetch can take a count. Thanks.
Tim.
