Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Use Native compilation in 9i

Oracle Database Tips by Donald Burleson

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:

1.        Update the supplied makefile and enter the appropriate paths and other values for your system. The path of this makefile is:


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

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

4.      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_NATIVE_LIBRARY_DIR (cannot be set by ALTER SESSION       security reasons)

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

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/';
alter session set plsql_compiler_flags='NATIVE';
create or replace procedure hello_native_compilation
  dbms_output.put_line('Hello world');
  select sysdate from dual;

Figure 28: 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.

Learn More about Oracle Tuning:


This is an excerpt from the top selling book "Oracle PL/SQL Tuning" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the online  code depot of working PL/SQL examples.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.