RE: Oracle PL/SQL Native Compilation

Chris,
 
If you are doing lots of spinning through record sets over
and over, I think you are right that you may indeed be
getting hammered by the PL/SQL.
Years ago, I compared loops in PL/SQL to C++ and found that
C++ was about 1000 times faster.  I have not done the test
recently so the results may no longer be valid.
 
My question to you is: are you sure that the code is written
well?  I have no idea what your application is, but my
experience has been that applications that do lots of
looping through record sets can usually be improved by
moving much the work in the SQL that reads the data in the
first place or perhaps by using associative arrays so you
can do direct lookups rather than "loop and look".
I would ask the question if the core algorithm of the
program you are rehosting is sound.
 
Looping through arrays will be faster than looping through
GTTs, so that will not help.
 
Paul Dorsey
Dulcian, Inc.
pdorseydulcian.com
732 744 1116 x110
 

________________________________

Chris.RethemeyerRotometrics.com
Sent: Thu 9/18/2008 6:45 PM
To: Multiple recipients of list ODTUG-SQLPLUS-L



Hi Paul, 

The app that has me considering this option is one that is
dealing with a lot of associative arrays.  I was curious to
find out from the community if there were any potential
issues with native compilation that weren't already covered
in Oracle's documentation. 

I am converting an app written in VB6 (that accesses Oracle)
into code residing in the database.  Phase 1 is to get the
functionality converted and working.  Phase 2 is to
re-architect the application.  The VB code utilizes a lot of
ADO recordsets which translate pretty well into either
cursors or associative arrays.  Even after initial tuning
things are a bit slow.  I'm thinking that the native
compilation could help on the code that uses the arrays.  (I
may also try to migrate the associative arrays to global
temp tables as another option.)   It's been an interesting
project... 

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. 
------------------------------------------------------------
--------- 

. 




"Paul Dorsey" <pdorseydulcian.com> 
Sent by: ml-errorsfatcity.com 

09/18/2008 10:45 AM 
Please respond to
ODTUG-SQLPLUS-Lfatcity.com


To
Multiple recipients of list ODTUG-SQLPLUS-L
<ODTUG-SQLPLUS-Lfatcity.com> 
cc
Subject
RE: Oracle PL/SQL Native Compilation	

		




Chris,

Don't expect to see your app run faster.  This only helps if
your time is being consumed by PL/SQL (actually a rather
rare source of performance lag).

This will not help your SQL, files and DB IO, or network
traffic.

If you think it might help, of course do not use it in
development, only in final testing and prod.

Paul Dorsey
Dulcian, Inc.
pdorseydulcian.com
732 744 1116 x110


________________________________

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. 
------------------------------------------------------------
--------- 

.