Subject: Re: DBD::Oracle Slow cursors

From: Calin Medianu <[email-masked]>
Date: Tue, 30 Apr 2002 11:34:29 -0700 (PDT)

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.

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

---

From: Calin Medianu <[email-masked]>
Date: Fri, 3 May 2002 05:36:40 -0700 (PDT)

Sure,

I'll give it a try next week.

Cheers,

Calin
--- Tim Bunce <[email-masked]> wrote:
> 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:
> >
>