How to Use the SYSSTAT Tables to Perform What-if Modeling and Analysis

Because DB2 maintains the SYSIBM and SYSCAT system catalog tables and views, they are read-only. However, you can update the SYSSTAT views. This is a special set of views that you can use to update database statistics. The information contained in these views affects how the DB2 optimizer chooses access plans when executing a query.

For planning purposes, you can change the statistical information in the SYSSTAT tables so that they do not reflect the actual state of tables and indexes. This lets you:

• Model query performance on a development system using production system statistics.

• Perform what-if query performance analysis by examining various possible changes to the query access plan.

You must have explicit DBADM authority for the database to modify statistics for tables and indexes and their components. That is, your user ID must have DBADM authority in the SYSCAT.DBAUTH table.

Table D.2 provides information about the system catalog tables that contain catalog statistics and the RUNSTATS options that collect specific statistics.

^[1] If the table does not have any indexes defined and you request statistics for indexes, no new CARD statistics are updated. The previous CARD statistics are retained.

The Yes or No in the RUNSTATS Option column indicates whether you need to execute the RUNSTATS command on the table, the indexes, or both to collect the statistics specified in the Statistic column. For example, if you want to collect the statistics on FPAGES, you must execute the RUNSTATS command on both the table and indexes. On the other hand, if you want to collect the statistics for OVERFLOW, you need to execute the RUNSTATS command on the table only.

Let's walk you through a what-if scenario: If the EMPLOYEE table had a lot more rows that it has now, which access plan would the DB2 optimizer choose?

First, you collect the statistics on the EMPLOYEE table using the RUNSTATS command:

					RUNSTATS ON TABLE SYLVIAQ.EMPLOYEE
				

After it is completed, obtain the statistics by querying the SYSSTAT.TABLES view:

					SELECT * FROM SYSSTAT.TABLES
					WHERE TABSCHEMA = 'SYLVIAQ' AND TABNAME = 'EMPLOYEE'
				

Figure D.8 shows the output.

In Figure D.8, the CARD column indicates that the EMPLOYEE table currently has 32 rows. To update the statistics for the EMPLOYEE table to reflect a bigger table, issue:

					UPDATE SYSSTAT.TABLES
					SET CARD   = 10000,
					NPAGES = 1000,
					FPAGES = 1000,
					OVERFLOW = 2
					WHERE TABSCHEMA = 'SYLVIAQ' AND TABNAME = 'EMPLOYEE'
				

After this is completed, you can run your query against the EMPLOYEE table and get the access plan in text-based format using the db2exfmt command, or in graphic format using the Visual Explain GUI tool (see Chapter 4, Using the DB2 Tools).

You must be careful when manually updating catalog statistics: arbitrary changes can seriously affect the performance of subsequent queries. You can use any of the following methods to revert your changes back.

• ROLLBACK the unit of work in which the changes have been made (assuming the unit of work has not been committed).

• Use the RUNSTATS utility to recalculate and refresh the catalog statistics.

• Update the catalog statistics to indicate that statistics have not been gathered. (For example, setting column NPAGES to –1 indicates that the number-of-pages statistic has not been collected.)

• Replace the catalog statistics with the data they contained before you made any changes. This method is possible only if you used the db2look command to capture the statistics before you made any changes.

In some cases, the optimizer may determine that some particular statistical value or combination of values is not valid, and it will use the default values and issue a warning. Such circumstances are rare, however, since most of the validation is done when updating the statistics.

Tables D.3 through D.8 briefly describe the rest of the updatable SYSSTAT views.

^[1] Column statistics are gathered for the first column in the index key.

^[2] These statistics provide information about data in columns that contain a series of subfields or subelements that are delimited by blanks. The SUB_COUNT and SUB_DELIM_LENGTH statistics are collected only for single-byte character set string columns of type CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC.

The multicolumn distribution statistics listed in Tables D.5 and D.6 are not collected by RUNSTATS. You can update them manually, however.

^[2] CLUSTERFACTOR and PAGE_FETCH_PAIRS are not collected with the DETAILED clause unless the table is of a respectable size. If the table is greater than about 25 pages, then CLUSTERFACTOR and PAGE_FETCH_ PAIRS statistics are collected. In this case, CLUSTERRATIO is –1 (not collected). If the table is a relatively small table, only CLUSTERRATIO is filled in by RUNSTATS while CLUSTERFACTOR and PAGE_FETCH_PAIRS are not. If the DETAILED clause is not specified, only the CLUSTERRATIO statistic is collected.

^[1] Detailed index statistics are gathered by specifying the DETAILED clause on the RUNSTATS command.

^[3] This statistic measures the percentage of pages in the file containing the index that belongs to that table. For a table having only one defined index, DENSITY should normally be 100. DENSITY is used by the optimizer to estimate how many irrelevant pages from other indexes might be read, on average, if the index pages were prefetched.

^[4] These statistics cannot be computed when this table is in a DMS table space.

Column distribution statistics (listed in Table D.8) are gathered by specifying the WITH DISTRIBUTION clause on the RUNSTATS command. Note that distribution statistics cannot be gathered unless there is a sufficient lack of uniformity in the column values.

^[1] DISTCOUNT is collected only for columns that are the first key column in an index.