SQL Tuning: Not just for hardcore DBAs anymore

IBM Optim Query Tuner for Linux, UNIX, and Windows helps developers create efficient queries and build tuning skills

A look at how IBM Optim Query Tuner for Linux, UNIX, and Windows helps developers create efficient queries and build tuning skills

Kathryn Zeidenstein (krzeide@us.ibm.com), Silicon Valley Lab, IBM

Kathy Zeidenstein has been with IBM's Silicon Valley Lab since 1987. She started in the DB2 for OS/390(r) organization which is where she got her first exposure to object-relational. After she began working on the SQL standards team, she became involved with the more advanced object-relational features in DB2, because much of the language design for that work was taken to the SQL standards committees. This article could not have been written without the help of the development team who implemented the technology. More recently, Kathy has worked on the business intelligence team and is now involved with IBM's new information integration strategy. You can reach Kathy Zeidenstein at krzeide@us.ibm.com.



Cliff Leung (cleung@us.ibm.com), Data Studio Software Architect, IBM  

Cliff Leung is a senior technical staff member in the Information Management organization at the Silicon Valley Laboratory in San Jose. He has been the chief architect of IBM Optim Query Tuner product since 2008 and is responsible for overall product direction and strategy. He has extensive experience in query compilation, optimization, and performance.



Tony Leung (leungtk@us.ibm.com), Data Studio Software Developer, IBM  

Tony Leung is an IBM Data Studio software developer.



Ray Willoughby (rwilloug@us.ibm.com), Technical Product Manager, IBM  

Ray Willoughby is the technical product manager for query tuning offerings



30 June 2009

Also available in Chinese Portuguese

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


Resources

Optim Query TunerOptim Integrated Data Management portfolioOptim Development StudioOptimize your existing JDBC applications using pureQueryWhat's new and exciting in IBM Data Studio Developer 2.1

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=405373
ArticleTitle=SQL Tuning: Not just for hardcore DBAs anymore
publish-date=06302009