Managing the data lifecycle

Matching: sql

Setting options for the tuning advisors

rdheath Tags: tuning advisors sql ‎ 5,059 Views

Each of the tuning advisors in IBM Data Studio, IBM InfoSphere Optim Query Tuner, and IBM InfoSphere Optim Query Workload Tuner has at least one option that you can set to influence the recommendations that they generate. Some of the advisors have exactly one option, but the advisors that help with statistics and indexes have many more than that. In this blog, I'd like to explain some of these options from time to time because a number of them look complicated, are not documented well, or both.

The options appear in two places. In one place, you can set values globally, in which case the values apply to all statements and workloads that you tune while working in the current workspace. In the other place, you can set values for individual statements and workloads, overriding the global values.

Before you start tuning a statement or a query workload, you can set values for these options in the global preferences by selecting Window > Preferences. When the Preferences window opens, in the tree view on the left expand the Data Management entry, and the expand the Query Tuner entry. Expand the Tune Single Query entry and the Tune Workload entry to see the entries for the advisors. Select one of the advisors and the right side of the Preferences window shows the options for it. After you set values for the options for an advisor, click the Apply button if you want to set preferences for another advisor before closing the Preferences window. After you've finished setting values and have closed the window, you can start tuning.

You can also set values for the advisor options after you have captured a statement or a workload and are either on the Run Single-Query Advisors and Tools page or the Run Workload Advisors page of the Query Tuner Workflow Assistant. On the left side of the page, select Set Advisor Options. The options that appear are the same ones that appear in the Preference window. The difference is that the values that you set here apply only to the current statement or workload that you are running.

Take some time to look through the options and let me know which ones you would like me to explain. In my next post, I'll start with the first option for the Workload Statistics Advisor for DB2 for z/OS, unless someone asks me to cover a different one first.

Robert Heath, heathr@us.ibm.com (Start the subject line with "MDL blog: ")
Information Development for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner

Tuning SQL statements that contain variables when you are developing SQL routines

rdheath Tags: performance sql routines tuning ‎ 7,021 Views

Here's a handy tip for a Monday. You might already know that, in InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner, you can use the client to develop SQL stored procedures. When you are working in a routine editor, you can right-click anywhere in an SQL statement and select Start Tuning to open the Query Tuner workflow assistant. The statement appears in the Query Tuner workflow assistant. From this location, you can run advisors and tools to get analyses and recommendations.

Until version 3.1, if the SQL statement contained variables, the variables were present in the statement after it was imported into the workflow assistant, and the statement couldn't be tuned. However, in version 3.1, the variables are replaced with parameter markers.
For example, the SQL statement in this routine uses the variables ORDER_DETAIL_CODE_IN and SALES_STAFF_CODE_IN. The first is a VARCHAR, the second is an INTEGER.

After you right-click in the routine editor and select Start Tuning, the Query Tuner workflow assistant opens to the Run Single-Query Advisors and Analysis Tools page. The variables are replaced with parameter markers, using the appropriate casts to indicate data types.

From this point, you can click the Select What to Run button and choose the tools and advisors that you want to run on the statement, and proceed tuning as you would with any other statement.

Robert Heath, heathr@us.ibm.com (Start the subject line with "MDL blog: ")

Information Development for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner

General steps for tuning SQL

rdheath Tags: tuning sql performance ‎ 6,456 Views

Have you ever noticed that you can't do anything that someone else hasn't declared to be an art? To use a now commonplace method of generating examples, go to Google and search on the words "art of". To list only those on the first page of results that I get:

art of manliness
art of trolling
art of participation
art of questioning
art of living
art of shaving
art of flight
art of dying
art of seduction
art of the steal
art of marriage
art of getting by

These are dubious arts, at best. If shaving is an art, then it's most likely a Dadaist one, and the masterwork would be to reverse what Marcel Duchamp did to the Mona Lisa. I wouldn't care to hear from anyone smug enough to think he'd mastered the art of living, and I suspect that I never will hear the wisdom of any masters in the art of dying. And is it just me or do you also get the idea that the inventors of the "art of getting by" were perhaps just tired of failing to be masters of anything else and thought they might have a chance with that?

Here and there on the internet are some of the opinion that tuning SQL statements is an art. That might be. However, you don't have to rely on artistic inspiration to solve performance problems quickly and resolutely if you are using IBM Data Studio, InfoSphere Optim Query Tuner, or InfoSphere Optim Query Workload Tuner. In fact, tuning generally follows a sequence of steps that can cover many cases and that you can modify when you encounter idiosyncratic problems. The general sequences for tuning single statements and for tuning query workloads are in the information centers for these products, but I want to recap them here, because they might not be widely known.

General steps for tuning query workloads that run on DB2 for Linux, UNIX, and Windows
(The original steps are here.)

