[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.
Comments (9)
Hi Steven,
This performance increase is worthwhile.
However, customers using this, especially Apps DBAs need to bear in mind the additional time, space overheads and related maintenance impact.
- What timeframe does it take to compile the whole R12 PL/SQL code base natively?
- What are the disk space requirements for compiling the whole R12 PL/SQL code base?
Any customers considering this option would need to consider the following:
1. Increased time to apply patches
2. Increased space requirements
3. If customer chooses to natively compile only a subset of packages, increased awareness is required as any PL/SQL packages compiled natively must have both the package header and package body compiled in the same way to avoid e.g PLS-00724, errors.
Gareth
Posted by Gareth Roberts | February 28, 2007 1:19 PM
Posted on February 28, 2007 13:19
Hi Steven,
I second Gareth's comments regarding the bigger picture. The performance benefits really do need to be weighed up with consideration to the longer time (disk space less so) required to natively compile the PL/SQL.
My tests with native compilation using GCC resulted in compilation times for all 70k packages in 11i taking over 24 hours to compile on an 8 cpu Sun E2900. Considering ATG rollups always seem to invalidate nearly every package, that would result in unacceptably long patching outages to our business.
Perhaps native compilation will be feasible to us with database 11g which apparently will include an Oracle-supplied compiler that's claimed to compile code twice as fast... but until then we'll be sticking with interpreted PL/SQL.
Tim.
PS: Great blog, Steven :-)
Posted by Tim Lycho | February 28, 2007 7:20 PM
Posted on February 28, 2007 19:20
I concur with Gareth and Tim. I won't claim to have done a rigorous analysis :-), but given the relatively high rate of change and low concurrency and data volumes in our current environment, the native PL/SQL option certainly evaluated to "not worth it" when I investigated it in our test systems.
I may change my tune as our hardware ages and we need to squeeze the last dregs of performance out of it, but for now we can't afford the additional maintenance time during patch cycles, and while the increased disk footprint isn't huge, it's non-trivial enough that my systems/storage admin colleagues would still ask, "*now* what do you need more storage for?" :-)
Posted by John Piwowar | March 1, 2007 7:36 AM
Posted on March 1, 2007 07:36
Tim, Gareth, John,Excellent points, and well taken. Native compilation has benefits, but there are costs as well. Our Applications Performance Group's testing indicates that recompiling with native compilation turned on takes approximately 3 to 4 times longer than compiling interpreted units. It also requires approximately 2 GB more disk space.And responding to Gareth's last point, here's a direct response from Ahmed Alomari, in our Applications Performance Group: "In 10g, we do allow the spec to be compiled in interpreted mode and the body in native
mode. However, since specs do not tend to have a lot of code, the benefit in
terms of time and/or space is trivial. "Regards,Steven
Posted by Steven Chan | March 1, 2007 11:37 AM
Posted on March 1, 2007 11:37
Although the idea of native PL/SQL compilation sounds exciting, I believe it needs more work from Oracle corporation. It sure is buggy. Proof? Run Oracle Applications 11.5.10 benchmark kit with and without native PL/SQL compilation -- when all PL/SQL packages are natively compiled, some components of the benchmark kit doesn't work anymore.
Posted by Giri Mandalika | March 13, 2007 1:22 AM
Posted on March 13, 2007 01:22
Giri, this is not expected behaviour. I'd recommend logging a Service Request with Metalink to have someone assist you with this.Regards,Steven
Posted by Steven Chan | March 13, 2007 8:47 AM
Posted on March 13, 2007 08:47
We plan to uptake NCOMP into our 11i instance.
We are not sure of the following:
1. Will RMAN take care of the compiled code on filesystem. Do we have to back it up seperately to ensure we do not have to recompile in teh event of a restore.
2. During cloning must we copy the NCOMP directories on filesystem? Or do we havet o recreate them on cloned env by recompiling.
On our test env it took 8 Hrs to compile for the first time.
This is very critical factor to us coz, we have rigid time constraints for clones. Are the copiled object files binary compatible ?
3. How does it fit into an existing Standby/Datagaurd environment.
Posted by Rammohan | March 21, 2007 4:02 PM
Posted on March 21, 2007 16:02
Ram,I've passed these questions on to our architects for this area. I'll post their replies as soon as I receive them.Regards,Steven
Posted by Steven Chan | March 21, 2007 5:16 PM
Posted on March 21, 2007 17:16
From Ahmed Alomari:<snip>Mohan, rman will not back up the native compilation files, and you will need to
back them up separately. In 10g, the ncomp DLL is stored in the DB, so you
don't need to backup the file system separately. For cloning, you need
to recreate (i.e. recompile) the native PL/SQL units if using 9iR2 as the exact
path and some other metdata is stored in the library cache object. In 10g, the
DLL is stored in the DB, so you just need to clone the DB. -- Ahmed
Posted by Steven Chan | March 25, 2007 10:16 PM
Posted on March 25, 2007 22:16