Hello Tim, I agree the bug is really in OCI and ideally, that is where it should be fixed. I really hope that there is some "switch" that can fix the issue - but I have not been able to find any to date. I have tried this using Pro*C too (using the PREFETCH=n commandline option) but the result is the same - does not work for ref cursors.
People using Pro*C or OCI do however have the option to do array-fetch - that is call OCIStmtFetch2 to fetch more than one row at a time - although the pain of managing the data structures is now upon the user. It is not as transparent as setting PREFETCH=n - but still, an option exists for them to speed things up for REF CURSORs. This option is however not accessible to perl users. As you say, it would be better to turn some caching on by default - even if a conservative amount of rows. I have been looking for a fix for more than a year and have found none. I see the same issue affecting nested cursors. In the patch, I have addressed REF CURSORs only, but I expect we could apply the same fix for nested cursors. (You may run t/55nested.t independently to see degraded performance of fetch from nested cursors). Thanks. Biswa 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 > > > > > > On 9/19/07, Alexander V Alekseev < [EMAIL PROTECTED]> wrote: > > > > > > Hello! > > > > > > On Wed, 19 Sep 2007, Biswadeep Chowdhury wrote: > > > > > > > This patch speeds up both the regular fetch, as well as the > > > > fetchall_arrayref(). To me that seems better than ONLY speeding up > > > > fetchall_arrayref. > > > If application requires fast response (as typical user > > > interface does), you may slow down the process. If you fetch from > > > complex query, you may get the first row immediately, and the next > > > row may require a few seconds to calculate. So, if you fetch by 1 > > > row, you receive immediate response. If you fetch by 3 rows, you have > to > > > wait for database to calculate 3 rows, before you get the response. > > > > > > As I understand DBD interface, there is fetchrow_... interface > > > for 1-row fetch, and fetchall_arrayref() for multi-rows fetch. > > > Am I right? > > > Data caching is a good idea, but it should be tuned > > > per-statement. Though I don't know the way to do it, as prepare() > > > doesn't accept any additional attributes. > > > > > > Bye. Alex. > > > > > > >
