Application Performance Tuning For Db2 Udb

Application Performance Tuning for DB2 UDB By Joshua Steffan

Contents Executive Summary................................................................................................................................. 3 Introduction: Why SQL Tuning?............................................................................................................. 3 Structured Query Language (SQL)......................................................................................................... 4 Overview of the SQL Compiler ......................................................................................................... 4 Dynamic and Static SQL ................................................................................................................... 5 Tuning SQL for Application Performance.............................................................................................. 5 SQL Statement ................................................................................................................................... 5 Objects ............................................................................................................................................... 6 Tuning Environment .......................................................................................................................... 6 Native DB2 UDB utilities ....................................................................................................................... 6 IBM Command Center....................................................................................................................... 7 IBM Control Center ........................................................................................................................... 7 SQL Tuning Component ......................................................................................................................... 7 Technical Details ............................................................................................................................... 7 SQL Statement Information ............................................................................................................... 8 Object Information............................................................................................................................. 9 Tuning Environment .......................................................................................................................... 9 Quest Central for DB2 ..................................................................................................................... 10 Summary ............................................................................................................................................... 10 Quest Software...................................................................................................................................... 11 About the Author................................................................................................................................... 11

2

Application Performance Tuning for DB2 UDB by Joshua Steffan

Executive Summary Today, databases are at the foundation of a business computing architecture that often includes Web servers, application servers, client workstations, application logic, networks, and other components. Since the database acts as the data server, it can quickly turn into an application bottleneck if a problem occurs. In many cases, newly developed applications pass quality assurance tests, but go on to fail in production because of scalability problems. Poorly written Structured Query Language (SQL)—the language used by the applications to retrieve and store data in a database—frequently causes this to happen. Like other databases, IBM DB2 UDB is negatively affected by poor application SQL. Bad SQL—like bad grammar—will still communicate the required message, but it does so much less efficiently. Application SQL must be tuned in order to optimize performance and maximize the return on an often-significant investment in business technology. Effective SQL tuning requires users to collect and analyze specific information, including the offending SQL code itself, SQL access plan, target object statistics, and dependent object relationships. Using the native DB2 utilities to gather this information is time-consuming and requires the use of several different tools, making iterative tuning a difficult process. Quest Software’s SQL Tuning module, part of Quest Central for DB2, allows application developers and DBA's to quickly gather information needed to make tuning decisions within an iterative analysis session. The expert advice function assists users by recommending corrections and rewriting a selected SQL statement based on the accepted recommendation. SQL Tuning is one of the four components of Quest Central™ for DB2, which helps DBA's improve availability and performance in DB2 installations. Quest’s integrated components encourage multitasking by offering true functional integration. The improved application performance and return on investment generated by Quest Central for DB2 directly benefits the business and the information technology infrastructure.

Introduction: Why SQL Tuning? A variety of factors can affect overall performance of production applications. For example, business demands may require room for additional users, support for new customers or Web access. The application is also affected by technical factors such as network bandwidth, memory constraints and database performance. Regardless of the application’s business and technical demands, performance and availability must continue to meet user expectations. To improve application performance, IT staff may purchase additional hardware or tune the application itself. While periodic hardware upgrades are necessary to support growing applications, tuning should be the primary method to improve performance. Tuning not only addresses the application, but improves the cost-benefit ratio of the system as well. Although the majority of tuning efforts are spent at the operating system and database level, the largest performance gains are obtained from tuning the application itself. Applications that use SQL to store data in a database are the best candidates for tuning. Since a relatively small number of SQL statements consume the majority of resources, SQL tuning

3

often results in significant performance improvements. However, SQL tuning can be complex. It requires an understanding of DB2 SQL processing, the collection of specific information about each SQL statement, and iterative analysis. While the native DB2 utilities can be used to assist with SQL tuning, they possess significant limitations. Quest Central's SQL Tuner provides a robust SQL tuning environment complete with proven expert advice and tuning recommendations, so application tuning is quick and efficient.

Structured Query Language (SQL) DB2 UDB, like all relational database management systems (RDBMS), contains a SQL processing engine that provides an interface to the database functions. SQL statements are generally categorized into three groups: •

Data Definition Language (DDL) – These statements are used to define and manage the data structures administered by the database manager. DDL statements are generally considered to be the CREATE, ALTER, and DROP commands. Database objects such as tables, views and indexes are created, altered and dropped with these statements.

•

