| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anyone implented compiled PL/SQL?
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=
.=
=
Pete
=
"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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] =
On Behalf Of Anthony Molinaro
Sent: Thursday, 29 July 2004 1: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?=3D20
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]=3D20
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?
plsql_native_library_dir parameter
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:
SQL>alter session set plsql_code_type=3D3D'NATIVE'
or
SQL>alter system set plsql_code_type=3D3D'NATIVE'
For interpreted mode:
SQL>alter session set plsql_code_type=3D3D'INTERPRETED'
or
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-----
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, MIReceived on Wed Jul 28 2004 - 14:09:54 CDT
"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 -----------------------------------------------------------------
 |
 |