Oracle 11g PLSQL Native Compilation

Machine code is sometimes called native code when referring to platform-dependent parts of language features or libraries.

Change parameter value,

SQL>alter system set plsql_code_type=native scope=spfile;

when use PLSQL_CODE_TYPE='NATIVE', arithmetic operations are done directly in the hardware which provides significantly better performance.

To compile a PL/SQL package to native code without setting plsql_code parameter,

ALTER PACKAGE COMPILE PLSQL_CODE_TYPE=NATIVE;

To compile a PL/SQL procedure to native code without setting plsql_code parameter,

ALTER PROCEDURE 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

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)