Oracle compiled PL/SQL
Oracle Tips by Burleson Consulting
Compiling PL/SQL in Oracle
10g and beyond
As we noted, starting in Oracle 10g, PL/SQL
can be compiled to native code. The compiling, along with overall
improvements in PL/SQL compilation can mean speeding up PL/SQL
execution by up to 60%. On most Oracle systems, the compile process
invokes a special makefile utility that accepts the PL/SQL as input
and compiles it into native executable C code. As you may know, Oracle
is written in C, and this is the native language for Oracle
There is another benefit to compiling PL/SQL.
The compiled code that corresponds to the PL/SQL program is mapped to
a PGA as opposed to SGA to allow better concurrent access. With native
compilation, PL/SQL that does not contain SQL references can be 2-10
However, turning on native PL/SQL compilation
is not trivial. To turn native compilation on, you need to provide the
proper links inside a special file called a makfile. For Oracle 10g on
Windows, we see the make file in the c:\oracle\ora90\plsql\demo
directory as prcmake.bat. To get an idea of the complexity, here is a
listing of the prcmake.bat file:
Rem Use this
script to create executables for the demo scripts with PL/SQL
blocks embedded in C code. To create all executables, at the prompt
if (%MSVCDir%) == () goto msvcdir_error
if (%1) ==
() goto usage
echo This makefile assumes demos are installed as
sqlcheck=full CHAR_MAP=VARCHAR2, DBMS=V7 ireclen=132 parse=full
-I%MSVCDir%\include -I%MSVCDir%\mfc\include -D_MT -Zi %1.c /link
/libpath:%MSVCDir%\lib msvcrt.lib /nod:libcmt
echo Environment variable MSVCDIR must be set before
running this batch
echo Please run vcvars32.bat from MS
Visual Studio directory.
echo usage prcmake filename.pc [i.e. prcmake examp10]
Here we see that the prcmake accepts the name
of the PL/SQL function or procedure as an input argument. As you can
see, setting-up these makefiles can be quite complex, and even an
experienced Oracle database Administrator can take several days to get
the compiler set-up and working.
In any case, once you gat the makefile
working, you must issue the following Oracle commands to turn-on
native PL/SQL compilation.
pubs/pubs as sysdba;
alter system set
alter system set
alter system set
In Oracle 10g, a PL/SQL procedure or function
can be compiled as native C code, rather than interpreted as byte
code. It is then stored as a shared library in the file system. The
process of compiling a PL/SQL function or procedure is very simple:
function pubs.plus_tax compile;
Compilation results in faster execution of
PL/SQL programs because of the elimination of the overhead associated
with interpreting byte code, and faster control flow in native code
than in interpreted code.
details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
Use PL/SQL Native Compilation in 9i
The native compilation option in Oracle9i allows you to convert
internal stored packages and procedures into compiled shared C
libraries on the host server. This native compilation can reduce
code execution time by up to a factor of four. The packages and
procedures are compiled as native C routines and linked into the
Oracle code. This new option in Oracle9i is most effective for
computational intensive PL/SQL and shouldn?t be used for PL/SQL that
does a great deal of SQL execution.
To speed up one or more procedures using this technique follow
Update the supplied makefile and enter the appropriate paths and
other values for your system. The path of this makefile is:
Use the ALTER SYSTEM or alter session command, or update
your initialization file, to set the parameter PLSQL_COMPILER_FLAGS
to include the value NATIVE. The default setting includes the value
INTERPRETED, and you must remove this keyword from the parameter
Compile one or more procedures, using one of these methods:
Use the ALTER PROCEDURE or ALTER PACKAGE command to
recompile the procedure or the entire package.
Drop the procedure and create it again.
Use CREATE OR REPLACE to recompile the procedure.
Run one of the SQL*Plus scripts that sets up a set
of Oracle-supplied packages.
Create a database using a preconfigured
initialization file with PLSQL_COMPILER_FLAGS=NATIVE.
During database creation, the UTLIRP script is run
to compile all the Oracle-supplied packages.
To be sure that the process worked, you can query the data
dictionary to see that a procedure is compiled for native execution.
To check whether an existing procedure is compiled for native
execution or not, you can query the data dictionary views
USER_STORED_SETTINGS, DBA_STORED_SETTINGS, and ALL_STORED_SETTINGS.
For example, to check the status of the procedure MY_PROC, you could
SELECT param_value FROM user_stored_settings WHERE
param_name = 'PLSQL_COMPILER_FLAGS'
and object_name = 'MY_PROC';
The PARAM_VALUE column has a value of NATIVE for procedures that
are compiled for native execution, and INTERPRETED otherwise.
After the procedures are compiled and turned into shared libraries,
they are automatically linked into the Oracle process. You do not
need to restart the database, or move the shared libraries to a
different location. You can call back and forth between stored
procedures, whether they are all compiled in the default way
(interpreted), all compiled for native execution, or a mixture of
Because the PLSQL_COMPILER_FLAGS setting is stored inside the
library unit for each procedure, procedures compiled for native
execution are compiled the same way when the procedure is recompiled
automatically after being invalidated, such as when a table that it
depends on is recreated.
You can control the behavior of PL/SQL native compilation through
the ALTER SYSTEM or alter session commands or by setting or
changing these parameters in the initialization file:
PLSQL_NATIVE_LIBRARY_DIR (cannot be set by alter
session for security reasons)
An example of compiling a PL/SQL Procedure for Native Execution
is shown in Figure 3.
set serveroutput on;
alter session set plsql_native_library_dir='/home/orauser/lib';
alter session set plsql_native_make_utility='gmake';
alter session set plsql_native_make_file_name='/home/orauser/spnc_makefile.mk';
alter session set plsql_compiler_flags='NATIVE';
create or replace procedure hello_native_compilation
select sysdate from dual;
Figure 3: Example use of Native Compilation
As the procedure is compiled, you see the various compilation and
link commands being executed. The procedure is immediately available
to call, and runs as a shared library directly within the Oracle
Limitations of Native Compilation
When a package specification is compiled for native execution,
the corresponding body should be compiled using the same settings.
The Oracle provided debugging tools for PL/SQL do not handle
procedures compiled for native execution.
When many procedures and packages (typically, over 5000) are
compiled for native execution, having a large number of shared
objects in a single directory might affect system performance. In
this case, you can have the DBA set the initialization parameter
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT in the initialization file before
creating the database or compiling the PL/SQL packages or
procedures. Set this parameter to a value that makes sense for your
environment and operating system, Oracle suggests 1000 but this
seems like overkill to me. Once the parameter is set and the DB
restarted, create subdirectories underneath the directory specified
in the PLSQL_NATIVE_LIBRARY_DIR parameter. The subdirectories must
be named d0, d1, d2 ... d999, up to the value specified for the
subdirectory count. When the procedures are compiled for native
execution, the DLLs will be automatically distributed among these
subdirectories by the PL/SQL compiler.
Also see this research on compiling PL/SQL:
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.