Compiled PL/SQL   Donald K. Burleson

Of all of the exciting new features of Oracle9i, compiled PL/SQL is one of the most exciting developments.  Starting in Oracle9i, PL/SQL code can be compiled, and preliminary reports show that code can run as much as 8 times faster than interpreted PL/SQL

One of the features of the Oracle PL/SQL was that it was an interpreted language. In an interpretive language each line of code is individually parsed and executed before moving onto the next line of code.  While interpretation results in tremendous runtime flexibility to PL/SQL, the downside was that the information could not be packed into a tight executable module that could be run directly from the operating system.

This has tremendous advantages:

• Oracle code can now be hidden inside an executable.  Prior to Oracle9i, commercial developers creating PL/SQL snippets for sale had no choice but to allow their customers to see the actual source code for their PL/SQL.  Today, the Oracle developer can hide to the details of the PL/SQL by delivering the executable directly to their customer.

• Oracle code will run much faster is a compiled module.  Oracle estimates the marginal benefits of using compiled PL/SQL to be anywhere from 2 to 8 times faster than with traditional PL/SQL.

The best part of all is how remarkably easy it is to compile Oracle PL/SQL.  We start by setting a few initialization parameters.

The compiled code corresponding to a PL/SQL program is mapped to a PGA as opposed to SGA to allow better concurrent access. With native compilation, PL/SQL that does not contain SQL references can be 2-10 times faster

To turn native compilation on, you need to issue the following statement:

ALTER SESSION SET plsql_compiler_flags=NATIVE;

In Oracle9i, a PL/SQL library unit can be compiled as native C code, rather than interpreted as byte code. It is then stored as a shared library in the file system. The process of compiling a PL/SQL function or procedure is very simple:

ALTER FUNCTION my_func COMPILE;

Compilation results in faster execution of PL/SQL programs because of the elimination of the overhead associated with interpreting byte code and better control flow in native code than in interpreted code

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.