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).
