Collecting Statistics on Data Dictionary Tables - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
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.