Deterministic Functions

In trying to see the affect of ARRAYSIZE on deterministic function caching of results, I used the following script.

set serveroutput on size unlimited
set lines 132

CREATE OR REPLACE PACKAGE pkg_deterministic_test
IS

  FUNCTION fn_deterministic (
    pi_num      NUMBER
  )
  RETURN NUMBER
  DETERMINISTIC;

  FUNCTION fn_execution_count
  RETURN NUMBER;

  PROCEDURE sp_reset_count;

END pkg_deterministic_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_deterministic_test
IS

  gn_execution_count    NUMBER := 0;


  FUNCTION fn_deterministic (
    pi_num      NUMBER
  )
  RETURN NUMBER
  DETERMINISTIC
  IS
  BEGIN
    -- DBMS_OUTPUT.put_line('FN_DETERMINISTIC function code executed');
    gn_execution_count := gn_execution_count + 1;

    RETURN pi_num;
  END fn_deterministic;


  FUNCTION fn_execution_count
  RETURN NUMBER
  IS
  BEGIN
    RETURN gn_execution_count;
  END fn_execution_count;


  PROCEDURE sp_reset_count
  IS
  BEGIN
    gn_execution_count := 0;
  END sp_reset_count;

END pkg_deterministic_test;
/
show errors

set arraysize 2

WITH do_the_calls AS (
  SELECT pkg_deterministic_test.fn_deterministic(MOD(rownum, 75)) + 1 fn_out
  FROM   all_objects
  WHERE  rownum <= 1000
)
SELECT max(fn_out)
FROM   do_the_calls;

SELECT pkg_deterministic_test.fn_execution_count FROM dual;

      

In summary, I found that the SQL*Plus ARRAYSIZE made no difference to the caching in 11.2.0.2 or 12.1.0.1.

With the 75 above at anything up to 75, the max(fn_out) was always the same as the pkg_deterministic_test.fn_execution_count. Then at 76, the pkg_deterministic_test.fn_execution_count jumped up to 88, indicating that an extra 12 function calls were executed. At slightly higher distinct numbers, the number of function calls started to become a bit erratic (details up to 100 shown below).

The values above came from 12.1.0.1, and those checked in 11.2.0.2 were the same up to 88, and which point the numbers started differing.