PL/SQL native compilation is not something that is real new but something that became real now in Oracle 11g.
How does Oracle world look without PL/SQL native compilation
- PL/SQL is compiled to intermediate form “mcode” or machine readable code.
- mcode is stored in database and interpreted at run time.
- This is the default mode set via init.ora parameter
PL/SQL native compilation in 9i/10g
- Convert PL/SQL code to C , then compile using C compiler and dynamically link into Oracle processes.
- Configure initialization parameters PLSQL_CODE_TYPE, PLSQL_NATIVE_LIBRARY_DIR and PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT. One more reason to hire Oracle DBA.
PL/SQL native compilation in 11g
- No need of C compiler ; PL/SQL is compiled to machine code(DLL) and stored in the SYSTEM tablespace instead of file system
- Just set init.ora parameter PLSQL_CODE_TYPE to NATIVE instead of default value of INTERPRETED. All Other init.ora parameters have been deprecated
How to set it
At Session Level
- At session level before creating the PL/SQL procedure
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE
At Object Level
- Recompile the stored procedure
……………….ALTER PACKAGE mypackage COMPILE PLSQL_CODE_TYPE = NATIVE.
At Database Level
- Start the database in UPGRADE mode.
- Execute $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
- Shutdown immediate and recompile all objects.
- Improved performance for computation intensive code.
- PL/SQL procedures with SQL will not see any significant benefits
- Data type SIMPLE_INTEGER provides significant performance improvements with native compilation vs. interpreted.