Home arrow Oracle arrow Page 4 - Troubleshooting PL/SQL Code

Using Native Compilation - Oracle

In this second part of a nine-part series on managing PL/SQL code, you'll learn how to analyze argument information, recompile invalid code, and more. This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Troubleshooting PL/SQL Code
  2. Analyze and modify trigger state through views
  3. Recompiling Invalid Code
  4. Using Native Compilation
By: O'Reilly Media
Rating: starstarstarstarstar / 8
October 25, 2007

print this article



In versions before Oracle9i Database Release 1, compilation of PL/SQL source code always resulted in a representation, usually referred to as bytecode or mcode, that is stored in the database and interpreted at runtime by a virtual machine implemented within Oracle. Oracle9i Database introduced a new approach: PL/SQL source code may optionally be compiled into native object code that is linked into Oracle. (Note, however, that an anonymous PL/SQL block is never compiled natively.)

When would this feature come in handy? How do you turn on native compilation? This section addresses these questions.

PL/SQL is often used as a thin wrapper for executing SQL statements, setting bind variables, and handling result sets. For these kinds of programs, the execution speed of the PL/SQL code is rarely an issue; it is the execution speed of the SQL that determines the performance. The efficiency of the context switch between the PL/SQL and the SQL operating environments might be a factor, but this is addressed very effectively by the FORALL and BULK COLLECT features introduced in Oracle8i Database and described in Chapter 14.

There are many other applications and programs, however, that rely on PL/SQL to perform computationally intensive tasks that are independent of the SQL engine. PL/SQL is, after all, a fully functional procedural language, and almost any real-world code is going to include a certain amount of “low-hanging fruit” that a modern compiler can chomp through, resulting in at least some increase in speed. You should realize, however, that the way that Oracle has chosen to implement the native compilation feature is not simply “translate your PL/SQL source into C and then compile it;” instead, Oracle always runs the normal PL/SQL compiler to generate mcode, and in native mode it takes this mcode itself as its input into the C translation process. This architecture has several consequences:

  1. Generating natively compiled code is by “definition” slower than generating conventional code.
  2. Any optimizations taken by the PL/SQL compiler will be applied regardless of compilation mode.
  3. The generated C code is going to be incomprehensible to anyone other than a few rocket scientists who work at Oracle Corporation (normally, the C source code is automatically deleted).

The tasks expressed in C are primarily housekeeping tasks: setting up and destroying temporary variables; managing stack frames for subprogram invocation; and making calls to Oracle’s appropriate internal routines. Speedup from using C will be greatest in programs that spend more time processing the mcode relative to the time spent in Oracle’s internal routines. To be sure, that’s difficult or impossible for customers to predict, but there are even more factors in the speedup equation, including:

  1. Oracle version in use. Later versions tend to exhibit more efficient runtime engines, which suppress the relative benefit of native compilation, although the total speedup will be greater.
  2. Setting of PLSQL_OPTIMIZE_LEVEL (Oracle Database 10g). If you are using aggressive PL/SQL optimization, the relative speedup from native compilation will be lower.
  3. Selection of datatypes. For example, a compute-intensive program that makes extensive use of the new IEEE floating-point types may also exhibit less relative speedup from native compilation.
  4. Behavior and optimizing capabilities of the C compiler you are using, plus effects that may vary based on your particular hardware.
  5. Degree of mixing native and interpreted code. Callouts between native and interpreted program units involve a context switch that homogeneous callouts can avoid.

Native compilation gives a broad range of speed increase, quoted by some sources as “up to 40%,” but even higher in certain unusual cases. Fortunately—and this is significant—I have never seen native compilation degrade runtime performance. That means the only thing you stand to lose with native compilation is speed of the compilation itself.

So how do you turn on this nifty feature? Read on...

Please check back next week for the continuation of this article.

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: