RE: Anyone implented compiled PL/SQL?
- From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Jul 2004 11:14:48 -0400
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:
- sql
- tons of string parsing
- associative arrays
Regards,
- ant
-----Original Message-----
From: Ellis R. Miller [mailto:sartre1@xxxxxxxxxxx]=20
Sent: Wednesday, July 28, 2004 12:16 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark
Sent: Wednesday, July 28, 2004 8:48 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