Data Control Language (DCL) – These statements are used to provide security within the database. DCL statements are generally considered to be the GRANT and REVOKE commands. Users of the database are granted or revoked permission to access objects and data with DCL commands.

•

Data Manipulation Language (DML) – These statements are used to manage the data stored within the database. DML commands are generally considered to be the SELECT, INSERT, UPDATE, and DELETE commands. Application data is selected, inserted, updated or deleted with DML commands; they are the most commonly used commands in application programming.

Of these three types, performance tuning is only concerned with DML statements. DML commands are the most frequently used by applications and have the greatest flexibility for interpretation. DDL and DCL statements, on the other hand, consist of rigid syntax that cannot be tuned. One notable exception is when DDL statements contain DML statements, such as the CREATE VIEW command, which is based on a DML SELECT statement.

Overview of the SQL Compiler SQL statements are processed in several steps by the DB2 SQL compiler, which converts the statement into a format the database can understand. It also performs error checking and statement optimization. •

Step 1: Parse Query The first task of the SQL compiler is to analyze the SQL query to validate the syntax. A query graph model, which is an internal representation of the query, is created when parsing is complete.

•

Step 2: Check Semantics The compiler’s second task is to further validate the SQL statement by checking to ensure that all parts of the statement make sense. The query graph model is updated to contain the semantics of all the query components.

•

Step 3: Rewrite Query The third phase of the SQL compiler uses the global semantics provided in the query graph model to transform the query into a form that can be optimized more easily. Tuning SQL for Application Performance

4

•

Step 4: Optimize Access Plan The SQL optimizer portion of the SQL compiler produces many alternative execution plans based on the query graph model and chooses the plan with the smallest estimated execution cost. This step generates an access plan, which provides the basis for the information captured in the Explain tables.

•

Step 5: Generate “Executable” Code The final step of the SQL compiler uses the access plan and the query graph model to create an executable access plan, or section, for the query. Information about access plans for static SQL is stored in the system catalog tables.

In a partitioned database environment, all the work the compiler does on a SQL query takes place at the database partition to which the user or application is connected. Once the executable access plan is created, the compiled query is distributed to all database partitions in the database.

Dynamic and Static SQL DB2 UDB allows developers to control when the SQL compiler processes SQL statements. Dynamic SQL statements are compiled by the database at the time the application submits them. The compiled statements are temporarily stored in memory known as the global package cache. The compiler will not be invoked again if the access plan for a dynamic SQL statement already exists in the package cache.

Tuning SQL for Application Performance Static SQL differs from dynamic SQL, because the static SQL statements of an application are processed by the SQL compiler and stored in the database before the application is executed. The BIND command causes the database to process the static SQL statements located in a bind file that correspond to the application. The information generated from the BIND is stored in a persistent database structure called a package. Given the complexity of the DB2 SQL compiler and the number of factors that can influence statement performance, SQL tuning is a challenging exercise. Application developers and DBA's need to be equipped with the proper information and tuning environment before educated tuning can begin.

SQL Statement The SQL statement is the focal point of tuning efforts, so the developer or DBA must first identify a target statement and collect supporting detail. SQL tuning can be done either proactively, during development; or reactively, in response to poor performance. Proactive statement tuning results in higher quality applications, but it requires discipline for developers to evaluate each statement that is used in an application. Reactive tuning is equally challenging, because detective work is required to identify the offending SQL statements that are degrading an application. Once a statement is identified, supporting details must be gathered as well. Most importantly, the SQL access plan must be obtained, since it describes how the database is processing the statement’s request. IBM provides both a graphical and a command line mechanism for generating access plans. These utilities use the SQL explain facility, part of the DB2 SQL compiler, to capture information about the environment where the static or dynamic SQL statement is compiled.

5

Explain information is typically stored within the database in a dedicated set of tables. In addition to the access plan, the explain tables are populated with other information, such as the statement cost. The cost represents the resource expense of the request and is used to measure tuning improvements. Execution results are another required supporting detail, but these are much easier to collect and interpret. Tuning a SQL statement often requires rewriting or restructuring the query. When attempting to improve performance by restructuring, it is easy to accidentally impair the statement’s result. The execution results of a modified statement should be compared against the results of the original to ensure that the correct data is still being returned.

