pl/sql code writing approach
A reader, September 29, 2008 - 1:21 pm UTC
hello,
thanks a lot for answering my question.
it's not, that i am bothering right off. our application will go developmet-testing-production. during the stress testing (like it happened last time) - i may be asked to look at the (not acceptable) performance. so i'll go through few tkprof/trace files, make little (ususally sql) changes, try again....
making those little changes (and observing what's going on), i made my 'personal observations' - code is big and in essence consists of what i call 'database work' and 'processing work'.
now it's somewhat showing that i can spend quite a few iterations going through database work code (not to mention that the recipient of the software - customer could have different dba/db settings/processing needs which affect the performance).
the fact, that there's the 'processing code' just got stuck in the back of my head.
i was thinking, that native compilation (for 'processing code') may provide 'peace of mind' - meaning that code (may be writen well) is natively compiled = there's "no place in it" to improve it, which will essentially eliminate this part as something to (even) think about when improving the performance.
your suggestion for native compilation:
a) use it for all code
b) not use it
is in a such way, that it brings another question - would you use a) because there are some benefits to have ALL code natively compiled (not taking into account maintenance of different compiler settings for different pieces of the code)?
also, would the extensive usage of oracle object types be a factor in the decision?
thanks again.
September 29, 2008 - 1:27 pm UTC
there is some overhead involved in non-native code invoking native code and vice versa.
Since the amount of work to do "one unit" and "all of the code" is identical (you have to compile everything and if you set up native compilation for that one unit, you have it set up for all) - either do it all or none would be my only comment.
"some overhead"
A reader, September 29, 2008 - 2:34 pm UTC
hello,
i am posting this as a review, since it directly relates to your follow-up (please remove the posting if it does violate asktom rules).
with respect to your note: "there is some overhead involved in non-native code invoking native code and vice versa.",
could you elaborate on that? (meaning: is it comparable to switching between sql and pl/sql or is it overhead of the different nature)?)
thanks.
September 29, 2008 - 2:59 pm UTC
There is a small, slight, negligible, hard to measure, tiny overhead.
I'm going to be very consistent here, either
compile none of it natively
or all of it.
nothing else really seems to make sense.
What about 9i and wrapped code
Hansp, October 13, 2008 - 6:04 am UTC
Hello Tom,
You only mention versions 10g and 11g.
What about native code in 9i?
I am currently investigating a performance issue on 9i.
The code is wrapped. So I cannot see its contents.
Can we convert a wrapped package to native code?
Regards Hans-peter
October 14, 2008 - 4:51 pm UTC
9i would be the same as 10g.
Native compilation would be the DEAD LAST STEP in performance tuning - DEAD LAST. If the code is wrapped - you cannot 'tune' the code really, you might be able to analyze the sql it does, but you cannot really affect the code - so your chance of tuning the code is near zero.
I would not give serious consideration to native compilation here, there is little you can do short of making the sql execute faster (indexing, materialized views, using different structures like Index organized tables, clusters and the like to make the sql more efficient...)
Hansp, October 15, 2008 - 2:49 am UTC
Ok thanks.
I understand what you mean.
But the package has to do with all kinds of string manipulations and has almost no sql code in it.
So it might have a slight chance.
Regards Hans-Peter
October 15, 2008 - 5:28 pm UTC
doubt it. And even if so, single digit percents - which is probably not what you are looking for.
Unless and until you have access to the code, real tuning is simply "not possible"
Schema Level
A reader, April 15, 2011 - 2:50 pm UTC
Hi Tom,
Regarding your statement:
"Turning native compilation on/off would probably be a schema level decision for me"
I have these questions relating to native compiliation in 11gR2
1) Do you NOT recommend turning on native compilation at DB level? (using alter system set plsql_code_type=native)
2) If the answer to above is YES, is it "OK" if the application schema packages are natively compiled but system packages are not?
Thanks as ever for your help.
April 15, 2011 - 5:01 pm UTC
1) probably not, I'd turn it on for what I wanted it on for - it is typically a "micro-optimmization"
2) yes, it is OK.
Native code data manupulation
krishnarjun rao, August 14, 2013 - 9:56 am UTC
Hi Tom,
Thanks for knowledge sharing.
Will native compiled procedure with lots of Inserts, Updates and Deletes give any performance improvement? Or Should we use this native compilation for the procedures which contains more logic?
Thanks in advance.
August 14, 2013 - 3:21 pm UTC
probably not, if most of the work is done in SQL, natively compiling the plsql won't have much of an effect.
you would need something where most of the time is spent in plsql in order to see a benefit from natively compiling the plsql code.
A reader, August 15, 2013 - 11:10 am UTC
In the past when I've mixed some type and packages being native and some being interpreted I'd get a ORA-06508 PL/SQL: could not find program unit being called. There are no errors in DBA_ERRORS when this happens. When I recompiled them all the same way they can all resolve the referenced code.
Could this be why in the PL/SQL Reference Guide (11R2 pg 12-52) they suggest if you're going to use native compilation you should set it at the database level instead of for schemas like you're suggesting or should this not be a problem?
August 28, 2013 - 4:39 pm UTC
this should not be a problem.
their suggest (in the docs) is to either user 100% native (get everything compiled, remove any overhead of non-compiled calling compiled calling non-compiled) or have everything in byte-code (interpreted). It is purely a performance thing.
but if you have one really cpu-intensive piece of code that runs for seconds now, just compiling that natively (assuming it doesn't spend much time in SQL) would provide direct benefit for that.
and it should not result in a 6508.
How about Regex ?
Rajeshwaran, August 30, 2013 - 5:50 pm UTC
but if you have one really cpu-intensive piece of code that runs for seconds now, just compiling that natively (assuming it doesn't spend much time in SQL) would provide direct benefit for that.
Tom, With reference to your above quote say if I have a plsql unit that does lots of regex stuff ( which will consume more cpu ) does Native compilation helps here ?
September 04, 2013 - 6:28 pm UTC
nope, regexp is a builtin function.
it is already in C