Oracle PL/SQL NATIVE Compilation

You can speed up the execution of PL/SQL modules (packages, triggers, procedures, function, and types) by compiling them into native code residing in shared libraries. The procedures are translated into C code, then compiled with a C compiler and dynamically linked into the Oracle process.

You can use this technique with both the supplied Oracle packages, and procedures you write yourself. Procedures compiled this way work in all server environments, such as the shared server configuration (formerly known as multi-threaded server) and Oracle Real Application Clusters.

If you do not use native compilation, each PL/SQL program unit is compiled into an intermediate form, machine-readable code (MCode). The MCode is stored in the database dictionary and interpreted at run time.

With PL/SQL native compilation, the PL/SQL program is compiled into machine native code that bypasses all the runtime interpretation, giving faster runtime performance.

When stored PL/SQL was introduced in release 7.0, the mechanism was to compile the code to machine code executable within the PL/SQL virtual machine, in the same way that Java code is compiled to run in a Java Virtual Machine. Then at run time, the PL/SQL engine maps the virtual machine calls onto whatever calls are native to the processor on which the code is running. This means that the code is compiled to the same form no matter what your platform, and at run time converted to machine code suitable for your processor (which might be SPARC, x86_64, or something else). This mode of operation (which is the default) is known as "interpreted" execution.

In release 9.x, Oracle introduced the possibility of "native" execution. This means that the PL/SQL is pre-compiled into C, then compiled into machine code suitable for your processor, and then dynamically linked into the Oracle executable at run time. This has to result in faster execution: the task of interpreting the code for the processor is done once, in advance, not every time the code is invoked. In release 9.x, it was a bit awkward: you had to tell Oracle where your C compiler was, where the make file was that controls the process, and where to save the dynamic link libraries that get generated. 

In 11.x and 12.x the process is much simpler. Oracle provides its own C compiler and linker, and the executable code is stored in the data dictionary. So all you need do is tell Oracle to use native compilation. 

Here's an example of the performance difference:

orclz>
orclz> create or replace procedure p1 as
  2  n number;
  3  begin
  4  for i in 1..100000000 loop
  5  n:=n+1;
  6  end loop;
  7  end;
  8  /

Procedure created.

orclz> set timing on
orclz>
orclz> alter procedure p1 compile plsql_code_type=interpreted;

Procedure altered.

Elapsed: 00:00:00.04
orclz>
orclz> exec p1

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.15
orclz>
orclz> alter procedure p1 compile plsql_code_type=native;

Procedure altered.

Elapsed: 00:00:00.04
orclz>
orclz> exec p1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.45
orclz>
orclz>

The general advice must be to convert all your code, and Oracle supplied code, to native compilation. The technique:

1. Set the instance parameter PLSQL_CODE_TYPE=NATIVE in your spfile. That will take care of all new code. 
2. Set the compilation flag PLSQL_CODE_TYPE to NATIVE for all existing code, by running the supplied script $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql. You do have to startup in upgrade mode to do this.
3. Recompile all existing code with $ORACLE_HOME/rdbms/admin/utlrp.sql
4. Wait for your users to tell you "Wow! The database is really flying today!"
   

plsql_code_type parameter

Values:

INTERPRETED

PL/SQL library units will be compiled to PL/SQL bytecode format. Such modules are executed by the PL/SQL interpreter engine.

NATIVE

PL/SQL library units (with the possible exception of top-level anonymous PL/SQL blocks) will be compiled to native (machine) code. Such modules will be executed natively without incurring any interpreter overhead.

The parameter plsql_code_type determines whether PL/SQL code is natively compiled or interpreted. The default setting is INTERPRETED. To enable PL/SQL native compilation, set the value of plsql_code_type to NATIVE. If you compile the whole database as NATIVE, Oracle Corporation recommends that you set plsql_code_type at the system level or in the initialization parameter file.

Use the following syntax to set this parameter:

For native compilation mode:

alter session set plsql_code_type='NATIVE'

or,

alter system set plsql_code_type='NATIVE'

For interpreted mode:

alter session set plsql_code_type='INTERPRETED'

or,

alter system set plsql_code_type='INTERPRETED'

PL/SQL native compilation in 9i/10g

• Convert PL/SQL code to C , then compile using C compiler and dynamically link  into Oracle processes.
• Configure initialization parameters  PLSQL_CODE_TYPE,  PLSQL_NATIVE_LIBRARY_DIR and PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT. One more reason to hire Oracle DBA.

PL/SQL native compilation in 11g

• No  need of C compiler ; PL/SQL is compiled to machine code(DLL) and stored in the SYSTEM  tablespace instead of file system
• Just set init.ora parameter PLSQL_CODE_TYPE to NATIVE instead of default value of  INTERPRETED. All Other init.ora parameters have been deprecated

How to set it.

.

At Session Level

• At session level before creating the PL/SQL procedure

                          ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE

.

At Object Level

•   Recompile the stored procedure

……………….ALTER PACKAGE mypackage COMPILE PLSQL_CODE_TYPE = NATIVE.

.At Database Level

• Start the database in UPGRADE mode.
• Execute $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
• Shutdown immediate and recompile all objects.

Benefits?

• Improved performance for computation intensive code.
• PL/SQL procedures with SQL will not see any significant benefits
• Data type SIMPLE_INTEGER  provides  significant performance improvements  with native compilation  vs. interpreted.