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 Xanax Generic Viagra Buy Acyclovir Prozac Valium Buy Effexor Cialis Buy Xanax Codeine Buy Lipitor Buy Effexor Tenuate Buy Tramadol Buy Vicodin Phentermine Online Levitra Buy Adipex Buy Lipitor Buy Nexium Flexeril Buy Zyrtec Soma Flexeril Buy Line Xanax Buy Lexapro Lortab Zovirax Viagra Online Buy Flexeril Buy Darvocet Buy Diazepam Buy Adderall Darvocet Ambien Buy Cheap Phentermine Buy Xanax On Line Xanax Online Paxil Zocor Adipex Buy Lipitor Buy Valium Phentermine Buy Viagra Online Zocor Cipro Buy Norco Buy Phentermine Online Diazepam Online Xanax Buy Bontril Ambien Percocet Buy Propecia Buy Cheap Phentermine Buy Ephedrine Buy Biaxin Buy Nexium Buy Butalbital Lortab Buy Hydrocodone Online Buy Xanax Online Buy Line Xanax Oxycontin Buy Diazepam Bupropion Buy Zithromax Lipitor Buy Zyrtec Effexor Zocor Buy Didrex Buy Codeine Biaxin Buy Adipex Buy Tenuate Buy Paxil Xanax On Line Buy Alprazolam Buy Soma Buy Biaxin