What is Native Compilation?

You can speed up the execution of PL/SQL modules (packages, triggers, procedures, function, and types) by compiling them into native code residing in shared libraries. The procedures are translated into C code, then compiled with a C compiler and dynamically linked into the Oracle process.

You can use this technique with both the supplied Oracle packages, and procedures you write yourself. Procedures compiled this way work in all server environments, such as the shared server configuration (formerly known as multi-threaded server) and Oracle Real Application Clusters.

Performance experiments (published on OTN), using a large set of representative test programs, indicate that in Oracle Database 10g pure PL/SQL programs applications can expect to see performance improvements ranging from a factor of about 1.05 to 2.4, comparing speed when compiled native with speed when compiled interpreted and holding everything else constant. The size of the improvement depends upon the nature of the program.

Note: Many of the Oracle-supplied packages (such as UTL_FILE, UTL_RAW, DBMS_LOB) are already implemented in C, with PL/SQL simply exposing the API. These are not likely to benefit significantly from NATIVE compilation. Similarly, PL/SQL program units that just call SQL statements might see little or no speedup. However, natively compiled PL/SQL is always at least as fast as the corresponding interpreted code. The compiled code makes the same library calls as the interpreted code would, so its behavior is exactly the same.

Back to top

How does PL/SQL Native Compilation Work?

If you do not use native compilation, each PL/SQL program unit is compiled into an intermediate form, machine-readable code (MCode). The MCode is stored in the database dictionary and interpreted at run time.

With PL/SQL native compilation, the PL/SQL program is compiled into machine native code that bypasses all the runtime interpretation, giving faster runtime performance.

PL/SQL uses the command file $ORACLE_HOME/plsql/spnc_commands, and the supported operating system C compiler and linker, to compile and link the resulting C code into shared libraries. The shared libraries are stored in the data dictionary, so that they can be backed up automatically and are protected from being deleted. These shared library files are copied to the filesystem and are loaded and run when the PL/SQL subprogram is invoked. If the files are deleted from the filesystem while the database is shut down, or if you change the directory that holds the libraries, they are extracted again automatically.

Back to top

What's new in Oracle Database 10g?

This feature now requires less setup and maintenance.

Back to top

What objects can be the subject of PL/SQL Native Compilation?

Any stored PL/SQL module can be natively compiled. Anonymous PL/SQL blocks are never natively compiled.

Note: Oracle supports the following types of stored PL/SQL modules:

FUNCTION
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY

Back to top

What is the naming convention for the Shared Libraries (DLLs)?

Native compilation generates a shared library per unit. For example, a shared library will be generated for the package specification as well as one for the package body. Some examples of generated names are:

PKG__SCOTT__S__54682.so
PKG__SCOTT__B__54683.so

The generated name typically includes the unit name, schema name, the type of object and the object number. However, this is an internal convention that is subject to change and users should not rely on the specifics of the filename format.

The file name extensions might be different on different platforms. For instance, on Solaris the shared libraries have a ".so" suffix whereas on HP-UX, the shared libraries have a ".sl" suffix.

Are the shared dynamically linkable libraries portable?

No, absolutely not! The shared libraries contain platform specific object code.

Note: MCode generated in the interpreted regime is also platform specific.

Back to top

Do I need a C compiler environment at the deployment site(s)?

Yes. Non-negotiably. PL/SQL units may need to be compiled at the deployment site and PL/SQL native compilation relies on a C compiler to generate object (machine) code.

For instance, various actions (such as adding a column to a table) may cause automatic invalidation of PL/SQL units (that depend on the table). Subsequent access to such a unit will trigger a recompilation. Under such a circumstance, the C compiler environment is needed to recompile the unit in native mode again.

Another situation is when a patch fix needs to be applied on a deployment database. Individual packages may need to be reloaded and compiled in a deployment environment.

Back to top

Which Oracle parameters are relevant to native compilation?

• plsql_native_library_dir
• plsql_native_library_subdir_count
• plsql_code_type

plsql_native_library_dir parameter

This parameter specifies the location of the directory where the OS copy of shared libraries (DLLs) are kept.

When a module is natively compiled, the shared libary is created in this location and then copied into the database dictionary table (ncomp_dll$). Although the master copy of the shared library resides in the database, the shared objects are also materialized in the file system so that they can be dynamically loaded into the Oracle's address space.

Note 1: Users (DBAs) must never delete the shared libraries manually from the plsql_native_library_dir when the system is up and running as these DLLs may be mapped to Oracle processes. The only time it is safe to delete the OS copy of a shared library is when the system is down.

