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.

Related

3 Responses to Determining Deterministic IV – Again Not So Simple

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.

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.