Skip to Main Content
  • Questions
  • PLSQL_OPTIMIZE_LEVEL 3 produces wrong function result

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Peter.

Asked: February 22, 2018 - 10:06 am UTC

Answered by: Chris Saxon - Last updated: June 04, 2020 - 7:51 am UTC

Category: SQL - Version: 11.2

Viewed 1000+ times

You Asked

We looked into tuning our PL/SQL codebase and tested with all our packages compiled with PLSQL_OPTIMIZE_LEVEL 3.

At first glance everything worked, but then our code started to act weird. After some investigation we found out, that PLSQL_OPTIMIZE_LEVEL = 3 corrupted some functions.

Check out a simple example, where the inline function produces a wrong result: https://livesql.oracle.com/apex/livesql/s/ga8hkmbh6mxq5rpuw0tp5qnph

It seems that l_test is being removed by the optimizer, because it isn't used.

Once we use l_test, the same function starts to produce correct results, as can be seen in this script: https://livesql.oracle.com/apex/livesql/s/ga8hkmbhafmqu4ilznduare5x

Unfortunately I wasn't able to find any good documentation on what optimize-level 3 does. So we don't know if there are other side-effects.

Is this a known behaviour? Which other things we have to watch out for?

with LiveSQL Test Case:

and we said...

The issue here is to do with subprogram inlining.

With optimize level 3, the PL/SQL compiler will inline subprograms. This opens up further optimizations. Which in this case appears to be causing it to factor out the assignment to l_test:

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;
ALTER SESSION SET PLSQL_CODE_TYPE      = 'NATIVE';

CREATE OR REPLACE PROCEDURE RAGTEST 
AS 
    FUNCTION is_nls_date 
      ( p_string        IN VARCHAR2 
      , p_format_mask   IN VARCHAR2 
      ) 
    RETURN NUMBER 
    AS 
        l_test   DATE; 
        l_return NUMBER := 0; 
    BEGIN 
        BEGIN 
            l_test   := TO_DATE(p_string, ''''||p_format_mask||''''); 
            l_return := 1; 
        EXCEPTION 
            WHEN OTHERS THEN 
                l_return := 0; 
        END; 
        -- 
        RETURN l_return; 
    END is_nls_date; 
BEGIN 
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.xxxx = '||is_nls_date('01.01.xxxx','DD.MM.YYYY')); 
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.2001 = '||is_nls_date('01.01.2001','DD.MM.YYYY')); 
END; 
/
sho err

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit RAGTEST omitted optional AUTHID clause; default value DEFINER used
3/5      PLW-06006: uncalled procedure "IS_NLS_DATE" is removed.
16/18    PLW-06009: procedure "IS_NLS_DATE" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
23/5     PLW-06005: inlining of call of procedure 'IS_NLS_DATE' was done
24/5     PLW-06005: inlining of call of procedure 'IS_NLS_DATE' was done


Besides lowering the optimization level, you could stop this with the inline pragma. Set inlining of is_nls_date to NO

CREATE OR REPLACE PROCEDURE RAGTEST 
AS 
    FUNCTION is_nls_date 
      ( p_string        IN VARCHAR2 
      , p_format_mask   IN VARCHAR2 
      ) 
    RETURN NUMBER 
    AS 
        l_test   DATE; 
        l_return NUMBER := 0; 
    BEGIN 
        BEGIN 
            l_test   := TO_DATE(p_string, ''''||p_format_mask||''''); 
            l_return := 1; 
        EXCEPTION 
            WHEN OTHERS THEN 
                l_return := 0; 
        END; 
        -- 
        RETURN l_return; 
    END is_nls_date; 
BEGIN 
    pragma inline (is_nls_date, 'NO');
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.xxxx = '||is_nls_date('01.01.xxxx','DD.MM.YYYY')); 
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.2001 = '||is_nls_date('01.01.2001','DD.MM.YYYY')); 
END; 
/
sho err

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit RAGTEST omitted optional AUTHID clause; default value DEFINER used
16/18    PLW-06009: procedure "IS_NLS_DATE" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
24/5     PLW-06008: call of procedure 'IS_NLS_DATE' will not be inlined
25/5     PLW-06005: inlining of call of procedure 'IS_NLS_DATE' was done

exec ragtest;

is_date 01.01.xxxx = 0
is_date 01.01.2001 = 1

select plsql_optimize_level 
from   user_plsql_object_settings
where  name = 'RAGTEST';

PLSQL_OPTIMIZE_LEVEL   
                     3


Or you could stop using when others handlers to return a failure value ;)

and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

hopes for the future

February 26, 2018 - 8:43 am UTC

Reviewer: Racer I.

Hi,

There may be hope for this specific problem*) in 12.2 :

VALIDATE_CONVERSION(data AS DATE, 'DD-MON-YYYY')

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/VALIDATE_CONVERSION.html#GUID-DC485EEB-CB6D-42EF-97AA-4487884CB2CD

