Oracle compiled PL/SQL Oracle Database Tips by Donald Burleson

Native compilation of PL/SQL

By default, PL/SQL code is compiled and stored in the form of byte code ready for execution.  During the execution process, this byte code is interpreted, a process which requires time and resources.  The process of native compilation converts PL/SQL stored procedures to Pro*C, which is then compiled to native code shared libraries, resulting in performance increases for the procedural code.    The extent of the performance increase depends on the content of the PL/SQL, with the best results shown in code containing lots of loops, logic, mathematical operations and comparatively less database work.

The setup required for native compilation depends on the version of Oracle being used. In Oracle 9i several parameters must be set and on some platforms the associated makefile may need adjustment, whereas Oracle 10g has made several parameters obsolete and the makefile rarely needs modification.

-- Oracle 9i setup.

ALTER SYSTEM SET plsql_native_make_utility = 'make';
ALTER SYSTEM SET plsql_native_make_file_name =
  '/u01/app/oracle/product/9.2.0/plsql/spnc_makefile.mk';
ALTER SYSTEM SET plsql_native_library_dir = '/u01/oradata/DB9I/native';

-- Oracle 10g setup.
ALTER SYSTEM SET plsql_native_library_dir = '/u01/oradata/DB10G/native'

Notice that the directory used to hold the shared libraries is database-specific to prevent issues when multiple instances are running on a single server.

Once these parameters are set the compilation style can be switched by setting session or instance level parameters.

-- Oracle 9i switch.

ALTER SESSION SET plsql_compiler_flags = 'INTERPRETED';
ALTER SESSION SET plsql_compiler_flags = 'NATIVE';

ALTER SYSTEM SET plsql_compiler_flags = 'INTERPRETED';
ALTER SYSTEM SET plsql_compiler_flags = 'NATIVE';

-- Oracle 10g switch.

ALTER SESSION SET plsql_code_type = 'INTERPRETED';
ALTER SESSION SET plsql_code_type = 'NATIVE';

ALTER SYSTEM SET plsql_code_type = 'INTERPRETED';
ALTER SYSTEM SET plsql_code_type = 'NATIVE';

The test_speed.sql script creates a procedure, which we will use to test the performance gains associated with native compilation.

test_speed.sql

CREATE OR REPLACE PROCEDURE test_speed AS
  l_number  NUMBER;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    l_number := i / 1000;
  END LOOP;
END;
/
SHOW ERRORS

With the procedure compiled we can compare the speed using the native_comp_test.sql script.

native_comp_test.sql

-- *****************************************************************
-- Notes:
--   Switch to plsql_compiler_flags when using 9i.
-- *****************************************************************

-- ALTER SESSION SET plsql_compiler_flags = 'INTERPRETED';
ALTER SESSION SET plsql_code_type = 'INTERPRETED';
ALTER PROCEDURE test_speed COMPILE;
PROMPT Interpretted
SET TIMING ON
EXEC test_speed;
SET TIMING OFF

-- ALTER SESSION SET plsql_compiler_flags = 'NATIVE';
ALTER SESSION SET plsql_code_type = 'NATIVE';
ALTER PROCEDURE test_speed COMPILE;
PROMPT Native
SET TIMING ON
EXEC test_speed;
SET TIMING OFF

The output from the script shows that the natively compiled code runs faster.

SQL> @native_comp_test.sql

Session altered.

Procedure altered.

Interpretted

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.74

Session altered.

Procedure altered.

Native

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.66

If we recreate the test_speed procedure using the test_speed_2.sql script we can measure the affect of native compilation of code containing database calls.

test_speed_2.sql

CREATE OR REPLACE PROCEDURE test_speed AS
  l_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    SELECT SYSDATE
    INTO   l_date
    FROM   dual;
  END LOOP;
END;
/
SHOW ERRORS

With the procedure recreated running the native_comp_test.sql again produces the following results.

SQL> @native_comp_test.sql

Session altered.

Procedure altered.

Interpretted

PL/SQL procedure successfully completed.

Elapsed: 00:01:22.03

Session altered.

Procedure altered.

Native

PL/SQL procedure successfully completed.

Elapsed: 00:01:19.32

Once again the natively compiled code is faster, but the differences here are still due to the procedural code, rather than the database calls.

By default all shared libraries are stored in the same directory, which can cause a problem when there are many natively compiled objects.  The point where problems can occur depends on the platform and the source quoting the figure, but the lowest I've read says in excess of 5,000 files can be a problem.

The system level plsql_native_library_subdir_count parameter gets round this issue by telling Oracle to distribute the shared libraries in the specified number of subdirectories.  The directories must be present in the directory pointed to by the plsql_native_library_dir parameter and there names must be in the form "dn", where "n" is the zero-based directory number.  Most articles suggest 1,000 subdirectories is a suitable number to work with, and the following script can be used to generate a directory creation script.

SET SERVEROUTPUT ON SIZE 1000000
SPOOL mkdir_cmd_file
BEGIN
  FOR i IN 0..999
  LOOP
    DBMS_OUTPUT.put_line('mkdir d' || TO_CHAR(i));
  END LOOP;
END;
/
SPOOL OFF

Once the subdirectories are present the parameter can be set to the appropriate number.

ALTER SYSTEM SET plsql_native_library_subdir_count=1000;

