Optimizing RUNSTATS in DB2 for Linux, UNIX, and Windows: Troubleshooting index fragmentation

Accurate database statistics allow the DB2® query optimizer to determine efficient access plans. Whether you manage statistics collection yourself or use DB2's automatic statistics collection facility, a slow RUNSTATS can be a problem. In this article, you will learn how to assess if index fragmentation is the root cause of a slow RUNSTATS.

Kwai Wong (kwaiw@hotmail.com), Software Developer, IBM

Author PhotoKwai Wong is a software developer with over 20 years of experience working on the DB2 development team at the IBM Canada Lab. Her development work has focused on the runtime, query optimizer, and database statistics components of DB2. Kwai also has expertise in performance analysis, tuning, and troubleshooting.



11 July 2013

Also available in Chinese Russian

Introduction

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-13.59.16.101750-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 [1]: 00:00:20
Prev Index Duration [2]: 00:00:49
Prev Index Duration [3]: 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 [1]: 00:00:49
Prev Index Duration [2]: 00:00:11
Prev Index Duration [3]: 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 [3] 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.

Conclusion

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.

Resources

Learn

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.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=936832
ArticleTitle=Optimizing RUNSTATS in DB2 for Linux, UNIX, and Windows: Troubleshooting index fragmentation
publish-date=07112013