Introduction

DB2 UDB V8.2 introduced a new tool called the Design Advisor, replacing the Index Advisor with a broadened scope. The Design Advisor now provides advice on materialized query tables (MQTs), multidimensional clustering tables (MDCs), and Data Partitioning Feature (DPF) partitioning keys, in addition to indexes. However, even for DBAs interested only in indexes, the Design Advisor is improved over the Index Advisor in the prior release. This article, Part 1 of a two-part series, looks at using the Design Advisor on online transaction processing (OLTP) databases where the primary focus is on designing the appropriate indexes to achieve high performance.

We’ll demonstrate the effective use of this tool by covering specific examples, examining pitfalls and common questions, and highlighting V8.2 enhancements. Part 2 covers additional details related to MQTs, MDCs, and DPF partitioning keys, focusing on the data warehouse environment.

Value of the Design Advisor

The Design Advisor can provide recommendations comparable to that of a database tuning expert. The benefit for non-experts is getting a better design than they would otherwise achieve. For experts, the Design Advisor can save their expensive time by providing an initial design that they can then try to further improve. The Design Advisor can also provide independent confirmation of an expert's design.

What percentage performance improvement can you expect from the Design Advisor? That depends on the quality of the initial design. However, improvements can be dramatic.

When the Design Advisor can help

There are many situations where DBAs may wish to use the Design Advisor. Two important contexts are using it prior to production, and using it after putting a system into production. Examples of when to use the Design Advisor prior to production include:

• Creating a new set of indexes during initial application development, and
• Fixing a poorly performing query during application system testing.

You can also use the Design Advisor on the production system to change indexes in response to:

• Differences in the actual production conditions compared to estimates during development. For example, a column is being accessed more frequently than anticipated; a table is much larger than anticipated; certain queries are being performed more frequently.
• Changes in schema design during the operational life of the system. Applications are often tweaked during their operational life, for example, to add a new column or a new query to generate a report.
• Database aging. As the database ages, performance can slowly degrade as tables get bigger, usage patterns change, and workload volume increases.

The Design Advisor is often used in conjunction with other DB2 features. Some examples include:

• Health Monitor. You can set up the Health Monitor feature to alert you to conditions like sort spills. You can then elect to use the Design Advisor to help find indexes to mitigate this performance degrader.
• Visual Explain. You may be using Visual Explain or related features to inspect query access plans. You may notice excessive numbers of table scans. You can use the Design Advisor to see if indexes would result in an access plan with fewer table scans.

Design Advisor overview and what's new in V8.2

You can access the Design Advisor either as a GUI tool or through the command line. In V8.2, you access the GUI from a new location. From the Control Center, right-click on the database that you want to work with. In the resulting menu, select Design Advisor....

The command line command remains unchanged: db2advis. GUI or command line? The choice is largely one of preference; the tools have only minor differences in functionality from one another.

Once you've identified the need to use the Design Advisor, follow the steps listed below to use the tool. For each step, we’ve listed some key V8.2 enhancements that apply to OLTP databases, where the primary interest is in indexes.

The Design Advisor is part of a wider set of Autonomic Computing improvements in V8.2. Other improvements are in the areas of:

• workload management
• utilities that regulate themselves
• quick performance tuning
• continuous system health checking
• simplified memory management
• and others

See the Resources section for details.

Introduction to the case study

The following case study demonstrates how you would use the Design Advisor: During the system testing phase for a new application, you find that two particular transactions run more slowly than you were expecting. After some initial investigation, you decide to determine whether indexes can address this performance problem.

The case study is divided into the following steps:

1. Collect and describe the workload to the Design Advisor
2. Decide how and when you want the Design Advisor to run
3. Review the recommendations
4. Next steps

The steps are illustrated with the GUI. A subsequent section illustrates the command line version of the tool.

Back to top

Step 1 - Collect and describe the workload to the Design Advisor

As you see in Figure 1, the Design Advisor allows us to select which features we want the Design Advisor to consider, including indexes, MQT, MDC, and partitioning. We select only Indexes as that is what we are interested in.

Next, we need to provide the workload to the Design Advisor. The Design Advisor has several options to input and collect the workload. In this case, we direct the Design Advisor to read the workload from a file which contains the transactions. The result is shown in Figure 2. Other options for collecting a workload include:

• Recent SQL statements (from the dynamic SQL snapshot)
• Query Patroller statements (more applicable to data warehouse databases)
• Static SQL statements (from application packages)
• Explained SQL statements
• Event Monitor statements

The last two options are available only from the Design Advisor GUI, and not the command line version.

The entire application consists of 40 transactions and reporting queries. In our file, we elect to provide 29 transactions, including the two that are causing production problems. We've not bothered to included certain ad hoc reporting queries.

