2 Replies Latest reply on Jul 2, 2013 5:58 PM by NickThurn

    Java stored procedure bridge to external system - performance


      We have been running a java bridge to an external timeseries database for about 10 years.


      The performance has been adequate but not stella... the same code runs an order of magnitude faster outside the oracle JVM...


      We are now going through a reengineering effort and (finally) are able to ditch a whole swodge of legacy junk, backward compatibility requirements and unnecessary complexity...


      But on reexamining the bridge process there appears to be no way to really improve performance.


      Our interface functions look like the following. The ARRAY contains STRUCT objects based on a type declared in the database schema. ARRAYs are returned so the functions can be used in the from clause of select statements.


      class Client {

           static ForeignServer server;

           static oracle.sql.ARRAY readData(String series, Date start, Date end) {

                if (server == null) server = new ForeignServer();

                return server.readData(series,start,end);




      The performance black spot is not (as I'd suspected) in the old homemade marshalling code but in the creation of STRUCT objects and the ARRAY itself?


      To put some numbers on this: returning series of 2000 points each our maximum throughput is 20 series/second (ie 50ms each). Outside the oracle JVM this jumps to 500 series/per second.


      We are using 11.2g and the JIT compiler is ON. (Bizzarly going from 10g to 11g made no performance difference!!)


      Has anyone done this sort of thing in java but with good performance? If so how?

      Would doing the same thing as an OCI based Data Cartridge run a LOT faster or hit the same bottlenecks?

      Is what we're doing a futile exercise?




        • 1. Re: Java stored procedure bridge to external system - performance

          It's hard to make suggestions without knowing more about exactly how the functions are used in queries, what the types matching the STRUCTs look like and how the instances of these types returned by the functions are used.

          Passing STRUCT or ARRAYs requires crossing the boundaries bw Java and C, which is expensive with Java in the database.

          I the if the type is some kind of aggregation of scalar types and the queries can be rewritten in terms of functions that return these scalar values individually things might be better.



          • 2. Re: Java stored procedure bridge to external system - performance

            Thanks for the response - you are correct that the STRUCT contained an aggregate type - basically an object with seven fields.


            This was very convenient because the function could be used in sql eg:








               some_names a,

               table(my_package.my_function(a.name)) b


               a.id in (some condition)


            I've done what you suggest creating arrays of primitive types but haven't really done any benchmarking yet.