In-Line Optimization with V11.1
Sometimes you might find that the Optimizer has chosen an access path with which you don’t concur. It’s not that likely, as the DB2 Optimizer is one of the most sophisticated of its ilk, but it’s possible that you may know something that the optimizer doesn’t: if, for instance, a table has just had some major INSERT or DELETE activity and the RUNSTATS hasn’t executed yet. Or if you know that something is going to change in the future and you want to experiment with some solutions before the fertilizer hits the mobile air-conditioning unit.
In this case, you might want to override the optimizer and tell it what to do. Before V11, that meant using an optimization profile. This is something that can fill the faint-hearted, and even some of the more gung-ho, DBAs with dread. It’s a tricky process to get right, with a number of gotchas, but V11.1 has just introduced a much more friendly way of applying the change you require. Let’s look at a V10.5 example and then a V11.1 version of the same thing and compare and contrast. Let’s just take a simple OLAP query and mess it around a little. The SQL is
If I run that as is, with SET CURRENT EXPLAIN MODE YES, the db2exfmt output will show an estimated cost of 15710 timerons. I’m wondering if forcing a Hash Join between the CONTRACT_TYPE_ROW and CONTRACT tables might be a better option.
- So, to start off, you need an OPT_PROFILE table to store the profile. Your best bet is to issue a call to the object builder Proc:
CALL SYSINSTALLOBJECTS(‘OPT_PROFILES’, ‘C’, ”, ”)
- You need to populate that with your profile. That needs to be an XML doc which includes your target SQL (some of which is omitted for brevity) wrapped up in a STMTKEY element :
- This can then be loaded into the OPT_PROFILE table using an IMPORT command
IMPORT FROM Q1_HSJOIN.del of del lobs FROM lobpath REPLACE INTO SYSTOOLS.OPT_PROFILE
Gotcha
Even if you overwrite an existing profile, it’s worth clearing the memory cache. You can even (and this stumped me for quite a while) drop the OPT_PROFILE table and still pick up an old profile, if you haven’t issued a
FLUSH OPTIMIZATION PROFILE CACHE
- Once you’ve done that, you can set the profile to be used with
SET CURRENT optimization PROFILE=’CONTRACT.Q1_HSJOIN’
If you push your SQL through db2exfmt again, you’ll have (hopefully) a new access path including your proposed HSJOIN and a section in the output stating
If you look in the Extended Diagnostic Information section and the following appears
No extended Diagnostic Information for this statement
It indicates that the optimization profile actions have been picked up and actioned.
Gotcha
If you examine your access path using db2expln, you won’t get any of this output and little or no indication that your profile is being used
V11.1
So how is this easier with V11.1? Well, you can conduct your experiment with telling the optimizer what to do, by putting the optimization guidelines into the SQL statement itself. No OPT_PROFILE table. No flushing the memory. In fact, omit steps 1 -4 and just put this in at the end of your SQL statement (NB the comment tags are mandatory) :
Because this is part of your SQL statement, it will show up if you use db2expln. But only as part of the statement; there will still not be an indication that an optimization guideline has been actioned.
And, because we haven’t used a Profile, there will not be any Profile Information output from db2exfmt. The only indication that the guidelines are being successfully used are that
- The guidelines are visible in the Original Statement (not the Optimized Statement)
- The Access Plan appears to show the operation that you specified
- The Extended Diagnostic Information does not contain any warnings or errors
And, just for the record, my guidelines made no difference to the estimated cost at all, so the Optimizer probably got it right in the first place.
Leave a Reply