*) in short : Oracle needed true IS-functions since forever. So users write their own conversion tests with manually catching and suppressing (WHEN OTHERS) conversion exceptions. In this case also the optimizer removed unused assignments, that were only used because To_Date is a function and thereby removed the (needed) side effect of the exception. Question : why didn't Oracle remove the assignment without inlining too? It's just as apparently useless there.

regards,
Chris Saxon

Followup  

February 26, 2018 - 1:28 pm UTC

Yep, 12.2 also has:

to_date(:str default null on conversion error, '<your format>')

Isn't that a bit dangerous?

February 26, 2018 - 11:34 am UTC

Reviewer: Peter Raganitsch from Vienna, Austria

Thanks for confirming the behaviour of PLSQL_OPTIMIZE_LEVEL 3.

I'm in a situation where we have roughly 80k lines of code. We tried level 3 to see if we can achieve some performance gains.
Now that there is a change in behaviour between level 2 and level 3 we can't use it, because we would have to review all our code for similar patterns.

Side-question: how would you test a string for being a date convertible with a given format mask, without simply converting TO_DATE and catching the exception?
Chris Saxon

Followup  

February 26, 2018 - 1:40 pm UTC

Well you could argue that using when others to say a value isn't a date is a bit dangerous too ;)

e.g. if there's an IO error or some other exception unrelated to date conversion, you'll be told it's not a date, even if it is...

12.2 has a couple of alternatives (see review above). Before then you could avoid the inlining issue by returning the date if you can convert it and null otherwise:

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;
ALTER SESSION SET PLSQL_CODE_TYPE      = 'NATIVE';

CREATE OR REPLACE PROCEDURE RAGTEST 
AS 
    FUNCTION is_nls_date 
      ( p_string        IN VARCHAR2 
      , p_format_mask   IN VARCHAR2 
      ) 
    RETURN DATE 
    AS 
        l_return date;
        format_mismatch exception;
        pragma exception_init ( format_mismatch, -1861);
    BEGIN 
        BEGIN 
            l_return   := TO_DATE(p_string, ''''||p_format_mask||''''); 
        EXCEPTION 
            WHEN format_mismatch THEN 
                l_return := null; 
        END; 
        -- 
        RETURN l_return; 
    END is_nls_date; 
BEGIN 
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.xxxx = '||is_nls_date('01.01.xxxx','DD.MM.YYYY')); 
    DBMS_OUTPUT.PUT_LINE('is_date 01.01.2001 = '||is_nls_date('01.01.2001','DD.MM.YYYY')); 
END; 
/
sho err

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit RAGTEST omitted optional AUTHID clause; default value DEFINER used
3/5      PLW-06006: uncalled procedure "IS_NLS_DATE" is removed.
23/5     PLW-06005: inlining of call of procedure 'IS_NLS_DATE' was done
23/51    PLW-06025: implicit use of NLS session parameters may be a security risk
24/5     PLW-06005: inlining of call of procedure 'IS_NLS_DATE' was done
24/51    PLW-06025: implicit use of NLS session parameters may be a security risk

exec ragtest;

is_date 01.01.xxxx = 
is_date 01.01.2001 = 01-JAN-2001 00:00:00

September 20, 2018 - 8:58 am UTC

Reviewer: A reader

This has to be a bug surely - has it been raised?
Connor McDonald

Followup  

September 21, 2018 - 1:03 am UTC

I agree, and I'll raise one

Bug number

August 14, 2019 - 4:06 pm UTC

Reviewer: João Borges Barreto from Portugal

Could you provide the bug id so that we may follow it on MOS? All my searches were without luck or to old bugs providing a "do not use it" as the workaround.
Connor McDonald

Followup  

August 15, 2019 - 2:13 am UTC

Sorry I should have reported back on this.

I did raise it, but it was rejected, because even in the docs we state:

"12.1 PL/SQL Optimizer

In even rarer cases, PL/SQL might raise an exception earlier than expected or not at all."

OTHERS handler warning

June 04, 2020 - 3:55 am UTC

Reviewer: Scott Wesley from Perth, AU

I'm not sure if this digresses from the original question, but I arrived here when searching for PLW-06009 with validation_conversion.

Turns out both of these examples raise that PL/SQL warning.

create or replace procedure vc_test is
  l_res date;
begin
  l_res := to_date('01-0z-2001' default null on conversion error, 'dd-mm-yyyy');
end;
/


create or replace procedure vc_test is
  l_result number;
begin
  l_result := validate_conversion('01-01-2001' as date, 'dd-mm-yyyy');
end;
/

LINE/COL  ERROR
--------- -------------------------------------------------------------
6/12      PLW-06009: procedure "VC_TEST" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR


Is this expected?
Chris Saxon

Followup  

June 04, 2020 - 7:51 am UTC

This is already logged as BUG 29792731.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.