Objects The objects involved with a SQL statement—either directly or indirectly—also weigh upon tuning decisions, so information must be gathered about these as well. The DB2 compiler reviews object statistics, gathered by the RUNSTATS command, to determine the best way to optimize the request. Absent or outdated statistics can cause the SQL compiler to make poor optimization decisions about index access, join method and data prefetching. Object relationships and dependencies are also important to SQL tuning. For example, a trigger that executes every time a row is inserted could cause insert statements that take an unusual amount of time to process. Without a complete picture of the objects involved with a SQL statement, tuning efforts may be dedicated to the wrong area.

Tuning Environment SQL tuning is a process that unavoidably requires trial and error. Each application, SQL statement and database is different, so what worked for one statement may not for the next. Because of this, a comprehensive tuning environment is perhaps the most important component required for efficient statement analysis. This environment should correlate the SQL statement and object information for ease of access and use. It should also support multiple scenarios, so the user can work with multiple versions of the statement at the same time. A comparison engine is needed as well, so users can quickly identify the most improved statement from the scenarios provided. A tuning environment that provides the user with expert advice is especially helpful reducing the time spent tuning each statement. This is possible because the DB2 SQL optimizer simply operates based upon an extensive set of rules. Once the tuning environment is made "aware" of these rules, it can then anticipate the impact of a statement and recommend corrections to improve performance.

Native DB2 UDB utilities IBM provides a basic set of administration tools with the DB2 UDB database. These tools are broad in scope to address the various tasks associated with managing DB2, such as object management, performance monitoring and command execution. However, when it comes to SQL tuning, the breadth of the native tools is actually a disadvantage, since they were not specifically designed for the task. IBM offers several tools that provide SQL statement and database object information that may be used to assist with tuning. Unfortunately, none of these tools provide a tuning environment that correlates the various pieces of information. Ambitious application developers and DBA's looking to address problem SQL using the IBM tools (Command Center and Control Center) may be discouraged, finding that these products do little to assist them. These tools are described below.

6

IBM Command Center

Figure 1: IBM only addresses part of the SQL tuning need.

Command Center is a graphical application that enables a user to interactively submit SQL statements to a selected DB2 database and receive statement results. Command Center also allows the user to generate the corresponding graphical access plan for a SQL statement using the Visual Explain function. While the information provided by Command Center is useful for SQL tuning, it is only designed for simple query execution and does not allow the user to work with multiple concurrent scenarios. This makes it difficult to compare different versions of a SQL statement to measure performance improvements.

IBM Control Center Control Center is a graphical application that is used for various database administration tasks. It can be used to collect object statistic and relationship data that complements the information gathered with Command Center. Unfortunately, the user is left to combine the information provided by these two independent utilities, since they don’t provide it in a manner conducive to SQL tuning.

SQL Tuning Component

Quest Central SQL Tuning

Quest Central for DB2 addresses the application tuning need with its SQL Tuning component, which provides a single source for SQL statement and object information, as well as a robust tuning environment for statement analysis. The comprehensive tuning lab Figure 2: Quest Central's SQL Tuning component provides all the provides a complete SQL optimization facility. information needed to tune a SQL statement. Users can easily step through the graphical representation of the access plan. Expert tuning advice and plain English translation of explain plan details make SQL tuning more accessible for developers.

Technical Details Quest Central for DB2 requires a Windows 95/98, NT 4.0, or 2000 workstation and the DB2 Runtime Client to access DB2 UDB instances. When retrieving access plan details, the SQL

7

Tuning module uses the same DB2 explain facilities as the SQL compiler. Using this method ensures that the plan details and associated costs are accurate. The component also retrieves statistic and dependency data from the DB2 catalog views.

SQL Statement Information The SQL Tuning component allows the user to work with SQL statements from a variety of sources. The simplest method entails directly typing the statement into the component or loading the contents of a SQL file; this scenario may prove most beneficial to application developers looking to quickly tune SQL queries for use in developing applications. Certain textual database objects like views, summary tables, triggers and packages also provide a source of SQL for the tuner. It extracts embedded SQL statements from the text of these object types and allows the user to tune the statement upon which the object is based. Once a statement has been entered into the tuning session, the user is able to execute an explain on the query to generate the statement’s access plan and other statistics. The explain statistics provide information such as cost, I/O and CPU required to execute each step in the access plan. The SQL Tuning component also displays optimizer arguments used for each step of the access plan. These arguments provide insight about the optimizer’s access decisions that control activities like direction of table access, table joins, locking and prefetch usage.

Figure 3: Quest Central's SQL Tuning module. Valuable SQL access plan and execution details are logically presented to help speed tuning analysis. •

List View – details contents of the access path in a format similar to what is obtained by executing the DB2EXFMT command

