Re: Unexplainable: "Beautified" code runs slower

userjohn wrote:
> 10gR2 EE (10.2.0.3.0) /Solaris
>
> I have run into the biggest "mystery" in my 7 years of PL/SQL programming -
> my inability to even GUESS is so unacceptable to me it is driving me up the
> walls.
>
> So I inherited this stored procedure (in pkg), it is very "ugly" - code in
> mixed cases , badly formatted, excess spaces here and there, lines out of
> alignment etc - you get the idea. I'd guess the original code was done in a
> text editor.
>
> So I "beautified" the code (in IDE tool "PL/SQL Developer") -
> WITHOUT CHANGING A SINGLE BYTE OF THE ACTUAL CODE.
>
> Mystery: Pretty code is nearly 6 times slower - I tested back and forth for
> the entire weekend.
> (no other users)
> Result is consistent: "beautified" code is ALWAYS 6 times slowers.
>
> Someone please shed some light on what might be going on.

I can think of at least two things that might have changed.

1. The code itself - you say it hasn't but is that actually true.

difficult for us to tell without seeing either bit.

2. The included sql statements have changed (the whitespace, capitilisation etc have changed) and so have been reparsed generating new plans.

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info/services