PLSQL Native Compilation

In 11g, the new spfile.ora parameter PLSQL_CODE_TYPE decides the compilation mode for PL/SQL blocks. Only this parameter needs to be set to compile PL/SQL into native code.

There are 2 options for PLSQL_CODE_TYPE:

1) Interpreted - Using this mode, the PL/SQL statements in a PL/SQL unit are compiled into an intermediate form, system code, which is stored in the database dictionary and interpreted at run time.

2) Native -Using this mode, the native code will not be interpreted at runtime. Hence it executes faster. The default value for PLSQL_CODE_TYPE is set to interpreted.

Native compilation can be used with Oracle supplied packages / procedures / triggers and custom code.

The DBA_PLSQL_OBJECT_SETTINGS view includes the current PLSQL_CODE_TYPE column for each PL/SQL object which states whether an object has been compiled natively or interpreted.

Oracle recommends having an optimization level (set by PLSQL_OPTIMIZE_LEVEL) of at least 2. If the optimisation level is less than 2, the compiler generates interpreted code, regardless of the PLSQL_CODE_TYPE. If you specify NATIVE, the compiler warns you that NATIVE was ignored.

Note that in 11g, the parameters PLSQL_NATIVE_LIBRARY_DIR and PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT have no effect and are not required, as natively compiled code is now stored in the database, and not in a file system.

Natively compiled subprograms and interpreted subprograms can call each other.

Using Native Compilation

You can perform PL/SQL native compilation by setting a session parameter before creating or recompiling stored code:




Procedure to convert the entire database and recompile all PL/SQL modules into native mode

1) Shut down database

2) Edit spfile.ora and set PLSQL_CODE_TYPE =native and plsql_optimise_level=2 (at least)

3) connect sys/password as sysdba

startup upgrade

4) @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql (which updates the execution mode of all PL/SQL modules to native) (You can use the TRUE command line parameter with the dbmsupgnv.sql script to exclude package specs from recompilation to NATIVE, saving time in the conversion process.)

5) shutdown immediate


@$ORACLE_HOME/rdbms/admin/utlrp.sql (to recompile all invalid objects)


I choose 2 PL/SQL procedures, one being a computation-intense procedure, and the other which is a very simple procedure (see below).

Although Oracle states that compilation time could be slower and execution times might be faster for native compilation, my results indicated that there is a consistent improvement both compilation and execution.

Interpreted code Native code Improvement

Procedure 1

Compilation 0.76 secs 0.3 secs 39.5% faster

Execution 54.1 secs 48.88 secs 10.7% faster

Procedure 2

Compilation 0.1 secs 0.03 secs 30% faster

Execution 0.02 secs 0.01 secs 50% faster

Converting the whole database to Native code:

Although it is very simple to convert a database, a restart is required and recompilation of invalid objects after running the convert script is required.

It is necessary to check how many invalid objects are before the change, as with the conversion all invalid objects would need to be recompiled.

In the database I used to test it, there were about 50000 invalid objects after native conversion.

To recompile the invalid objects took 5 hours.

Procedure 1 tech/pl_sql/htdocs/x/Native_Complilation/Cr_Computational_Intensive_Algorithm.htm

Procedure 2

create or replace procedure my_test is


dbms_output.put_line(’hello world’);



show errors;


Oracle has certified the use of compiled (native) PL/SQL code in Oracle Application Release 11i environments.

While Oracle states that compilation times may be slower for native PL/SQL code, my initial tests have shown better compilation times than interpreted PL/SQL. The improvement in runtime performance ranges from 10 up to 50% in some cases.

More large scale benchmark testing is required in this area as well as testing large patches in 11i.

For Full document: Native Compilation.pdf

One Response to “PLSQL Native Compilation”

  1. Blogroll Report 03/07/2009 10/07/2006 « Coskan’s Approach to Oracle Says:

    […] Pat Lehane – PL/SQL Native Compilation […]

Leave a Reply

You must be logged in to post a comment.

Buy Paxil Clonazepam Buy Bupropion Buy Didrex Buy Didrex Tenuate Generic Viagra Buy Alprazolam Zyprexa Buy Zithromax Buy Diflucan Valium Online Acyclovir Buy Nexium Percocet Buy Levitra Buy Effexor Buy Zyban Levitra Adderall Seroquel Buy Zocor Buy Vicodin Online Diflucan Buy Zyrtec Phentermine Buy Celexa Ambien Buy Valium Buy Norco Biaxin Buy Acyclovir Buy Soma Buy Valium Online Buy Norco Oxycontin Buy Lorazepam Meridia Zyprexa Buy Cialis Online Buy Ephedrine Buy Valium Online Phentermine Online Phentermine Lipitor Buy Zyprexa Buy Propecia Buy Tramadol Buy Soma Cialis Buy Vicodin Buy Tramadol Generic Viagra Codeine Darvocet Flexeril Buy Oxycontin Buy Cialis Buy Hydrocodone Online Xanax On Line Buy Carisoprodol Zocor Butalbital Propecia Buy Ativan Buy Hydrocodone Online Flexeril Buy Seroquel Buy Butalbital Buy Tramadol Bupropion Buy Lorazepam Diflucan Buy Lipitor Tramadol Online Buy Zovirax Buy Glucophage Ultracet Buy Codeine Buy Codeine Buy Zithromax