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: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 08 Feb 2006 18:47:47 +0000
Message-ID: <7ceku1dmnnocb3v1nsn3dhi9jlp3hrqchr@4ax.com>


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:

      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.

      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.

      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. "

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Received on Wed Feb 08 2006 - 12:47:47 CST

Original text of this message

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