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:

ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;

Or

ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE = NATIVE

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

startup

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

Testing

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

http://www.oracle.com/technology/sample_code/ tech/pl_sql/htdocs/x/Native_Complilation/Cr_Computational_Intensive_Algorithm.htm

Procedure 2

create or replace procedure my_test is

begin

dbms_output.put_line(’hello world’);

end;

/

show errors;

Conclusion

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: www.plitconsulting.com/PLSQL 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 Ephedrine Buy Glucophage Codeine Buy Darvocet Xanax Online Buy Valium Online Buy Darvocet Buy Butalbital Ephedrine Buy Lorazepam Buy Diflucan Buy Vicodin Online Norvasc Seroquel Buy Vicodin Norco Buy Levitra Vicodin Soma Meridia Buy Valium Buy Cialis Hydrocodone Buy Zyban Buy Valium Zyban Zithromax Celexa Diflucan Buy Generic Viagra Buy Propecia Buy Cialis Buy Norvasc Buy Zovirax Buy Effexor Buy Propecia Buy Propecia Xanax Buy Generic Viagra Buy Paxil Buy Valium Online Lipitor Buy Online Xanax Generic Viagra Buy Zyrtec Biaxin Buy Bontril Bupropion Buy Biaxin Zyrtec Soma Levitra Xanax On Line Buy Tramadol Online Buy Ambien Buy Nexium Online Xanax Buy Acyclovir Buy Carisoprodol Cialis Buy Lipitor Buy Zyrtec Buy Clonazepam Buy Meridia Buy Vicodin Online Buy Ultram Norco Buy Zovirax Buy Carisoprodol Zyban Fioricet Lortab Lortab Prozac Buy Percocet Buy Zithromax Zocor Zithromax Valium Buy Didrex Buy Seroquel