Sunday, February 27, 2011

Oracle: PL/SQL vs Java - one unconventional question

All the time since the introduction of Java engine within Oracle RDBMS people have been wondering how performance of Java stored procedures compare to orthodox ones written in PL/SQL. There are lots of performance tests demonstrating where things stand. Surprise! I am not going to discuss it here. What I am really curious about is what we may expect in future. Might it happen that one day PL/SQL will be fully replaced with the Java engine?
Indeed, they have lots in common:
  • both are portable virtual machines;
  • they can either interpret byte-code or compile it into native form;
  • they allow creating extensions (PL/SQL procedures/functions or Java classes) to the core database engine so that to process requirements of a specific application;
  • both languages are strictly typed;
  • both introduce rich sets of standard functions (and it does not seem to be a big problem to expose existing PL/SQL functionality through a set of Java classes if needed);
  • PL/SQL makes an attempt to structure code by introducing packages, types, objects, etc; same in Java though I reckon Java concepts are far more convenient and powerful (what I specifically like in Java is that a class combines both declaration and implementation in contrast to separated package declaration and package body in PL/SQL).
One obvious advantage of PL/SQL is that it allows mixing procedural statements with DML. But I am sure the same can be achieved with Java too.

One thing that probably compromises the whole idea is how optimizer processes a PL/SQL stored procedure. For example, if optimizer acquires some vital hints from the byte-code before actually running the procedure then I cannot see how similar functionality can be done in Java without special pre-processing. Another counterargument is the way each virtual machine controls memory. I doubt that PL/SQL memory management is similar to Java where garbage collection is one of the foundation concepts and quite versatile. It is quite likely that PL/SQL memory manager derives lots of hints from the byte-code and is very specialized therefore.

From the other point of view PL/SQL is such a heavy investment by Oracle that chances are very slim it will ever be written off.

Still I cherish a dream to be able to code logic with all power of Java rather than suffer from various limitations of PL/SQL. :-)

2 comments:

  1. For example, if optimizer acquires some vital
    hints from the byte-code before actually running the procedure then I cannot see how similar functionality can be done in Java without special pre-processing.

    It could be implemented quite simply with annotations/APT/bytecode instrumentation

    ReplyDelete
  2. to Den Orlov:
    That's exactly what I thought of when wrote "One obvious advantage of PL/SQL is that it allows mixing procedural statements with DML. But I am sure the same can be achieved with Java too." :-)
    Still I'm not quite sure whether optimizer really relies on any hints from the compiled form.

    ReplyDelete