1 2 Previous Next 18 Replies Latest reply: Jul 19, 2012 4:01 PM by 950391 Go to original post RSS
      • 15. Re: Interesting query
        chris227
        Dom Brooks wrote:
        Trust no-one - not unless you test it.
        And only in very recent versions has that declaration of DETERMINISTIC had any effect on the optimisation of function calls in SQL.
        It's cachable since 10g as far as i know.

        But this thread was about the number of executions of functions wether in predicates or projections:
        And i think we are all the same opinion.
        It is not really predictable and one should better not rely on it.
        Correct?
        • 16. Re: Interesting query
          Dom Brooks
          And i think we are all the same opinion.
          It is not really predictable and one should better not rely on it.
          Correct?
          Absolutely. We agree.

          But just to bang on and on and on about DETERMINISTIC ... see this:

          Version:
          SQL> select * from v$version;
          
          BANNER
          --------------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
          PL/SQL Release 11.2.0.3.0 - Production
          CORE    11.2.0.3.0      Production
          TNS for Linux: Version 11.2.0.3.0 - Production
          NLSRTL Version 11.2.0.3.0 - Production
          
          SQL> 
          Setup - nondeterministic function + table of 10000 rows with 10 distinct values in the column that we'll apply the function to.
          SQL> create or replace function f1 (
            2    i1 in number
            3  )
            4  return number
            5  --deterministic
            6  as
            7  begin 
            8     dbms_application_info.set_client_info( sys_context( 'userenv', 'client_info')+1 );
            9     return i1;
           10  end;
           11  /
          
          Function created.
          
          SQL> create table det1
            2  (id          number
            3  ,val1        number
            4  ,padded_val1 varchar2(128)
            5  ,constraint pk_det1 primary key(id));
          
          Table created.
          
          SQL> 
          SQL> insert into det1
            2  select rownum
            3  ,      mod(rownum, 10)
            4  ,      rpad(mod(rownum, 10),128,'X')
            5  from   dual
            6  connect by rownum <= 10000;
          
          10000 rows created.
          
          SQL> 
          SQL> exec dbms_stats.gather_table_stats(USER,'DET1');
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          Let's run a straight select:
          SQL> set arraysize 15
          SQL> exec dbms_application_info.set_client_info(0);
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ----------------------------------------------------------------------------------------
          0
          
          SQL> 
          SQL> set autotrace traceonly statistics
          SQL> 
          SQL> select id, val1 
            2  from   det1
            3  where  f1(val1) = 1;
          
          1000 rows selected.
          
          
          Statistics
          ----------------------------------------------------------
                   56  recursive calls
                    0  db block gets
                  332  consistent gets
                    0  physical reads
                    0  redo size
                15061  bytes sent via SQL*Net to client
                 1060  bytes received via SQL*Net from client
                   68  SQL*Net roundtrips to/from client
                    0  sorts (memory)
                    0  sorts (disk)
                 1000  rows processed
          
          SQL> 
          SQL> set autotrace off;
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ----------------------------------------------------------------------------------------
          10000
          
          SQL> 
          Unsurprisingly, the function is called 10000 times, once per row.

          So, let's make the function deterministic.
          SQL> create or replace function f1 (
            2    i1 in number
            3  )
            4  return number
            5  deterministic
            6  as
            7  begin 
            8     dbms_application_info.set_client_info( sys_context( 'userenv', 'client_info')+1 );
            9     return i1;
           10  end;
           11  /
          
          Function created.
          
          SQL> 
          And run again with the default sql*plus arraysize of 15:
          SQL> set arraysize 15
          SQL> exec dbms_application_info.set_client_info(0);
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ------------------------------------------------------------------------------------------
          0
          
          SQL> 
          SQL> set autotrace traceonly statistics
          SQL> 
          SQL> select id, val1 
            2  from   det1
            3  where  f1(val1) = 1;
          
          1000 rows selected.
          
          
          Statistics
          ----------------------------------------------------------
                   56  recursive calls
                    0  db block gets
                  332  consistent gets
                    0  physical reads
                    0  redo size
                15061  bytes sent via SQL*Net to client
                 1060  bytes received via SQL*Net from client
                   68  SQL*Net roundtrips to/from client
                    0  sorts (memory)
                    0  sorts (disk)
                 1000  rows processed
          
          SQL> 
          SQL> set autotrace off;
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ------------------------------------------------------------------------------------------
          671
          
          SQL> 
          So, we saved a whole bunch of calls to the function - not bad.

          What if we mess with arraysize?

          From one extreme:
          SQL> set arraysize 1
          SQL> exec dbms_application_info.set_client_info(0);
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          --------------------------------------------------------------------------------
          0
          
          SQL> 
          SQL> set autotrace traceonly statistics
          SQL> 
          SQL> select id, val1 
            2  from   det1
            3  where  f1(val1) = 1;
          
          1000 rows selected.
          
          
          Statistics
          ----------------------------------------------------------
                   40  recursive calls
                    0  db block gets
                  730  consistent gets
                    0  physical reads
                    0  redo size
                52299  bytes sent via SQL*Net to client
                 5823  bytes received via SQL*Net from client
                  501  SQL*Net roundtrips to/from client
                    0  sorts (memory)
                    0  sorts (disk)
                 1000  rows processed
          
          SQL> 
          SQL> set autotrace off;
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          --------------------------------------------------------------------------------
          5001
          
          SQL> 
          To the other:
          SQL> set arraysize 5000
          SQL> exec dbms_application_info.set_client_info(0);
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ---------------------------------------------------------------------------------
          0
          
          SQL> 
          SQL> set autotrace traceonly statistics
          SQL> 
          SQL> select id, val1 
            2  from   det1
            3  where  f1(val1) = 1;
          
          1000 rows selected.
          
          
          Statistics
          ----------------------------------------------------------
                    0  recursive calls
                    0  db block gets
                  236  consistent gets
                    0  physical reads
                    0  redo size
                 9385  bytes sent via SQL*Net to client
                  334  bytes received via SQL*Net from client
                    2  SQL*Net roundtrips to/from client
                    0  sorts (memory)
                    0  sorts (disk)
                 1000  rows processed
          
          SQL> 
          SQL> set autotrace off;
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ---------------------------------------------------------------------------------
          11
          
          SQL> 
          And yet if you stick an order by on it:
          SQL> set arraysize 1
          SQL> exec dbms_application_info.set_client_info(0);
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ------------------------------------------------------------------------------------
          0
          
          SQL> 
          SQL> set autotrace traceonly statistics
          SQL> 
          SQL> select id, val1 
            2  from   det1
            3  where  f1(val1) = 1
            4  order by val1;
          
          1000 rows selected.
          
          
          Statistics
          ----------------------------------------------------------
                   40  recursive calls
                    0  db block gets
                  243  consistent gets
                    0  physical reads
                    0  redo size
                52299  bytes sent via SQL*Net to client
                 5823  bytes received via SQL*Net from client
                  501  SQL*Net roundtrips to/from client
                    1  sorts (memory)
                    0  sorts (disk)
                 1000  rows processed
          
          SQL> 
          SQL> set autotrace off;
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ------------------------------------------------------------------------------------
          10
          
          SQL> 
          Suddenly things are more predictable regardless of arraysize, regardless of order by, even order by dbms_random.value().


          And as a comparison, how about if we make use of subquery caching via a SELECT FROM DUAL?

          With arraysize 1:
          SQL> set arraysize 1
          SQL> exec dbms_application_info.set_client_info(0);
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ----------------------------------------------------------------------------------
          0
          
          SQL> 
          SQL> set autotrace traceonly statistics
          SQL> 
          SQL> select id, val1 
            2  from   det1
            3  where  (select f1(val1) from dual) = 1;
          
          1000 rows selected.
          
          
          Statistics
          ----------------------------------------------------------
                    1  recursive calls
                    0  db block gets
                  722  consistent gets
                    0  physical reads
                    0  redo size
                52299  bytes sent via SQL*Net to client
                 5823  bytes received via SQL*Net from client
                  501  SQL*Net roundtrips to/from client
                    0  sorts (memory)
                    0  sorts (disk)
                 1000  rows processed
          
          SQL> 
          SQL> set autotrace off;
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ----------------------------------------------------------------------------------
          10
          
          SQL> 
          And arraysize of 5000:
          SQL> set arraysize 5000
          SQL> exec dbms_application_info.set_client_info(0);
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ----------------------------------------------------------------------------
          0
          
          SQL> 
          SQL> set autotrace traceonly statistics
          SQL> 
          SQL> select id, val1 
            2  from   det1
            3  where  (select f1(val1) from dual) = 1;
          
          1000 rows selected.
          
          
          Statistics
          ----------------------------------------------------------
                    0  recursive calls
                    0  db block gets
                  236  consistent gets
                    0  physical reads
                    0  redo size
                 9385  bytes sent via SQL*Net to client
                  334  bytes received via SQL*Net from client
                    2  SQL*Net roundtrips to/from client
                    0  sorts (memory)
                    0  sorts (disk)
                 1000  rows processed
          
          SQL> 
          SQL> set autotrace off;
          SQL> 
          SQL> select sys_context( 'userenv', 'client_info') from dual;
          
          SYS_CONTEXT('USERENV','CLIENT_INFO')
          ----------------------------------------------------------------------------
          10
          
          SQL> 
          Your mileage on these tests may vary.
          And there are other things you might try to influence the results.
          (For example, are the results above for subquery caching dependent on whether the function is or is not declared DETERMINISTIC?)


          In summary, it is interesting.

          Yes, the the influencing factors may be undocumented.
          Yes, the behaviour is likely to vary in future versions.

          But for functions called in SQL (functions whose nature is deterministic but whose declaration is not necessarily so) these tests above seem to show that subquery caching is the less unpredictable of the two features.
          • 17. Re: Interesting query
            chris227
            Well, one thing you have prooved: You are the ace :-)

            I am not an english native speaker but i understand this quote from you "that subquery caching is the less unpredictable of the two features." as scalar subquery caching may be more predictable.
            Well, it seems more efficient too as TK has shown here http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html (for the same reasons shown by you)

            But what if we exceed the internal cache size for this feature (hash collusions) ...
            • 18. Re: Interesting query
              950391
              Thank you everybody for your help.
              1 2 Previous Next