Db2 Performance: Tuning Db2 LUW Explains and db2advis, Part 2

This is part 2 of Db2 application tuning for Db2 Explains and db2advis. We are on a mission to make Explains and the Db2 Advisor faster and more efficient. PLEASE review PART 1 as a prerequisite so that the information flow is fresh in your mind before continuing. This blog picks up where Part 1 left off.
Reminder icon_idea This blog post contains several Exhibit images to illustrate key points. Due to blog limitations, some of the images are smaller than we'd like. If you right click on an image and choose "show image in a new tab", you will see full size images that are easier to read.

ADVISE_INDEX Indexes

Using the same methodology as previously described in Part 1, we've found another index that can be created. Refer to Exhibit #13 below (we left off on Exhibit #12 in part 1 ).

Exhibit #13- Index on ADVISE_INDEX table

Index on ADVISE_INDEX table

If we create the index in Exhibit #13 above, the results look pretty good: Five SQL statements improve, 13 are unchanged, and none degrade. See Predictive Index Impact Analysis in Exhibits #14 and #15.

Exhibit #14- Predictive Index Impact Analysis Summary

Predictive Index Impact Analysis Summary

Exhibit #15- Predictive Index Impact Analysis SQL Details

Predictive Index Impact Analysis SQL Details

UPDATE SQL - Can we do better?

Exhibit #16 below shows us the UPDATE statement seen in Exhibit #15 above with 16,361 executions. It's a beautiful Explain. Notice that the new index from Exhibit #13 is used, and the IXAND [3] operation tells us that Db2 is doing multiple index access. IXAND sets off alarms in my head because whenever we see this behavior, quite often it is possible, and much more efficient, to get Db2 to use just one "better" index instead of two. My hypothesis is, given the equal predicates, that an index on RUN_ID, NAME, and CREATOR would be an improvement.

Exhibit #16- Explain the UPDATE SQL

Db2 Explain of UPDATE SQL with 16,361 executions!

We passed the expensive UPDATE SQL statement to the IBM Design Advisor (db2advis) and, sadly, the Advisor didn't suggest a new index on columns RUN_ID, NAME, and CREATOR. But, that's okay because we can add our own recommendation and then test it with Predictive Index Impact Analysis. See Exhibit #17 below and notice the index named BRILLIANT_IDEA that I added. icon_smile

Exhibit #17- Db2 Advisor on the UPDATE SQL

db2advis gives no recommended indexes, so we add our own.

WHOOOOPS! We ran the BRILLIANT_IDEA index through the Predictive Index Impact Analysis process, and 2 statements improve but 8 statements degrade!. See Exhibits #18 for the Summary and #19 for the details.

Exhibit #18- Predictive Index Impact Analysis Summary on BRILLIANT_IDEA

Predictive Index Impact Analysis Summary on BRILLIANT_IDEA

7 of the 8 degradations are less than a timeron, but the SELECT with 33 executions degrades by 296.853%! We can do better!!! icon_wink

Exhibit #19- Predictive Index Impact Analysis SQL Details

Predictive Index Impact Analysis SQL Details

I dropped the index created in Exhibit #13 above, then came up with another brilliant index idea by studying the predicates further of this SQL and others. I'll add a second index called BRILLIANT_IDEA2, and then run Predictive Index Impact Analysis again using BOTH of the BRILLIANT_IDEA indexes. See exhibit #20 for the BRILLIANT index definitions.

Exhibit #20- Adding my BRILLIANT_IDEA Index Recommendations

2 Indexes to improve access to ADVISE_INDEX table

Exciting Results shown by Predictive Index Impact Analysis in Exhibit #21! icon_cool

Exhibit #21- Predictive Index Impact Analysis Summary- 18 SQLs Improve

Predictive Index Impact Analysis Summary- 18 SQLs Improve

But DARN! icon_mad 18 statements improve but 1 statement degrades, so we need to look at the details. Exhibit #22 shows all the statements that improve, and, after we scroll down, Exhibit #23 shows the statement that degrades.

Exhibit #22- Predictive Index Impact Analysis Details- 18 SQLs Improve

Predictive Index Impact Analysis Details- 18 SQLs Improve

Exhibit #23- Predictive Index Impact Analysis Details- 1 SQL Degrades

Predictive Index Impact Analysis Details- 1 SQL Degrades

Fortunately the UPDATE statement only degrades by 2.3697 timerons, or 6.2%. In light of the other 18 SQLs that demonstrate significant efficiency improvement, I'm willing to accept this slight degradation as a casualty of improvement. Exhibit #24 below shows the two indexes that you should create in your environments! Of course, you can give them different names if you want! icon_rolleyes

Exhibit #24- NEW Indexes for the ADVISE_INDEX table

Two new Indexes for the ADVISE_INDEX table

Plain Text DDL:

CREATE INDEX "DB2ADMIN"."BRILLIANT_IDEA1"
ON "DB2ADMIN"."ADVISE_INDEX"
("NAME" ASC, "CREATOR" ASC, "RUN_ID" ASC)
ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS ;

CREATE INDEX "DB2ADMIN"."BRILLIANT_IDEA2"
ON "DB2ADMIN"."ADVISE_INDEX"
("RUN_ID" ASC, "USE_INDEX" ASC, "EXPLAIN_TIME" DESC)
ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS ;

Db2 Performance Tuning is an Iterative Process

Stay tuned in Db2 friends! I'll have a couple more new indexes for you in Part 3, and Part 3 will conclude this series of blog posts. Moreover, I think you'll find the Explain analysis to be helpful too! icon_biggrin

Shout out to Ember Crooks

I am dedicating this blog post to my friend and fellow Db2 professional Ember Crooks. As you may know, she has appeared as our special guest on The Db2Night Show several times. She's an IBM Champion, an IBM GOLD Consultant, and a very helpful blogger about many things Db2 related. Read her DATA GEEK blog and follow @ember_crooks on Twitter!

A Shameless DBI Marketing Moment

Thanks to SOX and Business Best Practices, Change Control Management want to know why a DBA wants to make a change, what are the expected benefits, and are there any risks? This is why DBI Software introduced Predictive Index Impact Analysis in V6.3 of DBI pureFeat, and further enhanced Predictive Index Impact Analyis in DBI pureFeat V7.0. Impact Analysis allows DBAs and Managers to understand the anticipated benefits and risks of index changes. You can also easily prototype your own brilliant index ideas!

Sharing is Caring

Let your Db2 friends know that you found a helpful blog on improving the performance of Db2 Explains and Db2Advis! Together we can make Db2 Greater and grow the Db2 Community! When Db2 wins, all Db2 Professionals benefit!