Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL Procedure call from ado

Re: PL/SQL Procedure call from ado

From: Mark Wallace <markdw71-No_at_S-p-a-m-hotmail.com>
Date: Wed, 10 Apr 2002 15:11:27 +0100
Message-ID: <jKXs8.7$qo4.890@news.lhr.globix.net>


I have upgraded to 8.1.7.3 OLEDB provider... No change.

I have since isolated the type of call that is causing the problem. When calling a PL/SQL procedure that has an output parameter recordset, and no other output parameters there is no problem As soon as I start adding non-recordset (ie varchar2, number etc) output parameters, the call to the procedure immediatly starts taking excessivly long. Adding addtional recordset output parameters does not cause a problem.

As stated before this behaviour is not happening on all of our application servers.
On most of the servers, not matter which type of call I do, the speed is always fast.

I have also added logging to my pl/sql procedure, and once the procedure is hit it always executes in less than a second. And once completed, the ADO call immediatly returns thereafter... It seems that the bottleneck is when trying to setup the call to the database. Once setup, the actual call and returning of the data is always fast.

Mark

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:a90lfu$p37$1_at_babylon.agtel.net...
> A few suggestions:
>
> 1. upgrade to 8.1.7.3 client and OLEDB provider (download patches from
> MetaLink). See if that makes a difference.
> 2. Add some debugging to the stored procedure (for example, log the IP
> of the client and time elapsed using dbms_utility.get_time on entry and
> on exit) - see if there are any differences in sp execution speed
depending
> on client connected (I doubt it, but if there is any significant
difference,
> you will need to debug the sp and try to figure out what induces it).
> 3. This can be network transport problem - if it takes the same time to
> execute the procedure, and significantly different time to retrieve
the
> recordset (provided it is the same), then the difference in response
times
> should be accounted for network transport.
> 4. This can be client hardware (most probably insufficient memory).
>
> --
> Vladimir Zakharychev (bob_at_dpsp-yes.com)
http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
applications.
> All opinions are mine and do not necessarily go in line with those of my
employer.
>
>
> "Mark Wallace" <markdw71-No_at_S-p-a-m-hotmail.com> wrote in message
> news:Juis8.5$Re4.671_at_news.lhr.globix.net...
> > We are having the following problem...
> >
> > When using ADO to call a PL/SQL package that returns a recordset from
the
> > oracle database, the call takes excessively long, but only on one of our
web
> > servers!!
> > On another web server with the same software setup, i.e. same service
packs,
> > same oracle client etc, etc the call is almost instantaneous.
> > I am have uninstalled, re-installed ...
> > service packs...
> > Oracle client...
> > our software...
> > numerous times to see if I could track it down. No luck.
> >
> > Also when doing standard select queries down to the database (also with
ADO)
> > it all works great.
> > It is just when try to call a PL/SQL package that the one server is
hugely
> > slower that the other.
> >
> > Any help would be greatly appreciated.
> >
> > Summary of our setup :
> > ==========================================================
> > Client Machine :
> > Oracle Client 8.1.7
> > OraOLEDB provider 8.1.7.2 (latest version downloaded from oracle)
> > IIS 4
> > Win NT4 server with service pack 6a
> >
> > Server Machine :
> > Oracle 8.1.6
> > ==========================================================
> >
> > Regards
> > Mark
> >
> >
>
Received on Wed Apr 10 2002 - 09:11:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US