Performance Tools That Operate on Databases and Database Objects

In our last blog post here, we covered DB2 system performance management tools - that is, tools that look at the performance at a  system or subsystem level. Today, we turn our attention to the database objects...

Most DBMSs do not provide an intelligent database analysis capability. Instead, the DBA or performance analyst must use system catalog views and queries, or a system catalog tool, to keep watch over each database and its objects. This is not an optimal solution because it relies on human intervention for efficient database organization, opening up the possibility for human error.

DB2 for z/OS, however, does provide Real Time Statistics that can be used to drive database optimization and maintenance. What are Real Time Statistics (or RTS)?

Well, RTS are similar to traditional database statistics that are accumulated using a utility programs (RUNSTATS), but the RTS are accumulated by DB2 “on the fly” as the database management system and its applications are running. That is to say, without having to run a utility program.

RTS are stored in two tables in the DB2 Catalog:

• SYSIBM.SYSTABLESPACESTATS: Contains statistics on table spaces and table space partitions
• SYSIBM.SYSINDEXSPACESTATS: Contains statistics on index spaces and index space partitions

But since this post is supposed to be talking about database-performance tools, I don’t want to get into a full blown discussion of RTS… after all, RTS are a built-in component of DB2. That said, the ability of DB2 to generate and store RTS enables database performance tools to make decisions based on actual, up-to-date performance metrics. Of course, DB2 is not the only DBMS with such metrics, but since this is a blog about DB2, I won’t get into any details of the other database systems.

Database Analysis Tools

At any rate, database analysis tools are available that can proactively and automatically monitor your database environment. These database analysis tools typically can: 

• Collect statistics for tables and indexes: standard statistical information from the DBMS, extended statistics capturing more information (for example, data set extents), or a combination of both.
• Read the underlying data sets for the database objects to capture current statistics, read the database statistics from the system catalog, read tables unique to the tool that captured the enhanced statistics, or any combination thereof.
• Set thresholds based on database statistics whereby the automatic scheduling of database reorganization and other maintenance tasks can be invoked.
• Provide a series of canned reports detailing the potential problems for specific database objects.

Database Utilities

Another category of performance tool that operates at the database (or database object) level are database utilities. Usually there are some number of rudimentary utilities that ship for free with the DBMS. These are usually simple, no-frills programs that are notorious for poor performance, especially on very large tables. However, these utilities are required to populate, administer, and organize your databases. The typical utilities that are provided are LOAD, UNLOAD, REORG, RUNSTATS, BACKUP, and RECOVER, as well as utilities for integrity checking.

Although I suppose it is possible to make an argument, at some level, for any and all of these utilities to have a performance aspect to them, REORG and RUNSTATS are the ones that definitely impact database performance.

RUNSTATS is used to gather statistics on the composition of the database and REORG is used to organize table space data optimally.

There are third-party vendors that provide support tools that replace the database utilities and provide the same or more functionality in a more efficient manner. For example, it is not unheard of for third-party vendors to claim that its utilities execute anywhere from four to ten times faster than the native DBMS utilities. These claims must be substantiated for the data and applications at your organization (but such claims are believable). Before committing to any third-party utility, the DBA should be sure that the product provides all of the basic functionality required.

When testing utility tools from different vendors, be sure to conduct fair tests. For example, always reload or recover prior to testing REORG utilities, or you may skew your results due to different levels of table organization. Additionally, always run the tests for each tool on the same object with the same amount of data, and make sure that the data cache is flushed between each test run. Finally, make sure that the workload on the system is the same (or as close as possible) when testing each product because concurrent workload can skew benchmark test results.

Yet another category of database-focused tool is the Utility management tool. This type of tool provides administrative support for the creation and execution of database utility jobstreams. These utility generation and management tools:

• Automatically generate utility parameters, JCL, or command scripts.
• Monitor the database utilities as they execute.
• Automatically schedule utilities when exceptions are triggered.
• Restart utilities with a minimum of intervention. For example, if a utility cannot be restarted, the utility manager should automatically terminate the utility before resubmitting it.

Space Management Tools

Most DBMSs provide basic statistics for space utilization, but the in-depth statistics required for both space management and performance tuning are usually inadequate for heavy duty administration. For example, most DBMSs lack the ability to monitor the requirements of the underlying files used by the DBMS. When these files go into extents or become defragmented, performance can suffer. Without a space management tool, the only way to monitor this information is with arcane and difficult-to-use operating system commands. This can be a tedious exercise.

Additionally, each DBMS allocates space differently. The manner in which the DBMS allocates this space can result in inefficient disk usage. Sometimes space is allocated, but the database will not use it. A space management tool is the only answer for ferreting out the amount of used space versus the amount of allocated space.

Space management tools often interface with other database and systems management tools such as operating system space management tools, database analysis tools, system catalog query and management tools, and database utility generators.

Compression Tools

A standard tool for reducing storage costs is the compression utility. This type of tool operates by applying an algorithm to the data in a table such that the data is encoded in a more compact area. By reducing the amount of area needed to store data, overall storage costs are decreased. Compression tools must compress the data when it is added to the table and subsequently modified, then expand the data when it is later retrieved.

In the earlier days of DB2, compression tools that used an exit routine were common. But ever since DB2 Version 3, which introduced the built-in, hardware-assisted compression capability of DB2, compression duties are handled quite efficiently with out-of-the-box DB2 functionality.

Additionally, some tools are available that compress database logs, enabling more log information to be retained on disk before it is offloaded to another medium.

Synopsis

So, there are a number of different categories of performance tools that function at the database or database object level that are worth considering. These differ from system performance tools (covered in the last blog post) and application performance tools (which will be covered in the next blog post).