31 January 2018
Probably you already executed actions to optimize your dynamic statement cache: decrease the number of full prepare to increase the DSC hit ratio.
If so, you have been faced with the selection of programs candidates for the use of ‘CONCENTRATE STATEMENTS WITH LITTERALS’
Using a top down approach, some of the steps leading to the solution includes:
- Cache usage repartition by application
- Then for the Top-n applications : cache usage repartition by program
Afterwards, there are also some tips to take into account:
- Predicates containing expressions with constants will also be replaced so they should not be converted in case they are used by some INDEX ON EXPRESSION
- Predicates containing LIKE are not converted so programs generating statements of this type will not benefit from this feature
Click4DB automates all those steps in 1 click :
- Show cache usage repartition by application. Click4DB is able to apply a Pareto rule : limit to the x% ratio of the total cache content
- Show cache usage repartition by programs. Click4DB is able to apply a Pareto rule : limit to the x% ratio of the application cache content
- For each candidate program :
- Analyze the DSC to avoid statement with LIKE
- Analyze the DSC to avoid statement using INDEX ON EXPRESSION
- Show a repartition that simulates the usage after implementation
- Show the CPU impact of the CONCENTRATE option on a specific program
