Oracle PL/SQL Built For Speed

Everyone is looking for an edge. With PL/SQL that edge is
compiling the PL/SQL code into native code. This article will show you how to
setup and compile your PL/SQL procedures and packages.

The default behavior for creation of PL/SQL code execution
is purely interpretive. If you want to skip the interpreted aspect of PL/SQL
and jump through to native C code, then you must use a C compiler on the
system. When you do this Oracle generates native C code, then compiles it with
the C compiler on your system, turns it into shared libraries, and then it is
linked into the Oracle process. This means that when these procedures are
called they are immediately available for execution. All of this is dynamic and
you do not need to restart the database to take advantage of this feature. Just
remember, the only speed increase you will achieve is PL/SQL code execution,
not SQL execution. Therefore, if your PL/SQL code is laced with SQL statements
and not logical statements, the switch to C code will not buy you much speed.

Setup to Compile

1.      Within
the $ORACLE_HOME/plsql directory, there is a makefile called spnc_makefile.mk.
You will need to change this file for the appropriate paths for the C compiler.
A quick look at this file and a few verifications proved un-eventful as Oracle
had all the paths set for my Linux installation. Table 1
will show the paths I verified and what command I used to verify them.

TABLE 1:
Verification of path entries in
$ORACLE_HOME/plsql/spnc_makefile.mk file

Entery in file

Unix command to verify

Output to Unix command

PLSQLHOME=
$(ORACLE_HOME)/plsql/

ls -d $ORACLE_HOME/plsql

/u01/app/oracle/product/
9.2/plsql

PLSQLINCLUDE=
$(PLSQLHOME)include/

ls -d $ORACLE_HOME/plsql/include

/u01/app/oracle/product/
9.2/plsql/include

PLSQLPUBLIC=
$(PLSQLHOME)public/

ls -d $ORACLE_HOME/plsql/public

/u01/app/oracle/product/
9.2/plsql/public

RM=/bin/rm -f

which rm

/bin/rm

CC=/usr/bin/gcc

which gcc

/usr/bin/gcc

LD=/usr/bin/ld

which ld

/usr/bin/ld

2.      The
initialization parameter PLSQL_COMPILER_FLAGS must be set to NATIVE.

The default value for this parameter is INTERPRETED.
You can verify your current setting by issuing the ‘SHOW PARAMETER plsql_compiler_flags’
command. You can change the setting by issuing an ALTER SESSION or ALTER SYSTEM
command. Listing A shows my session to verify my
current setting and set this parameter to NATIVE.

LISTING A:
Session to set initializatio
parameter PLSQL_COMPILER_FLAGS


SQL> connect sys/<password> as sysdba
Connected.
SQL> show parameter plsql_compiler_flags
NAME TYPE VALUE
———————————— ———– ————————-
plsql_compiler_flags string INTERPRETED

SQL> ALTER SYSTEM SET plsql_compiler_flags = NATIVE SCOPE = both;
System altered.

SQL> show parameter plsql_compiler_flags
NAME TYPE VALUE
———————————— ———– ————————-
plsql_compiler_flags string NATIVE

3.      Listing B gives more parameters that you must set to
control the compiling of PL/SQL. Read through them and take appropriate action
according to your environment. Each of these parameters may be set through the
ALTER SYSTEM or ALTER SESSION commands. Listing C
gives my session for setting these parameters.

LISTING B:

Parameters for controlling PL/SQL compiling to Native C

Parameter

Description

Value

PLSQL_NATIVE_LIBRARY_DIR

is
the name of the directory where compiled objects will be stored

/u01/app/oracle/product/
9.2/plsql/lib

PLSQL_NATIVE_C_COMPILER

gives
the full path of where the C compiler is. This is an optional parameter and
Oracle will pick up the value for the ‘CC’ entry in the spnc_makefile.mk makefile.
By setting this parameter you will override the entry in the spnc_makefile.mk
makefile

Use default in
spnc_makefile.mk
makefile

PLSQL_NATIVE_
LIBRARY_SUBDIR_COUNT

Use
this parameter to help reduce the performance impact of a large number of
compiled objects in the PLSQL_NATIVE_LIBRARY_DIR.

If
a very high (10000+) compiled objects are done, it is best to segregate these
into subdirectories so that the performance impact of file access operations
is kept to a minimal. To take advantage of this, set the
PLSQL_NATIVE_
LIBRARY_SUBDIR_COUNT to a value where the total number of
compiled objects divided by the number of subdirectories will allow for less
than 10000 entries in each of the subdirectories. For example if you wanted
500 subdirectories, set PLSQL_NATIVE_
LIBRARY_SUBDIR_COUNT to 500 and create
500 subdirectories named d0 thru d499. the subdirectories must be prefixed
with the letter “d”.

I set this to 500 and
created subdirectories

/u01/app/oracle/product/
9.2/plsql/lib/d0
thru
/u01/app/oracle/product/
9.2/plsql/lib/d499

PLSQL_NATIVE_LINKER

is
the name of the linker to use. This is an optional parameter and Oracle will
pick up the value for the ‘LD’ entry in the spnc_makefile.mk makefile. By
setting this parameter, you will override the entry in the spnc_makefile.mk makefile.

Use default in
spnc_makefile.mk
makefile

PLSQL_NATIVE_MAKE_
FILE_NAME

this is the full path of the spnc_makefile.mk
makefile.
/u01/app/oracle/product/
9.2/plsql/spnc_makefile.mk

/u01/app/oracle/product/
9.2/plsql/
spnc_makefile.mk

PLSQL_NATIVE_MAKE_UTILITY

is
the full path of a make utility

/usr/bin/gmake


LISTING C:

Script for setting additional parameters for Native C
compiling

SQL> connect sys/<password> as sysdba
Connected.
SQL> show parameter native
NAME TYPE VALUE
———————————— ———– ——————————
plsql_native_c_compiler string
plsql_native_library_dir string
plsql_native_library_subdir_count integer 0
plsql_native_linker string
plsql_native_make_file_name string
plsql_native_make_utility string

SQL> alter system set PLSQL_NATIVE_LIBRARY_DIR =
‘/u01/app/oracle/product/9.2/plsql/lib’ scope=both;
System altered.
SQL> alter system set PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT = 500 scope=both;
System altered.

SQL> alter system set PLSQL_NATIVE_MAKE_FILE_NAME =
‘/u01/app/oracle/product/9.2/plsql/spnc_makefile.mk’ scope=both;
System altered.

SQL> alter system set PLSQL_NATIVE_MAKE_UTILITY = ‘/usr/bin/gmake’ scope=both;
System altered.

SQL> show parameter native
NAME TYPE VALUE
——————————— —— ——————————
plsql_native_c_compiler string
plsql_native_library_dir string /u01/app/oracle/product/9.2/plsql/lib
plsql_native_library_subdir_count integer 0
plsql_native_linker string
plsql_native_make_file_name string /u01/app/oracle/product/9.2/plsql/spnc_makefile.mk
plsql_native_make_utility string /usr/bin/gmake

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles