Determining Deterministic II – Simple Performance Benefits

In the first part I said that determinism was initially implemented as a required keyword for the function of a function-based index and that any other documented performance benefits were not (fully) implemented until at least 10.2.

As stated in the first part, the intention is to keep the examples restricted to a single version – 11.2.0.2 in this case – as the intention is not to compare how things have changed over time, just to make some observations and to draw some not too spurious conclusions.

So, what does the 11.2 documentation now tell us about DETERMINISTIC?

From the Advanced Application Developer’s Guide:

Certain performance optimizations occur on invocations of functions that are marked DETERMINISTIC without any other action being required. These features require that any function used with them be declared DETERMINISTIC:

  • Any user-defined function used in a function-based index.
  • Any function used in a materialized view, if that view is to qualify for Fast Refresh or is marked ENABLE QUERY REWRITE.

The preceding functions features attempt to use previously calculated results rather than invoking the function when it is possible to do so.

It is good programming practice to make functions that fall into these categories DETERMINISTIC:

  • Functions used in a WHERE, ORDER BY, or GROUP BY clause
  • Functions that MAP or ORDER methods of a SQL type
  • Functions that help determine whether or where a row appears in a result set

Keep these points in mind when you create DETERMINISTIC functions:

  • The database cannot recognize if the action of the function is indeed deterministic. If the DETERMINISTIC keyword is applied to a function whose action is not truly deterministic, then the result of queries involving that function is unpredictable.
  • If you change the semantics of a DETERMINISTIC function and recompile it, then existing function-based indexes and materialized views report results for the prior version of the function. Thus, if you change the semantics of a function, you must manually rebuild any dependent function-based indexes and materialized views.

I started off in part one looking at the only way DETERMINISTIC made any difference when first implemented – in function-based indexes.

But I want to rewind and yet go forward, leaving the indexing until a future part of this series, and focusing on the benefits of DETERMINISTIC for a simple unindexed function call.

So, the supposed performance benefits of a DETERMINISTIC function come from repeated function calls within a statement.

Set Up

SQL> drop table det1;

Table dropped.

SQL> 
SQL> create table det1
  2  (id          number
  3  ,val1        number
  4  ,padded_val1 varchar2(100)
  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),100)
  5  from   dual
  6  connect by rownum <= 1000;

1000 rows created.

SQL> 
SQL> exec dbms_stats.gather_table_stats(USER,'DET1');

PL/SQL procedure successfully completed.

SQL> 

Run without DETERMINISTIC

SQL> 
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> 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> select id, val1, f1(val1)
  2  from   det1;
-- Cut output --

SQL> select sys_context( 'userenv', 'client_info') from dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
-----------------------------------------------------------------------
1000

SQL> 

So, as expected, from the initial run without the DETERMINISTIC assertion, 10000 rows, 10000 function calls.

Next run with DETERMINISTIC function:

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> 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> select id, val1, f1(val1)
  2  from   det1;

-- Cut output

SQL> 
SQL> select sys_context( 'userenv', 'client_info') from dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
-----------------------------------------------------------------------------------
11

SQL> 

So, in this example, we’ve saved 989 function calls over our 1000 rows.

I’ve not touched on the actual performance benefits other than the number of calls to the function. The main benefit is usually going to be the reduction in context switching plus then any significant work that the function itself implements.

Obviously there are going to be variations in behaviour and different limits based on number of rows, number of distinct values and datatype.

Of course, the order of the data can make a difference.

This is all based on a simple example still, but if we state an order then we can make a further saving of one function call:

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> select id, f1(val1)
  2  from   (select /*+ no_merge */
  3                 id, val1
  4          from   det1
  5          order by val1);

-- cut output

SQL> select sys_context( 'userenv', 'client_info') from dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
---------------------------------------------------------------------------
10

SQL> 

This all seems mostly straightforward, but there’s one factor I’ve not been explicit about in these examples.

Any ideas?
(See next part)

Finally, there is no benefit to a DETERMINISTIC function between calls to the outer SQL – i.e. if I call the main SELECT above once and then again, there is no evidence that the second execution gets any benefit from the DETERMINISTIC function itseld. This is really the domain of the result cache.

And it should go without saying that if your DETERMINISTIC function involves a SELECT from a table, then it’s not really DETERMINISTIC is it? I may touch on another aspect of such misuse in a future article.

About these ads

5 Responses to Determining Deterministic II – Simple Performance Benefits

  1. Pingback: Determining Deterministic I – Introduction « OraStory

  2. Pingback: Determining Deterministic III – Not So Simple Performance Benefits « OraStory

  3. Pingback: Determining Deterministic « OraStory

  4. Pingback: Determining Deterministic IV – Again Not So Simple « OraStory

  5. Eric says:

    >> connect by rownum > So, as expected, from the initial run without the DETERMINISTIC assertion, 10000 rows, 10000 function calls.

    Should be: 1000 rows, 1000 function calls.

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

Follow

Get every new post delivered to your Inbox.

Join 62 other followers

%d bloggers like this: