Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 10.2 Bug?

Re: 10.2 Bug?

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 08 Feb 2006 15:02:52 -0800
Message-ID: <1139439765.143316@jetspin.drizzle.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US