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

Would you like Db2 LUW Explains and db2advis.exe to run faster and more efficiently? If so, then this blog is for you! Explain and Db2Advis are applications just like any other, and their performance can be improved by adding indexes to EXPLAIN and ADVISE tables!

In this new series of Db2 LUW Performance blog posts, we will share with you our performance analysis of Db2 EXPLAIN and Db2Advis processing and SQL with the goal of ultimately providing you with CREATE INDEX commands that will make Explain and Db2Advis processing faster and more efficient.

You might be thinking "Who cares?" Well, everyone should care because efficiency is important! And if your management is hounding you for a performance fix to a failing application, you probably want your Explains and Db2Advis to run as fast as possible!

DBI Customers using DBI pureFeat™ Performance Management Suite for IBM Db2 LUW V6.3 or V7.0 will also find this analysis, and the recommended new indexes, very beneficial to DBI's Predictive Index Impact Analysis processing! Briefly, Impact Analysis discovers all of the SQL contributing I/O to a table, then Db2 Explains are run with and without contemplated indexes to ascertain the benefits and risks, if any, of creating a new index.

---

Tip 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.

Analyzing the SQL Performance of Explains and Db2Advis

We will begin this tuning adventure by analyzing Db2 Application Performance for a one month time period (14 July 2018 to 14 August 2018 ). Looking at Exhibit #1 below, the "db2jcc_application" is the application that is running Db2 Explains for Brother-Panther®. Note that the Index Read Efficiency (IX Read Efficiency) is greater than 10 for both this application and the db2advis.exe application immediately below it. This is our first clue that performance improvements may be possible.

Exhibit #1- Application Performance

SQL Executed by an Application

