Enabling Native PL/SQL Compilation for Release 11i

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

Comments:

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 on February 28, 2007 at 05:19 AM PST #

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 on February 28, 2007 at 11:20 AM PST #

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 on February 28, 2007 at 11:36 PM PST #

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 on March 01, 2007 at 03:37 AM PST #

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 on March 12, 2007 at 06:22 PM PDT #

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 on March 13, 2007 at 01:47 AM PDT #

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 on March 21, 2007 at 09:02 AM PDT #

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 on March 21, 2007 at 10:16 AM PDT #

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 on March 25, 2007 at 03:16 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today