How works optimization of loops in PL/SQL in 11g: Part 1. Deterministic functions

As you know, since 10g Oracle can optimize PL/SQL loops with moving code out of loops for reducing number of identical code executions.
If you have not read yet about it, I advise you to first read these two articles:
1. “PLSQL_OPTIMIZE_LEVEL: The optimization strategy of Oracle” by Saurabh K. Gupta
2. “PL/SQL optimisation in 10g” by Adrian Billington

But since 11g Oracle also can optimize code with deterministic functions too. For this to happen, code must meet the following conditions:
1. PLSQL_OPTIMIZE_LEVEL greater or equal 2
2. Parameters should not be changed in the loop body. Strictly speaking, there should not be any assignment of parameters.
3. Should not be any implicit conversions of parameters in function call, i.e. all variables/literals must be the same type as input parameters declared.
4. Should not be any call of non-deterministic functions (except some standard sql functions like to_date, to_char, nvl) or procedures in the loop

Note that this rules concerns only same scope level as this loop and not inner loops or another blocks.

For example we have code like this:

...
for i in 1..100 loop
  n:=deterministic_function(c);
end loop;
...

With enabled optimization oracle will rearrange this code to:

...
for i in 1..100 loop
  null;
end loop;
n:=deterministic_function(c);
...

We can simply see it with this test case:

create or replace function xt_print_deterministic(p varchar2) return varchar2 deterministic as
begin
  dbms_output.put_line(p);
  return p;
end;
/
create or replace procedure p_test_deterministic as
  v varchar2(10);
begin
  for i in 1..3 loop
    v:=xt_print_deterministic('before');
    for j in 1..10 loop
      v:=i;
      v:=xt_print_deterministic('1');
    end loop;
    v:=xt_print_deterministic('after');
  end loop;  
end;
/
set serverout on;
call p_test_deterministic();

After execution it printed:

before
after
1
after
1
after
1

Note that:
1. ‘before’ printed once at start – it because this line was moved out from outer loop and was executed before it.
2. ‘After’ printed three times, because optimization of outer loop was stopped on inner loop.
3. ‘1’ printed only three times, because it was moved out from inner cycle and added to end of outer loop.

But see what will happen if we change parameter from varchar2 literal ‘1’ to number literal 1:

create or replace function xt_print_deterministic(p varchar2) return varchar2 deterministic as
begin
  dbms_output.put_line(p);
  return p;
end;
/
create or replace procedure p_test_deterministic as
  v varchar2(10);
begin
  for i in 1..3 loop
    v:=xt_print_deterministic('before');
    for j in 1..10 loop
      v:=i;
      v:=xt_print_deterministic(1);
    end loop;
    v:=xt_print_deterministic('after');
  end loop;  
end;
/
set serverout on;
call p_test_deterministic();

before
after
1
1
1
1
1
1
1
1
1
1
after
1
1
1
1
1
1
1
1
1
1
after
1
1
1
1
1
1
1
1
1
1

Optimization of inner loop was disabled, because of implicit conversion, as i said above.

Now change ‘after’ to ‘after’||v:

create or replace procedure p_test_deterministic as
  v varchar2(10);
begin
  for i in 1..3 loop
    v:=xt_print_deterministic('before');
    for j in 1..10 loop
      v:=i;
      v:=xt_print_deterministic('1');
    end loop;
    v:=xt_print_deterministic('after: '||v);
  end loop;  
end;
/

Let’s see what we have got:

before
1
after: 1
1
after: 1
1
after: 1

Everything was the same, but order was changed! It is a quite easy to explain: in the previous example, the order did not matter, because it has no effect to anything. But now oracle knows that result of execution in inner loop is needed for the line after inner loop. So this line moved directly after the loop and before the remaining code of the outer loop.

Previously i showed examples only with literals. Let’s see that this works also with variables:

SQL> create or replace procedure p_test_deterministic as
  2    v varchar2(10);
  3    p varchar2(10);
  4  begin
  5    for i in 1..3 loop
  6      v:=xt_print_deterministic('before');
  7      p:=i;
  8      for j in 1..10 loop
  9        v:=i;
 10        v:=xt_print_deterministic(p);
 11      end loop;
 12      v:=xt_print_deterministic('after');
 13    end loop;
 14  end;
 15  /

Procedure created.

SQL> exec p_test_deterministic();
before
after
1
after
2
after
3

PL/SQL procedure successfully completed.

Example showing that optimization will be disabled if exists non-deterministic function in scope:

create or replace function xt_print_deterministic(p varchar2) return varchar2 deterministic as
begin
  dbms_output.put_line(p);
  return p;
end;
/
create or replace procedure p_test_deterministic(p in varchar2) as
  v varchar2(30);
begin
  for i in 1..3 loop
    v:=xt_print_deterministic('before');
    for j in 1..10 loop
      v:=xt_print_deterministic(p);
      v:=sys_context('userenv','sid');
    end loop;
    v:=xt_print_deterministic('after');
  end loop;  
end;
/
set serverout on;
exec p_test_deterministic(1);

before
1
1
1
1
1
1
1
1
1
1
after
before
1
1
1
1
1
1
1
1
1
1
after
before
1
1
1
1
1
1
1
1
1
1
after

create or replace function xt_print_deterministic(p varchar2) return varchar2 deterministic as
begin
  dbms_output.put_line(p);
  return p;
end;
/
create or replace procedure p_test_deterministic(p in varchar2) as
  v varchar2(30);
  a varchar2(30);
begin
  for i in 1..3 loop
    v:=xt_print_deterministic('before');
    for j in 1..10 loop
      v:=xt_print_deterministic(p);
      if p='1' then 
        a:=sys_context('userenv','sid');
      else 
        a:='1';
      end if;
    end loop;
    v:=xt_print_deterministic('after');
  end loop;  
end;
/
set serverout on;
exec p_test_deterministic(1);
exec p_test_deterministic(2);

SQL> exec p_test_deterministic(1);
before
1
1
1
1
1
1
1
1
1
1
after
before
1
1
1
1
1
1
1
1
1
1
after
before
1
1
1
1
1
1
1
1
1
1
after

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> exec p_test_deterministic(2);
before
2
after
before
2
after
before
2
after

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

Instead of conclusion:
I’m absolutly agree with what Steven Feuerstein said:

“So at this point, it looks that the applicability of this new optimization will be fairly narrow.”