Note 2: In RAC configuration, this parameter must be set in each instance. The instances are not required to have a shared file system. On each instance the plsql_native_library_dir can be set to point to an instance local directory. Alternately, if the RAC configuration supports a shared (cluster) file system, you can use a common directory (on the shared file system) for all instances.

Note 3: You must create this directory. Oracle will not automatically create this directory for you.

Note 4: Oracle Corp does not support NFS mounted directories for plsql_native_library_dir either in a single instance or a RAC case. This is because NFS causes some unpredictable timing errors while writing or deleting files.

Back to top

plsql_native_library_subdir_count parameter

Some file systems may not be capable of handling large number of files in a single directory; you may need to create sub directories under the directory specified by plsql_native_library_dir for storing the native compilation generated shared libraries.

If you use a production quality file system (e.g Veritas) you may not need to create sub directories. However, if you are using a vanilla file system, it may not be able to gracefully handle a large number of files in a single directory. For Oracle Applications which has about 70,000 PL/SQL objects, when using a vanilla file system, we use about 500 sub-directories.

The default value for this parameter is 0. In order to instruct Oracle to distribute the shared libraries under the various sub-directories (rather than storing all the shared libraries in the single directory specified by plsql_native_library_dir), set the plsql_native_library_subdir_count parameter to the number of sub-directories you intend to create.

For example, to use 500 sub-directories, add the following line to your initialization parameter file:

plsql_native_library_subdir_count=500

You must then create 500 sub-directories (under the directory specified by the plsql_native_library_dir parameter) with exactly the names d0, d1, d2, .., d498, d499.

Back to top

plsql_code_type parameter

The parameter plsql_code_type determines whether PL/SQL code is natively compiled or interpreted. The default setting is INTERPRETED. To enable PL/SQL native compilation, set the value of plsql_code_type to NATIVE. If you compile the whole database as NATIVE, Oracle Corporation recommends that you set plsql_code_type at the system level or in the initialization parameter file.

Use the following syntax to set this parameter:

• For native compilation mode:

  alter session set plsql_code_type='NATIVE'

  or,

  alter system set plsql_code_type='NATIVE'

• For interpreted mode:

  alter session set plsql_code_type='INTERPRETED'

  or,

  alter system set plsql_code_type='INTERPRETED'

Back to top

Format of the spnc_commands File

The spnc_commands file, in the $ORACLE_HOME/plsql directory, contains the templates for the commands to compile and link each program. Some special names such as %(src) are predefined, and are replaced by the corresponding filename. The variable $(ORACLE_HOME) is also automatically replaced by the location of the Oracle home directory. You can include comment lines, starting with a # character. The file contains comments that explain all the special notation.

The spnc_commands file contains a predefined path for the C compiler, depending on the particular operating system. (One specific compiler is supported on each operating system.) In most cases, you should not need to change this path, but you might if you the system administrator has installed it in another location.

This approach means than (unlike in version 9.0.1 and version 9.2.0) there is no dependency on a make utility.

Back to top

In which dictionary table are the shared libraries generated by native compilation stored?

The shared libraries generated by native compilation are stored in the database as BLOBs in the ncomp_dll$ dictionary table.

SQL> connect / as sysdba;
Connected.
SQL> describe ncomp_dll$;
 Name	        Null?             Type
 ------------   --------------    -------- 
 OBJ#	        NOT NULL          NUMBER
 VERSION		          NUMBER
 DLL			          BLOB
 DLLNAME		          RAW(1024)

Back to top

What happens to the shared library on the filesystem when I drop a NATIVE unit?

Up until 9.2.0, nothing. The shared library was not garbage collected.

In version 10.1.0, when a natively compiled unit is either dropped or recompiled in interpreted mode, the shared library is deleted both from the ncomp_dll$ dictionary table as well as from the the file system location specified by the plsql_native_library_dir parameter. In case of RAC configuration, the shared library is deleted from each instance's plsql_native_library_dir location.

Back to top

How do I change plsql_native_library_dir

If you need to change the value for this parameter, the recommend steps are:

1. Shut down the DB instance
2. Change plsql_native_library_dir setting in the initialization parameter file to point to the new location.
3. Restart the DB instance. Oracle will automatically extract the shared libraries into this new location on demand.

Note 1: Although this parameter can be changed via the "alter system.. " command, Oracle recommends against it.

Note 2: Once the above steps have been followed, it is now safe to delete the old directory corresponding to the prior setting of plsql_native_library_dir. Oracle will not automatically delete the shared libraries from the old location.

Back to top

Are there any other circumstances where I need to delete a shared library by hand?

No.

The only exception is if you want to change the setting for plsql_native_library_dir. Follow the steps described earlier to change plsql_native_library_dir. The shared libraries can then be deleted from the old location.

