Native Compilation of PL/SQL

Beginning with Oracle9i, you can speed up many of your PL/SQL programs by compiling the stored programs natively. Oracle will translate your PL/SQL program into C code and compile it into a shared library (a DLL on Windows). You must have a supported C compiler on your database server machine to support native compilation.

We’ve summarized the steps needed to compile your PL/SQL program natively, but refer to the Oracle documentation for details:

  1. Edit the makefile spnc_makefile.mk, which should be in the $ORACLE HOME/plsql subdirectory.

  2. Set the initialization parameter PLSQL_COMPILER_FLAGS = `NATIVE’. You may also need to set these additional parameters (see Chapter 2 for information):

    PLSQL_NATIVE_C_COMPILER
    PLSQL_NATIVE_LINKER
    PLSQL_NATIVE LIBRARY_DIR
    PLSQL_NATIVE_MAKE_UTILITY
    PLSQL_NATIVE_MAKE_FILE_NAME

    You can set these in the SPFILE/INIT.ORA file or with an ALTER SYSTEM statement.

  3. Create or replace your stored programs.

  4. Verify the native compilation by querying the data dictionary view USER_STORED_SETTINGS and also by locating the shared library or DLL in the database server’s filesystem.

Get Oracle in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial