Re: External C Procedures performance

A PL/SQL call takes only less then 0.001 ms..

The test is repeated 10 times on a single PL/SQL block and the time is taken outside a loop of 1000. Out of order result was discarded.

Tested on a standalone computer and I'm the only session.

The ext-proc is a dummy one, no algorithm, it is empty. The PL/SQL also.

The difference is bigger then 700x. Unacceptable.

Thanks.

STW "Karen Abgarian" <abvk_at_ureach.com> wrote in message news:3DC5FC34.E0A89FB_at_ureach.com...
> Very valid comment.
>
> I would also add that PLSQL is also the overhead. And a call to any
> timing mechanism may be as well. And the test needs to be repeated
> several times. And even after all this is done, there is an effect of
> unix context switches which may invalidate the whole thing.
>
> Try ruling out everything else before concluding something.
>
> Although, I am not surprised by the number, it is actually pretty good
> for the Oracle architecture.
>
> Regs
> AK
>
>
> Finn Ellebaek Nielsen wrote:
>
> > 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 Mon Nov 04 2002 - 11:27:05 CST