Take the 2-minute tour ×

Currently we have quite a few functions (normal CDF, inverse CDF, Vasicek and all kinds of derivatives) coded out in PL/SQL, but they are very slow.

I can get much better performance by streaming the data over a workstation where I have coded out things in C# and then bulk insert the results back. This appproach however leaves the network as the bottleneck, it would be much better if I could 'put the mill where the wood is' by having faster functions inside the Oracle DB.

I want to see how I can speed that up by coding it out in either c(++) or Java (or any other alternative you may have). Does anyone here have any experience with this? Hopefully one of you has tried all approaches and can explain wich has worked best overall.

Extra complication here is that IT is busy as it is, so if I want a waiver to use some feature on the DB I need to make a solid case. I don;t get to play around much on that box, else I would do that.

We're on Oracle Database 11g Enterprise Edition Release - 64bit Production

Thanks in advance,



Here's an example of what a function, wich is the Normal CDF by Cody.

The difference between this and the cume_dist is that cume_dist finds the distribution within a set of rows. I just need to convert a probability into standard deviations and back (a lot of times), like the NORMDIST and NORMINV functions in Excel.

    function stdnormal_cdf(u number) return number is
  z number;
  y Number;
    if y <= 0.6629126073623883041257915894732959743297 then
      z:=y * y;
      y:=u * ((((1.161110663653770e-002 * z + 3.951404679838207e-001) * z + 2.846603853776254e + 001) * z + 1.887426188426510e + 002) * z + 3.209377589138469e + 003)/((((1.767766952966369e-001 * z + 8.344316438579620) * z + 1.725514762600375e + 002) * z + 1.813893686502485e + 003) * z + .044716608901563e + 003);
      return 0.5  +  y ;
      z:=exp(-y * y/2)/2;
      if y <= 5.65685424949238019520675489683879231428 then
        y:=((((((((2.15311535474403846e-8 * y + 5.64188496988670089e-1) * y + 8.88314979438837594) * y + 6.61191906371416295e01) * y + 2.98635138197400131e02) * y + 8.81952221241769090e02) * y + 1.71204761263407058e03) * y + 2.05107837782607147e03) * y + 1.23033935479799725e03)/((((((((1.00000000000000000e00 * y + 1.57449261107098347e01) * y + 1.17693950891312499e02) * y + 5.37181101862009858e02) * y + 1.62138957456669019e03) * y + 3.29079923573345963e03) * y + 4.36261909014324716e03) * y + 3.43936767414372164e03) *  + 1.23033935480374942e03);
        y:=z * y;
        z:=z * 1.41421356237309504880168872420969807857/y;
        y:=2/(y * y);
        y:=y * (((((1.63153871373020978e-2 * y + 3.05326634961232344e-1) * y + 3.60344899949804439e-1) * y + 1.25781726111229246e-1) * y + 1.60837851487422766e-2) * y + 6.58749161529837803e-4)/(((((y + 2.56852019228982242) * y + 1.87295284992346047) * y + 5.27905102951428412e-1) * y + 6.05183413124413191e-2) * y + 2.33520497626869185e-3);
        y:=z * (1/1.77245385102123321827450760252310431421-y);
      end if;

      if u < 0 then 
        return y;
        return 1-y;
      end if;    
    end if;  


Ok so here are the benchmarks. Test table with 100k rows. The functions between Oracle and F# are pretty straight translations of each other and give the same result.

The qeury: select sum(get_rwa(approach, exposure_class_code, pd_r, lgd_r, ead_r, maturity_r, net_sale, rwf_r)) from functest

