Oracle PL/SQL Built For Speed

Thursday Aug 7th 2003 by James Koopmann
Share:

Everyone is looking for an edge. With PL/SQL that edge is compiling the PL/SQL code into native code. James Koopmann shows you how to setup and compile your PL/SQL procedures and packages.


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

Compiling

Now that we have set the initialization parameter for NATIVE compiles, whenever we CREATE a new procedure or package, all procedures and packages will be compiled in C. If packages or procedures already exist and you want to compile them to NATIVE C, all you need to do is issue the ALTER [PROCEDURE | PACKAGE] {procedure_name|package_name} COMPILE command. Listing D gives a quick example of what happens for a newly created procedure. As you can see, the first time that it is complied it gives a message that the object does not exist in the subdirectory. This is fine since it is only trying to create a backup of the source that this created procedure is going to replace. Listing E shows that we can take a look at the newly created DLL in the directory. After subsequent calls to re-create the procedure, you can see in Listing F that Oracle will make backup copies of the DLLs. Whenever we re-compile this procedure, it will always compile to NATIVE C. It does not matter what the current settings of the init parameters have been changed to. The only way to re-compile into INTERPRETIVE is to drop the procedure, reset the init parameters for INTERPRETIVE, and CREATE the object.


LISTING D:
First creation of a simple procedure

SQL> CREATE OR REPLACE PROCEDURE testc
  2  AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('Hello world');
  5  END;
  6  /
mv: cannot stat '/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so': 
	No such file or directory
Procedure created.
SQL> set serveroutput on
SQL> exec testc
Hello world

PL/SQL procedure successfully completed.

LISTING E
Directory listing of the newly created procedure

sh-2.05$ ls -1 /u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so

LISTING F
Directory listing of backup copies made by Oracle

sh-2.05$ ls -1 /u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SY
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so.11141
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so.11149
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so.11157

Confirmation of NATIVE Compile

We should now confirm to ourselves that what we have compiled is truly in NATIVE C. To do this we just need to issue the following SQL to look at the object we have compiled and validate that it has a NATIVE value returned. Listing G shows the simple SQL that will verify a procedure named TESTC was effectively compiled to NATIVE C.

LISTING G:
SQL to verify NATIVE C compile

SELECT object_name,param_value
  FROM user_stored_settings
 WHERE object_name = 'TESTC'
AND param_name = 'plsql_compiler_flags' 

OBJECT_NAME                    PARAM_VALUE
------------------------------ -------------------------
TESTC                          NATIVE,NON_DEBUG

Start Your Engines

If your procedures or packages are laden with logic that seems to run on forever, you should take a look at compiling these to NATIVE C. It will speed up your execution by making code jumps more efficient, function calls quicker, and use of memory more efficient; NATIVE C will give you that boost of energy you have always wanted to give your code. Just remember that DML operations are not affected since table access is a constant and compiling to NATIVE C will not do anything for it. If you want to take the next step, you can also compile the Oracle supplied packages and procedures to give the Oracle engine a boost. If you want to go that route, Oracle supplies a script called utlirp.sql that is located in $ORCLE_HOME/rdbms/admin that will invalidate and recompile all Oracle PL/SQL modules. Go ahead, start your engines.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved