Index fragmentation can occur from normal database activity such as INSERT and UPDATE. When indexes become highly fragmented, there is a significant impact to RUNSTATS performance. You can learn to recognize when this is happening, and take corrective measures.
This article is targeted for installations running DB2 9.7 or earlier. In DB2 10.1 with the introduction of readahead prefetching, index fragmentation does not have the same effect on RUNSTATS performance.
How index scan performance can affect RUNSTATS performance
DB2 collects statistics for the table and all its indexes when you submit the following command:
RUNSTATS ON TABLE MY.TABLE1 AND INDEXES ALL
It does so by first scanning the table to determine table statistics, and then scans all the indexes of the table, one at a time, to determine index statistics.
For large tables with several indexes, index scan performance is a significant factor in RUNSTATS performance. If the appropriate index pages are prefetched into the buffer pool before the scan consumer requests the pages, an index scan will run fast. However, if the scan consumer must wait for disk I/O to bring pages into the buffer pool, the index scan will not run optimally. Other database operations, such as queries, also use index scans. However, RUNSTATS fully scans all the indexes of the table one at a time, while queries might scan only a portion of one index of the table. When index scan performance is poor, it is most noticeable in RUNSTATS.
DB2 9.7 and earlier use sequential detection to determine if index prefetching should be performed. As RUNSTATS processes index pages in the key order of the index, prefetching starts if the database manager detects sequential access of the index pages. When an index is unfragmented, with physically contiguous pages, prefetching is beneficial. However, if the pages of an index are scattered throughout a table space, prefetching pages might be wasteful if most of the pages prefetched are not used.
Multiple factors contribute to index fragmentation. One factor is having multiple indexes for a table. Indexes for a table are stored in a single index object (with the exception of non-partitioned indexes of a partitioned table) and therefore, the pages of one index can be intermixed with the pages of another index. Another factor is index page splits that can occur with INSERT and UPDATE activity.
Index reorganization rebuilds index data into unfragmented physically contiguous pages. This allows sequential detection to prefetch pages into the buffer pool so that the next page is available when RUNSTATS wants it. The result is a faster RUNSTATS.
DB2 10.1 provides new prefetching capabilities where index fragmentation no longer has a significant detrimental effect to index scan performance. This is discussed in more detail later in the article.
An example with fragmented indexes and unfragmented indexes
To demonstrate the effect of fragmented indexes to RUNSTATS performance, let's create an extreme case of index fragmentation. Listing 1 contains commands to create a table with 10 million rows. It creates five indexes first and then inserts data. With this insert method, the pages of the different indexes are intermixed, and each index is severely fragmented.
Listing 1. Script to create fragmented indexes
-- run this CLP file with autocommit off (db2 +c -tvf FILE) connect to db97; -- create not logged initially table drop table demo.t1; CREATE TABLE demo.t1 (i1 int not null, i2 int not null, i3 int not null, i4 int not null, i5 int not null, i6 int not null, i7 int not null) not logged initially; -- create indexes BEFORE inserting data create index demo.t1i1 on demo.t1 (i1); create index demo.t1i2 on demo.t1 (i2); create index demo.t1i3 on demo.t1 (i3); create index demo.t1i62 on demo.t1 (i6,i2); create index demo.t1i765 on demo.t1 (i7,i6,i5); -- insert 10M rows -- note: the pages for the five indexes will be intermixed in the table space -- and each index will be fragmented insert into demo.t1 with q(a) as (values 1 union all select a+1 from q where a<10000000) select -a,mod(a,1237),mod(-a,251),mod(a,353),mod(-a,100),mod(a,257),mod(-a,511) from q; commit; connect reset;
Now let's look at how long a RUNSTATS takes. With fragmented indexes, Listing 2 shows how a typical RUNSTATS runs for over six minutes.
Listing 2. RUNSTATS execution time with fragmented indexes
values (current timestamp, 'TEST1: start') 1 2 -------------------------- ------------- 2013-06-16-13.59.16.093219 TEST1: start 1 record(s) selected. runstats on table DEMO.T1 with distribution and sampled detailed indexes all DB20000I The RUNSTATS command completed successfully. values (current timestamp, 'TEST1: stop') 1 2 -------------------------- ------------ 2013-06-16-14.05.25.235269 TEST1: stop 1 record(s) selected.
Next, let's reorganize the indexes with the REORG command and do another RUNSTATS. Listing 3 shows the index reorganization took 1 minute to complete.
Listing 3. REORG INDEXES command and execution time
reorg indexes all for table demo.t1 DB20000I The REORG command completed successfully. real 0m58.54s user 0m0.01s sys 0m0.01s
Listing 4 shows that, with unfragmented indexes after the reorganization, RUNSTATS now runs in less than 30 seconds. Compared to the original time of six minutes, this is over 12x improvement.
Listing 4. RUNSTATS execution time after index reorganization
values (current timestamp, 'TEST2: start') 1 2 -------------------------- ------------- 2013-06-17-23.00.35.432198 TEST2: start 1 record(s) selected. runstats on table DEMO.T1 with distribution and sampled detailed indexes all DB20000I The RUNSTATS command completed successfully. values (current timestamp, 'TEST2: stop') 1 2 -------------------------- ------------ 2013-06-17-23.01.03.483116 TEST2: stop 1 record(s) selected.
This is a contrived example, but the potential impact is real. In large DB2 installations, DBAs have reported RUNSTATS elapsed time going from 18 hours to 40 minutes.
Using the statistics logs to find tables with long RUNSTATS time
You might be wondering how to check if you have any tables with this RUNSTATS performance issue. One approach is to identify tables with long running RUNSTATS. You want to focus on these tables because time is an important resource. If a table suffers from index fragmentation, but RUNSTATS elapsed time is not too bad, perhaps because the table is relatively small, that table does not need your attention.
Statistics logs, introduced in DB2 9.5, contain information about statistics operations in the instance. The logs are stored in the events directory within the diagnostic data directory path (db2dump/events). Listing 5 shows the COLLECT action log entries for the RUNSTATS in the fragmented indexes example. From the statistics logs, you can identify long running RUNSTATS by looking at the start and success timestamps.
Listing 5. Statistics log entries
2013-06-16-220.127.116.11750-240 E1597A582 LEVEL: Event PID : 2950020 TID : 1544 PROC : db2sysc INSTANCE: kwaiwong NODE : 000 DB : DB97 APPHDL : 0-29 APPID: *LOCAL.kwaiwong.130616175833 AUTHID : KWAIWONG EDUID : 1544 EDUNAME: db2agent (DB97) FUNCTION: DB2 UDB, relation data serv, sqlrLocalRunstats, probe:10 COLLECT : TABLE AND INDEX STATS : AT "2013-06-16-13.59.16.096268" : BY "User" : start OBJECT : Object name with schema, 11 bytes DEMO .T1 IMPACT : None 2013-06-16-14.05.25.231686-240 E2180A723 LEVEL: Event PID : 2950020 TID : 1544 PROC : db2sysc INSTANCE: kwaiwong NODE : 000 DB : DB97 APPHDL : 0-29 APPID: *LOCAL.kwaiwong.130616175833 AUTHID : KWAIWONG EDUID : 1544 EDUNAME: db2agent (DB97) FUNCTION: DB2 UDB, relation data serv, sqlrLocalRunstats, probe:220 COLLECT : TABLE AND INDEX STATS : AT "2013-06-16-14.05.25.231595" : BY "User" : success OBJECT : Object name with schema, 11 bytes DEMO .T1 IMPACT : None DATA #1 : String, 109 bytes RUNSTATS ON TABLE "DEMO"."T1" ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL
An easier way to review information from the statistics log is to use the SYSPROC.PD_GET_DIAG_HIST table function. You can use this table function to format different aspects of the statistics log to help you view and analyze statistics events. An example is shown in Listing 6.
Listing 6. Using SYSPROC.PD_GET_DIAG_HIST
select timestamp(varchar(substr(first_eventqualifier,1,26),26)) as eventtime, substr(objname_qualifier,1,20) as objschema, substr(objname,1,10) as objname, substr(eventtype,1,8) as eventtype, substr(second_eventqualifier,1,10) eventby, substr(eventstate,1,10) as eventstate from table(sysproc.PD_GET_DIAG_HIST('optstats','EX','NONE',null,cast(null as timestamp))) as sl order by objschema,objname,eventtime EVENTTIME OBJSCHEMA OBJNAME EVENTTYPE EVENTBY EVENTSTATE -------------------------- -------------------- ---------- --------- ---------- ---------- 2013-06-16-13.59.16.096268 DEMO T1 COLLECT User start 2013-06-16-14.05.25.231595 DEMO T1 COLLECT User success 2 record(s) selected.
Keep in mind that the statistics logs are a rolling log, and you can use the DB2_OPTSTATS_LOG registry variable to configure how DB2 retains the logs.
Checking in-progress RUNSTATS
Another way to look for long running RUNSTATS is to check what is running, and if it has been running a long time. Listing 7 shows output from LIST UTILITIES SHOW DETAIL submitted when the RUNSTATS was still executing. In this example, the user invoked RUNSTATS started at 13:59 on June16 for table DEMO.T1.
Listing 7. LIST UTILITIES output
list utilities show detail ID = 1 Type = RUNSTATS Database Name = DB97 Partition Number = 0 Description = DEMO.T1 Start Time = 06/16/2013 13:59:16.356724 State = Executing Invocation Type = User Throttling: Priority = Unthrottled
RUNSTATS execution information is also available from the db2pd command, discussed later in the article.
Identifying fragmented indexes from statistics
You can use index statistics to identify indexes that might be fragmented. The statistics involved are SYSCAT.INDEXES.SEQUENTIAL_PAGES and SYSCAT.INDEXES.NLEAF. When an index is perfectly contiguous, sequential_pages will be very close to nleaf. When an index is fragmented, sequential_pages will be much less than nleaf. Let's look at the statistics collected from the earlier example.
Listing 8 shows that before REORG, all the indexes have 0 sequential_pages, and after REORG, all the indexes have sequential_pages very close to nleaf. Along with table size, you can use these index statistics to identify tables with fragmented indexes on which you might want to take action.
Listing 8. SEQUENTIAL_PAGES and NLEAF index statistics
select substr(indname,1,8) as idxname, stats_time, nleaf, sequential_pages from syscat.indexes where tabschema='DEMO' and tabname='T1' --- -- first, before REORG ... --- IDXNAME STATS_TIME NLEAF SEQUENTIAL_PAGES -------- -------------------------- -------------------- -------------------- T1I1 2013-06-16-14.05.25.080000 41841 0 T1I2 2013-06-16-14.05.25.080000 25245 0 T1I3 2013-06-16-14.05.25.080000 24894 0 T1I62 2013-06-16-14.05.25.080000 31138 0 T1I765 2013-06-16-14.05.25.080000 81178 0 5 record(s) selected. --- -- next, after REORG ... --- select substr(indname,1,8) as idxname, stats_time, nleaf, sequential_pages from syscat.indexes where tabschema='DEMO' and tabname='T1' IDXNAME STATS_TIME NLEAF SEQUENTIAL_PAGES -------- -------------------------- -------------------- -------------------- T1I1 2013-06-17-23.01.03.320000 41667 41666 T1I2 2013-06-17-23.01.03.320000 22475 22474 T1I3 2013-06-17-23.01.03.320000 22473 22472 T1I62 2013-06-17-23.01.03.320000 22817 22816 T1I765 2013-06-17-23.01.03.320000 64103 64102 5 record(s) selected.
Confirming a long RUNSTATS time is from index statistics collection
Once you have identified tables that might have fragmented indexes, you might want to confirm that the long elapsed time is attributable to index statistics collection. The -runstats option of the db2pd monitor command can help. It provides the duration for different phases of RUNSTATS, including the time to collect table statistics, and the time to collect index statistics for up to four indexes. When a table has more than four indexes, you can run db2pd, repeatedly if necessary, during RUNSTATS execution to capture the collection time for the first indexes. Running db2pd after RUNSTATS completes will show the collection time for the last four indexes processed. Let's look at db2pd data, again, from our earlier example.
Listing 9 shows db2pd -runstats output after RUNSTATS completed.
Listing 9. db2pd -runstats output
DB Partition 0 - Database DB97 - Active - Up 0 days 00:07:44 - Date 06/16/2013 14:05:55 Table Runstats Information: Retrieval Time: 06/16/2013 14:05:55 TbspaceID: 2 TableID: 4 Schema: DEMO TableName: T1 Status: Completed Access: Allow write Sampling: No Sampling Rate: - Start Time: 06/16/2013 13:59:16 End Time: 06/16/2013 13:59:25 Total Duration: 00:00:09 Cur Count: 0 Max Count: 0 Index Runstats Information: Retrieval Time: 06/16/2013 14:05:55 TbspaceID: 2 TableID: 4 Schema: DEMO TableName: T1 Status: Completed Access: Allow write Start Time: 06/16/2013 13:59:25 End Time: 06/16/2013 14:05:25 Total Duration: 00:05:59 Prev Index Duration : 00:00:20 Prev Index Duration : 00:00:49 Prev Index Duration : 00:00:11 Cur Index Start: 06/16/2013 14:02:31 Cur Index: 5 Max Index: 5 Index ID: 5 Cur Count: 0 Max Count: 0
The first portion of the output Table Runstats Information shows information for table statistics collection. It took only nine seconds (13:59:16 to 13:59:25) to collect table statistics. The second portion of the output Index Runstats Information shows that statistics collection for index iid5 took 2 minutes 54 seconds (14:02:31 to 14:05:25). The Prev Index Duration area shows that iid4, iid3, iid2 took 20, 49, and 11 seconds, respectively. Both table and index sections show Completed status.
This table has five indexes. A db2pd -runstats output collected while RUNSTATS is executing can show the collection time for the first index. Listing 10 was collected when RUNSTATS was processing iid4.
Listing 10. In progress db2pd -runstats output
DB Partition 0 - Database DB97 - Active - Up 0 days 00:04:14 - Date 06/16/2013 14:02:25 Table Runstats Information: Retrieval Time: 06/16/2013 14:02:25 TbspaceID: 2 TableID: 4 Schema: DEMO TableName: T1 Status: Completed Access: Allow write Sampling: No Sampling Rate: - Start Time: 06/16/2013 13:59:16 End Time: 06/16/2013 13:59:25 Total Duration: 00:00:09 Cur Count: 0 Max Count: 0 Index Runstats Information: Retrieval Time: 06/16/2013 14:02:25 TbspaceID: 2 TableID: 4 Schema: DEMO TableName: T1 Status: In Progress Access: Allow write Start Time: 06/16/2013 13:59:25 End Time: 06/16/2013 14:02:11 Total Duration: 00:02:45 Prev Index Duration : 00:00:49 Prev Index Duration : 00:00:11 Prev Index Duration : 00:01:45 Cur Index Start: 06/16/2013 14:02:11 Cur Index: 4 Max Index: 5 Index ID: 4 Cur Count: 23150 Max Count: 47500
The status in the index section shows In progress. Prev Index Duration  indicates the collection time for the first index, iid1, as 1 minute 45 seconds.
Confirming a RUNSTATS is running long because of lack of prefetching
You can use the DB2 database monitor to confirm a RUNSTATS is running long because of lack of prefetching. Again, you will look at the example used earlier. Listing 11 shows application snapshot data for the RUNSTATS before and after REORG.
Listing 11. Application snapshot data
get snapshot for application AGENTID $runstatsAppId *** before REORG ... Snapshot timestamp = 06/16/2013 14:05:44.884178 Buffer pool index logical reads = 409356 Buffer pool index physical reads = 204435 Total buffer pool read time (milliseconds) = 685220 *** after REORG ... Snapshot timestamp = 06/17/2013 23:01:17.519335 Buffer pool index logical reads = 175391 Buffer pool index physical reads = 140 Total buffer pool read time (milliseconds) = 157
Before REORG, lack of prefetching is indicated by the high number of index physical reads. Accompanying this high number of physical reads is a high buffer pool read time which directly contributes to the long RUNSTATS execution time.
In contrast, the snapshot data after index reorganization has improved performance characteristics. There are fewer pages to process as indicated by the lower logical reads, and the low physical to logical ratio suggests index pages had been prefetched into the buffer pool by the time RUNSTATS accessed the pages. Both factors contribute to the 12x improvement in the demonstration.
Defragmenting indexes with REORG
In addition to RUNSTATS performance, fragmented indexes can affect general system performance. Since index scans can be used during query processing, defragmenting them might help to improve the execution time of queries as well as RUNSTATS.
The REORG INDEX command can be used to rebuild indexes and remove fragmentation. Listing 3 presented an example of the command.
It is important to consider that object reorganization can consume resources, such as temporary space.
Prefetching enhancements in DB2 10.1
DB2 10.1 contains a new type of prefetching called readahead (RA) prefetch. This feature improves performance of operations that access fragmented objects and reduces the need to perform REORG. If you repeat the demonstration in this article on DB2 10.1 with RA prefetching, you will observe the RUNSTATS times with and without REORG to be similar to each other, and to the post-REORG RUNSTATS time in DB2 9.7.
For further information, see the Resources section for a link to improved query performance through more efficient data and index prefetching in IBM DB2 Version 10.1 Information Center.
Fragmented indexes can have significant impact on RUNSTATS performance. This is especially true for large tables with many indexes and heavy insert/update activity. Regular REORG maintenance can avoid this performance impact. DB2 10.1, with readahead prefetching, effectively eliminates the problem.
- Learn more about RUNSTATS command from the Information Center.
- Explore the Information Center to learn more about REORG INDEXES/TABLE command .
- Explore the Information Center to learn more about Improved query performance through more efficient data and index prefetching.
- Explore the Information Center to learn more about db2pd - Monitor and troubleshoot DB2 database command.
- Explore the Information Center to learn more about LIST UTILITIES command.
- Explore the Information Center to learn more about GET SNAPSHOT command.
- Explore the Information Center to learn more about PD_GET_DIAG_HIST table function - Return records from a given facility.
- Visit the developerWorks Information Management zone to find more resources for DB2 developers and administrators.
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
- Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools as well as IT industry trends.
- Follow developerWorks on Twitter.
- Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
- Participate in the discussion forum.
- Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.