15.10 - Statistics Cache - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

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 using the value of its TableId (referred to in Data Dictionary as ObjectId), which is the NUPI for DBC.StatsTbl. 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 a rowID‑based express request to DBC.StatsTbl in the Data Dictionary to fetch the histogram.

The following bullets explain the retrieval mechanisms for 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 system‑wide 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.

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

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