Oracle Scalar Subquery Caching and Non-deterministic Functions

This Blog will demonstrate that Non-deterministic Function call in Scalar Subquery returns different result with Caching.

Note: Tested on Oracle 19.17.

1. Test Setup

drop table test_tab; 

create table test_tab (x number, y number); 

create index test_tab_ind_x on test_tab(x);

create or replace package test_pack_nd as
  hit_cnt        number := 0;
  sign_threshold number := 50;
end;
/

-- Non-deterministic Function
create or replace function test_cond_nd (p_num number) return number as
  l_ret number;
begin
  test_pack_nd.hit_cnt := test_pack_nd.hit_cnt + 1;
  l_ret := p_num;
  if test_pack_nd.hit_cnt > test_pack_nd.sign_threshold then 
    l_ret := -p_num;
  end if;
    
  return l_ret;
end;
/

create or replace procedure test_proc_nd (p_rows number, p_x number := 1, p_y number := 5) as
  l_res number := 0;
begin
  execute immediate 'truncate table test_tab';
  insert into test_tab select mod(level, 2) x, mod(level, 100) y from dual connect by level <=p_rows;
  commit;
  dbms_stats.gather_table_stats(null, 'TEST_TAB', cascade=>true);
  
   dbms_output.put_line('---------- Compare test_proc_nd('||p_rows||', '||p_x||', '||p_y||') Function-Calls -------------');
  test_pack_nd.hit_cnt := 0;
  l_res             := 0;
  for c in (select * from test_tab where x = p_x and p_y = test_cond_nd(y)) loop
    l_res := l_res + 1;
  end loop;
  dbms_output.put_line('Direct   FuncCall Count = '||test_pack_nd.hit_cnt||', Found Rows# = '||l_res);
  
  test_pack_nd.hit_cnt := 0;
  l_res             := 0;
  for c in (select * from test_tab where x = p_x and (p_y = (select test_cond_nd(y) from dual))) loop
    l_res := l_res + 1;
  end loop;
  dbms_output.put_line('InDirect FuncCall Count = '||test_pack_nd.hit_cnt||', Found Rows# = '||l_res);
  
  test_pack_nd.hit_cnt := 0;
  l_res             := 0;
  for c in 
     (with sq as (select /*+ materialize */ * from test_tab where x = p_x order by y) 
      select * from sq where (p_y = (select test_cond_nd(y) from dual))) loop
    l_res := l_res + 1;
  end loop;
  dbms_output.put_line('InDirectOrdered FuncCall Count = '||test_pack_nd.hit_cnt||', Found Rows# = '||l_res);
end;
/

2. Test Run and Output

A simple run shows the different result with Scalar Subquery Caching and Non-deterministic Functions:

exec test_proc_nd(1000, 1, 5);

Direct   FuncCall Count = 500, Found Rows# = 1
InDirect FuncCall Count = 77, Found Rows# = 10
InDirectOrdered FuncCall Count = 50, Found Rows# = 10

The next test shows the threshold of different result also depending on the function's input parameters.

begin
  test_proc_nd(100, 0, 4);
  test_proc_nd(183, 0, 4);
  test_proc_nd(184, 0, 4);
  test_proc_nd(200, 0, 4);
  test_proc_nd(100, 1, 5);
  test_proc_nd(186, 1, 5);
  test_proc_nd(187, 1, 5);
  test_proc_nd(200, 1, 5);
end;
/

---------- Compare test_proc_nd(100, 0, 4) Function-Calls -------------
Direct   FuncCall Count = 50, Found Rows# = 1
InDirect FuncCall Count = 50, Found Rows# = 1
InDirectOrdered FuncCall Count = 50, Found Rows# = 1
---------- Compare test_proc_nd(183, 0, 4) Function-Calls -------------
Direct   FuncCall Count = 91, Found Rows# = 1
InDirect FuncCall Count = 50, Found Rows# = 2
InDirectOrdered FuncCall Count = 50, Found Rows# = 2
---------- Compare test_proc_nd(184, 0, 4) Function-Calls -------------
Direct   FuncCall Count = 92, Found Rows# = 1
InDirect FuncCall Count = 51, Found Rows# = 2
InDirectOrdered FuncCall Count = 50, Found Rows# = 2
---------- Compare test_proc_nd(200, 0, 4) Function-Calls -------------
Direct   FuncCall Count = 100, Found Rows# = 1
InDirect FuncCall Count = 52, Found Rows# = 2
InDirectOrdered FuncCall Count = 50, Found Rows# = 2
---------- Compare test_proc_nd(100, 1, 5) Function-Calls -------------
Direct   FuncCall Count = 50, Found Rows# = 1
InDirect FuncCall Count = 50, Found Rows# = 1
InDirectOrdered FuncCall Count = 50, Found Rows# = 1
---------- Compare test_proc_nd(186, 1, 5) Function-Calls -------------
Direct   FuncCall Count = 93, Found Rows# = 1
InDirect FuncCall Count = 50, Found Rows# = 2
InDirectOrdered FuncCall Count = 50, Found Rows# = 2
---------- Compare test_proc_nd(187, 1, 5) Function-Calls -------------
Direct   FuncCall Count = 94, Found Rows# = 1
InDirect FuncCall Count = 51, Found Rows# = 2
InDirectOrdered FuncCall Count = 50, Found Rows# = 2
---------- Compare test_proc_nd(200, 1, 5) Function-Calls -------------
Direct   FuncCall Count = 100, Found Rows# = 1
InDirect FuncCall Count = 53, Found Rows# = 2
InDirectOrdered FuncCall Count = 50, Found Rows# = 2

By the way, Filter Subqueries (November 6, 2006) demonstrated Scalar Subquery Caching and its impact on performance.

If the queried tables got updated, such Scalar Subquery Caching has a huge performance fluctuation (a sudden performance degradation).

As further tested in Oracle 19.17, even flush shared pool, flush buffer cache, dbms_session.reset_package are not able to remedy the fluctuation (If the update is in another session and never committed, there is no impact).