For best results, identify all the SQL statements that the application will run against the DB. This enables the Design Advisor to provide recommendations that are globally optimal. The following options are listed in order of decreasing preference:

1. List a complete workload, including transactions that are not involved in the performance problem.
2. Include all the problematic transactions.
3. Deal with each problematic transaction separately; that is, run the advisor twice, once for each transaction.

To the extent that you omit SQL from the workload you provide to the Design Advisor, you should bear this in mind when interpreting the recommendations. In other words, ensure the recommendations make sense in light of your knowledge of other transactions that you've omitted from the workload provided to the Design Advisor.

In our example, we've given a frequency of 1, 10, 100, or 1000 to each transaction. This will cause the Design Advisor to weigh the transactions with a frequency of 10 ten times more heavily than those transactions where we've set frequency to 1.

Back to top

Step 2. Decide how and when you want the Design Advisor to run

In our case study, we are running the Design Advisor on a test machine because the application is not yet in production. In this situation, our main concern is ensuring this non-production machine reflects the anticipated production environment so that the Design Advisor recommendations will be valid on the production system. In particular, the catalog statistics need to be updated so that they are representative of the production system. This can be done with other DB2 tools, namely db2look. The database does not need to have data loaded. (Note: If you also want MDC recommendations, a representative sample of data is required in the tables, as well).

Also, we want to limit the amount of disk space used for the recommended indexes. The applicable wizard panel is shown in Figure 3.

While not illustrated in this case study, the Design Advisor can provide recommendations on systems after they are put in production. In this situation, the major consideration is the potential impact of running the Design Advisor on the production system. Here are some considerations for anticipating the impact you can expect.

• A major determinant of the resources used by the Design Advisor is the size of the workload that you provide for it to analyze. If you simply want recommendations related to a handful of queries, then the impact will be less than for a larger workload.
• Understanding the resources used by the Design Advisor can help further anticipate the impact. The Design Advisor uses CPU (for example, it generates queries that are evaluated by the DB2 optimizer), and consumes I/O resource by reading and writing to DB2 explain tables.

If you elect not to run the Design Advisor during prime shift, other options are:

• Schedule the Design Advisor to run during off hours.
• Copy the database to a separate test system and run the Design Advisor there.

Back to top

Step 3. Review the recommendations

As you see in Figure 4, the Design Advisor generated a set of recommendations that would lead to almost 20% improvement. The list of recommendations includes: (1) keeping the two existing indexes on the CUSTOMER table and (2) creating 1 index and keeping 1 index for the DISTRICT table. We have reason to be satisfied with this improvement given that we started with a well designed set of indexes. However, you'll recall we wanted to fix two particular queries so we still need to find out if we achieved our specific goal.

The Design Advisor has new detailed reports that provide deeper insight into the recommendations. The highlighted row in Figure 5 is one of the two transactions we were having trouble with. It has improved dramatically.

On the next panel, shown in Figure 6, the recommendations show there are indexes that can be dropped. By comparing the first index to the recommended ones in Figure 4, we realize that it is a subset of one of the existing indexes.

The recommendation should be understood as meaning that if you implement ALL these recommendations, then you'll get the performance benefit shown. There may be reasons you want to pick and choose recommendations; however, be aware that the performance benefit of your hand-picked set is unknown. You should be sure you are not degrading your performance.

Question: What about recommendations for wide indexes? The Design Advisor sometimes recommends indexes that have more columns than DBAs are used to including in their indexes, say more than 4 columns. DBAs need to use their judgment about whether to accept the recommendation or to trim columns before they implement the recommendation.

Some recommendations involve a small change to an existing index. Such recommendations are represented as two recommendations: Add the new index and drop the old one. So it’s a good idea to accept those recommendations as a pair; that is, add+drop or do nothing. Don't add the new index and keep the existing one or simply drop the existing index.

In our example, we are adding an index with columns in the order colA, colB and dropping the index with the same columns in the reverse order. This is how the Design Advisor presents the recommendation "you'll get better performance if you switch the order of columns in the index." Other examples of "add/drop" pairs are: (1) adding an index that is a superset of another (that is, includes an additional column), and (2) replacing two uni-directional indexes with a single bi-directional one.

Question: To drop or not to drop? The Design Advisor may identify indexes that can be dropped, other than those that form part of an add/drop pair. You should make the decision based on the risk and benefit. From what you know, will there be any impact of dropping the index? The biggest reason to keep an index is if you know it is used by a transaction or query that you have not included in the workload used by the Design Advisor. Also note that by default, the Design Advisor focuses a subset of the workload that consists of the most expensive statements. So, it is worthwhile to check which statements were actually used before dropping an index. Step 3 described how to view individual statements in the GUI tool. However, you should be aware of the pitfalls of keeping unneeded indexes "just in case." Unneeded indexes impair the performance of insert/update/delete actions, and consume storage and transaction log space.

