Truth about deterministic functions!

Unknown

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.

Jonathan Lewis

NightWing wrote:

 isn't it illogical to say "deterministic function caching is only available the lifetime of a fetch"? 

Right now it wouldn't be illogical it would simply be wrong because, as you have shown from the example suggested by rp0428, the dependency is not on a "fetch" it is on a "client call to database" of which a "fetch" is a particular example, and a fetch that occurs inside a pl/sql block is not (necessarily) a call from client to a database.

A more important observation about your comment is that you don't seem to be allowing for the process by which we learn about the internal mechanisms of Oracle. The examples you originally cited are a few years old, they present a scenario, show results and present a conclusion - and it is this scientific process that allows us to deduce things that Oracle Corporation does not tell us. The fact that someone can come up with a different scenario and extend our knowledge does not make the original conclusion illogical (in this case) it simply highlights the fact that the original conclusion was limited in its applicability.

Fortunately the examples have shown their working, so it is easy for anyone who reads them to see the assumptions they have made and the scenario they are considering and appreciate that (a) the conclusion is correct for the scenario and (b) that there may be other scenarios that they need to test for themselves.

Regards

Jonathan Lewis

Dom Brooks

"Truth" is sometimes tricky because a) the implementation detail is internal to Oracle and b) the truth changes over time, i.e. exact behaviour evolves from version to version.

Best thing is to have such scripts and demos which show how something does work and then we can try to reproduce and then also from version to version see if observations are still the same.

Dom Brooks

I reproduced your exact results above using sql*plus.

But choose a different tool and I get different results.

However the key is in the order by and when the function gets evaluated.

But one of the reasons I don't like deterministic functions for this purpose is exactly this sort of variability and lack of clarity/understanding

Sayan did some good investigations here:

Oracle SQL | Category Archives: scalar subquery caching

I never finished my investigations and articles but one of my summaries was that for this purposes I would always wrap the call in a scalar subquery, even if the function is deterministic.

You should find that all variability in the test goes away.

i.e.

select id, (select f_det(col1) from dual) from test_tab;

Unknown

Mostly a duplicate of your thread from a week ago:

How does deterministic function caching mechanism works?

I find it interesting that you provide THREE links to related info but DO NOT provide a link to your own thread from one week ago where people, including me, tried to help you with THE SAME ISSUE!

I also find it interesting that you NEVER tried what was suggested to you in that other thread: eliminate the CLIENT issues by executing a simple example using a stored procedure on the server.

That 'server' example eliminates ALL CLIENT contributions and gives you the baseline you need to help understand the difference.

You have also made several WRONG assumptions/statements in what you have posted.

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.

WRONG - you are making at least TWO invalid assumtions.

1. You are assuming that the data is magically physically stored in the order that the rows were inserted into the table.

2. You are assuming that the data is magically retrieved in a known physical order when you haven't used an ORDER BY

NEITHER of those is now, nor ever has been, true. For a new table with no extents yet allocated Oracle will often put new rows in different blocks; that is spred them around., You can NOT assume that the first row will go into a new block and then that block will be totally filled with rows before any rows are put into a different block.

So the first set of rows retrieved could have the SAME value for 'col1'. The next set could also have the same value. There aren't any guarantees about what mix of values you get with any one set.

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

WRONG!

The column ORDERING occurs AFTER ALL of the data has been fetched and thus after the function has been called. You are confusing the orde of the data selected from the table with the order of the results presented to the client.

The server can NOT order the data until ALL of the data has been selected. So NO results can be fetched by the client until AFTER all of the processing, function calls and ordering is complete. The client fetches will then just get 'chunks' of data based on the fetch size. And the first is typically just one row to get things initialized in the client.

Again - create a SERVER ONLY example to eliminate client buffer/fetch contributions. and see what the baseline is.

Unknown

Mostly a duplicate of your thread from a week ago:
How does deterministic function caching mechanism works?

I find it interesting that you provide THREE links to related info but DO NOT provide a link to your own thread from one week ago where people, including me, tried to help you with THE SAME ISSUE!

First of all, thanks for your help. I am here in order to learn new things from you. Thanks again for your support. In addition, this thread is not duplicate of the thread that I open a week ago.

eliminate the CLIENT issues by executing a simple example using a stored procedure on the server.

That 'server' example eliminates ALL CLIENT contributions and gives you the baseline you need to help understand the difference.

What do you mean can you give a small example? I don't get you.

You have also made several WRONG assumptions/statements in what you have posted.

1. You are assuming that the data is magically physically stored in the order that the rows were inserted into the table.

If I am wrong, how come I found the cache hit numbers (the number of CLIENT_INFO) in the first and second example? Besides, is this documented?

The column ORDERING occurs AFTER ALL of the data has been fetched and thus after the function has been called. You are confusing the orde of the data selected from the table with the order of the results presented to the client.

Most importantly, if you are right, how come the cache hit number changes with the same arraysize (15), please tell me ?

Finally, a million dolar question for you, please please tell me, does the the number of fetch equal the number of roundtrip?

As far as I know, in every roundtrip a fetch operation is perfomed. If this is not true please tell me the real story?

Thanks for your patient and support!

Unknown

So now just create a simple stored procedure to call the example function directly WITHOUT involving a client.

Create a WORK table to hold the results.

Then the  procedure can just SELECT from the function and insert the results into the WORK table. No client - no fetch buffer.

Compare the number of calls for each.

Do you mean like the following?

create procedure rp0428
is
begin
set_client_info(0);
insert into test_tab2
select id, f_det(col1), col2 from test_tab;
end;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec rp0428;
PL/SQL yordam² ba■ar²yla tamamland².

SQL> set serveroutput on;
SQL> exec get_client_info;
10

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

As you can see the result is 10 now. It means the function has executed only 10 times.

Can we say that the result is 10 because there are 10 distinct values on col1? There is not any fetch operation therefore it treats like scalar subquery caching. Am I right?

However, in my first example, (ordered query example) the result is same with this result. So, what can we say according from this result?

Thanks

Jonathan Lewis

NightWing wrote:

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.

The function is deterministic for the duration of a database call (from client to server)- and for the query with the "order by" clause the first database call acquires all the data sorts it, dumps it into the temp segment (if it is too large to stay in the pga) and returns the first few rows.  In the pl/sql example there is a single call from client to server, which is why you get only 10 calls for the function for the duration of the block.

Fetch = round-trips ... yes, ignoring parse/exec/call/commit/etc. round-trips

Regards

Jonathan Lewis

Unknown

Do you mean like the following?

Yes - that example is what I was talking about. There IS NO CLIENT so you have taken all of the client, client fetches and round trips out of the picture.

The result for that simple example is PERFECT CACHING. The cache is large enough to hold everything.

SQL> set serveroutput on;
SQL> exec get_client_info;
10

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


As you can see the result is 10 now. It means the function has executed only 10 times.

Can we say that the result is 10 because there are 10 distinct values on col1? There is not any fetch operation therefore it treats like scalar subquery caching. Am I right? 

However, in my first example, (ordered query example) the result is same with this result. So, what can we say according from this result

Correct - those two examples ARE IDENTICAL. They both do ALL of the work on the server with NO CLIENT INTERFERENCE!.

That is why I kept telling you to do that SERVER ONLY example to get the baseline. And that is why I said this:

The column ORDERING occurs AFTER ALL of the data has been fetched and thus after the function has been called. You are confusing the orde of the data selected from the table with the order of the results presented to the client.

The ORDER BY forces Oracle to do ALL of the work on the server with NO CLIENT INTERFERENCE!.

That is why you get the same results as your new SERVER ONLY example.

ALWAYS, ALWAYS, ALWAYS start with the simplest possible example that has the fewest possible variables to get the baseline. In this case it is the maximum possible amount of caching: no other users, no client interference, no extremely large amounts of data that might exceed PGA memory, etc.

That establishes the absolute MAXIMUM you can ever hope to achieve. Every other example you do will then be compared to that 'theoritical' maximum.

Unknown

Hello Sir Jonathan Lewis,

.

In the pl/sql example there is a single call from client to server, which is why you get only 10 calls for the function for the duration of the block.

Thanks for your valuable thoughts. I have one more question maybe the question is meaningless but I am just trying to work up a connection between DETERMINISTIC and FETCH. So, the function is executed without a fetch, right? As a result, isn't it illogical to say "deterministic function caching is only available the lifetime of a fetch"? Because, in this case there is without a fetch. For this reason, can we say logic of deterministic function caching beyond lifetime of a fetch?

Unknown

Thanks for your help.

Jonathan Lewis

NightWing wrote:

 isn't it illogical to say "deterministic function caching is only available the lifetime of a fetch"? 

Right now it wouldn't be illogical it would simply be wrong because, as you have shown from the example suggested by rp0428, the dependency is not on a "fetch" it is on a "client call to database" of which a "fetch" is a particular example, and a fetch that occurs inside a pl/sql block is not (necessarily) a call from client to a database.

A more important observation about your comment is that you don't seem to be allowing for the process by which we learn about the internal mechanisms of Oracle. The examples you originally cited are a few years old, they present a scenario, show results and present a conclusion - and it is this scientific process that allows us to deduce things that Oracle Corporation does not tell us. The fact that someone can come up with a different scenario and extend our knowledge does not make the original conclusion illogical (in this case) it simply highlights the fact that the original conclusion was limited in its applicability.

Fortunately the examples have shown their working, so it is easy for anyone who reads them to see the assumptions they have made and the scenario they are considering and appreciate that (a) the conclusion is correct for the scenario and (b) that there may be other scenarios that they need to test for themselves.

Regards

Jonathan Lewis