Create a workload of the SQL statements that you want to tune.
Run the Workload Statistics Advisor to generate RUNSTATS commands for ensuring that the DB2 optimizer has the most current and the most needed statistics. Then, run the commands.
Run the Workload Statistics Advisor again to generate recommendations for creating statistical views and modifying existing statistical views. Then, run the DDL scripts and RUNSTATS commands for collecting statistics for the views.
Run the Workload Index Advisor and follow its recommendations to ensure that the right indexes exist to help avoid unnecessary table scans. Then, run the DDL scripts to create the recommended indexes.

The idea is to make sure that the relevant statistics are current before you move on to getting recommendations from the Workload Index Advisor.

General steps for tuning query workloads that run on DB2 for z/OS
(The original steps are here.)

Create a workload of the SQL statements that you want to tune.
Collect EXPLAIN information about the SQL statements that are in the query workload. The workflow assistant must run the EXPLAIN statement for each of the SQL statements that are in the query workload. The Workload Statistics Advisor requires up-to-date information about the SQL statements.
Run the Workload Statistics Advisor to generate RUNSTATS commands for ensuring that the DB2 optimizer has the most current and the most needed statistics.
Run the Workload Index Advisor and follow its recommendations to ensure that the right indexes exist to help avoid unnecessary table scans.
Run the Workload Query Advisor and follow its recommendations to find and revise structures in SQL statements that are likely to cause the DB2 optimizer to choose a suboptimal access path.
Run the Workload Access Path Advisor to discover whether the DB2 optimizer is using a suboptimal access path to process the statement. Then, follow the advisor's recommendations.

Again, the idea is to make sure that the relevant statistics are current before you move on to getting recommendations from the other advisors.

General steps for tuning single SQL statements with the free features in IBM Data Studio full client or administration client
(The original steps are here.)

Format the SQL statement to make it easier to read and comprehend. Seeing a formatted version of the SQL statement can lead to insights that are not as apparent with the unformatted version.
Analyze the access plan graph for the SQL statement to find out how DB2 accesses data in the objects that the statement references.
Run the Statistics Advisor and follow its recommendations to ensure that the DB2 optimizer has the most current and most important statistics.

The idea here is to examine the SQL statement first to try to understand where the current bottlenecks might be, then to update the relevant statistics. You can then regenerate an access plan graph and find out whether the bottlenecks are improved.

General steps for tuning single SQL statements
(The original steps are here.)

Capture the SQL statement that you want to tune.
Format and annotate the problem statement to make it easier to read and comprehend. The annotations show relevant statistics that can help you understand what information the DB2 optimizer is using when generating the access plan.
Follow either or both of these steps:
Generate a visual representation of the access plan to see the choices that the optimizer makes in accessing the data.
Browse the access plan for problems by using the Access Plan Explorer.
Run the Statistics Advisor and follow its recommendations to ensure that the DB2 optimizer always has the most current and the most needed statistics.
Run the Index Advisor and follow its recommendations to ensure that the right indexes exist to help avoid unnecessary table scans.
Run the Query Advisor and follow its recommendations to find and revise structures in the statement that are likely to cause the DB2 optimizer to choose a suboptimal access path.
Run the Access Path Advisor to discover whether the DB2 optimizer is using a suboptimal access path to process the statement. Then, follow the advisor's recommendations.
If the DB2 optimizer continues to use a suboptimal access path, create, validate, and deploy a plan hint (for DB2 for z/OS) or optimization profile (DB2 for Linux, UNIX, and Windows) that gives the optimizer explicit instructions for choosing an access path.

The idea here is to examine the SQL statement first to try to understand where the current bottlenecks might be, then to update the relevant statistics. You can then regenerate an access plan graph and find out whether the bottlenecks are improved. You could also move on to the other advisors, regenerating an access plan graph at times to compare it against your benchmark. Plan hints and optimization profiles in this sequence are tools of last resort.

Do these general steps match those that you take to resolve 80% of performance problems when you are tuning with Data Studio, InfoSphere Optim Query Tuner, or InfoSphere Optim Query Workload Tuner? If not, how do your steps differ?

What sorts of uncommon situations do you find yourself in, and how do you modify these steps then?

Robert Heath, heathr@us.ibm.com (Start the subject line with "MDL blog: ")

Information Development for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner

Information roadmap for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner

rdheath Tags: sql tuning roadmap documentation performance ‎ 1 Comment ‎ 7,475 Views

Using software can be like trying to get to a freeway onramp that you know well by driving through an unfamiliar part of a city. You know what the outcome should look like (cruising on the open highway), but to get there you have to learn new landmarks ("Ah, so I turn left at the donut shop!"), new shortcuts ("So, I saved 5 minutes by taking Jefferson. I'll have to remember that."), the streets that go only one way (*pant, pant* "No, I guess I can't drive that way down Sycamore after all."), and more.

The information roadmap for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner will help you get to know the streets and landmarks of those two products, so that you can tune single SQL statements or workloads and get to your open highways faster. You'll find it at http://www.ibm.com/developerworks/data/roadmaps/roadmap_ioqt_ioqwt.html.

If you have any suggestions for links to include in it or have any other comments about it, please let me know.

Robert Heath
Information Development for InfoSphere Optim Query Tuner and InfoSphere Optim Query Workload Tuner

Feed for Blog Entries

Blogs