Skip to Main Content

Truth about deterministic functions!

unknown-879931Feb 4 2015 — edited Feb 8 2015

Hello All,

After I read very informative and valuable articles on caching and deterministic functions by famous Oracle gurus. I want to test it on my computer. According to the following articles it has been said that DETERMINISTIC function caching is only available for the lifetime of the fetch. However the following test case shows it is not. What would you like to say about this behavior?

https://orastory.wordpress.com/2010/12/16/determining-determinism/

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

http://oracle-base.com/articles/misc/efficient-function-calls-from-sql.php#deterministic-hint

Let's get down the business now!

First, in order to test the following table, procedures and function should be executed.

  CREATE TABLE TEST_TAB

  as

  select

  rownum id,

  mod(rownum, 10) col1,

  rpad(mod(rownum, 10), 10, 'X') col2

  from dual

  connect by rownum <= 1000;

  create or replace

  function f_det (f_var in number)

  return number

  DETERMINISTIC

  is

  begin

    dbms_application_info.set_client_info(USERENV('client_info')+1);

    return f_var;

  end;

  create or replace

  procedure get_client_info

  is

  begin

    dbms_output.put_line(USERENV('CLIENT_INFO'));

  end;

 

create or replace

  procedure set_client_info (var in integer default 0)

  is

  begin

    dbms_application_info.set_client_info(var);

  end;

SQL> show user
USER: "HR"
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> exec set_client_info(0);

PL/SQL yordam² ba■ar²yla tamamland².

SQL> exec get_client_info;
0

PL/SQL yordam² ba■ar²yla tamamland².

SQL> show arraysize;

arraysize 15

SQL> select id, f_det(col1) from test_tab;

....... ==> I omitted the output

1000 sat²rlar² seildi.

SQL> exec get_client_info;
670

PL/SQL yordam² ba■ar²yla tamamland².

Thus far, everything look great. The table has 1000 rows and 10 distinct values contain col1.

1000 / 15 =  66,66

Lets say 66

Every fetch, we expect 5 cache hit, there should be approximately 66 round trip between client (SQL*PLUS) and DB.

So 66*5 = 330

We expert the USERENV('CLIENT_INFO') value should be 670. And it show 670 so far so good!

Now let's change the arraysize

SQL> show arraysize;
arraysize 15


SQL> set arraysize 50;


SQL> show arraysize;
arraysize 50


SQL> exec set_client_info(0);

PL/SQL yordam² ba■ar²yla tamamland².

SQL> select id, f_det(col1) from test_tab;

.......==> I omitted the output

1000 sat²rlar² seildi.

SQL> exec get_client_info;
201

PL/SQL yordam² ba■ar²yla tamamland².

Let's do the same math again,

1000 / 50 = 20

There should be 40 cache hit for each round trip! 20 * 40 = 800. I expect cachit should.

So USERENV('CLIENT_INFO') should equal 1000 - 800 = 200

It shows 201. Alright, everything perfect so far!

Now, I want to order col1 and see the result for arraysize 15.

SQL> exec set_client_info(0);

PL/SQL yordam² ba■ar²yla tamamland².

SQL> set arraysize 15;

SQL> select id, f_det(col1) from test_tab order by col1;

...... ==> I omitted the output

1000 sat²rlar² seildi.

SQL> exec get_client_info;
10

PL/SQL yordam² ba■ar²yla tamamland².

If the column ordered. That means the cache hit should be very high. I almost expect to equal round trip number.

HOWEVER, It shows 10?

The arraysize is 15, if the deterministic function is available for lifetime of the fetch. How come it is equal to 10?

1000 / 15 approximately 66

How come cache hit less than 66 if it is dependent fetch life time?

In addition, in order to be sure, I set autotrace on.


SQL> set autotrace on;


SQL> exec set_client_info(0);

PL/SQL yordam² ba■ar²yla tamamland².

SQL> select id, f_det(col1) from test_tab order by col1;

................==> I omitted the output

1000 sat²rlar² seildi.


Y³r³tme Plan²
----------------------------------------------------------
Plan hash value: 1488739901

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |  1000 |  7000 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY     |          |  1000 |  7000 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST_TAB |  1000 |  7000 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------


¦statistikler
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
      21636  bytes sent via SQL*Net to client
       1245  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

As you can see roundtrips = 68.

Also please correct me if I am wrong, can we say fetch number equals to roundtrips?

If so, isn't it illogical the function execute is only 10?

Please explain this behaviour?

Thanks in advance.

Comments
Post Details
Added on Feb 4 2015
11 comments
6,022 views