Hello,
I hope a solution to the "prefetch issue for ref cursors and nested
cursors" will be considered in the next DBD-Oracle version - be it my
patch or an alternative fix.
To make it (seem) less daunting, I am attaching here a shorter version
of the patch diff file.
This diff file is about 100 lines shorter than the previous one I
sent. I have removed some (extra) comments and all changes made to
50cursor.t. Since this patch works for nested cursors, the performance
benefit can be tested can be tested using the existing test script
55nested.t
Thank You.
Biswadeep
============================== patch diff file start
==============================
diff -r -u DBD-Oracle-1.19/dbdimp.c DBD-Oracle-1.19.refcursor.min/dbdimp.c
--- DBD-Oracle-1.19/dbdimp.c 2006-11-03 06:05:46.000000000 -0800
+++ DBD-Oracle-1.19.refcursor.min/dbdimp.c 2007-11-06 13:18:50.000000000
-0800
@@ -1298,6 +1298,7 @@
/* set ACTIVE so dbd_describe doesn't do explicit OCI describe */
DBIc_ACTIVE_on(imp_sth_csr);
+ imp_sth_csr->rs_array_on=1;
if (!dbd_describe(sth_csr, imp_sth_csr)) {
return 0;
}
diff -r -u DBD-Oracle-1.19/dbdimp.h DBD-Oracle-1.19.refcursor.min/dbdimp.h
--- DBD-Oracle-1.19/dbdimp.h 2006-10-27 08:50:52.000000000 -0700
+++ DBD-Oracle-1.19.refcursor.min/dbdimp.h 2007-11-06 13:30:21.000000000
-0800
@@ -134,6 +134,11 @@
/* (In/)Out Parameter Details */
bool has_inout_params;
+ bool rs_array_on; /* if array to be used */
+ int rs_array_size; /* array size */
+ int rs_array_num_rows; /* num rows in last fetch */
+ int rs_array_idx; /* index of current row */
+ sword rs_array_status; /* status of last fetch */
};
#define IMP_STH_EXECUTING 0x0001
Only in DBD-Oracle-1.19.refcursor.min: Makefile.old
diff -r -u DBD-Oracle-1.19/oci8.c DBD-Oracle-1.19.refcursor.min/oci8.c
--- DBD-Oracle-1.19/oci8.c 2006-10-13 05:12:20.000000000 -0700
+++ DBD-Oracle-1.19.refcursor.min/oci8.c 2007-11-06 13:19:57.000000000
-0800
@@ -1294,6 +1294,17 @@
oci_error(h, imp_sth->errhp, status, "OCIAttrSet
OCI_ATTR_PREFETCH_ROWS");
++num_errors;
}
+ if (imp_sth->nested_cursor) /* array fetch for nested
cursors */
+ imp_sth->rs_array_on=1;
+ 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 */
+ else
+ imp_sth->rs_array_size=1;
+ imp_sth->rs_array_num_rows=0;
+ imp_sth->rs_array_idx=0;
+ imp_sth->rs_array_status=OCI_SUCCESS;
+ if (DBIS->debug >= 3)
+ PerlIO_printf(DBILOGFP, " sth_set_row_cache:
rs_array_size set to %d\n",imp_sth->rs_array_size);
}
else { /* set cache size by memory */
cache_mem = -imp_sth->cache_rows; /* cache_mem always +ve here */
@@ -1579,7 +1590,7 @@
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);
+ fbh->fb_ary = fb_ary_alloc(define_len, imp_sth->rs_array_size);
fb_ary = fbh->fb_ary;
if (fbh->ftype == 116) { /* RSET */
@@ -1663,8 +1674,20 @@
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);
+ imp_sth->rs_array_idx++;
+ if (imp_sth->rs_array_status==OCI_SUCCESS &&
imp_sth->rs_array_num_rows<=imp_sth->rs_array_idx)
+ {
+ OCIStmtFetch_log_stat(imp_sth->stmhp, imp_sth->errhp,
+ imp_sth->rs_array_size, (ub2)OCI_FETCH_NEXT, OCI_DEFAULT,
status);
+ imp_sth->rs_array_status=status;
+ OCIAttrGet_stmhp_stat(imp_sth, &imp_sth->rs_array_num_rows,0,
+ OCI_ATTR_ROWS_FETCHED, status);
+ imp_sth->rs_array_idx=0;
+ }
+ if (imp_sth->rs_array_num_rows>imp_sth->rs_array_idx)
+ status=OCI_SUCCESS;
+ else
+ status=imp_sth->rs_array_status;
}
if (status != OCI_SUCCESS) {
@@ -1698,7 +1721,9 @@
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];
+ int row_idx=imp_sth->rs_array_idx;
+ int rc = fb_ary->arcode[row_idx];
+ ub1* row_data=&fb_ary->abuf[0]+(fb_ary->bufl*row_idx);
SV *sv = AvARRAY(av)[i]; /* Note: we (re)use the SV in the AV */
if (rc == 1406 /* field was truncated */
@@ -1724,8 +1749,8 @@
++err; /* fetch_func already called oci_error */
}
else {
- int datalen = fb_ary->arlen[0];
- char *p = (char*)&fb_ary->abuf[0];
+ int datalen = fb_ary->arlen[row_idx];
+ char *p = (char*)row_data;
/* if ChopBlanks check for Oracle CHAR type (blank padded)
*/
if (ChopBlanks && fbh->dbtype == 96) {
while(datalen && p[datalen - 1]==' ')
@@ -1747,8 +1772,7 @@
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]);
+ sv_setpvn(sv, (char*)row_data,fb_ary->arlen[row_idx]);
if (CSFORM_IMPLIES_UTF8(fbh->csform))
SvUTF8_on(sv);
}
============================== patch diff file end
==============================
On Oct 2, 2007 6:12 PM, Tim Bunce <[EMAIL PROTECTED]> wrote:
> On Fri, Sep 28, 2007 at 12:42:24PM -0400, Biswadeep Chowdhury wrote:
> > Hello Tim,
> >
> > I have modified the patch taking into account your comments
> > -> Array fetch has been enabled for "nested cursors" too, in addition to
> > "ref cursors".
> > -> Array fetch is enabled by default (not for regular selects ofcourse -
> > which still uses OCI prefetch setting)
> > -> Array size is calculated based on fields (although I have restricted to a
> > max of 128 rows for now)
> > -> There is no separate parameter to manipulate array size
> > (RefCursorCacheSize), RowCacheSize itself is used.
> >
> > I am listing the patch diff file below (shorter now).
> > Unless someone can point out that elusive OCI setting, I request you to
> > consider this.
>
> John Scoles at pythian.com takes the lead for DBD::Oracle development now
> (for which I am eternally grateful). I'm sure he'll be considering it
> carefully.
>
> Thank you for your help - this is an important issue to resolve.
>
> Tim.
>
