Many applications have Service Level Agreements (SLAs) that promise quick turnaround or acceptable on-line response time. Lengthened transaction elapsed times are a cause for concern. Here are several methods the database administrator can use to alleviate these issues.
Generally speaking, the perceived elapsed time for a DB2 database transaction or single query depends upon multiple factors. The major factors are:
- Query interpretation and access path selection by DB2
- CPU speed and availability
- Physical I/O access to storage media
- Delays due to waits or locks
- Network transit time
Of these, physical access to storage media (usually disk or RAID storage) is the slowest. When there are I/O constraints or bottlenecks in the system, access is slower still. Therefore, when doing performance tuning that addresses long query elapsed times, the database administrator will usually concentrate on reducing I/Os.
In a case where a query is taking excessively long to complete, what steps should be taken?
The first task for the DBA is to determine the major contributors to the long query elapsed time. These are:
1. Incorrect or inefficient access path to the data
2. Excessive waits for I/Os to complete
3. Excessive waits for data locks to release
A complete discourse on access path tuning is beyond the scope of this article. However, some general tuning comments are in order. Some of the most common reasons for DB2 choosing an inefficient access path include:
- Lack of up-to-date statistics
- Poorly-clustered data
- SQL coding that precludes the optimizer from choosing some access path
- Poor index design
Most of these can be addressed by the following strategy.
Design tables so that data is clustered efficiently. Clustering means that table rows having keys with similar values should be store close to each other. Common examples are account tables, where rows are stored in account sequence, and transactional data, where transactions are stored physically, sorted by transaction date.
Clustering is most beneficial for queries that access a large number of rows. In the case of transaction tables, these are commonly accessed by date. For example, a query might summarize balances or count the number or category of transaction codes for all transactions dated in the current month.
Another aspect of efficient clustering is that tables are commonly joined by their primary and foreign key columns. In an order entry system containing a customer table and an order table, each might be clustered by their primary key (customer number and order number, respectively).
Design indexes so that common data access patterns (scans, joins) are supported. As noted previously, application tables that are related by their primary and foreign keys are commonly joined. The database designer usually defines indexes on these columns to permit index usage during joins of the tables. Indexes on secondary keys or searched fields are also common, so that queries can access the indexes to access that data. As indexes are usually much smaller than their base tables, this reduces physical I/Os.
Ensure that table reorgs are executed when needed. As table data is changed or updated by insert, update, and delete SQL statements, the physical locations of rows with similar keys may grow farther apart. The DB2 table reorganization (or reorg) utility restores the clustering sequence of the physical data by unloading it, sorting it by the clustering keys, then reloading the table.
Ensure that index reorgs are executed when needed. Similarly to tables, indexes on tables with update activity may become unclustered as well.
Ensure that data distribution statistics are up-to-date. DB2 maintains data distribution statistics on tables and indexes using its Real Time Statistics facility (or RTS). In addition to this, database administrators can execute the RunStats utility to gather additional statistics such as frequent values of columns, data skew, and other properties. See the relevant DB2 manuals for more information.
Along with the query itself, these statistics are used by the DB2 optimizer to determine the data access path having the least cost in total CPU and I/Os. Therefore, up-to-date statistics are strongly recommended.
Ensure that static SQL is bound after all of the above. SQL exists in two forms: dynamic and static. Dynamic SQL is usually created in an ad hoc manner by a user, perhaps using a query development tool or reporting tool. It arrives at the DB2 database engine and must be analyzed and executed at that time. Static SQL exists in hard-coded form in an application program. When the program is compiled, DB2 stores the SQL statements and access paths for later reference during execution. This means that the data access paths for SQL statements in a program are pre-determined at compile time. Storing the access path for later use is call binding, and the SQL statements are said to be bound.
Since static SQL statement access paths are determined at bind time, all of the above strategies (indexes, clustering, reorganization, and statistics gathering) must be completed prior to that time.
Back to Elapsed Time
With access path tuning addressed, the database administrator can now turn to the details of query execution to determine if a long elapsed time can be reduced. One common tactic is to use other underutilized resources such as CPU or disk space to compensate for slow I/O speeds. This is known as resource balancing.
Resource Balancing: Disk Space
How can one reduce physical I/Os to a DB2 table if there is abundance of disk space? One way is to create multiple indexes on a table. These indexes might contain a major subset of the table columns, perhaps all that are required by common queries. In this fashion, DB2 can determine that the best access path to the table is through that index. Indeed, if all the columns required by the query are contained in the index, the access path is said to be index-only. In general, indexes take up far less disk space than tables. Accessing such an index may involve far fewer I/Os and thusly reduce the number of physical I/Os.
Another alternative is data compression. DB2 comes with a hardware-assisted data compression algorithm that the DBA can use when defining a table. Data loaded to the table can be reduced by fifty percent, or even as much as eighty percent in cases of character data. This reduction in size results in the table being stored in a much smaller area. Again, table access will result in fewer physical I/Os.
Resource Balancing: CPU
The DBA can take advantage of the availability of CPU cycles by two database design concepts called partitioning and parallelism.
Partitioning is the splitting of a table into multiple physical files. The most common variation is called horizontal partitioning. The DBA designates a number of table partitions, and how rows will be assigned to each partition. By far the most common designs specify that each partition contains table rows whose key values are within a certain range. For example, one common method for data warehouse tables is to partition by date, where each partition contains table rows for a particular day, month, or date range.
Parallelism is the ability of the database management system (DBMS) to logically split a database query into multiple pieces and process each piece separately in parallel. For example, consider a query against a data warehouse table requesting all rows for a certain year. Assume that the table is partitioned by month; that is, each partition contains data for a particular month of the year. It is now possible for the DBMS to (internally and transparently) split our query into twelve similar queries, all equal except that each of the twelve accesses rows for a single month. One large query for a year’s worth of data now becomes twelve similar queries for one month each. Of course, the DBMS must also take the results of the twelve queries and combine them into a single result for the requestor.
We can now see how the combination of these two strategies can drastically reduce physical I/O times. The original query must access twelve months of data in sequence across the table. The twelve queries can be executed simultaneously, thus reducing the elapsed time by almost a factor of ten. The number of I/Os is almost the same in each case; however, by partitioning the table and by using parallelism, the same I/Os are done by twelve parallel processes.
Resource Balancing: Memory
DB2 uses blocks of memory for storing table and index data read from disk. These blocks or areas are called virtual pools. The DBA defines the sizes of these areas in DB2 configuration parameters; then, in table and index definitions, identifies which pools are used by which objects.
With an abundance of memory available, the DBA can greatly increase the sizes of some virtual pools to benefit some objects and queries against them. For example, if some tables are frequently used the DBA assigns them to their own buffer pool and allocates a large area. As these tables are accessed by queries DB2 reads them into memory. Later references to the object will probably find them still in memory, meaning that DB2 can retrieve data immediately rather than reading from disk. By accessing the table in memory DB2 avoids physical I/Os and greatly reduces elapsed times.
Long query elapsed times can be addressed in multiple ways. First, the DBA must ensure that the table design includes well-clustered data, and that the proper indexes are defined. Next, the DBA coordinates with the application support team to discover how often the table will need reorganization, and when to gather updated data distribution statistics. Finally, static SQL will need be bound in a regular basis if the data clustering or statistics have changed.
Once the DBA has completed these preliminary steps, long-running queries may benefit from resource balancing. The DBA can use available CPU, disk space, and memory to reduce elapsed times.
One final note: Most of the issues discussed here do not require the DBA to wait until a query is first executed. Proper clustering, partitioning, index selection, and reorg and statistics scheduling should be addressed during database design.