Update Statistics with SAPDBA (SAP Library - SAP Database Guide: Informix (BC-DB-INF-DBA))

Update Statistics with SAPDBA

Use

You can use SAPDBA for Informix to analyze and update the statistics held on database tables. This enables the query optimizer to choose the best query plan, so reducing the time taken by your SAP applications to access data. SAPDBA's integrated functionality offers you a much simpler and more secure approach than entering SQL commands by hand.

The query optimizer for the Informix database needs current, up-to-date statistics about the number of data rows, size of indexes and the distribution of data in individual fields of a table. Using this information, it can work out the optimal access path for a query. If the statistics held by the optimizer are out of date or not present at all, this can lead to, for example, poor access paths use of the wrong index or inappropriate use of a full table sequential scan when a suitable index exists. This leads to poor performance.

Integration

Update statistics is fully integrated in SAPDBA. You can use this functionality for databases running on UNIX and NT operating system platforms.

Prerequisites

You need to know how to use SAPDBA and to have set it up correctly. Refer to Getting Started with SAPDBA.

Features

SAPDBA executes the commands required to analyze the statistics on database tables, so identifying when an update is necessary.

SAPDBA executes the commands required to update statistics on different sets of tables.

SAPDBA can be customized for optimal processing of specified larger tables.

SAPDBA has a command-line option for extra flexibility.

SAPDBA enforces expert mode for updating statistics on more than one table. This reduces the risk of unintended changes to the database by inexperienced personnel.

Activities

You identify when update statistics is required using the normal SAPDBA menus.

See Listing Tables with Deviation with SAPDBA and Listing Tables with Non-Updated Indexes with SAPDBA.

You perform update statistics using the normal SAPDBA menus.

See Updating Statistics for One Table, Updating Statistics for All Tables, and Updating Statistics for All System Tables.

Caution

To avoid poor performance of your database, we recommend that you run update statistics frequently, at least once a week. You can schedule this using the DBA Planning Calendar in the Computing Center Management System (CCMS) of the SAP system.

If you want to, you can perform update statistics from the operating system command line.

See Updating Statistics from the Command Line with SAPDBA. Update statistics executes at different levels:

High

The exact distribution of all data in the table column is determined.

Medium

A sample of data for the table column is taken and the distribution of this sample is taken as representative of the overall data distribution. This is usually adequate for small tables but not for larger ones. With larger tables a significant discrepancy can occur since the maximum allowable sample size might be insufficient.

This is only relevant at the "table level" rather than for individual columns. Data is collected on the table as a whole, such as the number of rows and size of indexes.

SAPDBA runs update statistics at different levels for different columns of each table it processes, since the distribution of data in index columns (especially heading index columns) is more important than that of non-index columns.

SAPDBA can also perform update statistics in parallel when you execute it from the command line.

For more information about the defaults used in SAPDBA, see How SAPDBA Performs Update Statistics.

If you want to, you can override SAPDBA settings for particular tables (this makes sense especially for large tables). See Customizing Update Statistics with SAPDBA.

See also:

Informix documentation