We decided to optimize our dynamic cache management in 2018. The objective is to decrease the number of full prepare and thus increasing the DSC hit ratio.
Maybe you also have the case of programs dynamically generating statements of the same type, and filling the cache with thousands of statements. Not only each statement must be full prepared, but it also push other ones out of the cache!
We identified the biggest users of the DSC and asked them to add ‘CONCENTRATE STATEMENTS WITH LITTERALS’ in 13 of their stored procedures.
After this modification, the project used only 13% of the statement cache in place of 33%. Moreover, the global DSC hit ratio raised to 98% (from 84% before the change).
However we had a CPU increase for 1 of the 13 stored procedures due to this change.
As all the literals are replaced by variables, the following statement :
REPLACE(UPPER (TRANSLATE (COL1, ''eeeeeeaaaaauuuuiiooccy'',''éèêëÈÉàâäÀÂùûüÜîïôöçÇÿ'')), '' '','''') LIKE 'CONTE%'
AND UPPER (TRANSLATE (COL2, ''eeeeeeaaaaauuuuiiooccy'',''éèêëÈÉàâäÀÂùûüÜîïôöçÇÿ'')) LIKE 'T%'
is now stored in the cache in the form :
REPLACE(UPPER (TRANSLATE (COL1, &,&)), &,&) LIKE 'CONTE%'
AND UPPER (TRANSLATE (COL2, &,&)) LIKE 'T%'
The problem here was that as there is an INDEX ON EXPRESSION defined on COL1 and COL2, it can’t be used anymore. Probably you also noticed that the LIKE predicate is not taken into account by the CONCENTRATE option.
Conclusion : when implementing the ‘CONCENTRATE STATEMENTS WITH LITTERALS’, pay attention to statements containing LIKE predicates and/or INDEX ON EXPRESSION.