DB2 Explain doesn’t do it for Developers
So there I was running a regular check of PLAN_TABLE in a pre-production environment, checking for any potential poor access paths, when I noticed a new package with a somewhat worrying access path. A tablescan of a 10 million row table with a nasty non-matching index scan, via a nested loop join, of a 20 million row table. Not nice at all.
After some investigation, I found the Developer responsible (let’s refer to him as Dave for reasons of anonymity). Dave the Developer tells me that the offending package/SQL is part of a data clean up exercise that will also include an Update and a Delete within each Unit of Work. Dave the Developer also tells me that the SQL will be repeated approx. 500,000 times. Noticing my frown deepening, Dave the Developer attempts to assure me that he has tested the SQL against other environments with no problems.
“Much data in these environments?” I ask suspiciously
Blank look from Dave the Developer
“OK, how many rows in each table?” simplifying the question
“A couple” replies Dave the Developer
“A couple of million? A couple of thousand?”
“Nope, just a couple” says Dave the Developer in a matter of fact manner
Once I calm down, I explain DB2 Explain to Dave the Developer, Plan Tables, production catalog stats, access paths, etc. I show him the access path against production stats and what needs to change in the SQL to make it more efficient. “Always run an Explain, before promoting code” I advise. Dave the Developer runs away quickly, armed with his newly gained knowledge of DB2 Explain, eager to check his SQL.
A few days go by, and I hear nothing from Dave the Developer. “Maybe he’s run his Explain and needs to change the SQL” I assume? The following day however I get a call from the Migration Manager.
“Just heard that you’ve signed off Dave the Developer’s SQL”
“Err no. Who told you that?”
“Dave the Developer. He’s told me he’s executed a DB2 Explain, as you advised, and the SQL is ready for migration”
So, promotion of SQL is halted whilst I decide to have a ‘chat’ with Dave the Developer. Dave tells me how he has executed an Explain for his SQL against tables with production catalog stats and that the SQL is now ready for promotion. I check the access path and, as expected, the access path is the same as before.
“Have you checked the access path?” I ask knowing the answer already
“Well no, I thought Explain would correct it automatically” says Dave the Developer whilst shrugging his shoulders.
I sit with Dave the Developer and we change the SQL as suggested previously. We rerun the Explain and the table and non-matching index scans are replaced by a 2 column & 1 column matching index scan. This is much more efficient and code is now ready for promotion. Dave seems unimpressed, despite his SQL now running far more efficiently.
A few days later I hear a conversation between Dave and one of his colleagues new to the project. His colleague is asking questions about DB2 Explain as he has heard this has to be executed before any packages/SQL can be promoted. Dave shows him how to run an Explain.
“Good” I think to myself, “We have a new convert within the development team who now understands how useful DB2 Explain is”
Then I hear Dave the Developer’s parting shot to his new colleague
“Mind you,” says Dave, “DB2 Explain doesn’t work that well. It doesn’t even change the SQL for you. I had to do that myself!”
A vital process for DBAs in any DB2 shop, to mitigate risk of performance degradation in production for new or amended SQL, is to identify access path changes prior to live implementation. DBAs need to develop procedures to ensure code deployment or amended SQL do not negatively impact the performance and stability of production services. A common method is to replicate production catalog stats into a non production environment, REBIND EXPLAIN(YES) and compare access paths with those in the currently in live. And DB2 is evolving all the time with new features added to assist the DBAs in their battle to maintain production performance and availability.
With each release of DB2 z/OS the Optimizer is improved, increasing the number of SQL functions that are indexable, improving IN list processing, etc. Additionally other features introduced, such as additional Explain tables, provide DBAs with even more information to identify access path changes. And the introduction of Package Stability in DB2 V9, and subsequent improvement in V10, provides protection for production applications enabling fallback to old access paths if required.
However despite the many improvements to the Optimizer, increased catalog stats, and many enhancements to REBIND, DB2 Explain cannot, at the moment anyway, rewrite inefficient SQL.
And so, for one Developer at least, DB2 Explain just doesn’t do it…
Contact me @db2bloke
Click here for more info on DB2 10