Saturday, March 26, 2011

An exceptional question for the optimizing compiler

I thought about submitting this as a PL/SQL Challenge quiz, but decided it was unfair, undocumented and unhelpful.

Consider the following procedure

create or replace procedure plch_test is
  v_num   number(1);
  v_date  date;
  e_no_day_for_month exception;
  pragma exception_init(e_no_day_for_month, -1839);
  for i in reverse 1 .. 30 loop
    v_date := to_date(i||'-02-2011','dd-mm-yyyy');
    v_num := 10;
  end loop;
  when e_no_day_for_month then
    dbms_output.put_line('Date error');
  when value_error then
    dbms_output.put_line('Num error');

Then I execute it twice with different optimization settings. What error is returned for the executions ?

alter session set plsql_optimize_level =0;
alter procedure plch_test compile;
exec plch_test;

alter session set plsql_optimize_level =2;
alter procedure plch_test compile;
exec plch_test;

In the first case, with no optimization, the "date error" path is taken as there isn't a 30th of February.

In the second case, you may wonder what effect the optimization might have. The assignment of the number 10 to v_num doesn't need to be in the loop. This isn't a cursor loop or a WHILE loop, so the code inside the loop is guaranteed to be executed. If it was executed once prior to entering the loop, it would result in a value_error.

In fact, a date error is still returned. Possibly there is no such optimization. Or possibly it is done in a way that means it is only executed once, in the correct sequence, but is not repeated. Tests shown in AskTom indicate that there is some optimization happening. 

Ultimately, the PL/SQL optimizing compiler is a black box. It would be nice to assume that it wouldn't change the behaviour of the code. But I can't help looking at the procedure and wondering, would it be "wrong" (or more specifically a bug) if an optimization switched it to return a value error.


Filipe Silva said...


Nice post.

First a correction: you forgot the "is" in the create line.
Then a suggestion: use 02 instead of Feb in
'v_date := to_date(i||'-02-2011','dd-mm-yyyy'); so people can test your code without "nls_language" problems.

I would suggest that the optimization is "smart" enought to expect an exception at that point (v_num) so it will not take that out of the loop. I think that it's great that the "funcionality" is the same (as we hope when using different optimizations settings).

Damir Vadas said...

Nice example.

SydOracle said...


Amended as suggested.

My original thought was the code pattern that is

v_found := true;

where you obviously require that flag is only set in the loop.

I tried several variations to see if I could 'break' it (including one which would return the value error in a leap year, but the data error otherwise) and failed.

Whatever optimizations the compiler produces, I think they've done it well.

Anonymous said...

Well, I would have said that the assignment of v_num must always be done in the loop, because the optimizer cannot assume that the statement prior will never raise an exception.

SydOracle said...

Perhaps the mental model of 'in' and 'out' of the loop is invalid. There might be a 'first pass' and a 'subsequent pass' with the optimizations performed for the latter.
All guesswork - unless someone wants to parse the DIANA code.