Hypercharge performance by compiling PL/SQL
Jean-Luc Touzalin has published step-by-step instrictions for
How to use the new feature native compilation in Oracle
This new feature in 9i allows
to transform a procedure writing in PL/SQL in a shared
library (.so file)
It uses a C compiler on your
system (+ make and ld).
It's usefull if the process
is complex in PL/SQL and this feature allows up to 40% in
I didn't find a lot of
documentation on this topic so I'm sharing my experience.
My environment is Solaris 9 and GNU C
So, first download and install
the current version of GNU C.
Modify your init.ora file
Here I created a directory
/appli_std/oracle_9i/9.2.0/NATIVE. Oracle will put the
sources and .so libraries there.
Modify the file $ORACLE_HOME/plsql/spnc_makefile.mk
OPTIMIZE=-O1 # no optimization
When you will compile a procedure, a source will be created in /appli_std/oracle_9i/9.2.0/NATIVE
If the compilation is ok, you should have a file containing the
name of your procedure + __User_name.so
If it's not, the .c file still exist and you can test manually
to search for the problem with make
>/usr/ccs/bin/make -f ../plsql/spnc_makefile.mk
gcc: unrecognized option `-Kpic'
ld: warning: option -o appears more than once, first setting
You see here 2 problems of type Warning.
On my part, I had problem with the initial parameter OPTIMIZE
#OPTIMIZE=-xO1 -Xa -xstrconst
-dalign -xF -mr -xildoff -errtags=yes -v -xarch=v9 -xchip=ultra3
-W2,-AKNR_S -Wd,-xsafe=unboundsym -Wc,-Qiselect-funcalign=32
-xcode=abs44 -Wc,-Qgsched-trace_late=1 -Wc,-Qgsched-T5 -xalias_level=weak
-D_REENTRANT -DBIT64 -DMACHINE64