AUS expiration policies

The Auto Update Statistics (AUS) maintenance system uses expiration policies as criteria for identifying user tables that have changed to the extent that their statistics need to be recalculated.

Internally, the AUS maintenance system automatically skips any tables or fragments that have current statistics and prioritizes tables or fragments that have more changes. Therefore, all tables are scheduled for updating statistics. For more information, see Automatic management of data distribution statistics.

The ph_threshold table of the sysadmin database stores the following configurable thresholds for defining AUS expiration policies.

Table 1. AUS expiration policy thresholds
Threshold Name Default Value Description
AUS_AGE 30 (days) A time-based expiration policy. Statistics or distributions are updated for a table after this amount of time regardless of how much data has changed.
AUS_AUTO_RULES 1 (enabled) If enabled, statistics are updated using the higher of the following default minimum guidelines or user-created distribution options:
  • All tables are updated in LOW mode.
  • All the leading index keys are updated in HIGH mode.
  • All non-leading index keys are updated in MEDIUM mode.
  • The minimum resolution for MEDIUM mode is 2.0.
  • The minimum confidence for MEDIUM mode is 0.95.
  • The minimum resolution for HIGH mode is 0.5.

If the UPDATE STATISTICS statement was run manually for a table, the UPDATE STATISTICS statements generated by the AUS maintenance system do not reduce the level, resolution, confidence, or sampling size options.

If disabled by being set to 0, the AUS maintenance system checks which columns have existing distributions and generates update statistics statements to refresh them.
AUS_CHANGE 10 (percent) A modification-based expiration policy. Statistics or distributions are updated for a table after this percentage of data is changed.
AUS_PDQ 10 (priority) The PDQ priority for UPDATE STATISTICS statements run by the AUS maintenance system. By default, all fragments for each table are analyzed in parallel. For more information about PDQ priority, see Update statistics in parallel on very large databases.
AUS_SMALL_TABLES 100 (rows) Statistics or distributions are updated every time for a table that has fewer than this number of rows.