i have performance Problems with my parameterized sql statemens which are commited to the database.
The Problem is, that die Execution Plan predict only 9 steps, but the DBC.DBQLOGTBL shows for a couple of querys up to 296 steps.
The bulk of the querys have only 9 steps and a good performance, but the worst querys smashed up our performance.
How can i analyse why some querys for other values in the parameterized sql takes up to 296 steps ?
What would be the best way ?
I have seen, that the Querys which perform bad are Cached Queries.
For queries with the same ProcID, are you seeing the CacheFlag sequence S-G-T (Specific, Generic, Cached generic) where the G and T perform badly? Check Carrie's article on "peek using".
Perhaps you could enable DBQL Explain / XMLExplain logging (for a brief period) to capture the EXPLAIN for the bad Generic plan, then use that to try to identify stats, constraints, etc. that will improve that plan.