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.
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.