16.10 - About Collecting Statistics on Data Dictionary Tables - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

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.

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.TVM
  • 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 https://developer.teradata.com/blog/carrie/2010/05/if-you-re-not-collecting-statistics-on-your-dictionary-tables-do-it-now.