About Collecting Statistics on Data Dictionary Tables - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantageâ„¢

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