Oracle SQL
  • LICENSE

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

Posted on March 13, 2013 by Sayan Malakshinov Posted in deterministic functions, oracle, PL/SQL optimization 4 Comments

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:

Spoiler

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();

[collapse]

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);

Result:

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

[collapse]

And now more funny example – Try to guess what will be displayed:

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);
Result and explanation

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

I didn’t expect this, although it very simply explains: ‘IF…ELSE…END IF’ was moved outside from inner loop, so appeared two branches of execution: with optimized empty inner loop and non-optimized loop with function.

[collapse]

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

11.2 11g deterministic functions pl/sql optimization
« Controlling “direct path reads” decision with INDEX_STATS/table_stats
Deterministic function vs scalar subquery caching. Part 3 »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL