Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anyone implented compiled PL/SQL?

RE: Anyone implented compiled PL/SQL?

From: Pete Sharman <>
Date: Thu, 29 Jul 2004 05:05:05 +1000
Message-Id: <>

Not surprising. IIRC, the performance improvement figures are largely arou= nd things that use PL/SQL lots (number crunching and so on), not SQL per se= .=



"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook

"Oh no, it's not. It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA

-----Original Message-----
From: [] = On Behalf Of Anthony Molinaro
Sent: Thursday, 29 July 2004 1:15 AM
Subject: RE: Anyone implented compiled PL/SQL?

  That's interesting that you mentioned the 30 - 40% improvement. I benchmarked ncomp for 10 days (albeit, using procs and functions that contain mostly sql with some object type manipulations ) and saw almost no performance improvement.

Also, adding to what you mentioned about mixing interpreted and ncomp'ed procs, I ran into several errors when using UDTs that were interpreted and referenced by a proc/func that was ncomp'd.

What are you doing in your code that provides such a huge performance gain? Is it mostly pl/sql with very little sql references?=3D20

The benchmark I performed was on and on redhat AS 3.0

The procs I used contained mostly:


-----Original Message-----
From: Ellis R. Miller []=3D20 Sent: Wednesday, July 28, 2004 12:16 PM
Subject: RE: Anyone implented compiled PL/SQL?


I have used Oracle 9i NCOMP on Solaris 9 as well as Oracle 10G NCOMP on Fedora (Linux). It provides anywhere from a 30 to 40% improvement in application response time depending on the intensity of the application stored procedures, etc. Nevertheless, it is always faster. However, there are some caveats regarding native compilation of only some stored procedures, functions, etc. that are natively compiled by other stored procedures, for example, that are not. In general, don't do it, that is, either group those that are natively compiled, logically, or natively compile ALL applicaton PL/SQL.

There used to be a study, "C, Java, and PL/SQL: A language platform suitability study" comparing Java, Java (NCOMP), PL/SQL, PL/SQL (NCOMP), and C relative performance. The latter being the fastest, of course, but PL/SQL
(NCOMP) being a "close" second and significantly faster than PL/SQL. However, the link is currently dead.

On a final note, the Oracle 10G NCOMP is much easier to configure: do not require an external c compiler, such as gcc, or make utility. In essence, only have to set one parameter. Here are some really basic notes on NCOMP in Oracle 10G:

Which Oracle parameters are relevant to native compilation?

  1. plsql_native_library_dir -- create directory to hold shared libraries
  2. plsql_native_library_subdir_count -- optional 3. plsql_code_type -- set at the database level or session

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


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


        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.

Use the following syntax to set this parameter:

For native compilation mode:

        SQL>alter session set plsql_code_type=3D3D'NATIVE'


        SQL>alter system set plsql_code_type=3D3D'NATIVE'

For interpreted mode:

        SQL>alter session set plsql_code_type=3D3D'INTERPRETED'


        SQL>alter system set plsql_code_type=3D3D'INTERPRETED'

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

SQL> connect / as sysdba;
SQL> describe ncomp_dll$;

Name Null? Type
------------ -------------- --------

OBJ#	        NOT NULL          NUMBER
VERSION		          	  NUMBER
DLL			          	  BLOB
DLLNAME		          	  RAW(1024)

-----Original Message-----
[]On Behalf Of Bobak, Mark Sent: Wednesday, July 28, 2004 8:48 AM
Subject: Anyone implented compiled PL/SQL?

As part of our move to 9i, we're looking at various features, and I'm wondering about compiled PL/SQL. Does anyone have any experience with it? Any major issues or problems? Is there any gold at the end of the rainbow? (Does it actually provide noticeable performance improvement?)



PS Oracle (64-bit) on Solaris 8.

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI

"On two occasions, I have been asked [by members of Parliament], "Pray,
Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." -- Charles Babbage (1791-1871) ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: ---------------------------------------------------------------- To unsubscribe send email to: put 'unsubscribe' in the subject line. -- Archives are at FAQ is at ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: ---------------------------------------------------------------- To unsubscribe send email to: put 'unsubscribe' in the subject line. -- Archives are at FAQ is at ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: ---------------------------------------------------------------- To unsubscribe send email to: put 'unsubscribe' in the subject line. -- Archives are at FAQ is at ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: ---------------------------------------------------------------- To unsubscribe send email to: put 'unsubscribe' in the subject line. -- Archives are at FAQ is at -----------------------------------------------------------------
Received on Wed Jul 28 2004 - 14:09:54 CDT

Original text of this message