X

The Latest Oracle E-Business Suite Technology News direct from
Oracle E-Business Suite Development & Product Management

  • February 28, 2007

Enabling Native PL/SQL Compilation for Release 11i

Steven Chan
Senior Director

[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.

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. 

Native PL/SQL Compilation Benchmark:

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.

Join the discussion

Comments ( 9 )
  • Gareth Roberts Wednesday, February 28, 2007

    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


  • Tim Lycho Wednesday, February 28, 2007

    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 :-)


  • John Piwowar Thursday, March 1, 2007

    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?" :-)


  • Steven Chan Thursday, March 1, 2007

    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 


  • Giri Mandalika Tuesday, March 13, 2007

    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.


  • Steven Chan Tuesday, March 13, 2007

    Giri, this is not expected behaviour.  I'd recommend logging a Service Request with Metalink to have someone assist you with this.Regards,Steven


  • Rammohan Wednesday, March 21, 2007

    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.


  • Steven Chan Wednesday, March 21, 2007

    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


  • Steven Chan Sunday, March 25, 2007

    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


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.