11g PL/SQL native compilation

Oracle July 29, 2011

.

.
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  PLSQL_CODE_TYPE=INTERPRETED
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

At Database Level
  • Start the database in UPGRADE mode.
  • Execute $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
  • Shutdown immediate and recompile all objects.
Benefits?
  • 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.

Tagged: , , , , , , , ,