Query tuning: DBAs and developers can work together
IBM_Optim 27000269HS Visits (2773)
Traditionally query tuning has been perceived to be a function only performed by DBAs, but with a product such as DB2 Optimization Expert for z/OS, I’d like to suggest that this perception could change and become a task that a developer could perform. At the very least, developers could do some due diligence before calling in a DBA. As a former DBA, I was always more impressed with developers who were willing to do some of their own analysis before calling me.
It’s understandable why developers don’t give much thought to query tuning. First of all, developers tend to only focus on the results a query returns. Am I getting the data I need? Secondly, developers tend to be under tight deadlines to complete projects, and don’t have the time to truly examine how queries perform. Thirdly, understanding how the EXPLAIN tables relate and how to interpret the information contained in them can be intimidating and cryptic.
Optimization Expert offers several tools and advisors that can help a developer write more efficient SQL:
This tool takes a query and formats it so that is easier to read, and provides the ability to expand and collapse sections of the SQL. 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 in the view. The figure below is an example of a simple SQL statement that references a view, and how you can drill into the view to see the underlying SQL. An additional feature offered in this tool is Annotation, which gives you pertinent statistics used by the optimizer at the table and column level.
Access Plan Graph
This tool generates a diagram of the current access plan for a query, showing how DB2 accesses data to process a query. This information is the same information that is in the PLAN_TABLE, but is presented in an easy-to-read, visual format. Yes it is similar to the graph displayed in Visual Explain, but has some additional features such as infopops, which display information as you hover your mouse pointer over a node. The information displayed is dependent on the type of node that you hover over.
In the example below, the infopop provides information about the TBSCAN node, the Cardinality, Total Cost, I/O Cost, CPU Cost, that a sequential prefetch is being perfomed, as well as page ran access since this table is partitioned. Currently the Query node is selected, indicated by the squares in the four corners, and the Node Descriptor is providing costing information for this query. If you were to select the TBSCAN node additional information beyond what is displayed in the infopop would be displayed in the Node Descriptor.
Access Path Advisor
This advisor interrogates the plan table and highlights potential access path performance issues and provides recommendations on how to eliminate such issues. An explanation and description of issues are given to you, and the recommendations are ranked by severity as Low, Medium, High and Disaster. The figure below shows the results of running Access Path Advisor with three low severity warnings. When you select one of the warnings, a description and explanation for that warning are displayed, as well and the plan table information.
This advisor is the one single tool in Optimization Expert that can assist a developer in writing efficient SQL. It interrogates the SQL statement and makes recommendations on how to rewrite the query to make it more efficient based on best practices rules. When you click on a recommendation, it highlights the line of the SQL statement that the recommendation pertains to. Once again, an explanation and description are provided to you. The figure below shows the results of running Query Advisor. In this example there are two query rewrite recommendations. 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.
Most SQL performance issues are never discovered until they are in a production environment, where they impact Service Level Agreements, consume precious resources, or in extreme situations cause application outages. If an organization were to use Optimization Expert and expose it to their developers, production SQL performance issues could be eliminated or greatly reduced. Additionally, it could be used as an educational tool for coding efficient SQL.
Currently Optimization Expert is an Eclipse RCP application, but we will soon be releasing a version that will integrate into Data Studio and support shell-sharing. This is another reason for developers to incorporate this into their development process.
If you’re a DBA, do you think any of your developers would like to learn this tool? Do any of you have experience in your shop with developers doing query tuning? If so, I’d love to hear from you about what works and what doesn’t work, or any other suggestions that would make this tool even better for developers and DBAs.
One final note, for those of you attending IOD in October, I encourage you to attend the following hands-on lab: 2568A – SQL Tuning for Everyone: Using IBM Data Studio Optimization Expert for DB2. This lab will be led by two members of the Optimization Expert team. I think it will be well worth your time.
-- Ray Willoughby