Keywords: COMPILATION; NATIVE; PERFORMANCE; PL/SQL;
Products: 11/PLSQL;
Platforms: GENERIC;

PURPOSE
-------

The purpose of this bulletin is to describe native compilation of
PL/SQL program units, including configuration and limitations.

SCOPE & APPLICATION
-------------------

This bulletin is intended to consolidate information available from
a number of other sources as well as correct some errors in current
versions of the official Oracle9i documentation. This bulletin also
covers some rather subtle points that are not discussed explicitly
in the documentation. It does not provide a tutorial on PL/SQL in
general; an understanding of the PL/SQL language is assumed.

NATIVE COMPILATION OF PL/SQL STORED PROGRAM UNITS IN ORACLE9i
-------------------------------------------------------------

Introduction
------------

In versions prior to Oracle9i, all PL/SQL stored program units are
compiled to P-code. The resulting P-code is stored in the database
and interpreted at runtime. While this results in a very portable
solution, it does have some performance consequences; interpreted
languages are usually slower than natively compiled languages.

As a performance enhancement, Oracle9i provides the capability to
natively compile PL/SQL code into shared libraries. These shared
libraries are then loaded and executed whenever the corresponding
PL/SQL program unit is invoked at runtime.

Native compilation of PL/SQL, once it has been properly configured,
is transparent to both the user compiling a PL/SQL program unit and
to the end user invoking that same PL/SQL program unit.

Oracle9i generates C code corresponding to the PL/SQL code and then
compiles and links the resulting C code into shared libraries using
the compiler and linker for that platform. Therefore, a C compiler
must be installed on the same host for native compilation to work.

The next section discusses the initialization parameters that are
used to configure PL/SQL native compilation. Subsequent sections
discuss other considerations when using native compilation.

Initialization Parameters Related to PL/SQL Native Compilation
--------------------------------------------------------------

plsql_compiler_flags

Determines whether PL/SQL code is compiled native or interpreted
and determines whether debug information is included.

This parameter is settable at both the system and session levels.

Possible values are: 'INTERPRETED' - compile in interpreted mode
'NATIVE' - compile in native mode
'DEBUG' - include debug information
'NON_DEBUG' - no debug information

Defaults are: 'INTERPRETED','NON_DEBUG'

Examples:

alter session set plsql_compiler_flags='NATIVE';
alter session set plsql_compiler_flags='INTERPRETED','DEBUG';

Disallowed combinations:

'NATIVE','INTERPRETED' - for obvious reasons
'DEBUG','NON_DEBUG' - for obvious reasons
'NATIVE','DEBUG' - debugging of natively compiled PL/SQL
is not yet supported

This parameter affects ONLY those PL/SQL program units explicitly
compiled AFTER the parameter is set.

plsql_native_make_file_name

Determines the full path to the makefile used to create the shared
libraries that contain the natively compiled PL/SQL code.

This parameter is settable only at the system level.

This parameter is mandatory if plsql_compiler_flags = 'NATIVE'.

This parameter should be set to the full pathname of the makefile
provided with Oracle9i for this purpose. The provided makefile is
called 'spnc_makefile.mk' and resides in $ORACLE_HOME/plsql.

Since $ORACLE_HOME can vary from one installation to another, this
parameter does not have any default; it must be set explicitly.

Examples:

alter system set plsql_native_make_file_name=
'/usr/app/oracle/product/9.0.1/plsql/spnc_makefile.mk';

plsql_native_make_utility

Determines the full path to the make utility used to process the
makefile specified via plsql_native_make_file_name.

This parameter is settable only at the system level.

This parameter is mandatory if plsql_compiler_flags = 'NATIVE'.

This parameter should be set to the full pathname of the 'make'
utility on your machine. If the PATH environment variable is set
appropriately, you may find that providing just the name of the
make utility will work; however, it is highly recommended that
the full pathname be provided to avoid potential problems.

Since the location of the make utility can vary from one machine
to another, this parameter does not have any default; it must be
set explicitly.

Examples:

alter system set plsql_native_make_utility='/usr/ccs/bin/make';

plsql_native_library_dir

Determines the directory name used to store the shared libraries
that contain the natively compiled PL/SQL code.

This parameter is settable only at the system level.

This parameter is mandatory if plsql_compiler_flags = 'NATIVE'.

This parameter should be set to the full pathname of the directory
where the shared libraries are to be stored. The 'oracle' user must
have write permissions on this directory or native compilation will
fail. Other users' access to the directory should be restricted; in
particular, no user other than 'oracle' or 'root' should be allowed
to delete files from this directory.

The directory is assumed to have already been created. Oracle will
not create the directory for you.

There is no default value; this parameter must be set explicitly.

Examples:

alter system set plsql_native_library_dir=
'/usr/app/oracle/product/9.0.1/plsql_libs';

plsql_native_c_compiler

This parameter should NOT be set with an alter system command,
in init.ora or the stored parameter file (SPFILE). The provided
makefile should be modified to include the appropriate default
location for the C compiler.

Example (spnc_makefile.mk):

# Specify C Compiler
#
CC=/opt/SUNWspro/bin/cc

plsql_native_linker

This parameter should NOT be set with an alter system command,
in init.ora or in the stored parameter file (SPFILE). The provided
makefile should be modified to include the appropriate default
location for the linker.

Examples (spnc_makefile.mk):

# Specify Linker
#
LD=/usr/ccs/bin/ld

plsql_native_library_subdir_count

Determines the number of subdirectories to be created in the
directory
specified by the plsql_native_library_dir parameter.

Rationale:

Performance of certain file operations can be adversely affected
if
the number of files in a directory grows very large. In cases
where
many PL/SQL program units are natively compiled, each
corresponding
to a shared library in plsql_native_library_dir, the number of
files
in this directory can become unacceptably large.

By setting this parameter to some value N, you are asking that
Oracle
distribute the generated shared libraries across the N
subdirectories
of plsql_native_library_dir. The names of these subdirectories
are of
the form 'dn' where n is the zero-based directory number.

Setting this parameter is recommended if the number of PL/SQL
program
units to be natively compiled exceeds 10,000.

This parameter is settable only at the system level.

This parameter is optional; the default value is 0 (zero) so all
shared
libraries are by default stored directly in
plsql_native_library_dir.

A Typical PL/SQL Native Compilation Session
-------------------------------------------

$ sqlplus system/manager

SQL> alter system set plsql_native_make_file_name=
'/usr/app/oracle/product/9.0.1/plsql/spnc_makefile.mk';

System altered.

SQL> alter system set plsql_native_make_utility='/usr/ccs/bin/make';

System altered.

SQL> alter system set plsql_native_library_dir=
'/usr/app/oracle/product/9.0.1/plsql_libs';

System altered.


NOTE: The above parameters could also be set via the init.ora file. And
if
a stored parameter file (SPFILE) exists for this instance, the
above
commands will also persist these changes to the SPFILE,
eliminating
the need to reaccomplish these steps when the instance is
restarted.

NOTE: If there is some doubt about the current settings for the
parameters,
they can be viewed via the 'show parameters plsql' command in
sqlplus
or by querying the v$parameter table directly.

SQL> connect scott/tiger

Connected.

SQL> alter session set plsql_compiler_flags='NATIVE';

Session altered.

SQL> create or replace procedure nativetest as
eno number;
begin
select empno into eno from emp
where rownum <= 1;
end;
/

Procedure created.

NOTE: To verify that the procedure was created as natively compiled, use

USER_STORED_SETTINGS, ALL_STORED_SETTINGS, or DBA_STORED_SETTINGS.

SQL> select object_name, param_name, param_value
from user_stored_settings
where object_name = 'NATIVETEST';

OBJECT_NAME PARAM_NAME PARAM_VALUE
----------- --------------------- ----------------
NATIVETEST plsql_compiler_flags NATIVE,NON_DEBUG

To run the natively compiled procedure:

SQL> execute nativetest;

PL/SQL procedure successfully completed.

On Solaris, one can verify that the procedure is being executed natively
by
using 'pmap' to view the libraries loaded by the oracle executable.

Find the name of the shared library corresponding to the PL/SQL
procedure:

