| PL/SQL Native Compilation (NCOMP)
In Oracle Database 10g Rel 1
updated May 4 2004
What is Native Compilation?
How does PL/SQL Native Compilation Work?
What's new in Oracle Database 10g?
What objects can be the subject of PL/SQL Native Compilation?
What is the naming convention for the Shared Libraries (DLLs)?
Are the shared dynamically linkable libraries portable?
Do I need a C compiling environment at the deployment site(s)?
Which Oracle parameters are relevant to native compilation?
Format of the $ORACLE_HOME/plsql/spnc_commands File
In which dictionary table are the shared libraries generated by native compilation stored?
What happens to the shared library on the file system when I drop a NATIVE unit?
How do I change plsql_native_library_dir?
Are there any other circumstances where I need to delete a shared library by hand?
What if I delete a shared library by mistake?
Does Oracle Corp recommend mixing NATIVE and INTERPRETED units?
Do I need to regenerate the native shared libraries on an OS upgrade?
Can I use native mode to save time during application deployment (by shipping pre-generated NCOMP shared libraries)?
Test driving native compilation: Simple Test
Natively Compiling all PL/SQL units in the database
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.
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.
This feature now requires less setup and maintenance.
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
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:
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.Back to top
No, absolutely not! The shared libraries contain platform specific object code.
Note: MCode generated in the interpreted regime is also platform specific.
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
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.
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:
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 .
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:
alter session set plsql_code_type='NATIVE'or,
alter system set plsql_code_type='NATIVE'
alter session set plsql_code_type='INTERPRETED'or,
alter system set plsql_code_type='INTERPRETED'
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.
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)
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.
If you need to change the value for this parameter, the recommend steps are:
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.
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.
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.
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.
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.
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.
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
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;
[To read more about shared library naming conventions, click here.]
select name, type, plsql_code_type from user_plsql_object_settings where name = 'MY_TEST';
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.
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.
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
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.
Select count(*) From all_objects Where status='INVALID';
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.
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 22.214.171.124 to 10.1.0. First run the upgrade scripts ( u0902000.sql) and shutdown the database and run the dbmsupgnv.sql script.
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.
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;