8

•

Tree View – displays access path contents in a Windows Explorer-style tree — especially useful for large and complicated statements

•

Graph View – this access path display, which is similar to the DB2 Command Center utility, is useful to visualize processing order

Statement execution results provide further feedback while tuning SQL statements. Verifying query output is especially important since the goal is to tune the statement, but not at the expense of correct results. It is best to confirm that any newly tuned statements still achieve the same results as the original.

Object Information When a SQL statement is explained within the SQL Tuning component, it automatically retrieves statistics for the objects involved with the statement. The user can quickly identify whether statistics exist for a given object, and if so, show the last time the statistics were updated. The SQL Tuner also lets the user update statistics for objects, which is sometimes all that is needed to improve statement performance. Object dependencies and relationships are also presented, since identifying referential integrity constraints and dependent objects involved with a SQL statement is particularly important. Each relationship and dependency has an impact on the decisions made by the SQL optimizer.

Tuning Environment The SQL Tuning component provides a robust tuning environment that lets the user work with multiple scenarios of a base SQL statement. The scenario capability lets the user keep the target statement in its original form with modifications placed in separate scenarios.

Figure 4: The compare engine uses CPU cost and elapsed time to identify and illustrate the statement that will result in the most significant improvement.

Scenarios can be based on manual statement modifications or generated from the component's tuning advice. The SQL Tuner examines every part of the statement in question and offers

9

informed tuning recommendations. With the click of a button, the component then rewrites the SQL statement to implement the selected advice. The SQL Tuning component maintains separate access plan and object statistics for each scenario. The comparison engine uses cost, I/O and CPU statistics to identify and illustrate the statement that will result in the most significant improvement.

Quest Central for DB2 The SQL Tuning component is part of Quest Central for DB2, which helps database administrators improve support for DB2 installations with additional components to address Performance Monitoring, Database Administration, and Space Management. The Quest Central console provides a central location for performing database-related activities with Quest Software components. True functional integration is also provided, so users can take full advantage of component capabilities. For example, the SQL Tuning module allows users to tune active SQL statements that have been identified by the Performance Monitoring module, another component of Quest Central for DB2. When the Performance Monitoring component is active, cross-component integration is enabled, thus allowing users to invoke the tuner directly from the monitor. Quest Central Performance Monitor

SQL

Quest Central SQL Tuning

Figure 5: Quest Central’s Performance Monitoring module complements the SQL Tuner by identifying offensive queries.

The Database Administration module, Quest Central for DB2's object management component, provides integration with the SQL Tuning component as well. When using Database Administration to create or alter DB2 database objects such as views, triggers, and summary tables, the user can invoke SQL Tuning to tune the statement that will be used by the object under construction. The architecture of the Quest Central suite is robust enough to facilitate integration and multitasking. Since all components are integrated with a common interface, users can create a custom-tailored solution for their specific needs.

Summary Quest Central for DB2's SQL Tuning component correlates all the necessary statement and object information in a complete tuning environment that is specifically designed for analysis, comparison and iterative tuning. SQL Tuning guides the user through the tuning process with intelligent advice that suggests statement modifications to improve performance, and even rewrites SQL statements to implement the selected advice. SQL Tuning is an easy and efficient solution. Optimized application SQL creates higher-quality applications, increasing return on investment and providing the user with improved application response time.

10

Quest Software Quest Software, Inc. is a leading provider of performance management solutions designed to maintain the integrity of mission-critical business transactions and maximize the performance of enterprise applications. Our solutions address the needs of today’s 24x7x365 businesses where demands on the information technology infrastructure are high and tolerance for downtime is low. Founded in 1987, Quest Software now helps more than 100,000 users achieve the best possible performance from their enterprise systems so the end user experience is a positive one. Based in Irvine, California, Quest Software has offices worldwide and over 1,000 employees. For more information, visit www.quest.com.

About the Author Joshua Steffan is the DB2 UDB Product Manager at Quest Software. Before joining Quest, Joshua held various technical positions with leading database software companies. Most recently, he was the manger of quality assurance at Platinum Technology and later served as a systems consultant for BMC. Joshua has been invited to share his database expertise at a number of technology conferences including Oracle Open World and the Informix World User Conference. For more information about Quest Software, visit our Web site at www.quest.com.

© 2001 Quest Software, Inc. All rights reserved. Quest Central is a trademark of Quest Software. All other brands or product names are trademarks or registered trademarks of their respective owners.

11