SQL> !ls /usr/app/oracle/product/9.0.1/plsql_libs

NATIVETEST__SCOTT__0.so

Note that the shared library name (in this case, on Solaris) is
constructed
from the procedure, function or package name and the schema name.

Find the process id of the current sqlplus process:

SQL> !ps

PID TTY TIME CMD
593 pts/10 0:00 ksh
11695 pts/10 0:00 sqlplus

Find the process id of the oracle process: (Note: this assumes you
connected
via bequeath; otherwise the oracle process won't be a child of sqlplus)

SQL> !ps -ef | grep 11695

langor 12080 11695 0 14:28:56 pts/10 0:00 grep 11695
langor 11698 11695 0 14:13:22 ? 0:01 oracleO9iProd ...
langor 11695 593 0 14:13:22 pts/10 0:00 sqlplus scott/tiger

Use pmap to see all of the memory segments (including shared libraries)
that
are currently attached to this process. You will see
NATIVETEST__SCOTT__0.so
listed among the libraries displayed by the pmap utility.

SQL> !/usr/proc/bin/pmap 11698 | more

/* partial listing of output */

EF44C000 8K read/write/exec [ anon ]
EF460000 56K read/exec /usr/lib/libresolv.so.2
EF47C000 8K read/write/exec /usr/lib/libresolv.so.2
EF47E000 16K read/write/exec [ anon ]
EF490000 40K read/exec /usr/ucblib/libucb.so.1
EF4A8000 8K read/write/exec /usr/ucblib/libucb.so.1
EF4B0000 8K read/exec
/lang/product/9.0.1/plsqllib/NATIVETEST__SCOTT__0.so
EF4C0000 8K read/write/exec
/lang/product/9.0.1/plsqllib/NATIVETEST__SCOTT__0.so

NOTE: It may be possible to perform a similar test on other platforms
besides
Solaris, but the tools required will likely differ. See your
platform
documentation for how to do this on your operating system
platform.

Dependencies, Invalidation and Revalidation
-------------------------------------------

Dependencies between database objects are handled in the same manner as
in
previous Oracle versions. If an object upon which some natively compiled

PL/SQL program unit depends changes, the PL/SQL module is invalidated.
The
next time the same PL/SQL program unit is executed, an attempt is made
to
revalidate (recompile) the module. Any time a module is recompiled as
part
of revalidation, it is compiled using its stored setting (i.e. the
setting
in force the last time the module was explicitly compiled and appearing
in
the USER/ALL/DBA_STORED_SETTINGS data dictionary views).

The stored settings are ONLY used when recompiling as part of
revalidation.
Any time a PL/SQL module is explicitly compiled via 'create or replace'
or
'alter ... compile', the current setting for the session is used.

Although natively compiled PL/SQL program units are obviously dependent
on
their implementation shared libraries, the database is unable to track
such
dependencies (since the libraries, which reside on the file system, can
be
manipulated directly via OS commands). For example, attempting to
execute a
PL/SQL program unit whose shared library has been deleted results in
error.

$ rm /usr/app/oracle/product/9.0.1/plsql_libs/NATIVETEST__SCOTT__0.so

$ sqlplus scott/tiger

SQL> execute nativetest;

*
ERROR at line 1:
ORA-06549: PL/SQL: failed to dynamically open shared object (DLL):
ld.so.1:
oracleO9iProd: fatal:
/lang/product/9.0.1/plsqllib/NATIVETEST__SCOTT__0.so:
open failed: No such file or directory

Since the removal of the library is undetectable to Oracle until the
module
is executed, the program unit is not marked INVALID; therefore, there is
no
implicit revalidation (and recompilation). To recreate the missing
library,
you must explicitly recompile it or recreate it from source.

SQL> alter session set plsql_compiler_flags='NATIVE';

Session altered.

SQL> alter procedure nativetest compile;

Procedure altered.

SQL> exit

$ ls /usr/app/oracle/product/9.0.1/plsql_libs

NATIVETEST__SCOTT__0.so

On a related note, shared libararies corresponding to a PL/SQL program
unit
are not automatically deleted when the program unit is dropped; these
files
must be manually removed when they are no longer needed.