As good as IBM DB2 is at optimizing query performance, tuning SQL statements sometimes requires skilled human intervention. There are those who have built careers around their deep knowledge of how to tune SQL to achieve optimized performance. And no wonder: a poorly written SQL statement can wreak havoc, impacting service-level agreements, consuming precious resources, or even causing application outages-all of which can cost businesses time and money.
Recently, the need for highly optimized SQL crashed headlong into new advances in application programming. Frameworks that automatically handle database operations, such as Hibernate and Java Persistence Architecture (JPA), are becoming more popular with Java developers. Development frameworks can make developers more productive, but these frameworks and the DBAs who use them are unable to see or control the generated SQL. In fact, the frameworks may generate SQL "under the covers" that would make experienced DBAs tear out their hair.
Add to this the fact that these SQL statements are dynamic, and it becomes even more difficult to track down the originating code that produced the SQL. The result: SQL performance issues that may not be detected until the application is under a full load in the production environment.
What if it were easier for developers to do some SQL tuning during development? Could tuning become a routine task that every developer does easily and effectively before deploying the application? With IBM Optim Development Studio (formerly Data Studio Developer), the answer can be "yes."
With Optim Development Studio, DBAs and developers now have the ability to see and manipulate the actual SQL that is being used by the Java program in the context of the actual line of source code that produced the SQL. In addition, Optim Development Studio provides visualization capabilities that allow developers to see which SQL statements cost the most and, maybe even more important, how often they are run. Finally, the new query tuning offerings provide tuning advice designed to help developers and DBAs collaborate effectively to produce fast, less costly enterprise-ready database applications.
Introducing IBM Optim Query Tuner
It's understandable why developers don't make query tuning a high priority. First of all, developers tend to focus on the results a query returns, asking only, "Am I getting the data I need?" Second, developers are often under tight deadlines and don't have time to truly examine how queries perform- and they may not be able to unit test under a significant load to uncover problems. Finally, understanding how the EXPLAIN tables relate and how to interpret the information contained in them can be intimidating and cryptic.
Even if a developer spots a SQL issue, there is still a gap between knowing you have a problem and knowing what to do about it. IBM DB2 Visual Explain can provide a picture of what the database is doing, but that picture can be pretty mysterious to a developer, or even to a new DBA. Plus, DB2 Visual Explain only describes what the database is doing; it doesn't provide insight into the performance ramifications of that choice. Experienced DBAs who may be able to solve the problem are often so busy with other high-value activities that they may not be able to help until the problem lands in their lap on the production system.
Optim Query Tuner can help both novice and expert database developers write better-performing SQL. Its easy-touse advisors and query visualizations can help cut costs and improve performance by providing expert advice on writing high-quality queries and improving database design.
As the name suggests, Optim Query Tuner provides single-query tuning assistance. You can invoke Optim Query Tuner for SQL statements from:
- Packages, SQL stored procedures, triggers, and user-defined functions (UDFs)--all from within the Data Source Explorer in the Eclipse environment
- The integrated query editor and routine editor within IBM Data Studio or other Optim development offerings
- The SQL Outline and the Java editor within Optim Development Studio
- The DB2 catalog, for database packages and stored procedures
This single-query tuning capability is announced for IBM Optim Query Tuner 2.2. You can get combined single query and workload tuning today for IBM DB2 for z/OS with DB2 Optimization Expert for z/OS (which will soon be renamed to IBM Optim Query Workload Tuner for DB2 for z/OS).
Visualization and advisor options
Once you select a statement for tuning, there are several visualizations and advisors available to help you. Developers with less SQL experience may rely heavily on the Query Advisor. This advisor makes recommendations on how to rewrite a query to enhance efficiency based on bestpractice rules. When you select a recommendation, the line of SQL is highlighted, and you are presented with a description of how to rewrite the query, as well as an explanation of the recommendation (see Figure 1).
Figure 1: Optim Query Tuner includes a query formatter and advisors.
Note that recommendations for changing queries are based on the assumption that the DB2 catalog statistics are accurate. With Query Annotation, you can format the SQL statement for improved readability and navigation and see which statistics are being used at the table and column level. If there are no statistics, then you'll need to update the DB2 catalog statistics (the Query Tuner Statistics Advisor can help you do this).
With the formatting capability provided by Query Annotation, you can expand and collapse sections of the SQL query. If you click on a table in the FROM clause, it will highlight related columns in the SELECT and WHERE clauses. Conversely, when you select a column in the SELECT or WHERE clause, it will highlight the table the column is related to. Additionally, if your statement references a view, Query Annotation provides the ability to drill down into the SQL query in the view.
Those with more experience will find the Access Plan Graph and Access Path Advisor helpful. The Access Plan Graph is the same graph as DB2 Visual Explain but provides additional statistical or costing information. The Access Path Advisor identifies potential access path issues and provides an explanation and recommendations for dealing with those issues.
If you are a DBA, or if you are the virtual DBA for your own development database, you will also be interested in the Statistics Advisor and Index Advisor. As mentioned earlier, the DB2 optimizer is only as good as the catalog statistics are current. The Statistics Advisor will let you know when it's time to update those catalog statistics and generate the command to do so-all you have to do is click a button. Even if you are running auto-stats, the Statistics Advisor can provide additional information about column distribution.
The Index Advisor will analyze the query and let you know if the use of indexes on the columns used in the query may help improve the performance of the query. And yes, it will generate the index Data Definition Language (DDL) for you, so all you have to do is click a button to create the index.
When using the Index Advisor within Optim Query Tuner, remember that the advice is only relevant to the single SQL statement at hand; be sure to consider how this index will perform within the overall workload. The IBM DB2 Design Advisor can provide that analysis.
Using Optim Query Tuner with Optim Development Studio
Because Query Tuner can be installed into the same Eclipse instance as Optim Development Studio and there are integration points between these two products, Java developers now have natural points in their work where they can invoke the query tuning capability and get advice on writing better queries without slowing down the development process. There is even a way for DBAs or developers who don't have access to source code to use Optim Development Studio to replace poorly performing queries with ones that have been tuned using Optim Query Tuner. The following three scenarios illustrate these points.
Identifying and tuning performance hotspots during Java development
Optim Development Studio lets you capture SQL statements and related performance metrics for any new or existing Java application, including those created using a framework such as Hibernate (see "Optimize your existing JDBC applications using pureQuery" in the "Resources" sidebar for more detail). These captured statements and metrics are displayed in the Optim Development Studio "SQL outline" view (previously known as the "pureQuery outline").
Figure 2: The SQL outline view allows you to tune queries based on frequency or elapsed time.
As seen in Figure 2, the SQL outline view shows you how frequently SQL statements are run and how long they run. It provides a visual display of elapsed time for statements, including total, minimum, maximum, and average time. In addition, it tells you how many times each statement runs. You can easily identify the SQL hot spots and launch Optim Query Tuner directly from this view.
From the SQL outline view, you can also generate EXPLAIN information, then sort by cost to quickly find the most costly queries. Simply right-click on the query and launch Optim Query Tuner to tune that statement. With the combination of cost, elapsed time, and number of times executed, you can more easily decide which queries to focus your tuning efforts on.
Optimizing queries during new Java development
Let's assume you're developing a new Java application using the Optim Development Studio Java editor. Not only do you get SQL assistance to help you formulate queries correctly, you can now right-click on any SQL statement in the Java editor to be launched directly into Optim Query Tuner (see Figure 3). You will see your query formatted and annotated, as well as the access plan and advice on how to fix potential problems.
Figure 3: You can tune queries directly from the Optim Development Studio Java editor by right-clicking on any SQL statement.
Optimizing queries for existing Java database applications
This scenario is for DBAs or developers who are working without the ability to change the underlying source code or who need to make a quick fix for a critical performance problem before a source code change can be tested and rolled out. In this scenario, you run the application to capture the SQL statements, invoke Optim Query Tuner directly from the captured SQL file, and use the Optim Development Studio capture file editor to replace the poorly performing statement with one that you have tuned-all without changing source code. When you set a driver property for the application, it will pick up that improved SQL statement the next time it runs. For more information about SQL replacement, see the "What's new and exciting in IBM Data Studio Developer 2.1" article in the "Resources" sidebar.
Improving the development process
We hope that this article has given you some things to think about in terms of roles and responsibilities, skill building, and overall development process improvements. If you are a DBA who manages DB2 for Linux, UNIX, and Windows systems, you may also be interested to know that IBM is actively working on another capability for these systems that exists today for DB2 for z/OS: query workload tuning and advisory capabilities to help identify, isolate, and tune active queries in a production workload.