Oracle Scratchpad

July 1, 2013

12c Determinism

Filed under: 12c,Oracle,Troubleshooting — Jonathan Lewis @ 4:55 pm BST Jul 1,2013

Following a comment from Marcin Przepiorowski on my last post, it crossed my mind to check whether “with” functions can be deterministic – the answer seems to be “not yet”. Here’s a simple script that you can run from end to end to check current and future releases – it compares inline (with) and standalone functions when the “deterministic” keyword has been used.


create table t1
as
select
	1	n1
from
	all_objects
where
	rownum <= 10
;

execute dbms_stats.gather_table_stats(user,'t1')

create or replace function standalone_function(i_in number) return number
deterministic
is
begin
	dbms_output.put_line('Standalone function');
	return i_in;
end;
/

spool 12c_deterministic

prompt	============
prompt	arraysize 20
prompt	============

set arraysize 20

with
	function inline_function(i_in number) return number
	deterministic
	as
	begin
		dbms_output.put_line('Inline function');
		return(i_in);
	end;
select
	standalone_function(n1),
	inline_function(n1)
from
	t1
/

The code is very simple – create a table with 10 rows of one column, every row holding the same value; create a standalone function declared as deterministic that simple outputs a message and returns the input value. If determinism is actually in play then, within a single database call, Oracle will call the function just once per input value rather than once per reference. The query then defines an inline (with) function, and selects using the standalone and inline functions from the table. Here’s the output:

============
arraysize 20
============

STANDALONE_FUNCTION(N1) INLINE_FUNCTION(N1)
----------------------- -------------------
                      1                   1
                      1                   1
                      1                   1
                      1                   1
                      1                   1
                      1                   1
                      1                   1
                      1                   1
                      1                   1
                      1                   1

10 rows selected.

Standalone function
Inline function
Standalone function
Inline function
Inline function
Inline function
Inline function
Inline function
Inline function
Inline function
Inline function
Inline function

As you can see, the inline function has been called 10 times while the standalone function has been called twice. (The two calls to the standalone function is a feature of the way that SQL*Plus fetches the first row from a query and then starts using the arraysize for subsequent fetches – each fetch call is a new database call which requires a new call to the deterministic function … try running the code with the arraysize set to 2 (or 1 – which SQL*Plus will silently change to 2).

The intent of inline (with) functions is to make PL/SQL function calls within SQL more efficient – but before you change your code to take advantage of the feature, make sure that you are going to lose the benefit of determinism.

11 Comments »

  1. Interesting that we still can use scalar subquery caching with it:

    with
        function inline_function(i_in number) return number
        deterministic
        as
        begin
            dbms_output.put_line('Inline function');    
            return(i_in);
        end;
    select
        (select standalone_function(n1) from dual),
        (select inline_function(n1) from dual)
    from
        t1
    

    Unfortunately, both caching mechanisms stil works by each column separately:

    SQL> with
      2      function inline_function(i_in number) return number
      3      deterministic
      4      as
      5      begin
      6          dbms_output.put_line('Inline function');
      7          return(i_in);
      8      end;
      9  select
     10      standalone_function(n1) st1,
     11      standalone_function(n1) st2,
     12      standalone_function(n1) st3,
     13      (select inline_function(n1) from dual) i1,
     14      (select inline_function(n1) from dual) i2,
     15      (select inline_function(n1) from dual) i3
     16  from
     17      t1
     18  /
    
           ST1        ST2        ST3         I1         I2         I3
    ---------- ---------- ---------- ---------- ---------- ----------
             1          1          1          1          1          1
             1          1          1          1          1          1
             1          1          1          1          1          1
             1          1          1          1          1          1
             1          1          1          1          1          1
             1          1          1          1          1          1
             1          1          1          1          1          1
             1          1          1          1          1          1
             1          1          1          1          1          1
             1          1          1          1          1          1
    
    10 rows selected.
    
    Standalone function
    Standalone function
    Standalone function
    Inline function
    Inline function
    Inline function
    Standalone function
    Standalone function
    Standalone function
    
    

    ps. By the way, I compared them a little more on 11.2: http://orasql.org/category/oracle/deterministic-functions/

    Comment by Sayan Malakshinov — July 1, 2013 @ 7:38 pm BST Jul 1,2013 | Reply

    • Another disappointing that the such functions returns also inconsistent results as old pl/sql functions. I was hoping that if they are in the query, so the results will be consistent with it.

      SQL> @tests/deterministic2
      SQL> create table t as select 1 a from dual;
      
      Table created.
      
      SQL> declare
        2    j binary_integer;
        3  begin
        4    dbms_job.submit( j
        5                    ,'begin
        6                        for i in 1..10 loop
        7                          dbms_lock.sleep(1);
        8                          update t set a=a+1;
        9                          commit;
       10                        end loop;
       11                      end;'
       12                   );
       13    commit;
       14  end;
       15  /
      
      PL/SQL procedure successfully completed.
      
      SQL> with
        2     function f return int is
        3       res int;
        4     begin
        5       dbms_lock.sleep(1);
        6       select a into res from t;
        7       return res;
        8     end;
        9  select
       10     f
       11  from dual
       12  connect by level<=10;
       13  /
      
               F
      ----------
               1
               1
               1
               2
               3
               4
               5
               6
               7
               8
      
      10 rows selected.
      
      

      Comment by Sayan Malakshinov — July 2, 2013 @ 10:34 pm BST Jul 2,2013 | Reply

      • Sayan,

        That’s an interesting (and difficult) one.

        Oracle by default is read-consistent only at the statement level – so there is a case for saying this is expected (and required) behaviour. Imagine running the query as a pl/sql loop with a statement call for each iteration of the loop – logically it’s equivalent and should return the same result.

        Somewhere, I’m sure, I have an example of a query which I optimized – by taking advantage of one of the “hidden cache” mechanisms like scalar subquery caching – only to find that the optimum execution gave different results as a consequence; I can’t find the example at present so I can’t be sure which technology it applied to, but I think I had to set the transaction or session to serializable or read-only to get correct read-consistency.

        Apart from anything else, though, I like the way that your pursuit of this mechanism gives an indication of how thoughtful one has to be before adopting even a tiny new feature because it looks so cute and simple.

        Comment by Jonathan Lewis — July 3, 2013 @ 9:12 am BST Jul 3,2013 | Reply

        • Thanks, Jonathan!

          Disappoints me is that for standalone function we can create operator and it will be consistent(except some cases like functions with autonomous transactions or with “as of” cursors), but not for inline functions.

          I’m very interested to see your example with “hidden cache”! Please, post it, if you find it someday
          Btw, i did opposite example for seminar at work: with isolation level=serializable, function in query results as uncommitted data of own session and commited data of another session through nested function with autonomous_transaction

          Comment by Sayan Malakshinov — July 5, 2013 @ 8:27 pm BST Jul 5,2013

        • Sayan,

          I’ve found the think I was thinking of – thought I was remembering it completely incorrectly. The think I had demonstrated was that Oracle assumes that filter subqueries are deterministic, and I had produced a remarkably silly little test to demonstrate it:

          select 
          	/*+ gather_plan_statistics */
          	count(*)
          from (
          	select	/*+ no_merge */
          		outer.* 
          	from emp outer
          	where outer.sal >
          		(
          			select /*+ no_unnest */ avg(inner.sal) 
          		 	from emp inner 
          			where inner.dept_no = outer.dept_no +
          				trunc(dbms_random.value(-6,6))
          		)
          )
          ;
          
          

          The subquery runs once per dept_no, even though the presence of the random number generation means this ought to be the wrong behaviour, and in the best possible case (there being 6 department numbers) the subquery needs to have run 13 times to have covered all possible return values for the subquery.

          Comment by Jonathan Lewis — July 10, 2013 @ 8:29 pm BST Jul 10,2013

    • Sayan,

      Nice detail, and thanks for the link.

      Comment by Jonathan Lewis — July 3, 2013 @ 8:53 am BST Jul 3,2013 | Reply

      • Jonathan,

        many thanks for sharing and so fast answer!

        By the way, dbms_random has own specific “determinism” – http://orasql.org/2012/06/13/dbms_random-in-parallel/
        Little example:

        with
         t  as ( select/*+ materialize */ level n from dual connect by level<=4000)
        ,t1 as (
                 select--+ materialize parallel(t 4)
                    dbms_random.string('x',4)
                    ||';'
                    ||(select sid||';'||process||';'||pid
                       from v$session, v$process
                       where sid=sys_context('USERENV','SID')
                         and PADDR=ADDR
                         and n>0
                      ) f
                 from t
        )
        ,t2 as (
                 select
                    t1.f
                   ,count(*) over(partition by regexp_substr(f,'^[^;]+')) cnt
                 from t1
        )
        select f
              ,regexp_substr(f,'[^;]+')     rnd
              ,regexp_substr(f,'[^;]+',1,2) sid
              ,regexp_substr(f,'[^;]+',1,3) process
              ,regexp_substr(f,'[^;]+',1,4) pid
              ,cnt
        from t2 
        where cnt>1
        order by f;
        
        F                         RND    SID    PROCES PID           CNT
        ------------------------- ------ ------ ------ ------ ----------
        00PU;131;5624;34          00PU   131    5624   34              2
        00PU;196;1388;35          00PU   196    1388   35              2
        02JQ;131;5624;34          02JQ   131    5624   34              2
        02JQ;196;1388;35          02JQ   196    1388   35              2
        02PM;131;5624;34          02PM   131    5624   34              2
        02PM;196;1388;35          02PM   196    1388   35              2
        0366;131;5624;34          0366   131    5624   34              2
        0366;196;1388;35          0366   196    1388   35              2
        ...
        

        Comment by Sayan Malakshinov — July 10, 2013 @ 10:25 pm BST Jul 10,2013 | Reply

  2. with function f return int result_cache is begin return 1; end f;
    select f from dual
    /
    select f from dual
           *
    ERROR at line 2:
    ORA-06553: PLS-313: 'F' not declared in this scope
    ORA-06552: PL/SQL: Item ignored
    ORA-06553: PLS-999: implementation restriction (may be temporary) RESULT_CACHE
    is disallowed on subprograms in anonymous blocks
    

    sounds as they plan to support even inline result caching

    Comment by Matthias Rogel — July 3, 2013 @ 7:05 am BST Jul 3,2013 | Reply

    • It’s really interesting!
      I’ve done test of query with inline view which contain inline function call

      with function f 
           return int is 
           begin
             return 1; 
           end;
           
          rc_view as (
                   select/*+ result_cache no_merge */
                      f as f1
                   from dual
                   )
      select * 
      from rc_view,rc_view v2
      

      In that case inline view will not be cached

      But all query will be cached successfully:

      with function f 
           return int is 
           begin
             return 1; 
           end;
           
          rc_view as (
                   select/*+ result_cache no_merge */
                      f as f1
                   from dual
                   )
      select/*+ result_cache */ * 
      from rc_view,rc_view v2
      

      Comment by Sayan Malakshinov — July 10, 2013 @ 10:47 pm BST Jul 10,2013 | Reply

  3. All,

    Thanks for the follow-up.
    No matter how hard we try, there are always a few more details to discover, a few more gaps to fill.

    Comment by Jonathan Lewis — July 24, 2013 @ 12:41 pm BST Jul 24,2013 | Reply

  4. […] when i was going through an post by Jonathan Lewis http://jonathanlewis.wordpress.com/2013/07/01/12c-determinism/ I thought what the h*** they are doing to determistic function and Oracle is not taking advantage […]

    Pingback by With Enhancement | jagdeepsangwan — March 24, 2014 @ 11:24 am BST Mar 24,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers