30 November 2011

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.

4 comments:

  1. Hello All,
    I also made some inquiries around the DETERMINISTIC function issue on the same day of the quiz and found the following, tested in
    11.1.0.7.0

    SET SERVEROUTPUT ON SIZE 1000000

    -- package for counting number of executions
    CREATE OR REPLACE PACKAGE plch_pkg
    AS
    g_counter NUMBER := 0;
    END plch_pkg;
    /

    Package created.


    /* deterministic function
    Oracle IS NOT aware of the side effect, it just takes our "deterministic" declaration
    for granted
    */
    CREATE OR REPLACE FUNCTION plch_getdata
    (n NUMBER)
    RETURN NUMBER
    DETERMINISTIC
    IS
    BEGIN
    plch_pkg.g_counter := plch_pkg.g_counter + 1;
    RETURN n;
    END;
    /

    Function created.

    /* if the counter initialization and the loop are in the same block,
    the function is NOT EXECUTED AT ALL
    ( probably because its return value is in fact NOT used ! )
    */
    DECLARE
    n NUMBER;
    BEGIN
    plch_pkg.g_counter := 0;

    FOR indx IN 1 .. 10000000
    LOOP
    n := plch_getdata (1);
    END LOOP;

    dbms_output.put_line('g_counter='||
    plch_pkg.g_counter);
    END;
    /
    g_counter=0

    PL/SQL procedure successfully completed.


    /* put counter initialization in a separate block (and server call)
    */
    BEGIN
    plch_pkg.g_counter := 0;
    END;
    /

    /* now the function will be executed ONCE only (as expected for a DETERMINISTIC function)
    */
    DECLARE
    n NUMBER;
    BEGIN
    -- plch_pkg.g_counter := 0;

    FOR indx IN 1 .. 10000000
    LOOP
    n := plch_getdata (1);
    END LOOP;

    dbms_output.put_line('g_counter='||
    plch_pkg.g_counter);
    END;
    /
    g_counter=1

    PL/SQL procedure successfully completed.

    ( to be continued )

    ReplyDelete
  2. (continued)

    /* if we use the return value (display "n"), then the function is executed once,
    even if the counter initialization and the loop are in a single block (server call).
    */
    DECLARE
    n NUMBER;
    BEGIN
    plch_pkg.g_counter := 0;

    FOR indx IN 1 .. 10000000
    LOOP
    n := plch_getdata (1);
    END LOOP;

    dbms_output.put_line('n='||n);

    dbms_output.put_line('g_counter='||
    plch_pkg.g_counter);
    END;
    /
    n=1
    g_counter=1

    PL/SQL procedure successfully completed.


    /* non-deterministic function */
    CREATE OR REPLACE FUNCTION plch_getdata (n NUMBER)
    RETURN NUMBER
    -- DETERMINISTIC
    IS
    BEGIN
    plch_pkg.g_counter := plch_pkg.g_counter + 1;
    RETURN n;
    END;
    /

    Function created.

    /* here the count is correct, even with a single block and still without using the return value */
    DECLARE
    n NUMBER;
    BEGIN
    plch_pkg.g_counter := 0;

    FOR indx IN 1 .. 10000000
    LOOP
    n := plch_getdata (1);
    END LOOP;

    dbms_output.put_line('g_counter='||
    plch_pkg.g_counter);
    END;
    /
    g_counter=10000000

    PL/SQL procedure successfully completed.


    So, it looks like the number of executions of the function is determined NOT only by declaring it as DETERMINISTIC or not, but also by other decisions that the optimizing compiler may take based on this declaration.

    In other words, when we declare a function as DETERMINISTIC we are in fact saying to the pl/sql compiler:
    "Take that function and "act upon it as you (the compiler) wish", I don't even care whether it is not executed at all, if you so decide" ...

    So, it is entirely our responsibility to make sure that the results will not be affected
    by the optimizer taking "practically ANY" decision regarding the function execution.

    In my opinion, the fact that the optimization happens at compile time and thus only for arguments
    that are static at compile time makes this feature much weaker than the result cache feature,
    at least at the PL/SQL level, its only advantage upon the result cache being its speed.

    It is interesting however that, in the SQL layer, a deterministic function is able to behave as expected, though here the input values are received at run time only,
    so this behavior is realy closer to a "query level result_cache-like behavior"
    than to how the deterministic function behaves in PL/SQL.

    Thanks & Best Regards,
    Iudith Mentzel

    ReplyDelete
  3. Steven, Iudith,

    some of the points are not quite correct. i wrote about it in details with examples now:
    http://orasql.org/2013/03/13/optimization-of-loops-in-plsql-part-1/

    Iudith, btw, about caching mechanism of deterministic functions in SQL engine i wrote here:
    http://orasql.org/2013/02/11/deterministic-function-vs-scalar-subquery-caching-part-2/

    ReplyDelete
  4. Many thanks, Sayan. These articles are very interesting and thorough.

    ReplyDelete