Back to top

What if I delete a shared library by mistake?

This could cause undesirable internal errors and problems. The suggested remedy is to shutdown and startup the instance. Oracle will automatically extract the shared library on demand from the ncomp_dll$ dictionary table.

Back to top

Does Oracle Corporation recommend mixing NATIVE and INTERPRETED units?

Mixing is supported and never results in wrong behavior. However, there's a cost when a NATIVE unit calls an INTERPRETED one. You might compromise some of your potential performance improvement if NATIVE units frequently call INTERPRETED units.

If time to compile all modules natively into shared libraries is an issue one option to consider is to compile specifications for packages and types in interpreted mode and all the other types of PL/SQL units (package bodies, type bodies, procedures, functions and triggers) in native mode. Since specifications generally do not have much executable code, you won't be losing much in terms of run-time performance either.

Back to top

Do I need to regenerate the native shared libraries on an OS upgrade?

Provided that you have already established that Oracle Corp supports this type of OS upgrade without needing to regenerate the ordinary Oracle executables, then the shared libraries that arise from PL/SQL NATIVE compilation will also be viable without needing regeneration.

Back to top

Can I use native mode to save time during application deployment (by shipping pre-generated NCOMP shared libraries)?

No! You cannot do this even if the shared libraries are generated on the same platform as the eventual deployment environment. You cannot do this with MCode in interpreted mode either.

This is for reasons unrelated to the compilation mode. Installation of a PL/SQL based application in a database requires the PL/SQL modules to be loaded and compiled in that database so that all the external references are correctly resolved.

Back to top

Test driving native compilation: Simple Test

1. Create a directory for storing the shared libraries generated by native compilation and set the plsql_native_library_dir parameter in the initialization parameter file to point to that directory path. For example, you will need to add something like this to your initialization parameter file:

   plsql_native_library_dir=/home/kmuthukk/oracle10g/dbs/ncomp_libraries
   

2. Optionally set up plsql_native_library_subdir_count in the initialization parameter file and create the subdirectories.
3. Ensure that the OS path of the C compiler (and if applicable, the linker) used in the spnc_commands file is correct.
4. System parameters Sanity Check: plsql_native_library_dir MUST be specified. plsql_native_library_subdir_count is optional and may be left at 0 (if you are not planning to use sub-directories based scheme). It is best if these parameters are specified in the initialization parameter file.

   SQL> show parameters plsql_native
   NAME				     TYPE	 VALUE
   ------------------------------------ ----------- ------------------------------
   plsql_native_library_dir	     string	 /home/kmuthukk/oracle10g/dbs/ncomp_libraries
   plsql_native_library_subdir_count    integer	 0
   

5. Natively Compiling a PL/SQL module
   
   1. To load and compile new modules in native mode you must set the plsql_code_type parameter to NATIVE.

      For example, try the following in SQL*Plus:

      connect scott/tiger
      alter session set plsql_code_type='NATIVE';
      
      create or replace procedure my_test is
      begin
       dbms_output.put_line('hello world');
      end;
      /
      show errors;
      
      set serveroutput on;
      execute my_test;
      

      Verification Steps:

      1. Take a peek at the location specified by plsql_native_library_dir. You should find a new shared library corresponding to MY_TEST procedure. On Solaris, the DLL may have a name like MY_TEST__SCOTT__P__54765.so.

         [To read more about shared library naming conventions, click here.]

      2. Also, make sure that the plsql_code_type setting for this module is indeed NATIVE by issuing the following query:

         select name, type, plsql_code_type 
         from user_plsql_object_settings 
         where name = 'MY_TEST';

   2. If you want to manually compile specific already loaded modules native, you can use the "alter ... compile .. " statements.

      Suppose you have a package MYPKG whose specification and body are compiled interpreted. Now, if you want to natively compile just its body, then issue the following command:

      alter package MYPKG compile body plsql_code_type=native reuse settings
         

      The reuse settings clause above indicates that the compiler should preserve all the prior settings for the unit, except for the ones which have been explicitly overridden (such as the setting for plsql_code_type setting in the example above).

      If the body clause is dropped from the above statement, as in...

      alter package MYPKG compile plsql_code_type=native reuse settings

      ... then both the specification and body will be recompiled in native mode. Note however that recompiling a specification triggers a invalidation of other modules that depend on the specification.

Back to top

Natively compiling all PL/SQL units in the database

Before attempting to natively compile all PL/SQL units in the database, try it out on a simple test case as described in the previous section. This will help ensure that your setup is ok for native compilation.

