Reminder
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
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
Exhibit #15- 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
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. 
Exhibit #17- Db2 Advisor on the UPDATE SQL
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
7 of the 8 degradations are less than a timeron, but the SELECT with 33 executions degrades by 296.853%! We can do better!!! 
Exhibit #19- 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
Exciting Results shown by Predictive Index Impact Analysis in Exhibit #21! 
Exhibit #21- Predictive Index Impact Analysis Summary- 18 SQLs Improve
But DARN! 
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
Exhibit #23- 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! 
Exhibit #24- 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!
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!
Tweet
Post from : http://www.dbisoftware.com/blog/db2_performance.php
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=759