 |
|
Oracle Tips by Burleson |
Compiling PL/SQL in Oracle
10g
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
executables.
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
times faster.
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:
@echo off
REM
REM Copyright (c) 1999 by Oracle Corporation. All Rights Reserved.
REM
Rem Use this
script to create executables for the demo scripts with PL/SQL
Rem blocks embedded in C code. To create all executables, at the
prompt
type:
Rem
Environment Check:
if (%MSVCDir%) == () goto msvcdir_error
if (%1) ==
() goto usage
echo This makefile assumes demos are installed as user scott
proc iname=%1.pc
sqlcheck=full CHAR_MAP=VARCHAR2, DBMS=V7 ireclen=132 parse=full
sqlcheck=SEMANTICS user=scott/tiger
cl -I%ORACLE_HOME%\oci\include
-I%MSVCDir%\include -I%MSVCDir%\mfc\include -D_MT -Zi %1.c /link %ORACLE_HOME%\oci\lib\msvc\oci.lib
%ORACLE_HOME%\precomp\lib\msvc\orasql9.lib /libpath:%MSVCDir%\lib
msvcrt.lib /nod:libcmt
goto end
:msvcdir_error
echo .
echo Environment variable MSVCDIR must be set before running this
batch
file.
echo Please run vcvars32.bat from MS Visual Studio directory.
echo .
goto end
:usage
echo .
echo usage prcmake filename.pc [i.e. prcmake examp10]
echo .
:end
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.
connect
pubs/pubs as sysdba;
alter
system set plsql_compiler_flags=NATIVE;
alter system set plsql_native_library_dir='c:\oracle\ora90\lib';
alter system set plsql_native_make_utility='prcmake';
alter system set
plsql_native_make_file_name='c:\oracle\ora90\plsql\demo\prcmake.bat';
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:
SQL> alter
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.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
