Re: External C Procedures performance

From: Finn Ellebaek Nielsen <_remove_fen_remove__at_changegroup.dk>
Date: Thu, 31 Oct 2002 22:40:55 +0100
Message-ID: <3dc1a360$0$34811$edfadb0f@dspool01.news.tele.dk>

Hi WST.

Have you tried comparing a timing of the same algorithm with either:

• A call to an empty PL/SQL procedure.
• NULL.

Then it's easier to find the overhead with calling an external procedure compared to a stored procedure (or nothing).

Does the external procedure have any parameters or an OCI context?

Have you done your timing with a "warm" external procedure? Where you ensure that the external procedure has been loaded (that's the expensive part because of a new process and a DLL/shared object load). You do this by simply calling it once or perhaps a few times before doing your timing.

My measurements indicate that there's only a 10% overhead with "warm" external procedures compared to a PL/SQL stored procedure.

HTH. Finn Ellebaek Nielsen

---

Finn Ellebaek Nielsen ChangeGroup ApS

Product Manager, Principal Consultant, Partner Kronprinsessegade 54, 4.

E-mail: finn.ellebaek.nielsen_at_changegroup.dk DK-1306 Koebenhavn K

Mobile: +45 20 32 49 25 Denmark

Phone: +45 33 32 77 78 http://www.changegroup.dk

---

"Where do you want to GPF today?"

"WST" <me_at_privacy.net> wrote in message
news:aprpr5$4d7si$1_at_ID-127694.news.dfncis.de...
> I timed it like tihs,
>
> for n in 1..10 loop
> for i in 1..1000 loop
> >call the function
> end loop;
> >time here
> end loop;
>
> the result is about 700+ ms per outer loop.
>
> So the .7 ms was spend almost entirely by the Oracle external procedure
> calling mechanism, no parsing time there.
>
> And this seem to me that Oracle is doing very badly.
>
> In .7 ms the CPU can do about 500k operation, and Oracle used all these
for
> just a call. I do expect that it use 1/1000th of that (even so it's still
> kind of too much).
>
>
> WST
>
>
>
> "Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
> news:apquvp$gbv$1_at_ctb-nnrp2.saix.net...
> > WST wrote:
> >
> > > 0.7 ms is about 245k clock cycle for the mentioned machine, it is
way
> > > too much. You only need about 20 clock to call a DLL function.
> > > This overhead makes external C procedure much less useful.
> >
> > How did you measure the time of the call? I think it will be very
> difficult
> > to measure the actual physical call from Oracle to the DLL.. so maybe
the
> > reason for this seeming large delay is due to Oracle first parsing the
SQL
> > (or PL/SQL) and do whatever else it needs to do, before making the
actual
> > call, and then the time it takes to assemble the results in a cursor and
> > return it to you.
> >
> > After all, I think we can safely assume that it does something along the
> > lines of a GetProcAddress and calling the result via a type cast method
> > pointer... The speed of these being a direct function of the hardware
and
> > operating system, right?
> >
> > Back to your question about tuning - I can not see how one can tune the
> > speed of a external procedure call. There are no optimiser hints or
Oracle
> > parameters that I know of, that specifically address the speed of an
> > external DLL call..
> >
> > > And why you think that I develope SW for goverment?
> >
> > Hey, don't tell me you missed that smiley at the end. Was pulling your
leg
> > about writing something that does nothing (i.e. "empty" external proc).
> > Something like a technical spec drawn up by some government bureaucrat
in
> > an attempt to fix the problem with CPU idle time being too high. ;-)
> >
> > --
> > Billy
>
>
Received on Thu Oct 31 2002 - 15:40:55 CST