RE: Native compiled code *much* slower??

From: Mark W. Farnham <>
Date: Thu, 24 Dec 2009 09:21:36 -0500
Message-ID: <>

Without knowing what you are doing, we cannot know whether we are evaluating the same execution path on the same data.

For example, your script could be looping arbitrary long until there is a row to process found in some table, and some asynchronous event could be putting rows in that table. Now while I doubt you would submit something intentionally misleading, we often do not see subtle equivalents to this case in our own code. I (and many before me) call this "code blindness" and it is remarkably common and is often the underlying reason why simply explaining your problem to someone else can solve the problem.

Now, barring a bug, I believe it is true that PL/SQL things like if-then-else, math, loops and the like all have a chance with native to be faster than interpreted and that calls into the database engine to get data should be equivalent.

Bryn Llwellyn (sp?) presented some examples of code running significantly faster native at both Collaborate and OOW. I have not done any serious benchmarking.

Spewing your sample code at the list as per Toon's request might be useful. Since he often yields substantial improvements back when folks asking for help comply with his information requests, I know that in addition to asking why it could make a difference (which I'm not sure is a genuine question in your case), I'd be showing my code to him. I always allow for the possibility that I'm doing something really stupid and that someone else will see the problem immediately without much fuss. Embarassing, maybe, but usually productive, even if it just yields a shift from wondering what to focus on the actual problem.

Showing the code might also reference something that is a known problem. I unfortunately do not know of any current "called by native compiled problems," but I haven't looked.

Summary: Even without changing from native to interpreted the same code could run very differently from run to run. Yes, I've seen what appear to be valid reports of native running much faster.

Good luck. mwf

-----Original Message-----
From: [] On Behalf Of Steve Baldwin
Sent: Thursday, December 24, 2009 3:10 AM To: Toon Koppelaars
Subject: Re: Native compiled code *much* slower??

Hi Toon,

Should it matter? I thought that *everything* was at least as fast native compiled. From the Oracle docs ...

"Because native compilation applies only to PL/SQL statements, a PL/SQL unit that only calls SQL statements might not run faster when natively compiled, but it does run at least as fast as the corresponding interpreted code. The compiled code and the interpreted code make the same library calls, so their action is the same."

I have tried a trivial package and the natively compiled package was marginally faster than interpreted however the package I'm testing here is pretty much all PL/SQL code so I expected it to be significantly faster, not 100x *slower*.

I guess now it's a matter of trying to pull it apart to find out what is causing the slowdown.

Has anyone seen significant improvement using native compilation?



On Thu, Dec 24, 2009 at 6:48 PM, Toon Koppelaars <> wrote:
> What's in: msc$log_p?
> And in sb2.sql?


Received on Thu Dec 24 2009 - 08:21:36 CST

Original text of this message