Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Anyone implented compiled PL/SQL?

From: Ellis R. Miller <sartre1_at_comcast.net>
Date: Wed, 28 Jul 2004 10:53:12 -0600
Message-ID: <AOEDLEKLOHPKBBLDFCLAMEGNDOAA.sartre1@comcast.net>


I offer this one disclaimer: no Oracle database application I have worked with has leveraged NCOMP in a production environment. On the other hand, I still give mini-seminars on the benefits of locally managed tablespaces and explain at length to veteran DBA's how STATSPACK doesn't damage the performance of their otherwise crippled database application.

I wouldn't mix and match, by the way. In other words, if your environment prohibits the universal use of NCOMP or your testing proves the performance improvement is negligible I simply wouldn't use it. Despite all of the documentation and other recommendations I have found all or nothing is easiest to administrate.

Again, Oracle 10G makes NCOMP much, much simpler to administrate. Oracle 9i actually had a serious NCOMP bug in the base release. Later, I found it easy to administrate and test in a development environment yet it is still somewhat inconvenient to configure and administrate in Oracle 9i.

Fascinates me to this day how there are those who perceive "advanced" functionality as risky but the flagrant abuse of things such as the use of bind variables, packages, Veritas Quick I/O, etc. as a safe, standard industry practice. Thus, I wouldn't go charging into testing NCOMP at my local IT department as it may incite a riot or a series of heated debates about who has the most seniority and why Johnny wasn't told about Bob wantin' time off for his hip operation...cause we go testin' this NCOMP while we got some of our top talent off at the Mayo and that's how things start to change (the "C" word)...

Ellis

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Anthony Molinaro Sent: Wednesday, July 28, 2004 9:15 AM
To: oracle-l_at_freelists.org
Subject: RE: Anyone implented compiled PL/SQL?

Ellis,
  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?=20

The benchmark I performed was on 9.2.0.1 and 9.2.0.4 on redhat AS 3.0

The procs I used contained mostly:

Regards,

-----Original Message-----
From: Ellis R. Miller [mailto:sartre1_at_comcast.net]=20 Sent: Wednesday, July 28, 2004 12:16 PM
To: oracle-l_at_freelists.org
Subject: RE: Anyone implented compiled PL/SQL?

Mark,

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

        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.

Use the following syntax to set this parameter:



For native compilation mode:

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

        or

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

For interpreted mode:

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

        or

        SQL>alter system set plsql_code_type=3D'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-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark Sent: Wednesday, July 28, 2004 8:48 AM
To: oracle-l_at_freelists.org
Subject: Anyone implented compiled PL/SQL?

Hi,
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?)

Thanks,

-Mark

PS Oracle 9.2.0.5 (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: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Jul 28 2004 - 10:37:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US