(Originally posted 2005-12-01.)
If you ask Performance Trace nicely enough it’ll tell you what statement in a package is consuming all the CPU.
Over the past couple of years we’ve been working on code to analyse the DB2 Catalog and PLAN_TABLE tables to do SQL tuning. It’s been an interesting journey – and, I guess, a never-ending one.
Each client provides fresh insight and challenges. Our current Chinese one is just such a client: The statement numbers in Performance Trace and SYSIBM.SYSPACKSTMT/PLAN_TABLE don’t always tie up. SYSPACKSTMT and PLAN_TABLE agree with each other. It’s the Performance Trace that’s anomalous.
It turns out that the static data and the Performance Trace were collected some weeks apart. In a busy installation code gets edited, tested, recompiled all the time. Particularly if the code is underperforming. I think we got bitten by that.
So in the future we’ll be surer to try and get fresh Catalog/PLAN_TABLE data when we take a trace – which probably means a respin as we generally home in on a particular package to trace after the main DB2 (and z/OS) study is done. Unfortunate but necessary.