Interpreted: 12.8 sec
Native: 13.2 sec
.Net (F#): 0.04 sec.

This would make the .Net function 320x (!) faster than the Oracle implementation, I really don't understand where this difference could come from. Anything up to 3-10x would seem reasonable. I really think I'm missing something here. Anyone?

In F# I loaded the 100k rows into a List first. (seemed fair, just summing up any other column in Oracle cost 0.06 seconds, so it seemed fair to exclude the data acces time in both cases. It takes about 3 sec to load the data into a list, so even if I include the time it takes to open up the connection, execute and stream over the networks etc, then still it's 4x faster.)

share|improve this question
Have you profiled the pl/sql? If so what did it point at? –  EvilTeach Aug 23 '12 at 15:50
Actually no, didn't know that existed. Hope I have priviliges for that. I'll get back on this. –  gjvdkamp Aug 23 '12 at 15:55
Google DBMS_PROFILER as a start –  EvilTeach Aug 23 '12 at 16:00
I'd be interested to see a stored proc (with easy access to the data) that performs slower than some C# program. NOt saying it can't happen, but most likely the pl/sql code could be improved dramatically. CAn you post a simple example of the type of pl/sql you tried? –  tbone Aug 23 '12 at 23:35
Thanks for example, native compilation as Justin notes can help, but I doubt it will be dramatically faster. Are you sure this function is the performance bottleneck? How are you calling it? (i assume looping through some cursor of data). If you say that C# pgm gives much better performance, I doubt its because of a .NET implementation of this one function. –  tbone Aug 24 '12 at 12:56
show 4 more comments

2 Answers

up vote 4 down vote accepted


Likely, the time difference is due to context switching between the SQL engine and the PL/SQL engine. Each of the 100,000 rows in functest is put through the PL/SQL routine get_rwa (and/or stdnormal_cdf). A context switch involves saving state and restoring state which you probably won't notice when done once. But doing it 100,000 times adds up.

So I'd suggest loading up the 100,000 rows in a nested table with 100,000 rows and pass this nested table only once to a PL/SQL-routine that does a simple "for i in 1 .. [nested_table_variable].count loop ... end loop;", while summing up the individual outcomes.

Another alternative is doing it all in SQL without resorting to PL/SQL.


share|improve this answer
Do you know if using any of the external procedures (c, java, .Net) would not incur this overhead? Will give doing the whole thing from PL/SQL a try. –  gjvdkamp Aug 25 '12 at 9:14
It depends on how you use those procedures. Inside SQL (if possible, I don't know for sure) context switching will occur once for each row as well. When called from PL/SQL, after loading all rows in a collection upfront, you won't have this overhead. –  Rob van Wijk Aug 25 '12 at 9:45
Ok thanks for that.. so if I can chop up my work in smaller batches then I could run them from within the PL/SQL engine and be faster. Need to do some 30M rows and each of them with many different scenarios etc.. Was hoping for a lean as possible user experience but maybe I can wrap this in some kind of procedure. Thanks! –  gjvdkamp Aug 25 '12 at 9:58
add comment

Oracle supports the ability to define and call external procedures. Assuming you can compile your C/ C++/ C# application into a DLL/ .so and move that library to the database server, you could then expose the DLL's functions as external procedures and then call the DLL's functions from within the database. Since everything would be running on the same machine, the network wouldn't be a bottleneck. Of course, that would mean that your C/ C++/ C# code would be using the server's processing resources-- that may or may not be a good thing depending on how beefy the server's CPUs are compared to the workstation's and what else the server is doing.

Depending on exactly how you have coded the logic in PL/SQL, you might also want to look into either leveraging Oracle's built-in analytic functions like cume_dist for cumulative distributions (I'm assuming that's what you mean by "normal CDF") or writing your own analytic functions. Since your code is computationally intensive, it's also reasonably likely that you could benefit from native compilation. Of course, this assumes that you have profiled the code and that there aren't obvious places/ approaches to tuning the PL/SQL as it sits.

share|improve this answer
hi, I updated te question, cume_dist finds the cum dostribution of a set of rows, I need to convert probablities (0-100%) into standard deviations (roughly -5-+5) and back again a lot of times, like the NORMDIST and NORMINV functions in Excel. They don;t seem to exist in Oracle so we wrote our own. But that seems to be quite slow and I don;t know yet why. –  gjvdkamp Aug 24 '12 at 10:34
Theexternal procedures is what I was thinking of, I was hoping someone would have had good results with one approach. There might be pitfalls or issues that might make one approach work better than the other, but I couldn;t find anything on the perf of these approaches. –  gjvdkamp Aug 24 '12 at 10:36
Voted answer for native compilation. If all this stuff was indeed inetrpreted that would easiliy explain the poor perf. Wow, bnever expected it could be that simple. Haven;t tested it yet but this seems very likely to fix it. –  gjvdkamp Aug 24 '12 at 12:00
Hmmm, so far for the native theory, the native functions are actually a litle bit slower than the interpreted ones. Not getting this at all. –  gjvdkamp Aug 24 '12 at 16:11
Sorry, native compilation turned out not to speed things up after all. I updated the example with benchmarks. –  gjvdkamp Aug 24 '12 at 16:49
add comment

Your Answer