Back to top

Step 4. Next steps

It may seem that the natural next step after accepting the recommendations is to make the changes. However, there are other things you may wish to do at this point:

• Save the recommendations to show someone else.
• Re-run the Design Advisor with different inputs to see if you can get a better recommendation.

In this case, we set a limit on the amount of storage. Let's run the advisor again and remove this restriction to see if we get a better result. First, we'll save the results of this run by clicking the Generate push button shown in Figure 7 to use the new Generate Report feature.

We can then use the Back button to return to the panel where we can remove the restriction. After running the advisor again, we find that the recommendations are the same.

We now decide to implement the recommendations. Typical steps DBAs do before implementing recommendations are to:

• Replace the generated index names with names that confirm to shop conventions.
• Review the tablespaces where the indexes are to be stored.
• Add comments to the index to indicate why it was created, for example, to help the performance of transaction XYZ.

DB2 V8.2 has some complementary enhancements that make it easier to implement these recommendations. These are:

• Online index creation
• Alter Table support

Back to top

Command-line (db2advis) walkthrough

This section walks through the same case study using the command line. Highlights of the command and output are discussed. See the Resources section for links to more comprehensive information.

The command used in this case study is shown below. We're running the basic command and accepting most defaults.

db2advis –d dtw –i D:\dtw_workload –q tpcc –m I –o D:\report

Highlights include:

• -m I: This specifies that the Design Advisor should consider new indexes. This parameter is not required as the default is indexes only, which is all we are interested in this case study.
• -o output file. This saves the script to create the recommended objects in a file.

As the command executes, it describes work in progress, then outputs the results shown below.

Optimization finished.
5 indexes in current solution
[1425961] timerons (without recommendations)
[1225904] timerons (with current solution)
[14.03%] improvement
			

Highlights include:

• The recommendation set consists of 4 indexes (which could be current or new ones).
• This solution results in a 14% improvement.

Next the Design Advisor displays the recommendations in the form of DDL, which it has selectively commented out. The recommendations appear in the following order:

• New clustering indexes (none in this case)
• Index recommendations: Indexes to create, indexes to keep, then unused indexes.

Excerpts of these recommendations are shown below.

-- LIST OF RECOMMENDED INDEXES
-- ===========================
--  index[1], 0.087MB 
     CREATE UNIQUE INDEX "PAULMCI"."IDX50316201" 
     ON "TPCC  "."DISTRICT" ("D_W_ID" ASC, "D_ID" ASC)
        INCLUDE ("D_YTD", "D_ZIP", …) ALLOW REVERSE SCANS; 
     COMMIT WORK; 
     RUNSTATS ON TABLE "TPCC"."DISTRICT" FOR INDEX "PAULMCI"."IDX50316201" ; 
     COMMIT WORK; 
-- index[2], 0.024MB
     CREATE UNIQUE INDEX "PAULMCI"."IDX50316206" ON "TPCC  "."DISTRICT" …. 
	…
-- RECOMMENDED EXISTING INDEXES 
-- ============================ 
-- RUNSTATS ON TABLE "TPCC"."DISTRICT" FOR INDEX "TPCC"."DIST_IDX1"; 
-- COMMIT WORK;  
-- RUNSTATS ON TABLE "TPCC"."NEW_ORDER" FOR INDEX "TPCC"."NU_ORD_IDX1"; 
    … 
-- UNUSED EXISTING INDEXES 
-- =========================== 
-- DROP INDEX "TPCC"."CUST_IDX3"; 
-- =========================== 

Finally, the Design Advisor completes and presents the information shown below.

 
1744 solutions were evaluated by the advisor.
DB2 Workload Performance Advisor tool is finished.

Back to top

Conclusions

In DB2 V8.2, the Design Advisor was introduced as a replacement for the Index Advisor, reflecting its broadened scope. The Design Advisor now provides advice on MQTs, MDCs, and DPF partitioning keys in addition to indexes. However, even for DBAs interested only in indexes, the Design Advisor is has improved over the prior release. Stay tuned for Part 2, when we’ll look at how to use the Design Advisor in a data warehouse environment.

Resources

• DB2 Information Center: Learn more about the Design Advisor from product documentation in the DB2 Information Center.
  
  

About the authors

Paul McInerney participated in the development of the Design Advisor. His interests include designing effective software tools that provide advice to people.

Danny Zilio has led the development of the Design Advisor over several releases. He is actively involved in the larger Autonomic Computing initiative.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Step 1 - Collect and describe the workload to the Design Advisor
• Step 2. Decide how and when you want the Design Advisor to run
• Step 3. Review the recommendations
• Step 4. Next steps
• Command-line (db2advis) walkthrough
• Conclusions
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers