Subject: Re: DBD::Oracle Slow cursors

From: Calin Medianu <[email-masked]>
Date: Tue, 30 Apr 2002 13:23:20 -0700 (PDT)

I "Solved" the problem. For Now.. I did
perl Makefile.PL -8

hoping that the buggy code would be recently added,
and it was. Now both the select and the cursor return
the data at the same speed, meaning fast..

Am I am missing much by not using the code for Oracle
8?

Thanks,

Calin

__________________________________________________
Do You Yahoo!?
Yahoo! Health - your guide to health and wellness
http://health.yahoo.com

---

From: Tim Bunce <[email-masked]>
Date: Wed, 1 May 2002 15:57:18 +0100

Thanks. I'll take a look when I get to DBD::Oracle again.

I think the last row of that table applies and it doesn't refer to OCIBindObject():
    REF CURSOR variables 
    SQLT_RSET 
    Allocate a statement handle, OCIStmt, and then bind its address
    (OCIStmt **) using the SQLT_RSET datatype.
Note that SQLT_REF isn't the same as SQLT_RSET.

You could always try patching it yourself!

Tim.

On Tue, Apr 30, 2002 at 11:34:29AM -0700, Calin Medianu wrote:
> It says here:
> http://technet.oracle.com/doc/oracle8i_816/appdev.816/a76975/oci05bnd.htm#421007
> 
> that 2 calls are neede to bind a ref , the second is
> to OCIBindObject() which I don't see in  dbdimp.c.
> 
> Could this be a reason?
> 
> Cheers,
> 
> Calin
> 
> --- Tim Bunce <[email-masked]> wrote:
> > On Tue, Apr 30, 2002 at 04:04:47PM +0300, Calin
> > Medianu wrote:
> > > 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?
> > 
> > Do a level 9 trace to get a log of the OCI calls and
> > confirm that
> > the fragment I posted is being called (may be
> > helpful to also
> > add an extra print statement into that code since
> > parsing the
> > OCI trace can be painful).
> > 
> > Assuming the code is being called then as far as I
> > can see the code is
> > doing the right thing and it's probably an Oracle
> > OCI issue.
> > 
> > You'd need to talk to Oracle support. No need to
> > mention perl etc.
> > Just talk about your OCI application and provide the
> > OCI call trace.
> > 
> > Let me know what you find out!
> > 
> > Tim.
> > 
> > > 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.
> 
>