It is possible to natively compile the whole database, including the objects owned by the SYS schema, but this should only be done by following the appropriate note from Oracle Technology Network as it can cause problems if done incorrectly.  At the time of writing the method was only described for Oracle 9i in the following document:

http://www.oracle.com/technology/tech/pl_sql/htdocs/README_2188517.htm

In Oracle 10g the compiled shared libraries are now stored in the database and extracted to the file system as necessary.  This means they form part of the normal backup and recovery process, require no manual maintenance and are available in Real Application Cluster (RAC) configurations.  Native compilation of the package specification and body are independent of each other, meaning either one, the other or both can be natively compiled.

In the next section we will look at one way of hiding performance problems from end users by decoupling processes.

Compiling PL/SQL in Oracle 10g and beyond
Mike Ault

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 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.

*****************************************

Use PL/SQL Native Compilation in 9i
Mike Ault

The native compilation option in Oracle9i allows you to convert internal stored packages and procedures into compiled shared C libraries on the host server. This native compilation can reduce code execution time by up to a factor of four. The packages and procedures are compiled as native C routines and linked into the Oracle code. This new option in Oracle9i is most effective for computational intensive PL/SQL and shouldn't be used for PL/SQL that does a great deal of SQL execution.

To speed up one or more procedures using this technique follow this procedure:
Update the supplied makefile and enter the appropriate paths and other values for your system. The path of this makefile is:

     $ORACLE_HOME/plsql/spnc_makefile.mk.

Use the ALTER SYSTEM or alter session command, or update your initialization file, to set the parameter PLSQL_COMPILER_FLAGS to include the value NATIVE. The default setting includes the value INTERPRETED, and you must remove this keyword from the parameter value.

Compile one or more procedures, using one of these methods:

• Use the ALTER PROCEDURE or ALTER PACKAGE command to recompile the procedure or the entire package.
• Drop the procedure and create it again.
• Use CREATE OR REPLACE to recompile the procedure.
• Run one of the SQL*Plus scripts that sets up a set of Oracle-supplied packages.
• Create a database using a preconfigured initialization file with PLSQL_COMPILER_FLAGS=NATIVE.
• During database creation, the UTLIRP script is run to compile all the Oracle-supplied packages.

To be sure that the process worked, you can query the data dictionary to see that a procedure is compiled for native execution. To check whether an existing procedure is compiled for native execution or not, you can query the data dictionary views USER_STORED_SETTINGS, DBA_STORED_SETTINGS, and ALL_STORED_SETTINGS. For example, to check the status of the procedure MY_PROC, you could enter:

SELECT param_value FROM user_stored_settings WHERE
  param_name = 'PLSQL_COMPILER_FLAGS'
  and object_name = 'MY_PROC';

The PARAM_VALUE column has a value of NATIVE for procedures that are compiled for native execution, and INTERPRETED otherwise.

After the procedures are compiled and turned into shared libraries, they are automatically linked into the Oracle process. You do not need to restart the database, or move the shared libraries to a different location. You can call back and forth between stored procedures, whether they are all compiled in the default way (interpreted), all compiled for native execution, or a mixture of both.

Because the PLSQL_COMPILER_FLAGS setting is stored inside the library unit for each procedure, procedures compiled for native execution are compiled the same way when the procedure is recompiled automatically after being invalidated, such as when a table that it depends on is recreated.

You can control the behavior of PL/SQL native compilation through the ALTER SYSTEM or alter session commands or by setting or changing these parameters in the initialization file:

• PLSQL_COMPILER_FLAGS
• PLSQL_NATIVE_LIBRARY_DIR (cannot be set by alter session for security reasons)
• PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
• PLSQL_NATIVE_MAKE_UTILITY
• PLSQL_NATIVE_MAKE_FILE_NAME

An example of compiling a PL/SQL Procedure for Native Execution is shown in Figure 3.

connect scott/tiger;
set serveroutput on;
alter session set plsql_native_library_dir='/home/orauser/lib';
alter session set plsql_native_make_utility='gmake';
alter session set plsql_native_make_file_name='/home/orauser/spnc_makefile.mk';
alter session set plsql_compiler_flags='NATIVE';
create or replace procedure hello_native_compilation
as
begin
  dbms_output.put_line('Hello world');
  select sysdate from dual;
end;

Figure 3: Example use of Native Compilation

As the procedure is compiled, you see the various compilation and link commands being executed. The procedure is immediately available to call, and runs as a shared library directly within the Oracle process.

Limitations of Native Compilation

When a package specification is compiled for native execution, the corresponding body should be compiled using the same settings.
The Oracle provided debugging tools for PL/SQL do not handle procedures compiled for native execution.

When many procedures and packages (typically, over 5000) are compiled for native execution, having a large number of shared objects in a single directory might affect system performance. In this case, you can have the DBA set the initialization parameter PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT in the initialization file before creating the database or compiling the PL/SQL packages or procedures. Set this parameter to a value that makes sense for your environment and operating system, Oracle suggests 1000 but this seems like overkill to me. Once the parameter is set and the DB restarted, create subdirectories underneath the directory specified in the PLSQL_NATIVE_LIBRARY_DIR parameter. The subdirectories must be named d0, d1, d2 ... d999, up to the value specified for the subdirectory count. When the procedures are compiled for native execution, the DLLs will be automatically distributed among these subdirectories by the PL/SQL compiler.

Also see this research on compiling PL/SQL:

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.