Advise and Consent

By Howard Goldberg One of DB2‘s most advanced autonomic features, the DB2 Design Advisor analyzes performance options and makes recommendations faster than any human. The final decisions, though, are all up to you. How much would you pay an expert to come into your shop and improve your DB2 database performance by 25 to 50 percent? DBAs who can deliver those results typically possess years of experience working with many different companies in different industries. Drawing on that experience, a performance expert would most often look for poorly performing SQL. (Most performance problems can be traced to untamed SQL.) To tune troubled SQL statements, the expert would ascertain response-time boundaries, then begin collecting and analyzing a wide array of information, including Database Manager (DBM) and database configuration parameters as well as information pertaining directly to the SQL itself, such as the statement‘s purpose, columns in the WHERE clause, and the description of tables used with associated indexes. The expert would identify each poorly performing SQL statement, analyze it, execute it to establish baseline performance metrics, and then execute it again to assess the change‘s impact. Although this process is iterative, arduous, and time-consuming, it does work. You may not need to hire someone to perform this task because your company already has a resident expert: the DB2 Design Advisor. Part of the core DB2 Universal Database (UDB) v.8.2 for Linux, Unix, and Windows product, the Design Advisor was developed as part of IBM‘s research into self-managing, self-tuning technologies. The goal of the research is to drive down DB2‘s total cost of ownership (TCO) by using autonomic features to make DB2 capable of maintaining itself with less human intervention. Autonomic features have been phased into the database engine since version 7. In v.8.1, IBM renamed the Index Advisor as the DB2 Design Advisor, and then enhanced the product with even more autonomic capabilities in v.8.2. The DB2 Design Advisor suite is composed of two components: the Configuration Advisor, which analyzes, suggests, and implements changes to DBM and database configuration parameters, and the Design Advisor. The Design Advisor provides performance guidance on objects such as b-tree indexes, multidimensional clustering (MDC) tables, indexes, materialized query tables (MQTs), and table partitioning. Using a database or SQL statements as input, the advisor can recommend any of these database object types to improve performance. The object types suggested are controlled by options used with the Design Advisor. Who Needs It? DBAs have been tuning poorly performing SQL for years. Why do they need another tool? A DBA‘s job is to help the application areas leverage and tune the database tier to deliver timely business solutions. Management expects these systems to be available 24x7x365 and that any problems will be resolved as soon as possible. With an increasing, diverse workload, keeping this commitment has become more difficult. The Design Advisor augments DBA skills by automating SQL-statement analysis and suggesting performance improvements. In addition, the Design Advisor can analyze hundreds of SQL statements in a single batch and provide performance suggestions or insights that aren‘t obvious to a DBA performing this analysis manually. As the DBMS engine becomes more complex, the Design Advisor tool can suggest practical uses of new features such as reverse scans allowed, MQTs, and MDC indexes. The Design Advisor also expedites problem resolution by providing performance recommendations in minutes. Manual analysis takes considerably longer. And, the Design Advisor provides a more scientific approach to choosing performance-enhancing database objects rather than the trial-and-error approach currently used. Working with IT You can initiate the Design Advisor through the control center GUI or by issuing the db2advis command from the command line. For the Advisor to function properly using the GUI, the db2admin server must be started. To initiate the Design Advisor from the control center, navigate to the database of interest and right-click on it. Choose the Design Advisor option from the subsequent popup menu and follow the advisor‘s workflow. Although the GUI does a nice job of stepping you through the process and displaying the results, I prefer using the command line interface (db2advis) because the control center isn‘t always available. DB2ADVIS Command To use the Design Advisor command in batch, execute the db2advis command from a command prompt. The following command will connect to the dbtest database, analyze the SQL contained in the file named sql.in, and place all recommendations (indexes in this case) in executable DDL format in the sql.out file: db2advis -d dbtest -i sql.in -t 2 -m I -o sql.out Listing A shows the db2advis syntax diagram. See Table A for a complete list of parameters and their descriptions. Listing A. The db2advis command syntax. Table A. Complete list of parameters and their descriptions. -d database-name Specifies the name of the database to which a connection is to be established.   -w workload-name Specifies the name of the workload for which indexes are to be advised. This name is used in the ADVISE_WORKLOAD table. This option cannot be specified with the -g, -i, qp, or -s options.   -s " statement " Specifies the text of a single SQL statement whose indexes are to be advised. The statement must be enclosed by double quotation marks. This option cannot be specified with the -g, -i, -qp, or -w options.   -i filename Specifies the name of an input file containing one or more SQL statements. The default is standard input. Identify comment text with two hyphens at the start of each line; that is -- < comment > . s tatements must be delimited by semicolons.   -g   Specifies the retrieval of the SQL statements from a dynamic SQL snapshot. If combined with the -p command parameter, the SQL statements are kept in the ADVISE_WORKLOAD table. This option cannot be specified with the -i, -s, -qp, or -w options.   -qp   Specifies that the workload is coming from Query Patroller. This option cannot be used with the -w, -s, -i, or -g options.   -a userid/passwd   Name and password used to connect to the database. The slash (/) must be included if a password is specified. A password should not be specified if the -x option is specified.   -m advise-type   Specifies the type of recommendation the advisor will return. Any combination of I, M, C, and P can be specified. The values must be entered in upper case. For example, db2advis -m PC will recommend partitioning and MDC tables.   I Recommends new indexes. This is the default. M Recommends new materialized query tables (MQTs) and indexes on the MQTs. In partitioned database environments, partitioning on MQTs is also recommended. C Recommends the conversion of standard tables to multidimensional clustering (MDC) tables. P Recommends the repartitioning of existing tables. -x   Specifies that the password will be read from the terminal or through user input. -u   Specifies that the advisor will consider the recommendation of deferred MQTs. Incremental MQTs will not be recommended. When this option is specified, comments in the DDL CLP script indicate which of the MQTs could be converted to immediate MQTs. If immediate MQTs are recommended in a partitioned database environment, the default partitioning key is the implied unique key for the MQT. -l disk-limit   Specifies the number of megabytes available for all indexes in the existing schema. Specify -1 to use the maximum possible size. The default value is 20% of the total database size.   -t max-advise-time   Specifies the maximum allowable time, in minutes, to complete the operation. If no value is specified for this option, the operation will continue until it is completed. To specify an unlimited time enter a value of zero. The default is zero.   -k Specifies to what degree the workload will be compressed. Compression is done to allow the advisor to reduce the complexity of the advisor‘s execution while achieving similar results to those the advisor could provide when the full workload is considered. HIGH indicates the advisor will concentrate on a small subset of the workload. MED indicates the advisor will concentrate on a medium-sized subset of the workload. LOW indicates the advisor will concentrate on a larger subset of the workload. OFF indicates that no compression will occur. The default is MED. -f Drops previously existing simulated catalog tables. -r   Specifies that detailed statistics should be used for the virtual MQTs and for the partitioning selection. If this option is not specified, the default is to use optimizer statistics for MQTs. Note that although the detailed statistics might be more accurate, the time to derive them will be significant and will cause the db2advisexecution time to be greater.   -n schema-name   Specifies the qualifying name of simulation catalog tables, and the qualifier for the new indexes and MQTs. The default schema name is the caller‘s user ID, except for catalog simulation tables where the default schema name is SYSTOOLS. -q schema-name Specifies the qualifying name of unqualified names in the workload. It serves as the schema name to use for CURRENT SCHEMA when db2advis executes. The default schema name is the user ID of the person executing the command. -b tablespace-name   Specifies the name of a table space in which new MQTs will be created. If not specified, the advisor will select the table spaces from the set of table spaces that exist. -c tablespace-name   Specifies the name of a table space (file name or directory) in which to create the simulation catalog table space on the catalog database partition group. The default is USERSPACE1 It is recommended that the user create the table space employed for the simulation instead of using the default USERSPACE1. In addition, the ALTER TABLESPACE DROPPED TABLE RECOVERY OFF statement should be run on this table space to improve the performance of the db2advisutility. When the utility completes, turn the history back on for the table space. In a partitioned database environment, the user-created table space must be created only on the catalog partition of the database. -h help Display help information. When this option is specified, all other options are ignored, and only the help information is displayed. -p Keeps the plans that were generated while running the tool in the explain tables. -o outfile Saves the script to create the recommended objects in the specified file Care and Feeding SQL drives the Design Advisor. The tool can parse, analyze, and make suggestions on single or multiple SQL statements. Giving the Design Advisor a complete picture of the system‘s workload will vastly improve the accuracy and completeness of the Design Advisor‘s performance suggestions. The Design Advisor can accept SQL input in multiple ways. You can feed in individual SQL statements directly from the command prompt using the -s option. You can store single or multiple SQL statements in workload files, separated by semicolons, and pass them into the Design Advisor using the -i option. Another technique is to dynamically capture the SQL statements by using the -g option, which executes the SQL snapshot command. You can also acquire SQL directly from the DB2 Query Patroller using the -qp option. The Design Advisor GUI offers similar SQL capture techniques. SQL Capture You can create workload files using the SQL snapshot command by writing a script to execute on a set frequency and insert SQL queries that exceed predefined thresholds (CPU or elapsed time) into historical snapshot tables. Export the data in these tables to create a custom workload file. This technique lets you systematically collect long-running SQL statements to feed into the Design Advisor for analysis. You can enhance the process by adjusting thresholds and filtering criteria (for example, to capture long-running SQL statements by time, user, or client application). SQL Frequency and Priority You can provide additional information along with the SQL to help the Design Advisor return the best performance suggestions. The SET FREQUENCY and SET PRIORITY commands can be interspersed throughout an SQL workload. These commands provide the Design Advisor and, ultimately, the optimizer more information about the actual workload. If the Advisor knows that an SQL statement will be executed thousands of times rather than once, it may suggest a different index or index option. The SET FREQUENCY command informs the Design Advisor that the SQL command will be executed multiple times. The SET PRIORITY command lets the DBA give the Advisor critical information about the importance of each SQL statement. These commands enable a DBA to build benchmarks and "what-if" workloads using representative SQL and frequency or priority values. For example, a DBA could create a workload file anticipating increased user volume by adding a frequency value of 1000 prior to an SQL statement. The Design Advisor would analyze these workloads and suggest potential performance-enhancing objects that might be required if an application SQL workload were changed in this manner. The following are examples of the Design Advisor SET FREQUENCY and SET PRIORITY workload commands: SET FREQUENCY 100 SELECT COUNT(*) FROM TBSTATE SET PRIORITY 1 SELECT SUM(ASSETS) FROM TBACCT These commands can‘t be used with the -g, -s, -qp, or -w options. The -w option uses the Advise_Workload table to pass SQL to the Design Advisor. (See Table B in the online version of this article for an example Advise_Workload table.) Table B. ADVISE_WORKLOAD table. Control Recommendations The Design Advisor combines multiple advisors into a single tool. The objects the Design Advisor recommends are controlled by the Advisor type parameter (-m). Table 1 shows the values that direct which object types are recommended. If options are specified, the Design Advisor can recommend multiple object types. For example, if you want recommendations about regular b-tree and MDC indexes, you would specify I and C options following the -m parameter (-m IC). The Advisor will also indicate indexes that aren‘t being used to resolve the SQL statement and whether or not Runstats have been executed on the underlying tables. Although you can use the Design Advisor in production, I recommend using it in a test environment because the MQT and MDC performance analysis options will scan tables associated with the SQL and could cause performance problems. Another usage note: Change the object names in the DDL produced by the Design Advisor, because the object names the tool generates won‘t follow your naming standards. In Listing 1, IDX412171915190000 is the object name created by the Design Advisor; I recommend changing it to a more recognizable name. Listing 1. DDL produced by the Design Advisor. CREATE INDEX MIDSP . IDX412171915190000 ON MIDS .TBPOSITION (ACCOUNT_NO ASC ,SHARE_QTY ASC ,POSITION_VALUE ASC ,SECURITY_NO ASC) ALLOW REVERSE SCANS ; Make the Choice Indexes, MQTs, and parallelism are some of the most powerful tools that DBAs can use to improve performance. The Design Advisor gives a DBA a glimpse into the cost-based optimizer and evaluates many permutations before delivering its recommendations, a daunting task to accomplish manually. Although the Advisor suggests potential performance-enhancing changes, there‘s no guarantee that the suggestions will work. The DBA must judiciously review and test each suggested modification. Blindly implementing changes without testing their impact may have negative effects on your environment (and credibility). The Design Advisor suggests database changes based on SQL input; however, the DBA must choose which recommendations to use. Adding the correct performance-enhancing object can have remarkably positive effects on query performance. But other areas may be affected by the addition of these objects. For example, database utilities could take longer to execute and complete. Additional temporary space must be allocated to rebuild and manage these objects. And the new objects can have ramifications on transaction length when performing inserts or updates. Lengthening the unit of work for transactions can cause significant locking and contention problems. You should follow the typical change-management process before implementing the suggested recommendations. Understand, test, implement, and monitor each recommendation, one change at a time. Aid not Authority Treat the Design Advisor as an aid, not an authority. The DBA (not the tool) should drive performance-enhancing decisions. Only a DBA who works closely with an application can choose the correct performance optimizations. Design Advisor recommendations and improvements will vary depending on application and system-usage patterns. Take advantage of the Design Advisor in your shop. Use it often, but use it wisely. When to Use the Design Advisor At Merrill Lynch, we use the Advisor in the following situations: To suggest performance-enhancing objects when a long-running query is identified (in one case, a query‘s execution time dropped from six minutes to 20 seconds) To analyze SQL that executed in a data warehouse during peak usage time (as a result of this analysis, we added and modified indexes to add the REVERSE SCANS ALLOWED or INCLUDE column clauses) To determine whether MQTs would be beneficial, based on the same data warehouse workload To analyze very large SQL statements (some are unformatted and as large as 6K bytes) To determine the best use of MDC indexes. A number of our large historical tables would benefit from an MDC index, and the Advisor helped us determine which columns to index and the dimensions to use. Howard Goldberg has more than 22 years of experience with databases and data warehousing. He currently works with DB2 on mainframe and distributed platforms in his role as a vice president at Merrill Lynch. Goldberg is a member of IBM‘s Information Integration Leadership Board.

Return to Article