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;
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
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
Compilation 0.76 secs 0.3 secs 39.5% faster
Execution 54.1 secs 48.88 secs 10.7% faster
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.
create or replace procedure my_test is
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