[Editor Mar 1, 2007 update: A few of our readers have posted several interesting comments about their own experiences with native compilation - worthwhile reading if you're considering using this feature. In response to their comments, I've revised this article to include a mention of some of the costs associated with native compilation.]
A significant part of Oracle E-Business Suite Release 11i is built on PL/SQL. Ordinarily, this code is compiled and stored in an intermediate form in the Oracle database's data dictionary and interpreted at run-time. This is the default mode delivered as part of the Apps Rapid Install process. It's possible to squeeze a bit more performance out of your existing hardware by enabling native PL/SQL compilation in your Apps 11i environment.
What's Native PL/SQL Compilation?
From our Oracle Database documentation:
You can speed up PL/SQL procedures by compiling them into native code residing in shared libraries. The procedures are translated into C code, then compiled with your usual C compiler and linked into the Oracle process.What are the Performance Benefits?
You can use native compilation with both the supplied Oracle packages, and procedures you write yourself. Procedures compiled this way work in all server environments [including] ... Oracle Real Application Clusters.
PL/SQL native compilation provides the greatest performance gains for computation-intensive procedural operations... it is most effective for compute-intensive PL/SQL procedures that do not spend most of their time executing SQL.
The actual improvement in performance benefit is highly-dependent upon the specific application modules that you use, as well a number of other factors. I was reviewing an older (unpublished) benchmarking analysis comparing interpreted vs. native compilation for the older 9iR2 + 11.5.7 database, running on a small 4 CPU 296 MHz server with 4 GB of RAM with Solaris 2.6.
In this environment, performance was improved by ~32% for the Order Import process. At the other end of the range, performance was improved by ~ 5% for the Payroll process, which consists mainly of a PRO*C client making calls to PL/SQL service-side packages.Nothing Comes for Free
In the immortal words of Heinlein, "there ain't no such thing as a free lunch
." Unsurprisingly, the benefits of native compilation come with their own costs, too. For example, the compilation time of native units for Release 11i takes approximately 3 to 4 times longer than interpreted units. Also, turning on native compilation requires approximately 2 GB more disk space.
Customers with tight constraints on downtimes or storage may find these costs to be prohibitive. If you're considering this configuration, it'd be worthwhile doing some careful before-and-after benchmarks of your key business processes to assess the real performance improvements of native compilation in your own environment.Resources for Enabling Native PL/SQL in Apps 11i
For the 10gR2 Database:
For the 10gR1 Database:
For the 9iR2 Database and earlier:
- Native compilation isn't certified or supported for these database releases. If you want to use native compilation, this may be a good justification for a database upgrade for your environment.