So far in this series of blog posts on DB2 performance tools, we have looked at system and database performance solutions. But perhaps the most important solution area involves monitoring and tuning application SQL statements.
Why do I say that? Well, the cause of most performance problems is usually due to bad SQL and application code. Not every problem, of course, but maybe as much as 70 to 80 percent of DB2 (and relational) performance issues is likely due to inefficient application code.
Writing SQL statements to access database tables is the
responsibility of an application development team. However, the DBA usually
gets involved when it comes to the performance of SQL. With SQL’s flexibility,
the same request can be made in different ways. Because many of these methods
are inefficient, application performance can fluctuate wildly unless the SQL is
analyzed and tuned by an expert prior to implementation.
The EXPLAIN command provide information about the access paths used by
SQL queries by parsing SQL in application programs and placing encoded output
into a PLAN_TABLE
or by producing a standard access path report. To gauge efficiency, a DBA must
decode this data and determine if a more efficient access path is available.
SQL code reviews are required to ensure that optimal SQL
design techniques are used. An application design walkthrough should be performed for each program before it moves to production. This is done to review all SQL statements, the selected access
paths, and the program code in which the SQL is embedded. The review also
includes an evaluation of database statistical information to ascertain whether
production-level statistics were used at the time of the EXPLAIN.
A line-by-line review of application source code and EXPLAIN
output is tedious and prone to error, and can cause application backlogs. SQL
analysis tools greatly simplify this process by automating major portions of
the code review process. The SQL analysis tool typically
- Analyzes the SQL
in an application program, describing the access paths chosen in a graphic
format, an English description, or both.
- Issues warnings
when specific SQL constructs are encountered. For example, each time a sort is
requested (by ORDER BY, GROUP BY,
or DISTINCT),
a message informs the user of the requisite sort.
- Suggests
alternative SQL solutions based on an “expert system” that reads SQL statements
and their corresponding PLAN_TABLE entries and
poses alternative SQL options.
- Extends the rules
used by the “expert system” to capture site-specific rules.
- Analyzes at the
subsystem, instance, server, application, plan, package, or SQL statement
level.
- Stores multiple
versions of EXPLAIN output, creates performance
comparisons, and plans history reports.
Tools that analyze the performance of the application
code in which the SQL is embedded are available too. These tools usually
capture in-depth information about programs as they are run and provide reports
that specify which areas of the code consume the most resources. Unfortunately,
most of these tools do not necessarily interface to SQL analysis tools. Why might this be a problem?
Well, consider an application program that contains a singleton SELECT inside a loop. The singleton SELECT requests a single row based on a WHERE clause, checking for the primary key of that table. For each iteration of the loop, the program changes the primary key value being searched such that the entire table is read from the lowest key value to the highest key value.
SQL analysis tools will probably not target the SQL statement as inefficient because the predicate value is for the primary key, which should invoke indexed access. The application program analysis tool may flag the section of the code that accesses the data as inefficient, but it will not help you to fix it or tell you why it is inefficient.
A knowledgeable performance analyst or DBA would have to use both tools and interpret the output of each to arrive at a satisfactory conclusion. For example, it could be more efficient to code a cursor, without a predicate, to retrieve every row of the table, and then fetch each row one by one. This method would eliminate index I/O, might use parallel access, and therefore should reduce I/O and elapsed time—thereby enhancing performance.
Only a trained analyst can catch this type of design problem during a code walkthrough. Although a plan analysis tool significantly reduces the effort involved in the code review process, it cannot eliminate it.
So what should you look for in an SQL analysis tool? The first feature required of SQL analysis tools is the
ability to read and interpret standard EXPLAIN or SHOW
PLAN output. The tool should be able to read the plan table or
interface directly with the DBMS command to obtain the output. It then must be
able to automatically scan the EXPLAIN or SHOW
PLAN data and report on the selected access paths and the
predicted performance. Advanced tools will provide recommendations for
improving the SQL by adding indexes or modifying the SQL.
Yet another category of tool can evaluate access paths as you REBIND programs and categorize them into changed and unchanged access paths. This helps to identify where SQL tuning may be required. Advanced forms of these tools also apply rules tot he changed SQL to indicate if the access path is better or worse than the prior access path. Such tools can be incredibly helpful for performing mass rebinds of your production programs.
SQL Monitors
An SQL monitoring solution can identify running SQL statements, filter the information, and display it in an appropriate order and configuration. For example, you can use an SQL monitor to identify the Top Ten CPU users over the past hour (or the past day, week, etc.)
Usually, there is the on-line capability, that displays what is happening right now, and the historical capability, which can display details and trends over time.
An SQL monitor is particularly helpful when working to remediate production performance issues where hundreds or thousands (or more) of SQL statements can be running at any one time.
End-to-End Performance Tools
Modern applications require multiple system components
and run across multiple networked devices to deliver functionality. When
performance problems arise, it can be difficult to determine what, exactly, is
causing the problem. Is it on the client or the server? Is it a networking
problem? Is it a database issue or a code problem?
End-to-end performance monitoring tools exist that track
an application request from initiation to completion. These solutions provide
enhanced visibility specifically into application performance—giving
organizations the power to understand both when and why performance has
degraded, and the information needed to improve matters in a
business-prioritized fashion.
By following the workload as it progresses across multiple pieces of hardware and software, problem determination becomes possible.
Workload Testing and Estimation
Another category of SQL performance tool allows you to identify a workload consisting of programs and transactions that are to be run during a specific timeframe. The tools help to identify performance issues that crop up only when the application is running at a production volume.
Data
Studio
Finally, no overview of application performance tools for DB2 would be complete without a brief mention of IBM's Data Studio. Data Studio is a free-of-charge tool for basic DB2 administration and development tasks.
Data Studio offers an easy to use GUI interface for the following:
- Designing data access queries and routines
- Building, running, and tuning SQL
- Building, testing, and deploying stored procedures (using SQL or
Java)
- Creating Web services in for Service Oriented Architecture (SOA)
solutions
- Developing DB2 SQLJ applications
- Managing database objects and authorizations
You can download Data Studio
at IBM’s website. It is available as a stand-alone package geared mostly for
DBAs, or as an IDE geared for both DBA and development work.
Of course, IBM sells other DB2
tools for a fee, some of whichcan integrate and work well with Data Studio. And there are other tools that compete with Data Studio that offer a lot more functionality than the basics provided by the free capabilities of Data Studio (such as Dell's Toad).
Summary
These past few posts have taken a broad overview look at the categories and types of performance tools available for managing the performance of your DB2 for z/OS environment. Many of the same categories of tools are available for DB2 for LUW (as well as other DBMS offerings).
Have I missed any important categories? If so, drop me a line or add a comment here to the blog. I'm always interested in getting feedback.
Thanks... and happy performance tuning!
