Oracle Performance and Engineering

I remember the upgrade from 8i to 9i we did in Summer, 2002. That - for our application - was a straight forward 30% to 35% performance improvement over the baseline PL/SQL. Quite amazing.

Oracle 9i to 10g offers PL/SQL performance improvements on two counts.

(a) General PL/SQL compiler enhancements - I (on my test app) found this to be around 12% to 15% faster on most program units compared to PL/SQL 9i.

(b) Using "NATIVE" compiler for PL/SQL. This gives about 200% to 300% performance improvement of PL/SQL. NATIVE PL/SQL is "interpreted" by the hardware instruction set directly. Conventional PL/SQL, just like other programming languages, is "interpreted" by a software called "virtual machine". In other words, NATIVE means faster :)

Basically, PL/SQL program units will be stored as compiled C files on the directory mentioned by you on the Oracle database server. Two initialization parameters are to be changed.

-- Change plsql_native_library_dir to a directory where the PL/SQL program units will live in a C-compiled form. Remember, since it's stored externally - your backup procedures should include this directory into account. I have just created a sub-directory "plsql" on my data file folder and used that as the library directory. That whole directory is anyway backed up and thus, I did not have to effect any change.

-- PLSQL_CODE_TYPE should be changed to NATIVE.

That's it! Now your PL/SQL programs are ready to glide. SQL embedded within PL/SQL won't be impacted much. But pure Pl/SQL as in LOOP iterations, variable assignment, multiset or collection iterations etc are much more faster (Oracle claims 300% or more and my tests show that they are not at all exaggerating!).

Remember, after you turn "NATIVE" - you'd have to recompile your PL/SQL program units otherwise they would still be living as "INTERPRETED" form in database.

There's an excellent white paper from Oracle talking about these changes. This paper was first presented on Oracle Openworld 2003 (it may be noted that production version of 10g shipped much*after* OOW 2003). I really liked this paper. Specially -

"When PL/SQL is a carrier for SQL statements, the performance of the PL/SQL portion of the combination is generally not very important; execution of even the simplest interesting SQL operation takes much more time than does the typical PL/SQL statement. When PL/SQL is used as a conventional programming language, then its performance is important, just as is that of any other programming language."

"The PL/SQL compiler uses Diana (for Descriptive Intermediate Attributed Notation for Ada) for its internal representation, reflecting PL/SQL’s historical debt to the Ada language."

[So far, I've always wondered what DIANA really was!]

-- "Obviously the performance characteristics of a PL/SQL unit are influenced by how the author chooses and expresses the algorithm in PL/SQL. Without a psychic subsystem in the front-end, the compiler can do nothing to determine the programmer’s ultimate high-level intent!...

... the best way to improve PL/SQL program performance is to choose the right algorithms and data structures in the first place!"

[I just cannot say how much I *LOVED* this sentence. This, in essence, describes the whole performance improvement process.]

"PL/SQL programmers who use suchnew features appropriately will be rewarded with faster programs."

[Cannot agree more. Things like Bulk Binding, Bulk Collect, FORALL, associative arrays index by varchar2 etc - can literally have dramatic effect on performance.]

"The relative potential for improvement in program performance due to changes in the code generator or in the PVM can be understood by analogy with what determines the speed of a car journey. The choice of route (as the crow flies or hugely circuitous) is analogous to the code generation phase. And the speed at which you drive is analogous to the expressiveness and the efficiency of the PVM. Famously, choosing the optimal route, even when you drive conservatively in a modest car, gets you there much quicker than choosing a poor route and driving flat out in a sports car. Of course, driving a sports car flat out on the optimal route is quickest of all."

[This paragraph should be read and re-read and re-read and then be remembered whenever you're coding! The "expert" guy - who's a PL/SQL performance hero - has severe limitation when he/she goes to tune. You as a programmer has no such limitation.]

"Oracle10g removes this confusion by redefining binary_integer to be identical to pls_integer. This has both a usability benefit and an immediate performance benefit for extant programs that accidentally or unavoidably use binary_integer. The effect is to speed up integer arithmetic in PL/SQL by a factor of 2.5 to 3."

[In my 'best practice lecture' - using PLS_INTEGER replacing the NUMBER datatype has always come up very high in importance. With Oracle's commitment to maintain PLS_INTEGER I feel even safer to use it just everywhere!]


Trying the given FIB_N function to compute fibonacci number - (the code is wrong there - ELSEIF does not exist in PL/SQL) -

create or replace function fib_n(n in pls_integer) return pls_integer
is
begin
if n=1 then return 1;
elsif n=2 then return 1;
else return fib_n(n-1) + fib_n(n-2);
end if;
end ;
/

fib_n(50) took -

on a 10g database with Native compilation - 0.05 sec
on a 9i database with interpreted compilation - 0.35 sec

Both ran on the same phyical server with similar configurations. Oracle10g is about 700% faster in this case!

10g has another parameter to "pre-tune" PL/SQL called PLSQL_OPTIMIZE_LEVEL - which is set to 2 by default (was 0 in 9i).

Next to review is an OTN paper (supposed to be companion to this one) called "“Freedom, Order, and PL/SQL Optimization”.