Nuances of Deterministic Functions in PL/SQL (9588)

The 23 November quiz tested your knowledge of the little-known fact that as of Oracle Database 11g, the compiler will optimize the execution of deterministic functions in PL/SQL blocks. Previously, defining a function with the DETERMINISTIC clause led to optimizations when the function was called inside an SQL statement, but not in PL/SQL code itself.

_Nikotin, a devoted and high-ranking PL/SQL Challenge player, took the quiz as a launch point for an in-depth analysis of this optimization and came up with several interesting conclusions:

1. The optimization only occurs when the values passed as arguments to the function are compile-time constants, such as 1 or 'abc' or true or even TO_NUMBER('123') when the optimization level is set to at least 2. If you pass to one of the IN arguments an expression that cannot be determined at compile time to be a constant , no optimization occurs. Makes perfect sense, as this optimization occurs at compile time.

2.The optimization does not seem to occur outside of the context of a loop. In other words, calling the function N times with the same compile-time static arguments explicitly in your code results in the body of the function being executed N times.

3. Furthermore, if the loop contains non-deterministic code (that is, it is not simply calling deterministic functions with compile-time static constants), then the optimization also does not occur.

So at this point, it looks to _Nikotin and I that the applicability of this new optimization will be fairly narrow. Perhaps over time, the PL/SQL team will extend the circumstances under which the optimization will occur.

You will find below some details reinforcing the above points. In addition, I have attached a file to the list of resources for this quiz to demonstrate circumstances of optimization.

Cheers, Steven

DBMS_PROFILER Demonstrates No Optimization Outside of Loop (from _Nikotin)

def proc_name = PLCH_TEST_LOOPS
def func_name = PLCH_TEST_DETERM

create or replace function &func_name
(
  i varchar2
) return varchar2
  deterministic is
begin
  return i;
end;
/

create or replace procedure &proc_name is 
  res varchar2(1);
begin
  res := &func_name('a');
  res := &func_name('a');
  res := &func_name('a');
  res := &func_name('a');
  res := &func_name('a');
  res := &func_name('a');
  res := &func_name('a');
  res := &func_name('a');
  res := &func_name('a');
  res := &func_name('a');
end;
/

var res number
exec :res := dbms_profiler.start_profiler('&proc_name');

exec plch_test_loops;
exec :res := dbms_profiler.stop_profiler;


col text format a60
set pagesize 100

select s.text, d.total_occur
from plsql_profiler_data d
   , user_source s
   , plsql_profiler_units u
where d.runid =
      (
        select max(runid) runid
        from plsql_profiler_runs
        where run_comment = '&proc_name'
      )
  and d.runid = u.runid
  and u.unit_name in ('&proc_name', '&func_name')
  and d.unit_number = u.unit_number
  and s.name = u.unit_name
  and s.line = d.line#
order by u.unit_number, d.line#;

And the results:

TEXT                                        TOTAL_OCCUR
------------------------------------------- -----------
procedure PLCH_TEST_LOOPS is                                           0
  res := PLCH_TEST_DETERM('a');              1
  res := PLCH_TEST_DETERM('a');              1
  res := PLCH_TEST_DETERM('a');              1
  res := PLCH_TEST_DETERM('a');              1
  res := PLCH_TEST_DETERM('a');              1
  res := PLCH_TEST_DETERM('a');              1
  res := PLCH_TEST_DETERM('a');              1
  res := PLCH_TEST_DETERM('a');              1
  res := PLCH_TEST_DETERM('a');              1
  res := PLCH_TEST_DETERM('a');              1
end;                                         1
function PLCH_TEST_DETERM                    0
  return                                    10
end;                                        10

For comparison with loops:

def proc_name = PLCH_TEST_LOOPS
def func_name = PLCH_TEST_DETERM

create or replace function &func_name
(
  i varchar2

) return varchar2
  deterministic is
begin
  return i;
end;
/

create or replace procedure &proc_name is 
  res varchar2(1);
begin
  for i in 1 .. 10 loop
    res := &func_name('a');
  end loop;
end;
/

var res number
exec :res := dbms_profiler.start_profiler('&proc_name');

exec plch_test_loops;
exec :res := dbms_profiler.stop_profiler;

col text format a60
set pagesize 140

select s.text, d.total_occur
from plsql_profiler_data d
   , user_source s
   , plsql_profiler_units u
where d.runid =
      (
        select max(runid) runid
        from plsql_profiler_runs
        where run_comment = '&proc_name'
      )
  and d.runid = u.runid
  and u.unit_name in ('&proc_name', '&func_name')
  and d.unit_number = u.unit_number
  and s.name = u.unit_name
  and s.line = d.line#
order by u.unit_number, d.line#;

And result is:

