Transcript Native PL/SQL Compilation

Native PL/SQL
Compilation
Julian Dyke
Independent Consultant
Web Version
1
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

Oracle 9.0.1 and above includes support for native
compilation of PL/SQL into a shared library (DLL) in the file
system

A C compiler is required to use native compilation of PL/SQL.
This can be a GNU C compiler

New parameters include
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_MAKE_UTILITY
PLSQL_NATIVE_MAKE_FILE_NAME
PLSQL_NATIVE_C_COMPILER
PLSQL_NATIVE_LINKER
PLSQL_COMPILER_FLAGS
2
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

PLSQL_COMPILER_FLAGS
 Determines whether PL/SQL code is compiled native or
interpreted
 Determines whether debug information is included
 Can be set at system or session level
 Possible values


3
INTERPRETED
compile in interpreted mode
NATIVE
compile in native mode
DEBUG
include debug information
NON_DEBUG
no debug information
Defaults are 'INTERPRETED','NON_DEBUG'
Invalid combinations
 NATIVE and INTERPRETED
 DEBUG and NON_DEBUG
 NATIVE and DEBUG
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

PLSQL_COMPILER_FLAGS
 For example
ALTER SESSION
SET PLSQL_COMPILER_FLAGS = 'NATIVE';
ALTER SESSION
SET PLSQL_COMPILER_FLAGS = 'INTERPRETED', 'DEBUG';
4
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

PLSQL_NATIVE_MAKE_FILE_NAME
 Determines full path to makefile used to create shared
libraries containing natively compiled PL/SQL code
 Set at SYSTEM level
 Mandatory if PLSQL_COMPILER_FLAGS = 'NATIVE'
 Should specify full pathname of makefile
 Sample makefile is
 $ORACLE_HOME/plsql/spnc_makefile.mk
 No default value - must be set explicitly
 For example:
ALTER SESSION
SET PLSQL_NATIVE_MAKE_FILE_NAME =
'/u01/app/oracle/product/9.0.1/plsql/spnc_makefile.mk'
5
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

PLSQL_NATIVE_MAKE_UTILITY
 Determines full path to make utility used to process
makefile specified by PLSQL_NATIVE_MAKE_FILE_NAME
 Set at SYSTEM level
 Mandatory if PLSQL_COMPILER_FLAGS = 'NATIVE'
 Should specify full pathname of make utility
 No default value - must be set explicitly
 For example:
ALTER SESSION
SET PLSQL_NATIVE_MAKE_UTILITY = '/usr/ccs/bin/make';
6
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

PLSQL_NATIVE_LIBRARY_DIR
 Determines directory name used to store shared libraries
that contains natively compiled PL/SQL code
 Set at SYSTEM level
 Mandatory if PLSQL_COMPILER_FLAGS = 'NATIVE'
 Should be set to full pathname of specified directory
 Oracle user must have write permissions on specified
directory
 Access by other users should be restricted
 Directory must be created manually at operating system
level
 No default value - must be set explicitly
 For example:
ALTER SESSION
SET PLSQL_NATIVE_LIBRARY_DIR = '/usr/app/oracle/product/9.0.1/plsql_libs;
7
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

PLSQL_NATIVE_C_COMPILER
 Should not be set
 with an ALTER SYSTEM command
 in init.ora
 in stored parameter file (SPFILE)
 Should be set in spnc_makefile.mk
 For example:
# Specify C Compiler
#
CC=/opt/SUNWspro/bin/cc
8
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

PLSQL_NATIVE_LINKER
 Should not be set
 with an ALTER SYSTEM command
 in init.ora
 in stored parameter file (SPFILE)
 Should be set in spnc_makefile.mk
 For example:
# Specify Linker
#
LD=/usr/ccs/bin/ld
9
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

10
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
 Determines number of subdirectories to be created in
directory specified by PLSQL_NATIVE_LIBRARY_DIR
 Performance of some file operations degrades if there are
a large number of files in a directory
 Use this parameter to specify number of subdirectories
that should be created
 Directories should be called d0, d1, d2... dN
where N is the value specified for this parameter
 Set at SYSTEM level
 Default value is 0 - do not use subdirectories
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

To compile an interpreted function
ALTER SESSION
SET PLSQL_COMPILER_FLAGS = ‘INTERPRETED’;
ALTER FUNCTION f1 COMPILE;

To compile a native function
ALTER SESSION
SET PLSQL_COMPILER_FLAGS = ‘NATIVE’;
ALTER FUNCTION f2 COMPILE;
11
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation


Four tests were performed using the same user-defined
function written in different languages
The PL/SQL version of the function was
CREATE OR REPLACE FUNCTION double (n NUMBER)
RETURN NUMBER IS
v_total NUMBER;
BEGIN
v_total := 0;
FOR f IN 1..n LOOP
v_total:= v_total + 2;
END LOOP;
RETURN v_total;
END;
12
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation

The function was called using the statement
SELECT double (5000000)
FROM dual;

Results were
Language
9.0.1
9.2.0
13.7
11.7
PL/SQL (Native Compiled)
6.0
5.9
Java Stored Procedure
1.6
1.6
C-based External Procedure
0.3
0.3
PL/SQL (Interpreted)
13
Time (seconds)
© 2005 Julian Dyke
juliandyke.com
Native PL/SQL Compilation
14

In Oracle 10.1 and above compiled native PL/SQL libraries are
stored in BLOBs

Advantages
 Libraries can be backed up using RMAN
 Libraries compiled on primary database will be
automatically propagated to standby
© 2005 Julian Dyke
juliandyke.com
Thank you for your interest
For more information and to provide feedback
please contact me
My e-mail address is:
[email protected]
My website address is:
www.juliandyke.com
15
© 2005 Julian Dyke
juliandyke.com