RE: Oracle PL/SQL Native Compilation

I've tried this a little bit, and my experiences seem
consistent with some other people I've talked with about
this.

1.	
	Yes, you can have a mix of natively compiled and
non-natively compiled PL/SQL objects.
2.	
	In 10g it is a bit of a hassle to set up because you have
to have a C compiler that your database owner (oracle is
usually the owner on *nix) can execute.  That's because it
translates PL/SQL to C, then to native code.  11g doesn't
have the intermediate code - goes directly to executable,
but I haven't got 11g yet.
3.	
	Native compilation helps the most for code that doesn't hit
the database very much - like math and string manipulation.
4.	
	For database intensive code, like looping through rows of a
cursor, native compilation can actually make performance
worse.
5.	
	Your mileage can and will vary.  I've heard a wide range of
experiences with this.  The best I can say is, "try it
on your own code, on a development system, and see."


________________________________

Chris.RethemeyerRotometrics.com
Sent: Thu 9/18/2008 10:30 AM
To: Multiple recipients of list ODTUG-SQLPLUS-L



I just recently "discovered" that it is possible
to do native compilation of PL/SQL code instead of
interpretted compilation.  Oracle's documentation suggests
that there can be a 10%-30% performance increase by using
native compilation.  The biggest drawback appears to be a
lot slower compilation time (3x-5x?).  (It is also indicated
that PL/SQL debug tools won't work with native
compilations...but I'm not sure that is big issue here.)  I
would be interested to find out what experiences people have
had when either switching to native compilation or
attempting to do so.  Is it worth the extra wait for
compilations to complete?  Have there been other problems
that arise?  Is having a mixture of native and interpretted
code OK or is it better to go all the way?  Our environment
is a 10g database running Oracle E-Business Suite 11.5.10
Applications and quite a bit of custom code. 

Thanks in advance for your input. 
Chris 

------------------------------------------------------------
--------- 
This e-mail message is intended only for the personal use of
the recipient(s) named above. This message is confidential.
If you are not an intended recipient, you may not review,
copy or distribute this message. If you have received this
communication in error, please notify the sender immediately
by e-mail and delete the original message. 
------------------------------------------------------------
--------- 

.