TEXT                                    TOTAL_OCCUR
--------------------------------------- -----------

procedure PLCH_TEST_LOOPS is              0
  for i in 1 .. 10 loop                  11
    res := PLCH_TEST_DETERM('a');        10
end;                                      1
function PLCH_TEST_DETERM                 0
  r                                       1
end;                                      1

Impact of Non-Deterministic Code Inside Loop

The following code uses inserts into a table (via an autonomous transaction) to demonstrate how often the body of the deterministic function is executed. You might think that this logging means the function is not deterministic and therefore will turn off the optimization, but that is not the case. The PL/SQL compiler does not validate that a function is trulydeterministic; it does not make a decision on whether or not to avoid execution of the function based on the code inside the function. All of that is decided at the time the code is compiled, based solely on the way the function is called - and, in turns out, the other code executed in the loop.

CREATE TABLE plch_log (created_on DATE)
/

CREATE OR REPLACE PACKAGE plch_getdata
IS
   PROCEDURE log_count (title_in IN VARCHAR2);

   FUNCTION vc (vc VARCHAR2)
      RETURN VARCHAR2
      DETERMINISTIC;
END;
/

CREATE OR REPLACE PACKAGE BODY plch_getdata
IS
   PROCEDURE log_count (title_in IN VARCHAR2)
   IS
      l_count   PLS_INTEGER;
   BEGIN
      SELECT COUNT (*) INTO l_count FROM plch_log;
      DBMS_OUTPUT.put_line (title_in || '=' || l_count);
   END;

   PROCEDURE log_call
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO plch_log VALUES (SYSDATE);
      COMMIT;
   END;

   FUNCTION vc (vc VARCHAR2)
      RETURN VARCHAR2 DETERMINISTIC
   IS
   BEGIN
      log_call ();
      RETURN vc;
   END;
END;
/

DECLARE
   vc   VARCHAR2 (100);
BEGIN
   FOR i IN 1 .. 10
   LOOP
      vc := plch_getdata.vc ('abc');
   END LOOP;

   plch_getdata.log_count ('VC 10 iterations in loop');

   FOR i IN 1 .. 10
   LOOP
      vc := plch_getdata.vc ('abc');
      vc := 'abc' || TO_CHAR (SYSDATE);
   END LOOP;

   plch_getdata.log_count (

'VC 10 iterations with non-deterministic code in loop');
END;
/

VC 10 iterations in loop=1
VC 10 iterations with non-deterministic code in loop=11

DROP TABLE plch_log
/

DROP PACKAGE plch_getdata
/

Effect on Inlining Optimization (level 3) - from _Nikotin

Because Oracle can inline the local subroutines with plsql_optimize_level = 3, the "non-deterministic" part of the loop can became deterministic:

def proc_name = PLCH_TEST_LOOPS
def func_name = PLCH_TEST_DETERM

create or replace function &func_name
(
  i varchar2
) return varchar2
  deterministic is
begin
  return i;
end;
/

alter session set plsql_optimize_level = 2;

create or replace procedure &proc_name is
  res varchar2(1);
  procedure inline_proc (i number) is
  begin
    null;
  end;
begin
  for i in 1 .. 10 loop
    res := &func_name('A');
    inline_proc(1);
  end loop;
end;
/

var res number
exec :res := dbms_profiler.start_profiler('&proc_name');
exec plch_test_loops;
exec :res := dbms_profiler.stop_profiler;

col text format a60
set pagesize 140

select s.text, d.total_occur
from plsql_profiler_data d
   , user_source s
   , plsql_profiler_units u
where d.runid =
      (
        select max(runid) runid
        from plsql_profiler_runs
        where run_comment = '&proc_name'
      )
  and d.runid = u.runid
  and d.unit_number = u.unit_number
  and s.name = u.unit_name
  and s.line = d.line#
order by u.unit_number, d.line#;

And result:

TEXT                                             TOTAL_OCCUR
------------------------------------------------ -----------
procedure PLCH_TEST_LOOPS is                        1
  procedure inline_proc (i number) is               0
    null;                                          10
  for i in 1 .. 10 loop                            11
    res := PLCH_TEST_DETERM('A');                  10
    inline_proc(1);                                10
end;                                                1
function PLCH_TEST_DETERM                           0
  return i;                                        10
end;                                               10

And if uses:

alter session set plsql_optimize_level = 3;

Then it will:

TEXT                                             TOTAL_OCCUR
------------------------------------------------ -----------
procedure PLCH_TEST_LOOPS is                        0
  for i in 1 .. 10 loop                            11
    res := PLCH_TEST_DETERM('A');                  10
    inline_proc(1);                                10
end;                                                1
function PLCH_TEST_DETERM                           0
  return i;                                         1
end;                                                                   1

 

And nothing with inline_proc.