|
List Info
Thread: RE: Oracle PL/SQL Native Compilation
|
|
| RE: Oracle PL/SQL Native Compilation |
 
United States |
2008-09-19 10:00:43 |
|
|
Chris,
Clearly if you have a range query on an
indexed column the GTT will be faster if there are lots of rows.
Setting up a cursor for loop and doing row
by row compares will be slower in the GTT than looping through the
array.
Not sure that casting will buy you anything
for performance in this case. ; I am not optimistic.
In our experence, "quick and dirty"
refactoring is usually more "dirty" than "quick". We had a 6000 line Java
routine to refactor. We had to do a serious mod to the rules in the
algorithm, so we threw it out and started from scratch. The result was a
200 line PL/SQL routine that ignored the Java algorithm completely. My
point is that it may take LESS time to do it right and intelligently than
to blindly rehost.
Paul Dorsey
Dulcian, Inc.
732 744 1116 x110
From: ml-errors fatcity.com on behalf of
Chris.RethemeyerRotometrics.com
Sent: Fri 9/19/2008 10:40
AM
To: Multiple recipients of list ODTUG-SQLPLUS-L
Subject:
RE: Oracle PL/SQL Native Compilation
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-errorsfatcity.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 )
|