[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.
From our Oracle Database documentation:
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.
What are the Performance Benefits?
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:
- Use the standard Oracle Database documentation, Oracle Database PL/SQL User’s Guide and Reference 10g Release 2 (10.2) (Part Number B14261). In particular, see the “Compiling PL/SQL Code for Native Execution” section in Chapter 11i, “Tuning PL/SQL Applications for Performance.”
- PL/SQL Native Compilation of Applications 11i on Oracle Database 10g Release 1 (10.1.0) (Metalink Note 312421.1)
- 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.