Determining Deterministic IV – Again Not So Simple

In the second part, we skipped quickly over some simple performance benefits of a DETERMINISTIC function whilst in the third part, we touched every so briefly on the surprising influence of fetch sizing.

Obviously there are going to be variations in behaviour with different limits to the caching depending on the number and order of rows, the number of distinct values and the length and datatypes in and out of the function.

However, here are some interesting trial and error observations based on variations of the code used in the other articles. These observations have been made after messing with the sizing of the column PADDED_VAL1 (a column designed to give the deterministic mechanism a bit of width to deal with for a change), keeping the length of the data to that column limit and also by changing the number of distinct values across the 10000 rows.

I’ve ordered the rows by padded_val1 to try to get the best gains from DETERMINISTIC.

I was expecting to see some sweet spots but there are some surprising variations.

I’ve kept ARRAYSIZE consistent in all tests.

So, just following this script and modifying where indicated:

set arraysize 1000

drop table det1;

create table det1
(id          number
,val1        number
,padded_val1 varchar2(64)
,constraint pk_det1 primary key(id));

insert into det1
select rownum
,      mod(rownum, 8 )
,      rpad(mod(rownum, 8 ) , 64 )
from   dual
connect by rownum <= 10000;

exec dbms_stats.gather_table_stats(USER,'DET1');

exec dbms_application_info.set_client_info(0);

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

select id, f2(padded_val1)
from   (select /*+ no_merge */
               id, padded_val1
        from   det1
        order by padded_val1);

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

The observations were as follows:

VARCHAR2 length of padded_val1 col No of distinct values # fn calls when padded with ‘X‘ # fn calls when padded with ‘ ‘
64 4 12 12
128 4 511 12
256 4 511 12
512 4 12 12
64 8 16 16
64 9 18 18
64 10 10 10
64 16 24 648
16 8 1514 16
32 8 16 515
64 8 16 16
128 8 765 16
256 8 764 515
512 8 16 16

I could play around plugging in different values for ages.

This is something I might have to come back to another time, but for now if anyone has any thoughts on this behaviour they’d certainly be appreciated.


3 Responses to Determining Deterministic IV – Again Not So Simple

  1. Pingback: Determining Deterministic « OraStory

  2. Hi Dominic,

    you probably know the investigations Jonathan Lewis did on the Subquery / Filter caching – it uses a in-memory hash table for the input and output values. There was a significant change between 9i and 10g regarding the organisation of this in-memory table and you could get some interesting effects due to hash collisions.

    I haven’t checked this myself, but any chance that you encounter a similar thing here with the caching of the deterministic functions?

    I wouldn’t be too surprised if Oracle used the same mechanism for both forms of caching.

    Happy New Year!


    • DomBrooks says:


      Yes, hash collisions – a good point, definitely worthy of further investigations.

      There are a number of deterministic-related threads that I’ve started on but are still outstanding.

      To be honest, I’ve probably lost a bit of momentum and motivation – Xmas, etc plus it’s hard when the last part probably concludes that if you’re looking at deterministic functions for performance then you should probably be looking at other features …. I need a New Year’s resolution to finish what I’ve started here.

      Subquery caching and deterministic functions probably do use the same hashing mechanism but having said that, from the previous part, I observed that deterministic functions were influenced by client fetch size settings in a way that subquery caching was not – at least not to the same extent – so who knows. Futher investigations still required there as well.

      Thanks for stopping by and providing some input as well.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: