Re: External C Procedures performance

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 - 01:00:58 CST