Dictionary Statistics for 16.10 - Recommendations

Blog
The best minds from Teradata, our partners, and customers blog about relevant topics and features.
Teradata Employee

 

This is an update to an earlier posting in which data dictionary statistics to collect in Teradata Database 15.10 were recommended. This posting makes recommendations for dictionary statistics from the 16.10 perspective. You may not require all these statistics, but they cover the tables and columns most likely to be accessed in the data dictionary views.

 

Collecting statistics on data dictionary tables is an option you have if your site supports queries that access multi-table dictionary views. Third party tools often access the data dictionary several times for meta-data purposes, primarily using X-views. Without statistics, the optimizer may not be doing an optimal job in building query plans for such queries.

 

New statistics included in this list come from these tables:

  • DBC.Maps
  • DBC.MapGrants
  • DBC.DatabaseSpace
  • DBC.GlobalDBSpace

 

COLLECT STATISTICS
 COLUMN TvmId
 , COLUMN UserId
 , COLUMN DatabaseId
 , COLUMN FieldId
 , COLUMN AccessRight
 , COLUMN GrantorID
 , COLUMN CreateUID
 , COLUMN (UserId ,DatabaseId)
 , COLUMN (TVMId ,DatabaseId)
 , COLUMN (TVMId ,UserId)
 , COLUMN (DatabaseId,AccessRight)
 , COLUMN (TVMId,AccessRight)
 , COLUMN (FieldId,AccessRight)
 , COLUMN (AccessRight,CreateUID)
 , COLUMN (AccessRight,GrantorID)
 , COLUMN (TVMId ,DatabaseId,UserId)
 , COLUMN (PARTITION)
ON DBC.AccessRights;

COLLECT STATISTICS
 COLUMN (ProfileName) 
 , COLUMN (ProxyUser)
 , COLUMN (TrustUserID)
 , COLUMN (TrustUserID,ProxyUser) 
 , COLUMN (ProxyUserID,GrantStatus) 
 ON DBC.ConnectRulesTbl ;

COLLECT STATISTICS
 COLUMN DatabaseId
 , COLUMN DatabaseName
 , COLUMN DatabaseNameI
 , COLUMN OwnerName
 ,  COLUMN LastAlterUID
 , COLUMN JournalId
 , COLUMN ZoneID
 , COLUMN (DatabaseName,LastAlterUID)
ON DBC.Dbase;

COLLECT STATISTICS
 COLUMN LogicalHostId
 , INDEX ( HostName )
ON DBC.Hosts;

COLLECT STATISTICS
 COLUMN OWNERID
 , COLUMN OWNEEID
 , COLUMN (OWNEEID ,OWNERID)
ON DBC.Owners;

COLLECT STATISTICS
 COLUMN ROLEID
 , COLUMN ROLENAMEI
ON DBC.Roles;

COLLECT STATISTICS
INDEX (GranteeId)
ON DBC.RoleGrants;

COLLECT STATISTICS
 COLUMN (ProfileId) 
 , COLUMN (ProfileName) 
 , COLUMN (ProfileNameI) 
ON DBC.Profiles ;

COLLECT STATISTICS 
COLUMN (TableId)
, COLUMN (FieldId)
, COLUMN (FieldName)
, COLUMN (FieldType)
, COLUMN (DatabaseId)
, COLUMN (CreateUID)
, COLUMN (LastAlterUID)
, COLUMN (UDTName)
, COLUMN (TableId, FieldName)
, COLUMN (TableId, FieldID)
ON DBC.TVFields;

COLLECT STATISTICS
 COLUMN TVMID
 , COLUMN TVMNAME
 , COLUMN TVMNameI
 , COLUMN DATABASEID
 , COLUMN TABLEKIND
 , COLUMN CREATEUID
 , COLUMN CreatorName
 , COLUMN LASTALTERUID
 , COLUMN CommitOpt
 , COLUMN (DatabaseId, TVMName)
 , COLUMN (DATABASEID ,TVMNAMEI)
ON DBC.TVM;

 
COLLECT STATISTICS
 INDEX (TableId) 
 , COLUMN (FieldId)
 , COLUMN (IndexNumber)
 , COLUMN (IndexType)
 , COLUMN (UniqueFlag)
 , COLUMN (CreateUID)
 , COLUMN (LastAlterUID)
 , COLUMN (TableId, DatabaseId)
 , COLUMN (TableId, FieldId)
 , COLUMN (UniqueFlag, FieldId)
 , COLUMN (UniqueFlag, CreateUID)
 , COLUMN (UniqueFlag, LastAlterUID)
 , COLUMN (TableId, IndexNumber, DatabaseId)
ON DBC.Indexes;

