Determining Deterministic III – Not So Simple Performance Benefits

In the second part, I hopefully showed a simple example of the benefits of the DETERMINISTIC assertion in a function.

However, as mentioned, there was one influencing factor in the test results which I was deliberately not explicit about.

I was not expecting it to be a factor – I hadn’t even thought about it.

I only came upon it when investigating a difference in behaviour between tools.

Setting up as before:

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> 
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> 

PL/SQL procedure successfully completed.

What’s missing here?

SQL> XXXXXXXXXXXXXXXXXXX                      <---------------- What's this?
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 rows

1000 rows selected.

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

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

SQL> 
SQL> XXXXXXXXXXXXXXXXXXX                      <---------------- And what's this?
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 rows
1000 rows selected.

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

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

SQL> 

Any ideas?































































It’s the fetch size – ARRAYSIZE in the case of SQL*PLUS.

Does it seem obvious that it would be factor?

Scalar subquery caching (a comparison with which I was leaving for a later article) is not similarly affected.

SQL> set arraysize 1
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, (select f1(val1) from dual)
  2  from   det1;
-- Cut rows

1000 rows selected.

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

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

About these ads

3 Responses to Determining Deterministic III – Not So Simple Performance Benefits

  1. Pingback: Determining Deterministic II – Simple Performance Benefits « OraStory

  2. Pingback: Determining Deterministic « OraStory

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

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 69 other followers

%d bloggers like this: