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” (the 12c inline) 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.


rem
rem     Script:         12c_deterministic.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2013
rem

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 not going to lose the benefit of determinism.

Update May 2019

I was prompted to review some optimisation of function calls recently, and this made me re-run this test. Things change in 12.2 – the inline function now behaves as a deterministic function.

Addendum (May 2019): A follow-up tweet from Iudith Mentzel warns that whatever has been done is at best incomplete, after testing a similar example using varchar2() on LiveSQL (19.2).  To be investigated further.

 

20 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 https://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 GMT Mar 24,2014 | Reply

  5. Hi Jonathan,

    >execute dbms_stats.gather_table_stats(user,’t1′)

    Gathering stats on a CTAS is no longer required in 12c. Correct me if I am wrong or being too generic.

    Regards,
    Lalit

    Comment by Lalit Kumar B — March 11, 2015 @ 7:00 am GMT Mar 11,2015 | Reply

  6. […] the upgrade to 12.2 the deterministic keyword started working for the inline “with function”, and some change to the coding for pragma UDF also […]

    Pingback by PL/SQL Functions | Oracle Scratchpad — May 15, 2019 @ 11:28 am BST May 15,2019 | Reply

  7. hi sir,
    i have proc that populates a glob@l temp and i am trying to have a inline function that calls that SP and runs a select (as part of in-line SQL) that uses that glob@l temp but the data of that temp table isn’t visible, don’t see any oracle documentation either. but if that function returns a xmltype (has auto pragma) and convert the xmltype to rows and columns then the sql able to show data. can you please share your thoughts.
    criteria: we can’t create anything on the db, should be in sql only and we the output should be rows and columns (these are dynamic based on the input parameter that we pass to the proc). version 12.2 or 18

    some thing on below lines….

     with   function fn_name   return xmltype   as     o_refcursor sys_refcursor;
    pragma autonomous_transaction;
      begin  ( ''  ) ;  --- gtt gets populated here. 
      open o_refcursor for SELECT  .gtt_col1, chg.col_1, chg.col_2, chg.col_3
    FROM ( SELECT  AS pk,   UPDATE_DT FROM 
    )  xx JOIN  chg ON xx.PK = chg.col1 JOIN  ha ON ha. = chg.  ;
      return xmltype.createxml(o_refcursor);  end;
      select xt.* from
      (  select fn_name().getClobVal() as myxml      from dual ) p
     
      , XMLTABLE('/ROWSET/ROW' PASSING XMLPARSE (DOCUMENT p.myxml ) COLUMNS
        gtt_col1     VARCHAR2(50)  
      , col_1       NUMBER(18,0)                       
      , col_2       NUMBER(38,0)                       
      , col_3       NUMBER(18,0)      ) xt ;
    

    Comment by radnor — October 21, 2020 @ 9:13 pm BST Oct 21,2020 | Reply

    • radnor,

      I’m not entirely sure of what you’re trying to achieve and what you’ve achieved so far. And you haven’t said anything about what you’ve tried so far and what errors this produced. Comments on a blog note aren’t the ideal platform for problem solving, of course, so you ought to take this to the Oracle Developer Forum.

      It’s possible that the only problem with your code is that you need to declare your gtt as “on commit preserve rows”.
      I do wonder though whether your pragma should appear earlier in the function as in:

      with function fn_name return xmltype   
      as
              pragma autonomous_transaction;
              o_refcursor sys_refcursor;
      begin
      

      I’ve just run up a simple version of this, using a single table with a couple of rows, to check the validity of the method and code structure and it seems to work perfectly well on 12.2.0.1

      Regards
      Jonathan Lewis

      Afterthought: If you’re not committing in the procedure that populates the GTT you will need a commit after calling that procedure.

      Comment by Jonathan Lewis — October 22, 2020 @ 9:34 am BST Oct 22,2020 | Reply

  8. thanks a lot for your response and time sir. I am trying to call the inline function/stored proc which populates a gtt and a sql that uses that gtt, trying to do all in one sql statement (call SP & select part that uses the gtt). i don’t get any error or do see any data. when i opted the xml route and tried to convert xml to rows and columns and i am able to see the data.
    the gtt has “on commit preserve rows” , sp does commit it after loading it, i also did as part of sql ( below). below is what i tried then moved to the xml code showed earlier. version 18.10.0.0.0.
    really wasnt sure if it is a bug or not. sorry about this.

    WITH  PROCEDURE call_sp_that_load_gtt 
         AS PRAGMA AUTONOMOUS_TRANSACTION; 
         v_sql   VARCHAR2 (1000); 
         BEGIN
             v_sql := q'[ BEGIN ( 'param1',  'param_2',  sysdate-3, sysdate   ) ; END; ]' ; 
                EXECUTE IMMEDIATE v_sql ;
    commit;
        END; 
        
         FUNCTION get_cnt  RETURN NUMBER  AS pragma autonomous_transaction ;  v_cnt   NUMBER; 
         BEGIN
            SELECT count(*)
              INTO v_cnt
              FROM  ;
    
            call_sp_that_load_gtt ;
            COMMIT  ;
            RETURN v_cnt;
         END;
     select * from  ; --- no data if we run this on 1st run or any run. I tried this one initially then took the xml route.
    --  select get_cnt from dual ; -- this gives 0 on 1st run but correct count on 2nd run going forward.
    

    Comment by radnor — October 22, 2020 @ 2:39 pm BST Oct 22,2020 | Reply

    • i think function part, call and commit should be prior to select.
      execute a sp and run a select that uses a gtt populated by that sp is what i am trying to do , all in one sql statement.

      Comment by radnor — October 22, 2020 @ 4:46 pm BST Oct 22,2020 | Reply

      • radnor,

        So you’re trying to write and execute a select statement that first populates a GTT before selecting from it.

        Problem – you can’t insert into a table as part of a select statement or Oracle will raise error “ORA-06519: active autonomous transaction detected and rolled back
        , so you have to hide the insert inside an autonomous transaction

        Problem 2 – if the insert is inside an autonomous transaction it behaves as if “someone else” executed it, so your select statement can’t see the data

        Solution step 1: if you open a refcursor to query the data while still inside the autonomous transaction you can use it outside the autonomous transaction to fetch the data

        Problem 3 – Oracle doesn’t have a mechanism to select from a refcursor

        Solution step 2: if you can find a built-in function that fetches from a refcursor and returns something that you can treat as a table your job is done. (Alternatively you can write a pipelined function that knows how to fetch from the cursor and pipe rows – but this requires you to have created database objects (a row type and a table type) and you’ve said you can’t do this).

        Solution step 3: you’ve already found an XML function that will do what you want. There are probably others, and maybe better ones since your solution constructs the entire XML object before passing it to the select statement. I haven’t followed the development of XML in Oracle and things are constantly being deprecated and replaced by new shiny alternatives, but the xml_sequence() look like a better option than you original:

        
        with function f6 return sys_refcursor
        as
                pragma autonomous_transaction;
                o_refcursor sys_refcursor;
        begin
                p2;
                commit;
                open o_refcursor for
                select
                        object_type, object_name
                from    gtt1
                where   rownum <= 1000
                ;
                return(o_refcursor);
        end;
        select * from table(xmlsequence(f6()))
        /
        
        
        

        p2 is a procedure to populate a GTT that looks like “all_objects”.
        f6 returns a refcursor NOT an XML object
        xmlsequence() seems to be a pipelined function that takes a refcursor as an input.

        The benefit here is that for large volumes of data in the GTT your function creates and returns a large XMLTYPE, while my use of xmlsequence means Oracle never has to instantiate the entire set, so the PGA memory usage is reduced.

        Regards
        Jonathan Lewis

        Comment by Jonathan Lewis — October 25, 2020 @ 8:29 pm GMT Oct 25,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.