Andy Hassall wrote:
> On Wed, 08 Feb 2006 09:07:40 -0800, DA Morgan <damorgan_at_psoug.org> wrote:
>
>
>>I know ... I know ... but this seems to be worth reporting but I would
>>like someone else to confirm it.
>>
>>SQL> CREATE OR REPLACE PROCEDURE dead_code AS
>> 2 x NUMBER := 10;
>> 3 BEGIN
>> 4 IF x = 10 THEN
>> 5 x := 20;
>> 6 ELSE
>> 7 x := 100; -- dead code (never reached)
>> 8 END IF;
>> 9 END dead_code;
>> 10 /
>>
>>SP2-0804: Procedure created with compilation warnings
>>
>>SQL> show err
>>Errors for PROCEDURE DEAD_CODE:
>>
>>LINE/COL ERROR
>>-------- -------------------------------------------------------------
>>4/8 PLW-06002: Unreachable code
>>7/5 PLW-06002: Unreachable code
>>SQL>
>>
>>This change in behaviour, reporting line 4 as unreachable, did not occur
>>in 10.1.0.4.
>
>
> Interesting - it looks like a result of the new PL/SQL optimiser - so perhaps
> an unexpected result, but not necessarily a bug.
>
> An optimiser capable of realising that line 7 cannot be reached because both
> the value of "x" and the value it's compared to on line 4 are constant can go
> further and completely remove the conditional since it'll never be false.
>
> It seems this is exactly what the optimizer now does. It's transforming the
> source code, but Oracle seems to have a decent go at matching the warnings back
> to the original source - but the optimisation process marks the optimised-away
> conditional as dead code.
>
> Consider: (procedure previously loaded as per your original message):
>
> SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=0;
>
> Session altered.
>
> SQL> alter procedure dead_code compile;
>
> Procedure altered.
>
> SQL> show errors
> No errors.
>
> SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=1;
>
> Session altered.
>
> SQL> alter procedure dead_code compile;
>
> SP2-0805: Procedure altered with compilation warnings
>
> SQL> show errors
> Errors for PROCEDURE DEAD_CODE:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 7/5 PLW-06002: Unreachable code
>
> SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;
>
> Session altered.
>
> SQL> alter procedure dead_code compile;
>
> SP2-0805: Procedure altered with compilation warnings
>
> SQL> show errors
> Errors for PROCEDURE DEAD_CODE:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 4/8 PLW-06002: Unreachable code
> 7/5 PLW-06002: Unreachable code
>
>
> I bet you've got PLSQL_OPTIMIZE_LEVEL=2 (as that's the default). As with
> debugging any compiled language, trying to trace back to line numbers in the
> original source for optimised output presents problems.
>
> Interesting that PLSQL_OPTIMIZE_LEVEL=0 ends up disabling the warnings - now
> _that_ might be a bug as I can't immediately find a reference that says it's
> got to be at least 1 for PLSQL_WARNINGS to work.
>
> Looks like PLSQL_OPTIMIZE_LEVEL=1 is the level to keep it at whilst developing
> and debugging.
>
> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams164.htm#REFRN10255
> "
> PLSQL_OPTIMIZE_LEVEL specifies the optimization level that will be used to
> compile PL/SQL library units. The higher the setting of this parameter, the
> more effort the compiler makes to optimize PL/SQL library units.
>
> Values:
>
> * 0
>
> Maintains the evaluation order and hence the pattern of side effects,
> exceptions, and package initializations of Oracle9i and earlier releases. Also
> removes the new semantic identity of BINARY_INTEGER and PLS_INTEGER and
> restores the earlier rules for the evaluation of integer expressions. Although
> code will run somewhat faster than it did in Oracle9i, use of level 0 will
> forfeit most of the performance gains of PL/SQL in Oracle Database 10g.
>
> * 1
>
> Applies a wide range of optimizations to PL/SQL programs including the
> elimination of unnecessary computations and exceptions, but generally does not
> move source code out of its original source order.
>
> * 2
>
> Applies a wide range of modern optimization techniques beyond those of
> level 1 including changes which may move source code relatively far from its
> original location.
>
> Generally, setting this parameter to 2 pays off in better execution
> performance. If, however, the compiler runs slowly on a particular source
> module or if optimization does not make sense for some reason (for example,
> during rapid turnaround development), then setting this parameter to 1 will
> result in almost as good a compilation with less use of compile-time resources.
> "
Thanks. I've duplicated your test changing the optimizer level and you
seem to be correct. From where I stand, looking at how it performs I'd
say it is (if not a bug) not as helpful as it used to be as I can see
it marking and IF ELSIF ELSIF ELSIF ELSIF ELSE ENDIF as unreachable
just because one element is: Something for me to test this weekend when
what I really want is a pointer to the element(s) that are specifically
unreachable.
Thanks again.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Wed Feb 08 2006 - 17:02:52 CST