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