In versions before Oracle9i Database Release 1, compilation of PL/SQL source code always resulted in a representation, usually referred to as bytecode or mcode, that is stored in the database and interpreted at runtime by a virtual machine implemented within Oracle. Oracle9i Database introduced a new approach: PL/SQL source code may optionally be compiled into native object code that is linked into Oracle. (Note, however, that an anonymous PL/SQL block is never compiled natively.)

When would this feature come in handy? How do you turn on native compilation? This section addresses these questions.

PL/SQL is often used as a thin wrapper for executing SQL statements, setting bind variables, and handling result sets. For these kinds of programs, the execution speed of the PL/SQL code is rarely an issue; it is the execution speed of the SQL that determines the performance. The efficiency of the context switch between the PL/SQL and the SQL operating environments might be a factor, but this is addressed very effectively by the FORALL and BULK COLLECT features introduced in Oracle8i Database and described in Chapter 14.

There are many other applications and programs, however, that rely on PL/SQL to perform computationally intensive tasks that are independent of the SQL engine. PL/SQL is, after all, a fully functional procedural language, and almost any real-world code is going to include a certain amount of “low-hanging fruit” that a modern compiler can chomp through, resulting in at least some increase in speed. You should realize, however, that the way that Oracle has chosen to implement the native compilation feature is not simply “translate your PL/SQL source into C and then compile it;” instead, Oracle always runs the normal PL/SQL compiler to generate mcode, and in native mode it takes this mcode itself as its input into the C translation process. This architecture has several consequences:

1. Generating natively compiled code is by “definition” slower than generating conventional code.
   
2. Any optimizations taken by the PL/SQL compiler will be applied regardless of compilation mode.
   
3. The generated C code is going to be incomprehensible to anyone other than a few rocket scientists who work at Oracle Corporation (normally, the C source code is automatically deleted).

The tasks expressed in C are primarily housekeeping tasks: setting up and destroying temporary variables; managing stack frames for subprogram invocation; and making calls to Oracle’s appropriate internal routines. Speedup from using C will be greatest in programs that spend more time processing the mcode relative to the time spent in Oracle’s internal routines. To be sure, that’s difficult or impossible for customers to predict, but there are even more factors in the speedup equation, including:

1. Oracle version in use. Later versions tend to exhibit more efficient runtime engines, which suppress the relative benefit of native compilation, although the total speedup will be greater.
   
2. Setting of PLSQL_OPTIMIZE_LEVEL (Oracle Database 10g). If you are using aggressive PL/SQL optimization, the relative speedup from native compilation will be lower.
   
3. Selection of datatypes. For example, a compute-intensive program that makes extensive use of the new IEEE floating-point types may also exhibit less relative speedup from native compilation.
   
4. Behavior and optimizing capabilities of the C compiler you are using, plus effects that may vary based on your particular hardware.
   
5. Degree of mixing native and interpreted code. Callouts between native and interpreted program units involve a context switch that homogeneous callouts can avoid.

Native compilation gives a broad range of speed increase, quoted by some sources as “up to 40%,” but even higher in certain unusual cases. Fortunately—and this is significant—I have never seen native compilation degrade runtime performance. That means the only thing you stand to lose with native compilation is speed of the compilation itself.

So how do you turn on this nifty feature? Read on...

Please check back next week for the continuation of this article.