We right-click on the db2jcc_application, and select option "Statement Performance". Now we see the SQL executed by this application (refer to Exhibit #2 below). Notice there is a very costly DELETE that has used 45% of the CPU (across all of the statements executed by this application), and the Average Index Logical Reads (Avg IX L Reads) is 107.6 which suggests the high probability that this SQL is doing an index leaf page scan.

Exhibit #2- SQL Executed by an Application

SQL Accessing the EXPLAIN Tables

While Exhibit #2 provides some immediately interesting information, we need to make sure that we are focused on the SQL that accesses Db2's Explain tables, so we apply a filter (Exhibit #3) to limit the Application SQL display to only SQL statements referencing EXPLAIN tables. The result is shown in Exhibit #4. The DELETE SQL is still the most costly.

Exhibit #3- Apply a text filter for EXPLAIN

Exhibit #4- Application SQL containing string EXPLAIN

SQL Trouble Maker! Flag it!

We right-click on the DELETE verb, and choose "Follow Up Note..." option. This allows us to make notes about the performance of a statement, record solutions, or share information with other DBA team members. Think of Flags as Post-It notes that you can attach to SQL, and the flags "stick" to the SQL statement whenever and wherever the statement appears. These Flags are also very helpful to performance trend charts and comparisons - capabilities that will help you verify successful tuning results. Exhibit #5 shows adding a Red flag to this costly DELETE statement.

Exhibit #5- Flag for Follow Up

Db2 Explain the DELETE SQL

Refer to Exhibit #6 below. The DELETE is relatively expensive with a Db2 Timeron cost of 2,530! The IXSCAN predicates show that EXPLAIN_TIME is being used to search the table, and the only available index is SQL180618181220940. EXPLAIN_TIME is the second column in the index, therefore the costly index leaf page scan. EXPLAIN_TIME is the highest cardinality column in the table. Thus, our hypothesis is that an index with EXPLAIN_TIME as the first column might be helpful. Note that while this DELETE SQL only refers to the EXPLAIN_INSTANCE table, you can see on the top right of the Explain that all of the EXPLAIN tables are actually involved in this DELETE because of cascading delete referential integrity. It is possible that other EXPLAIN tables may need improved indexes as well. Next, we will pass this expensive DELETE to DBI's enhanced Db2Advis process by clicking the "Design Analysis" button at top left.

Exhibit #6- Explain the DELETE SQL

Db2 Design Analysis - db2advis.exe output

Sadly, but not surprisingly , Db2Advis did not recommend any new indexes. In Exhibit #7 below, you can see there are no new indexes recommended (these would begin with IDX followed by a bunch of digits) and the existing indexes on tables are shown. It is still our intuitive DBA hunch that a new index on EXPLAIN_TIME on the EXPLAIN_INSTANCE table would be beneficial - we will check this next!

Exhibit #7- DBI Enhanced Db2Advis.exe output

DBI Predictive Index Impact Analysis to the Rescue!

From the list of existing indexes in Exhibit #6, we right-click on one of the existing indexes, select option "Impact Analysis...", then option "Add Index". This gives us a dialogue window where we can "invent" our own index recommendation and test our new index hypothesis. Because the existing index on EXPLAIN_INSTANCE contains columns EXPLAIN_REQUESTER, EXPLAIN_TIME, SOURCE_NAME, SOURCE_SCHEMA, and SOURCE_VERSION, we will test a new index that likewise contains all of these columns but with EXPLAIN_TIME as the first column and EXPLAIN_REQUESTER as the second column. Refer to Exhibit #8. After clicking OK, the "SCOTTSIDEA" index appears in the list of recommended indexes (see Exhibit #9).

Exhibit #8- Adding a New Recommended Index

Exhibit #9- Updated List of Recommended Indexes

Run Impact Analysis on the new SCOTTSIDEA Index

Right-click on the SCOTTSIDEA Index (Exhibit #9 above), and select option "Impact Analysis", then option "Run Impact Analysis". As you'll see in Exhibit #10 below, five SQL statements improve! Exhibit #11 shows a detailed statement analysis for all SQL contributing I/O to the EXPLAIN_INSTANCE table during a recent three hour time period (time periods can be changed). Not only does the Red Flagged DELETE statement show improvement with the SCOTTSIDEA proposed index, but four other DELETE statements show cost improvement as well. And, the best news is, no SQL statements forecast performance degradation!

A Shameless 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.

Exhibit #10- Predictive Index Impact Analysis Summary

Exhibit #11- Predictive Index Impact Analysis SQL Details

CREATE INDEX SCOTTSIDEA DDL

We've arrived at the first of several index ideas for improving the performance of Db2 Explains and Db2Advis. This is the first blog post of a series of blogs that will reveal more indexes that will speed up Explains and Db2Advis, and make these functions more efficient. The final post of the series will summarize all of the recommended indexes and include all of the CREATE INDEX DDL (in plain text) so that you can easily create the new indexes in your environments. Exhibit #12 below shows the CREATE INDEX DDL syntax for the first recommendation, and plain text DDL appears beneath it. Go ahead! You can create it now! This one is a keeper!

Exhibit #12- New Index on EXPLAIN_INSTANCE

Plain Text DDL:
CREATE INDEX "DB2ADMIN"."SCOTTSIDEA" ON "DB2ADMIN"."EXPLAIN_INSTANCE"
("EXPLAIN_TIME" ASC,
"EXPLAIN_REQUESTER" ASC,
"SOURCE_NAME" ASC,
SOURCE_SCHEMA" ASC,
"SOURCE_VERSION" ASC)
ALLOW REVERSE SCANS
COLLECT DETAILED STATISTICS ;

Db2 Performance Tuning is an Iterative Process

In subsequent blog posts of this series, I've found more useful indexes to create. Sometimes I created a new index, and then later found a better index solution, so I dropped the prior one. Don't worry, I will guide you through all of the new indexes and you will be a very happy DBA at the end!

Dedication to Michael Krafick

I am dedicating this blog post to my good friend and fellow Db2 professional Michael Krafick. Michael recently started a new Db2 training video series on YouTube called #DiscoverDb2. It's a mighty fine collection of shorter videos that cover very helpful Db2 topics. You should check it out. You can follow Michael on Twitter at @MKrafick. Why dedicate this post to Michael? Well, he told me that his youngest child would probably graduate from college before I blogged again. #ThanksForTheMotivationMike

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