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.
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-28 06:54:17.000000000-0700
@@ -1298,6 +1298,13 @@
/* set ACTIVE so dbd_describe doesn't do explicit OCI describe */
DBIc_ACTIVE_on(imp_sth_csr);
+/**********************************************************************
+* rs_array: mark array fetch on for ref cursor - START
+**********************************************************************/
+ imp_sth_csr->rs_array_on=1;
+/**********************************************************************
+* rs_array: mark array fetch on for 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-28 06:49:33.000000000-0700
@@ -134,6 +134,17 @@
/* (In/)Out Parameter Details */
bool has_inout_params;
+/************************************************************
+* rs_array: state variables - START
+************************************************************/
+ 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 */
+/************************************************************
+* rs_array: state variables - END
+************************************************************/
};
#define IMP_STH_EXECUTING 0x0001
Only in DBD-Oracle-1.19.refcursor: Makefile.old
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-28 08:57:14.000000000 -0700
@@ -1294,6 +1294,23 @@
oci_error(h, imp_sth->errhp, status, "OCIAttrSet
OCI_ATTR_PREFETCH_ROWS");
++num_errors;
}
+/**********************************************************************
+* rs_array: initialize cache - START
+**********************************************************************/
+ 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);
+/**********************************************************************
+* rs_array: initialize cache - END
+**********************************************************************/
}
else { /* set cache size by memory */
cache_mem = -imp_sth->cache_rows; /* cache_mem always +ve here */
@@ -1579,7 +1596,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_array: allocate field buffer array - START
+**********************************************************************/
+ fbh->fb_ary = fb_ary_alloc(define_len, imp_sth->rs_array_size);
+/**********************************************************************
+* rs_array: allocate field buffer array - END
+**********************************************************************/
fb_ary = fbh->fb_ary;
if (fbh->ftype == 116) { /* RSET */
@@ -1663,8 +1686,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_array: iteration - START
+**********************************************************************/
+ 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;
+/**********************************************************************
+* rs_array: iteration - END
+**********************************************************************/
}
if (status != OCI_SUCCESS) {
@@ -1698,7 +1739,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_array: offset values - START
+**********************************************************************/
+ 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);
+/**********************************************************************
+* rs_array: 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 +1773,14 @@
++err; /* fetch_func already called oci_error */
}
else {
- int datalen = fb_ary->arlen[0];
- char *p = (char*)&fb_ary->abuf[0];
+/**********************************************************************
+* rs_array: use offset data - START
+**********************************************************************/
+ int datalen = fb_ary->arlen[row_idx];
+ char *p = (char*)row_data;
+/**********************************************************************
+* rs_array: 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 +1802,13 @@
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_array: use offset data/len - START
+**********************************************************************/
+ sv_setpvn(sv, (char*)row_data,fb_ary->arlen[row_idx]);
+/**********************************************************************
+* rs_array: 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-28 08:41:17.000000000-0700
@@ -96,6 +96,49 @@
ok(0, $close_cursor->execute);
}
+##################################################
+# Row fetch timing: START
+##################################################
+use Time::HiRes qw(gettimeofday tv_interval);
+
+sub timed_fetch {
+ my($rs,$caption) = @_;
+ my $tm_start=[gettimeofday];
+ my $rows = $rs->fetchall_arrayref();
+ my $elapsed=tv_interval($tm_start);
+ print "Fetched ".(@{$rows})." 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 default-cache)
+##################################################
+$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,'refcursor default-cache');
+
+##################################################
+# Row fetch timing: END
+##################################################
+
$dbh->disconnect;
exit 0;
============================== patch file end ==============================
On 9/20/07, Tim Bunce <[EMAIL PROTECTED]> wrote:
>
> On Wed, Sep 19, 2007 at 08:57:26PM +0530, Biswadeep Chowdhury wrote:
> > Hello,
> >
> > There are several things that I would like to point out:
> >
> > 1. Look no further than what happens for SELECTs. Data caching is turned
> on
> > by default - and I have not heard much complaining about it. Perl is
> seldom
> > used for user interface development and the scenario you describe is an
> > exception rather than the norm. If turning on data caching by default
> was a
> > good decision for SELECTs, then there is little reason not to turn on
> > caching by default for REF CURSORs.
>
> Agreed. The fact it doesn't work now is purely a bug/limitation of the
> Oracle OCI interface.
>
> It would be good if someone with Oracle support would search for
> (and/or submit) a bug report about this. Perhaps there is some magic
> OCI API incantation that would make row caching for ref cursors work.
>
> I believe there must be, otherwise a great many oracle applications
> would be suffering the same slowdown.
>
> Any volunteers to check Oracle support about this issue?
>
> > 2. Nevertheless, in the patch I have kept data caching turned off by
> default
> > for REF CURSORs so that users who are currently happy fetching a single
> row
> > per DB roundtrip can remain in that state. Only if users set the caching
> > parameter, multiple rows will be fetched per DB roundtrip.
>
> It should be enabled by default. Lack of caching is a bug.
>
> > 3. Per-statement cache tuning may be done by setting the
> RefCursorCacheSize
> > to an appropriate before executing/fetching from a REF CURSOR. This is
> just
> > as you would do for RowCacheSize - which controls rows fetched per DB
> > roundtrip for SELECTs (Set this to explicitly 1 and fetch a significant
> > number of rows and you will realize that data caching is turned on by
> > default for SELECT statement results).
> >
> > The reason I recommend this patch is that there is currently no way
> (that I
> > am aware of) to improve performance of fetch from REF CURSORs in perl. I
> > dont mind it being turned off by default - but at least users who want
> the
> > efficiency will have an option to do so.
>
> I'd want it on by default, with a suitable default cache size (ideally
> calculated the same way the default row cache is sized), but I'd rather
> not have to apply the patch at all.
>
> Before applying a large workaround we should be quite sure there isn't
> a simple fix via the OCI API.
>
> Tim.
>
> > Biswa
>