COLLECT STATISTICS
 COLUMN (IndexNumber)
 , COLUMN (StatsType)
ON DBC.StatsTbl;

COLLECT STATISTICS
 COLUMN (ObjectId)    
 , COLUMN (FieldId)
 , COLUMN (DatabaseID)
 , COLUMN (UsageType)
 , COLUMN (IndexNumber)
 , COLUMN (DatabaseId, ObjectId)
 , COLUMN (DatabaseId, ObjectId, IndexNumber)
ON DBC.ObjectUsage;

COLLECT STATISTICS
 INDEX (FunctionID )
 , COLUMN DatabaseId
 , COLUMN ( DatabaseId ,FunctionName )
ON DBC.UDFInfo;

COLLECT STATISTICS
 COLUMN (TypeName)
 , COLUMN (TypeKind)
ON DBC.UDTInfo;

COLLECT STATISTICS
 COLUMN (ConstraintType) 
 , COLUMN (DBaseId) 
 , COLUMN (TVMId) 
ON DBC.TableConstraints ;

COLLECT STATISTICS
 COLUMN (BaseTableId)
ON DBC.TempTables ;

COLLECT STATISTICS
 COLUMN (DatabaseId)
 , COLUMN (SpoolOnly)
 , INDEX (DatabaseId, TableId)
ON DBC.DatabaseSpace;

COLLECT STATISTICS
 COLUMN (DatabaseId)
 , COLUMN (SpoolOnly)
 , COLUMN (SoftLimitPercent)
ON DBC.GlobalDBSpace;

COLLECT STATISTICS
 COLUMN (MapNameI)
 , COLUMN (MapNo)
 , COLUMN (ParentSystemMapNo)
 , COLUMN (CreatorId)
ON DBC.Maps;

COLLECT STATISTICS
 COLUMN (GranteeId)
 , COLUMN (GrantorId)
 , COLUMN (MapNo)
ON DBC.Mapgrants;

 

 

3 Comments
Teradata Employee

Hi Carrie,

 

I had a question regarding using NO Threshold for current in DBC stats script. We are on TD 16.10 and someone suggested to use this option in our script. Kindly see the below SQL:

 

COLLECT STATISTICS
    USING NO THRESHOLD FOR CURRENT
                        
    COLUMN ( TableId,IndexNumber,DatabaseId ) ,
    COLUMN ( TableId,DatabaseId ) ,
    COLUMN ( TableId,FieldId ) ,
    COLUMN ( UniqueFlag,FieldId ) ,
    COLUMN ( UniqueFlag,CreateUID ) ,
    COLUMN ( UniqueFlag,LastAlterUID ) ,
    COLUMN ( TableId ) ,
    COLUMN ( FieldId ) ,
    COLUMN ( IndexNumber ) ,
    COLUMN ( IndexType ) ,
    COLUMN ( UniqueFlag ) ,
    COLUMN ( CreateUID ) ,
    COLUMN ( LastAlterUID )
    ON DBC.Indexes ;

 

Should we use it in our data dictionary script? Thanks a lot.

 

/Mubasher

Teradata Employee

Also, please suggest if we should also collect summary statistics on daily DBQL tables.

COLLECT SUMMARY STATISTICS
    ON DBC.DBQLXMLTbl ;
 
COLLECT SUMMARY STATISTICS
    ON DBC.DBQLogTbl ;
 
COLLECT SUMMARY STATISTICS
    ON DBC.DBQLObjTbl ;
 
COLLECT SUMMARY STATISTICS
    ON DBC.DBQLSqlTbl ;

COLLECT SUMMARY STATISTICS
    ON DBC.DBQLExplainTbl ;
 
COLLECT SUMMARY STATISTICS
    ON DBC.DBQLStepTbl ;

 

 

BR,

/Mubasher

Teradata Employee

On the NO THRESHOLD question:

 

Data Dictionary tables are small and statistics on DD tables are very important. SYSTEM THRESHOLD uses “THRESHOLD 7 DAYS” internally for DD tables, so the stats will not get stale. If you wish to be more conservative by using NO THRESHOLD FOR CURRENT so you can recollect more often that the 7-day threshold, that option is fine to use. Usually DD stats are quick to collect and do not require much resource.

 

On the DBQL stats question:

 

Whether or not there is a value in collecting stats on DBQL tables depends on whether or not your or end users are accessing those tables. Most often, sites offload the DBC tables regularly onto PDCR tables and access those history tables. So stats on PDCR tables probably make more sense, especially if you only have one day of DBQL data kept in the DBC.

 

However, it will not hurt anything if you want to make those collections. It just adds some complexity into your stats strategy and may not be providing mcuh value.

 

Thanks, -Carrie