SQL & PL/SQL

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.