1. Operating System File Descriptor Limit

   Two SQL*Plus scripts (dbmsupgnv.sql and utlrp.sql) on the $ORACLE_HOME/rdbms/admin directory are used to recompile all PL/SQL modules in native mode. They use parallel jobs (depending on the number of available CPUs). In such situations, many jobs concurrently generate C files and attempt to compile them. It is therefore important that the OS limits on max number of file descriptors isn't set too low.

   For example, on my Solaris machine:

   % ulimit -a
   time(seconds)        unlimited
   file(blocks)         unlimited
   data(kbytes)         2097148
   stack(kbytes)        8192
   coredump(blocks)     3
   nofiles(descriptors) 64
   
   vmemory(kbytes)      unlimited
   

   Note the value of 64 for max number of file descriptors. This is too low in production environments if you have many jobs running native compilation in parallel. You may want to check what the limits are on your machine and increase them. For example:

   On Solaris, the steps are to edit the /etc/system file to have something like:

   set rlim_fd_max=4096
   set rlim_fd_cur=1024
   

   On HP-UX, the corresponding parameters are in the /stand/system file.

   maxfiles        2048 
   maxfiles_lim    2048 
   

2. System Table Space

   Since the master copy of the DLLs is stored in dictionary tables, you may need more SYSTEM table space than before. If you run into errors with space limits on the SYSTEM table space, you would need to do something like..

   alter tablespace system
   add datafile '/home/kmuthukk/oracle10g/dbs/mydata10.dbf'
   size 500 m;
   

   .. or set up autoextend on the datafile.

3. Recompiling all modules native (dbmsupgnv.sql and utlrp.sql)
   
   1. Shutdown all of the Application services including the Forms Processes, Web Servers, Reports Servers, and Concurrent Manager Servers. After shutting down all of the Application services, ensure that all of the connections to the database have been terminated.

   2. Shutdown the TNS listener of the database to ensure that no new connections are made.

   3. Shutdown the database (in normal or immediate mode).

   4. Check that you have already set the plsql_native_library_dir in the initialization paramater file. If not, see steps described in the previous section.

   5. Set plsql_code_type to NATIVE in the initialization parameter file.

      plsql_code_type=NATIVE
      

   6. Startup the database in UPGRADE mode (using STARTUP UPGRADE or ALTER DATABASE OPEN UPGRADE).

   7. Run the following query to obtain the number of invalid objects. This value will be used later as a comparison to ensure that the recompilation did not produce additional invalid objects due to native compilation failures.

      Select count(*)
      From all_objects
      Where status='INVALID';
      

   8. The following query should tell you how many objects are compiled native versus interpreted.

      SELECT plsql_code_type, count(*) 
      FROM dba_plsql_object_settings 
      GROUP BY plsql_code_type;
      

      You might see some objects with a NULL plsql_code_type. These are special internal objects and may be ignored.

   9. cd (change directory) to $ORACLE_HOME/rdbms/admin.

   10. Run the $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql (as SYS).

       Note 1: dbmsupgnv.sql invalidates all PL/SQL modules and updates dictionary tables to change their plsql_code_type settings to NATIVE. This phase must be done when no other activity is happening on the database; hence, we require that the DB be started in UPGRADE mode during dbmsupgnv.sql.

       Note 2: If you are upgrading from a lower version of database to higher version of database (for example to Oracle Database 10g) then you must first run the upgrade scripts and then run the dbmsupgnv.sql. For example, if you are upgrading from version 9.2.0.3 to 10.1.0. First run the upgrade scripts (u0902000.sql) and shutdown the database and run the dbmsupgnv.sql script.

   11. Commit the changes after running dbmsupgnv.sql

       SQL> commit;
       

   12. Shutdown the DB.

   13. Restart DB in normal mode.

   14. Run $ORACLE_HOME/rdbms/admin/utlrp.sql script (as SYS). This step will recompile all the PL/SQL modules. [During this process, you may want to periodically peek in the directory specified by the plsql_native_library_dir to ensure that the new shared libraries are indeed getting created by the compilation process.]

       Note: This step is restartable. If for any reason this step is abnormally terminated, you may simply restart this step (i.e. rerun the utlrp.sql script). It will recompile any remaining invalid PL/SQL modules.

   15. After the compilation completes successfully, you should verify that the number of invalid objects is less than or equal to the number of invalid objects prior to the recompilation. You can use the following query to obtain the number of invalid objects.

       Select count(*)
       From all_objects
       Where status='INVALID';
       

       Also run the following query to make sure you have no more interpreted modules left:

       SELECT plsql_code_type, count(*) 
       FROM dba_plsql_object_settings 
       GROUP BY plsql_code_type;