Gary is a DB2 course developer at IBM's Toronto Lab. He can be contacted at [email protected].
Any developer writing applications to access databases (and who isn't?) faces a big challenge. Working in a hectic, downsized environment, you probably write apps in record time to work within a rat's nest of clients and servers on a variety of operating systems that are strung together with an assortment of communication protocols. So how do you monitor and tune these apps for performance? The answer lies in good, cross-platform, visual tools that allow you to quickly spot bottlenecks in a database and inefficiencies within the Structured Query Language (SQL). These tools transform complex information into easy-to-interpret, graphical presentations. And both experienced users and novices work faster and make fewer errors when using visual tools. The best visual tools are easy to learn, simple to use, and produce quick results. In this article, I'll discuss two such tools -- the Performance Monitor and Visual Explain. Both come with IBM's DB2 for common servers database. (DB2 follows IBM's open system policy, which means that you can put DB2 on the operating system of your choice. It is therefore available on many platforms, including the OS/2 client to AIX server configuration, on which the examples shown in this article were tested.)
What Tools are Needed?
Faced with performance deficiencies in one or more applications accessing a database, you need the following tools to resolve the problem:
- A "health check" tool to periodically sample the database and provide an indication of when the problem is occurring and what database resources are affected.
- A tool to trace events occurring around the time of the performance degradation. By drilling down through data linked to these events, you can identify which application to examine and spot some indicators of the source of the problem.
- A tool that can analyze a SQL query in the application under scrutiny for inefficiencies.
How would these tools work together? Let's say the first tool indicates that, at a certain time, a database has a dramatic increase in the number of SQL statements accessing a database. The second tool would then identify the application issuing those statements and the tables being accessed. It would also list the other applications waiting for locks on the tables to be released by the offending application. The third tool would analyze the particular query causing everything else to wait. You could then modify the query and remove the bottleneck at the database.
The Performance Monitor
Let's look at a typical performance scenario in today's workplace and how one developer used DB2's tools to resolve it. Stella Dialtone is an industrious, young developer in one of those large computer corporations known only by three letters. This morning, she found the following message in her phone mail: "Stella, this is Guinevere Hare. I used to have this fast application to modify the dates in the incident table in the sample database. That's the table where we track dates when projects are due. But it's like molasses recently! And I know it couldn't possibly be my code! I hear you're an expert with those DB2 tools and figured you could take care of it. I run the application every day at 3:00 p.m. Can you get the system working right by tomorrow? Thanks."
Visual tools should lead users quickly to their goals, so Stella wrote down her goal: Analyze and fix the performance problem today. Then she fired up her Performance Monitor, a tool with two independent components -- the Snapshot Monitor and Event Analyzer. The Snapshot Monitor provides a view of database activity at a point in time, and the Event Analyzer provides a view of events over time. The Performance Monitor can also set audible alerts to sound when thresholds are exceeded. For example, a beep can be issued if more than three deadlocks occur. The Performance Monitor can be run from a remote client so that the cost of the monitoring operation is not added to the (usually already overburdened) server containing the database. Stella set up her monitoring from an old PC largely used as a footrest.
Setting up the Snapshot Monitor
To set up the Snapshot Monitor, Stella clicked on the Start Monitoring button and selected the output as Performance Graph. This choice provides a graph similar to one doctors would use when monitoring a patient's health signs. The Snapshot Monitor watches these key areas of a database:
- Buffer pool usage.
- SQL statements issued.
- Tables accessed.
- Sorting activity.
- Locks held and deadlocks.
- Units of work.
Each area contains a dozen or more monitored elements. For example, the SQL statements area watches dynamic and static statements issued, committed and failed statements, data definition language statements, selects, updates, inserts, deletes, rollbacks, rebinds, and so forth. For users tackling an elusive performance problem, more elements can be added by issuing an UPDATE MONITOR SWITCHES command. Up to 167 elements can be monitored using either snapshot or event monitoring. Stella chose to accept the defaults.
Setting Up The Event Analyzer
To set up the Event Analyzer, Stella created and then turned on an event monitor to trace events on transactions, statements, and tables. An event monitor captures a series of events. An event monitor becomes input for the Event Analyzer. Stella could have traced more events and created more event monitors; the level of detail is up to the user. The events that are typically traced are:
- Transaction start and stop times.
- Statistics on tables, table spaces, and database operation.
- User identification of SQL statements.
- Resources and applications involved in locking and deadlocks.
- Information on overflows.
Stella's plan was to collect the events she believed were associated with Guinevere Hare's problem. Later, she would turn off the event monitor and use the Event Analyzer to examine the events occurring around 3:00 p.m.
The Snapshot Monitor
The Snapshot Monitor takes snapshots of database activity at regular time intervals. At those points in time, you can view the current state of activity on the buffer pool, SQL statements, sorts, locks, and catalog views. Output is in the form of easy-to-interpret, color-coded graphs. Alternatively, you can get text-based information in summary or detailed form. Shortly before 3:00 p.m. (15:00), Stella saw the following jump in activity on her Snapshot Monitor (see Figure 1).
Glancing at the legend (see Figure 2), it was obvious the blue spikes indicated the buffer pool was suddenly becoming very active. The buffer pool is an area where database pages containing table rows and index entries are loaded to improve system performance. A jump in this area suggested to Stella that a large amount of data was being accessed. The purple spikes indicated a dramatic increase in SQL statements. Stella assumed that an application that was consuming a lot of database resources had started. This application was probably the one affecting Guinevere Hare's application. But Stella needed to identify the offending application and verify the correlation. She closed the Snapshot Monitor and turned off the event monitor.
The Event Analyzer
The Event Analyzer analyzes event monitor input files, which contain traces of events. You can see when transactions are starting and stopping, how long SQL statements are taking to run, what applications are waiting for locks to be released, and which tables, user IDs, and applications are associated with these events. Output appears in tabular form, aided by visual clues such as icons, highlighting, and color coding. A browser lets you jump among several events. When Stella started the Event Analyzer using her event monitor, she saw the "transaction view" in Figure 3. The highlighted transaction at 15:07 spent about a third of its time in a lock wait state; in other words, waiting for a lock to be released. Stella reasoned that this transaction was Guinevere Hare's application. It was probably waiting for the lengthy transaction listed above it at 15:03. Browsing through other views identified the user IDs of the transactions and the tables they were accessing. The 15:03 transaction was run by a senior executive named Thelonious Tortoise. His application was accessing the incident table that Guinevere Hare's application also needed to access. She called Thelonious Tortoise and told him he was the lucky recipient of a recent draw. The prize was a free performance tune-up of his application.
Visual Explain
Visual Explain provides a visual representation of how the optimizer (the component that gets requested data) processes a query. Looking at color-coded, geometric shapes to indicate objects (like tables and indexes) or operations (like scans and sorts), you can easily see how the optimizer processes the query. An overview feature lets you zoom in for details or zoom out for a high-level view. The cost of each operation is shown and the total cost appears in the heading. Visual Explain is helpful when you have a slow-running query because, once you know how the optimizer is processing the query, you can make changes to the tables, database, or query itself that will help the optimizer process the query faster.
Stella browsed through Thelonious Tortoise's application and found the query accessing the incident table; see Example 1. She understood why Guinevere Hare's app was left waiting. Tortoise's query was complex; it joined tables, then sorted and ordered data. If one or both tables were large -- that is, contained tens of thousands of rows of data -- then scanning for the data would be costly.
Setting Up Visual Explain
As with the Event Analyzer, you must first capture the required information before Visual Explain can display it in graphical form. Stella set DB2's Explain Snapshot Special Register to "yes." This setting instructed DB2 to collect Explain information when a query was run. She entered the query at the command line and let DB2 execute it. Selecting this query from the Explained Statements History produced Figure 4.
Analyzing and Correcting a Slow Query
If you're developing database applications, make a mental note that "table scan" is a bad term and "index scan" is a good term. Why? Suppose you have to find a needle in a haystack. One way would be to start at the top of the stack and remove a straw at a time until you find the needle. If the needle is on the bottom, it will take a long time to find it. But suppose you know that the needle is at the bottom of the haystack. In that case, you could go to the bottom right away, saving yourself a great deal of time. A table scan works in the first way: It starts at the top of a table and searches row by row until it finds the data for a query. An index scan uses an index. Since an index lists what data is at what row, an index scan finds data much faster. As a general rule, large tables benefit from an index. One good use of Visual Explain is to examine the indexes used by the optimizer.
Stella began her analysis by looking at the total cost of the query: 45867 timerons (a timeron is a unit to indicate computer resources consumed by the query, particularly the CPU and I/O cost). Using the count function, she found the incident table had 43,000 rows (it was a large table). But the optimizer was only using table scans (TBSCAN), and not only in the incident table but also in the empserv table to which it was joined. She added an index called INCSERDU on the serialwkg and duedate columns of the incident table, and an index called EMPDEPT on the department column of the empserv table. After running a utility called "Runstats," which lets the optimizer know indexes have been added, Stella executed the query again. This time the Explained Statements History produced Figure 5. The total cost this time dropped to 7294 timerons -- one sixth of the former cost.
Conclusion
In this article, I've described how a developer might quickly identify the source of a performance problem and correct it with DB2's visual tools. These tools can be used to tune applications or to resolve common performance problems that database administrators face every day. They can also be used to tune the database itself by monitoring the effect of modified configuration parameters. Finally, these visual tools can help prepare your applications for a benchmark.
Acknowledgments
I'd like to thank Serge Bourbonnais and Heinz Boente, for reviewing this article, and Dennis Bockus, for providing editorial guidance. All work at IBM.
DDJ
