CONCENTRATE STATEMENTS WITH LITTERALS side effect

alain pary

CONCENTRATE STATEMENTS WITH LITTERALS side effect

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.

Joe Geller

RE: CONCENTRATE STATEMENTS WITH LITTERALS side effect
(in response to alain pary)

You also have to watch out for filter factors.  Db2 will now be using default filter factors instead of using distribution stats for a literal.   This can be a problem if you have skewed data and have collected distribution stats.  For example, WHERE STATUS='OPEN' (for which there might be less than 100 rows) would be cost estimated as if you had WHERE STATUS=?.

Obviously, not all queries will get a change in access path because of that, but some will.

If you have a SP that you really want to concentrate literals for, but one query within it that you don't, it would be best to modify that query (if allowed to).  If a query has a mix of literals and ?s, then Db2 doesn't concentrate the literals for that query.  Of course, if you are allowed to modify the SP, you could change the other queries to use variables instead of literals.

Joe

In Reply to alain pary:

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.