Subject: Re: DBD::Oracle Slow cursors

From: Calin Medianu <[email-masked]>
Date: Tue, 30 Apr 2002 16:04:47 +0300

[Add note to DBD::Oracle docs about using ethereal to sniff Oracle packets]
[Not sure if this bug got fixed yet. Maybe not.]

Me again with the slow cursors.

I modified both queries to only return 10 rows.
I ran a sniffer (ethereal) on the NIC. It is pretty cool, it also decodes TNS.

when I am using the SQL, it works like this, there are about 7 packets 
received by my workstation to set up the session, then all 10 rows are in the 
same packet, then there is another packet probably saying goodbye.

When I am using the REF cursor, each row comes in it's own TNS packet, that 
is why it is so slow!

Any idea how to fix it?

thanks a lot,

Calin

> On Mon, Apr 29, 2002 at 01:18:53PM -0700, Calin Medianu wrote:
> > Hello,
> >
> > I did the following. Wrote a perl script that retreves
> > data via a straight select from the database. Then I
> > wrote a stored procedure returning a ref cursor open
> > on the same select statement and retrieved the data as
> > well. Using the REF CURSOR/ sotred procedure was about
> > 3 time slower, that is 40 seconds instead of around
> > 10.
> >
> > Is this normal? Is this a problem with oracle or with
> > DBD::Oracle?
>
> DBD::Oracle. It probably isn't setting up a row cache for the ref cursor.
>
> Get a level 3 trace and look for the "dbd_describe'd" line for the
> ref cursor.
>
> Tim.