Interpreted Versus Native Compilation Mode - Oracle

After your administrator sets everything up, you are ready to go native. The first thing to do is set the compiler parameter. A user may set it as described here.

In Oracle9i Database, specify:

  ALTER SESSION
    SET plsql_compiler_flags = 'NATIVE';  /* vs. 'INTERPRETED' */

Starting with Oracle Database 10g Release 1, the PLSQL_COMPILER_FLAGS parameter is deprecated, so you should use this instead:

  ALTER SESSION
     SET plsql_code_type = 'NATIVE';   /* vs. 'INTERPRETED' */

The compilation mode will then be set for subsequently compiled PL/SQL library units during that session, as long as they are compiled by one of the following:

1. A script or explicit CREATE [OR REPLACE] command
   
2. An ALTER... COMPILE statement
   
3. The DBMS_UTILITY.COMPILE_SCHEMA packaged procedure

In addition, the DBA can change the mode on a system-wide basis using ALTER SYSTEM.

Oracle stores the compilation mode with the library unit’s metadata so that if the program is implicitly recompiled as a consequence of dependency checking, the last mode used will be used again. Note that this “stickiness” applies only to automatic recompilations; other rebuilds or recompiles will use the session’s current setting. You can determine the saved compilation mode for your stored programs by
querying the data dictionary using the statement shown here (for Oracle Database 10g):

  SELECT name, type, plsql_code_type
    FROM USER_PLSQL_OBJECT_SETTINGS
    ORDER BY name;

The result will show something like this:  

In Oracle9i Database, the WHERE clause would instead look for PLSQL_COMPILER_FLAGS, and you would get additional information about whether the unit has been compiled with debug mode.

Incidentally, PL/SQL debuggers will not work with natively compiled programs. This is one of the only downsides to native compilation, but in most cases you could work around it by using interpreted mode during development, and native mode in testing and production.

Oracle recommends that all of the PL/SQL library units called from a given top-level unit be compiled in the same mode (see the sidebar “Converting an Entire Database to Native (or Interpreted)”). That’s because there is a cost for the context switch when a library unit compiled in one mode invokes one compiled in the other mode. Significantly, this recommendation includes the Oracle-supplied library units. These are always shipped compiled in interpreted mode because they may need to get recompiled during subsequent upgrades, and Oracle cannot assume that you have installed a supported C compiler.

Our conclusion? If your application contains a significant amount of compute-intensive logic, consider switching your entire database—including Oracle’s supplied library units—to use native compilation. Making such a change is likely to offer the most dramatic performance improvements for applications that are unable to take advantage of the optimizing compiler introduced in Oracle Database 10g.

Converting an Entire Database to Native (or Interpreted)

The simplest way to follow Oracle’s recommendation that all PL/SQL library units called from a given top-level unit be compiled in the same mode is to convert the whole database so that all PL/SQL library units are compiled native, and to set the system-wide parameter PLSQL_COMPILER_FLAGS (Oracle9i Database) or PLSQL_CODE_TYPE (Oracle Database 10g) to NATIVE.

While this is not technically difficult to do, it can be very time-consuming if you have a large number of modules. There are a number of nonobvious steps to the process; we suggest following Oracle’s explicit instructions closely.

1. For Oracle9i Database: http://otn.oracle.com/tech/pl_sql/htdocs/README_ 2188517.htm
   
2. For Oracle Database 10g: http://www.oracle.com/technology/tech/pl_sql/ htdocs/ncomp_faq.html#ncomping_db

The latter link is actually part of a larger FAQ that contains a wealth of useful information on native compilation.