DBD:Oracle 1.07 & ref cursor

Hi all.

I'm having trouble with stored pl/sql function which returns ref cursor.
This stored proc runs for 6 minutes while SQL itself executed for 6
seconds when executed outside pl/sql function.

Is there any way to speed things up without rewriting app without
packages/stored procedures ?

P.S. I know that DBD::Oracle 1.07 is VERY old version but unfortunately
I can't update it to newest one on production server...

Oracle server version is 9.2.0.6.0

Thanks in advance.

--
With Best Regards,
Eugene Krivdyuk

Re: DBD:Oracle 1.07 & ref cursor

--Next_1160457220---0-59.160.240.224-7753
Content-type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

  =0AHi I am unable to unsubscribe from this group. The mai=
l bounces back. Pls help.=0AMurali On Mon, 09 Oct 2006 Eugene Kri=
vdyuk wrote :=0A>Hi all.=0A>=0A>I'm having trouble with stored pl/sql funct=
ion which returns ref cursor.=0A>This stored proc runs for 6 minutes while =
SQL itself executed for 6=0A>seconds when executed outside pl/sql function.=
=0A>=0A>Is there any way to speed things up without rewriting app without=
=0A>packages/stored procedures ?=0A>=0A>P.S. I know that DBD::Oracle 1.07 i=
s VERY old version but unfortunately=0A> I can't update it to newest o=
ne on production server...=0A>=0A>Oracle server version is 9.2.0.6.0=0A>=0A=
>Thanks in advance.=0A>=0A>--=0A>With Best Regards,=0A> Eugene Krivdyuk=
=0A
--Next_1160457220---0-59.160.240.224-7753--

Re: DBD:Oracle 1.07 & ref cursor

On Mon, Oct 09, 2006 at 04:49:12PM +0300, Eugene Krivdyuk wrote:
> Hi all.
>
> I'm having trouble with stored pl/sql function which returns ref cursor.
> This stored proc runs for 6 minutes while SQL itself executed for 6
> seconds when executed outside pl/sql function.

Possibly due to a bug where row caching isn't enabled for ref cursors.

> Is there any way to speed things up without rewriting app without
> packages/stored procedures ?

Avoid fetching data directly from an sth created from a ref cursor.

You could, for example, use some pl/sql to copy the data from the ref
cursor into a temp table, then use DBI to fetch from that. There may
also be a more direct way (avoiding the temp table) but I'm not that
familiar with pl/sql. Perhaps others here can help.

Tim.

> P.S. I know that DBD::Oracle 1.07 is VERY old version but unfortunately
> I can't update it to newest one on production server...
>
> Oracle server version is 9.2.0.6.0
>
> Thanks in advance.
>
> --
> With Best Regards,
> Eugene Krivdyuk

RE: DBD:Oracle 1.07 & ref cursor

I've found that PIPELINED functions work really well. From Perl you simply
say something like :

my $sth = $dbh->prepare(q(
SELECT *
FROM TABLE(mypipeline_function)
));

Steve

> -----Original Message-----
> From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> Sent: Tuesday, 10 October 2006 6:54 PM
> To: Eugene Krivdyuk
> Subject: Re: DBD:Oracle 1.07 & ref cursor
>
> On Mon, Oct 09, 2006 at 04:49:12PM +0300, Eugene Krivdyuk wrote:
> > Hi all.
> >
> > I'm having trouble with stored pl/sql function which returns ref cursor.
> > This stored proc runs for 6 minutes while SQL itself executed for 6
> > seconds when executed outside pl/sql function.
>
> Possibly due to a bug where row caching isn't enabled for ref cursors.
>
> > Is there any way to speed things up without rewriting app without
> > packages/stored procedures ?
>
> Avoid fetching data directly from an sth created from a ref cursor.
>
> You could, for example, use some pl/sql to copy the data from the ref
> cursor into a temp table, then use DBI to fetch from that. There may
> also be a more direct way (avoiding the temp table) but I'm not that
> familiar with pl/sql. Perhaps others here can help.
>
> Tim.
>
> > P.S. I know that DBD::Oracle 1.07 is VERY old version but unfortunately
> > I can't update it to newest one on production server...
> >
> > Oracle server version is 9.2.0.6.0
> >
> > Thanks in advance.
> >
> > --
> > With Best Regards,
> > Eugene Krivdyuk