Collecting Statistics on Data Dictionary Tables - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Collecting Statistics on Data Dictionary Tables

If you are using custom applications or business intelligence tools, you may find it useful to collect statistics on Data Dictionary tables because these applications and tools often query the Data Dictionary for validation or information checks in the background.

Components within the Teradata Database software use internal express requests that are optimized single-AMP queries. Statistics on Data Dictionary tables will not benefit these internal activities but will instead benefit SQL submitted directly by a user or an application.

Any potential benefit from collecting statistics will depend on the amount and complexity of the dictionary access activity. Using DBQL can help determine the benefit although it requires the DBC logon to collect against Data Dictionary tables. Before you decide whether to collect statistics on Data Dictionary tables, examine the DBQL output to determine if your applications use a lot of resources to access the Data Dictionary.

Note: Not all companies find value in collecting statistics on dictionary tables. The value depends on the frequency and complexity of the queries that access the dictionary tables, the amount of resource they use, and how long they run on your platform.

The following tables are good candidates for statistics collection:

  • DBC.AccessRights
  • DBC.DBase
  • DBC.Indexes
  • DBC.Owners
  • DBC.Profiles
  • DBC.Roles
  • DBC.RoleGrants
  • DBC.TVFields
  • DBC.UDFInfo
  • Only hashed dictionary tables allow statistics collections. Do not collect statistics on these non-hashed Data Dictionary tables:

  • DBC.Acctg
  • DBC.ChangedRowJournal
  • DBC.DatabaseSpace
  • DBC.LocalSessionStatusTable
  • DBC.LocalTransactionStatusTable
  • DBC.OrdSysChngTable
  • DBC.RecoveryLockTable
  • DBC.RecoveryPJTable
  • DBC.SavedTransactionStatus
  • DBC.SysRcvStatJournal
  • DBC.TransientJournal
  • DBC.UtilityLockJournalTable
  • For more information, see