What is Native Compilation?

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?

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?

• A package body and its spec do not need to be compiled with the same setting for code type. For example, a package body can be compiled natively while the package spec is compiled interpreted.
• Natively compiled subprograms are stored in the database, and the corresponding shared libraries are extracted automatically as needed. You do not need to worry about backing up the shared libraries, cleaning up old shared libraries, and so on.
• The initialization parameters and command setup for native compilation have been simplified. The only required parameter is plsql_native_library_dir . The parameters related to the compiler, linker, and make utility have been obsoleted. The $ORACLE_HOME/plsql/spnc_commands commands file is used for native compilation. It contains commands and options for compiling and linking. here is no longer a dependency on a make utility. The parameters plsql_native_make_utility and plsql_native_make_file_name are no longer needed or supported. plsql_native_library_dir must still be specified like in version 9.0.1 and version 9.2.0. This specifies the full path of the directory where the shared libraries will be generated before being copied to the database.
• Any errors that occur during native compilation are reflected in the USER_ERRORS dictionary view and by the SQL*Plus command SHOW ERRORS.
• Native compilation is turned on and off by a separate initialization parameter, plsql_code_type , rather than being one of several options in the plsql_compiler_flags parameter, which is now deprecated.
• RAC support: PL/SQL native compilation is now supported in RAC environments that do not have a cluster (shared) file system.
• Shared library cleanup: When a natively compiled PL/SQL module is dropped (or recompiled in interpreted mode), the file system copy of the shared library (in the location specified by the plsql_native_library_dir) is now deleted. In RAC configuration, the shared library is deleted from all instances.

Back to top

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

FUNCTION
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY

Back to top

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

PKG__SCOTT__S__54682.so
PKG__SCOTT__B__54683.so

Are the shared dynamically linkable libraries portable?

Back to top

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

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

Back to top

plsql_native_library_subdir_count parameter

plsql_native_library_subdir_count=500

Back to top

plsql_code_type parameter

alter session set plsql_code_type='NATIVE'

alter system set plsql_code_type='NATIVE'

alter session set plsql_code_type='INTERPRETED'

alter system set plsql_code_type='INTERPRETED'

Back to top

Format of the spnc_commands File

Back to top

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

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?

Back to top

How do I change plsql_native_library_dir

Back to top

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

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?

Back to top

Does Oracle Corporation recommend mixing NATIVE and INTERPRETED units?

Back to top

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

Back to top

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

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

• Optionally set up plsql_native_library_subdir_count in the initialization parameter file and create the subdirectories.
• Ensure that the OS path of the C compiler (and if applicable, the linker) used in the spnc_commands file is correct.
• 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

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

• 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';
                            

• 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 

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

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

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

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

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

• Set plsql_code_type to NATIVE in the initialization parameter file.

plsql_code_type=NATIVE

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

• 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';

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

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

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

• Commit the changes after running dbmsupgnv.sql

SQL> commit;

• Shutdown the DB.

• Restart DB in normal mode.

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

• 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;

Back to top