16.10 - Statistics Cache - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
June 2017
Programming Reference
User Guide

The Statistics Cache

The statistics cache minimizes Parser overhead required to optimize queries. It does this by caching statistics, UDI counts, and other demographic information from the Data Dictionary that has been fetched during a session to optimize SQL queries.

When the Optimizer requires statistics to process a table, it first retrieves the summary information for all the statistics on the table. Teradata Database stores the retrieved demographic data in the statistics cache, where it is expected to remain cached for an extended period. The purpose of the statistics cache is to retain statistics that have been fetched to optimize queries in memory for as long as possible.

The statistics cache is a buffer in Parser engine memory that stores the most recently used summary statistics and demographic information for a table, minimizing the number of express requests that the Optimizer must issue to the Data Dictionary to retrieve statistics.

If the information for a required column set is not cached at the time a request is being optimized, the Optimizer sends express requests to the Data Dictionary to fetch this information:

  • To retrieve summary statistics only (excluding histograms) from DBC.StatsTbl, the Optimizer uses the NUPI for the table, which is its TableId value.
  • To retrieve detailed statistics (including the interval histogram) from DBC.StatsTbl, the Optimizer uses the ROWID from the summary statistics row to retrieve the interval histogram.

The statistics cache is global to each parsing engine and is shared by all sessions of a PE.

Teradata Database allocates memory for the statistics cache at system start-up.

You can use the DBS Control field NumStatisticsCacheSegs to change the size of the statistics cache. The default size of the cache is 4 segments of 1024 KB each, which makes the default size of the cache 4 MB for each PE.

See Utilities for further information about fine tuning the statistics cache.

Unlike the dictionary cache, Teradata Database does not purge the statistics cache every four hours, purging it only when operations to collect or drop statistics occur.