1

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in DB2 through which I can get the information like, which plans the optimizer considers, the cost of each plan?

3

I don't know of a way to trace all the plans the optimizer considers. Obviously the EXPLAIN statement gives you the plan the optimizer eventually selects (and the re-written statement), but that doesn't tell you anything about the ones it doesn't select.

One way to get a little more info might be to set CURRENT QUERY OPTIMIZATION to different values (the default is 5), run EXPLAIN on the statement, then set it back to the default (5) and run EXPLAIN again.

If you get different plans with the other optimization levels, this may provide a clue as to the paths DB2 is taking in optimizing your statement (of course, if you get the same plan, it doesn't tell you anything!). I would suspect that the simpler the query the more likely you are to find no difference (so conversely this technique would work better with more complex statements).

  • For a complex or even moderate complex statement I assume there is huge number of plans considered (most of them poor). For educational purposes it would be rather cool though to be able to save them in copies of the explain tables and be able to examine them with say db2exfmt --rejected_plans or similar – Lennart Aug 21 '15 at 5:15
  • It would be fascinating. More than any engine I've worked with, DB2 spends its time planning but often with excellent results (I recall one horrifically complex set of views denormalising about 20 tables with about 60 joins - DB2 spent 3 of the 4 minutes of execution time planning ... but that meant everything ran in 1 minute!) – Dave Jones Aug 21 '15 at 5:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.