AUTO_STAT_MODE session environment option

Use the AUTO_STAT_MODE environment option to enable or disable an automatic mode for UPDATE STATISTICS operations on column distribution statistics during the current session. In automatic mode, the user can define a minimum data change threshold as a property of the table. The database server refreshes statistics on the table, its indexes, and on table and index fragments selectively, only if the data has changed in a percentage of rows beyond that threshold since the distribution statistics were last calculated.

The AUTO_STAT_MODE session environment option has this syntax:

AUTO_STAT_MODE environment option

Usage

Distribution statistics are used by the query optimizer to identify efficient execution plans for DML operations. Because calculating statistics for a large table is a resource-intensive operation, however, recalculating distributions that have not substantially changed from their current values in the system catalog degrades the performance of the database server, compared to a more efficient allocation of system resources.

The value that the SET ENVIRONMENT AUTO_STAT_MODE statement specifies can enable or disable the automatic identification and recalculation of stale column distribution statistics:

ON: Automatic UPDATE STATISTICS mode is enabled, and only stale statistics are automatically recalculated.
OFF: Automatic mode is disabled and UPDATE STATISTICS operations recalculate both stale and current statistics.

Automatic mode has no effect on routine statistics, or on UPDATE STATISTICS statements that include the FORCE keyword.

When automatic UPDATE STATISTICS mode is enabled, the UPDATE STATISTICS statement selectively refreshes only the table, column, and index data distribution statistics that it identifies as stale or missing. The user can specify the minimum change threshold as a table attribute when the table is created or altered. The value of this attribute overrides the explicit or default setting of the STATCHANGE configuration parameter.

The SET ENVIRONMENT STATCHANGE statement similarly overrides the STATCHANGE configuration parameter setting for the current session. If no STATCHANGE threshold is explicitly set, the system default threshold (of at least 10 percent of the rows changed since statistics were last calculated) defines stale data distributions when the automatic UPDATE STATISTICS mode is enabled.

When automatic mode is disabled, the database server does not consider any user-defined or default threshold for stale statistics when the UPDATE STATISTICS statement recalculates distribution statistic. In nonautomatic mode (or when you include the FORCE keyword in the UPDATE STATISTICS statement), the database server drops and recalculates the statistics for all of the specified tables and indexes, without reference to any previously calculated data distributions.

The automatic mode for UPDATE STATISTICS requires all of the fragments of a table to maintain the distribution of a column at the same resolution. This implies that consecutive UPDATE STATISTICS operations with a resolution different from what was used for creating the current column distribution in the system catalog forces a refresh of all column distributions for all fragments. If no resolution is specified, the database server uses the one that is stored with the distribution, rather than the default resolution of 2.5.

Only permanent tables are affected by automatic mode. The AUTO_STAT_MODE setting has no effect on temporary tables.

The AUTO_STAT_MODE and STATCHANGE configuration parameters

The AUTO_STAT_MODE configuration parameter can specify a '1' or '0' global value for the automatic mode for UPDATE STATISTICS operations for all sessions of the database server, respectively encoding the enabled or disabled modes that this session environment option encodes as ON and OFF. You can use the SET ENVIRONMENT AUTO_STAT_MODE statement of SQL, however, to override the AUTO_STAT_MODE configuration parameter setting for the current session.

The STATCHANGE configuration parameter can specify a positive integer as a global percentage of the change threshold to define stale data distributions. When the automatic mode for UPDATE STATISTICS is enabled by the AUTO_STAT_MODE configuration parameter, this setting takes effect as the default change threshold for any table whose STATCHANGE table attribute is specified as AUTO, or that is AUTO by default. You can use the SET ENVIRONMENT STATCHANGE statement of SQL, however, to override the STATCHANGE configuration parameter setting for the current session.

Examples of SET ENVIRONMENT AUTO_STAT_MODE

The following statement enables automatic mode for the current session:

SET ENVIRONMENT AUTO_STAT_MODE ON;

This overrides the setting of the AUTO_STAT_MODE configuration parameter, if it is 0, for the remainder of the current session, or until you reset the AUTO_STAT_MODE session environment variable.

If you are satisfied the behavior of UPDATE STATISTICS operations on distribution statistics without automatic mode, you can disable automatic mode for the session, as in this example:

SET ENVIRONMENT AUTO_STAT_MODE OFF;

Statement-level granularity for setting automatic mode

For individual UPDATE STATISTICS FOR TABLE statements, appending the AUTO keyword can override the disabled AUTO_STAT_MODE status in contexts where automatic mode is appropriate, as in this example:

SET ENVIRONMENT AUTO_STAT_MODE OFF;
UPDATE STATISTICS MEDIUM FOR SPECIFIC TABLE orders AUTO;

Here the distribution statistics for the orders table will be refreshed (or for its fragments, if it has distributed storage with fragment-level STATCHANGE behavior) only for system catalog statistics of the orders table that qualify as stale. The scope of the AUTO keyword is restricted to the statement that includes it, rather than persisting. In contrast, the AUTO_STAT_MODE session environment variable remains disabled until the mode is reset or until the session ends.

Conversely, when automatic mode is enabled, you can temporarily disable it by including the FORCE keyword in individual UPDATE STATISTICS statements, so that the STATCHANGE status is disregarded and all table or fragment distribution statistics are refreshed while the statement is running, as in the next example:

SET ENVIRONMENT AUTO_STAT_MODE ON;
UPDATE STATISTICS MEDIUM FOR TABLE FORCE;

Because no table is specified, the scope of UPDATE STATISTICS in this example is every table in the database, but automatic mode remains in effect for any subsequent UPDATE STATISTICS operations that omit the FORCE keyword in the same session.

For more information about the AUTO keyword of UPDATE STATISTICS, and about its logical inverse, the FORCE keyword, see Using the FORCE and AUTO keywords.

For more information about the AUTO_STAT_MODE and STATCHANGE configuration parameters, see your HCL OneDB™ Administrator's Reference.

For more information about the STATCHANGE table attribute, see the topics Statistics options of the ALTER TABLE statement, Statistics options of the CREATE TABLE statement, and Performance considerations of UPDATE STATISTICS statements.