RE: Oracle PL/SQL Native Compilation

Thread: RE: Oracle PL/SQL Native Compilation

United States 2008-09-19 09:40:40

Paul,

I am using associative arrays for some of this, but they only buy me an advantage when I can lookup by direct match, and much of what I have to do doesn't fit that scenario. I have to find records with values that either fall within a range, out of a range, greater, less, etc. I use SQL to help with some of that, but am limitted to how much I really can rewrite of the app at this point.  (Phase 1 versus Phase 2 where I get to re-architect things more.)

My initial thought was to try and load arrays and then CAST them to tables so that I could use SQL against the array content, but the hoops that have to be jumped through to get that working make that approach rather unattractive. That's what got me thinking about the GTT approach. It keeps things pretty much within the original app architecture and I was hoping the performance would be helped. (One of the guys here actually ran a comparison of accessing data in arrays cast as tables versus the same data in GTT...and the GTT approach was quite a bit faster.)

I know the core architecture of the original app is not optimal (and my Phase 1 will not be either). I did get to restructure the app's tables so that I at least have normalized data to work with now. In Phase 2 I will get to restructure things in the algorithm. Management wants something running "just like the VB app" so that we can get the VB app off the system and THEN have time to restructure. (sigh...)

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

fatcity.com

09/18/2008 05:40 PM

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

[1]

about | contact Other archives ( Real Estate discussion Medical topics )