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