Determining Deterministic IV – Again Not So Simple
December 16, 2010 3 Comments
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 ‘ ‘|
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.