- DBC.AccessRights table
When a user or database is created, there is a default number of privileges added to DBC.AccessRights. These entries should be reviewed and obsolete or redundant entries deleted as necessary. Otherwise, this table can grow very large and can greatly impact space limits negatively. This table may also potentially become skewed.
In addition, when an object is dropped, the system may do a full table scan against DBC.AccessRights. If DBC.AccessRights is large, this can adversely affect the performance of the drop operation. The smaller this table can be made, the better.
To reduce the size of the DBC.AccessRights table:
- Determine which users have the most entries. The following query checks for the userid/database ID that has the most rows that hash to the same value:
.set retlimit 10 .set ret cancel LOCKING ROW FOR ACCESS SELECT COUNT(*) (TITLE '# of//Entries', FORMAT 'ZZZZZZ9'), a.userid|| a.databaseid (TITLE 'UserID|DatabaseID'), dbase.databasenamei (TITLE 'UserName') FROM accessrights a , dbase WHERE a.userid = dbase.databaseid GROUP BY 2,3 ORDER BY 1 DESC,2 ;
- REVOKE rights at the table level and GRANT rights at the database level to reduce the number of entries.
- Convert individual privileges to role privileges where possible.
- Determine which users have the most entries. The following query checks for the userid/database ID that has the most rows that hash to the same value:
- DBC.TSETQueryText table
If you receive a 3610 error, the query text may be saved to the DBC.TSETQueryText table for use by Teradata Support. After the problem is resolved, you may need to manually delete the rows where the “Handled” column is set to 1 or has a timestamp that indicates the information is no longer needed.
- Archive and recovery tables
When associated removable media is expired and over-written, clean out the following:
Table Purpose DBC.RCConfiguration archive or restore configuration DBC.RCMedia VolSerial for archive or restore DBC.RCEvent archive or restore events - Accumulated values in Data Dictionary tables
Resetting values can help you do better resource usage analysis, and keep cleaner historical records. The following fields should be reset when necessary:
Fields Purpose PeakPermSpace, PeakSpoolSpace, and PeakTempSpace in the DBC.DataBaseSpace table. Contains database and table space accounting information. To reset the values, execute the macro: EXEC DBC.ClearPeakDisk;
Due to the importance of tracking space usage in your system, you should try to run this macro regularly. Or, you can submit an UPDATE statement yourself.
CPUTime and DiskIO of the DBC.AMPUsage view (which corresponds to the DBC.Acctg table). Holds resource usage data by Acct/User. To reset the values, submit the following: UPDATE DBC.AMPUsageV SET CPUTime = 0, DiskIO = 0 ALL;
AccessCount and LastAccessTimeStamp of the following views: - DBC.ColumnsV
- DBC.DatabasesV
- DBC.IndicesV
- DBC.TablesV
- DBC.UsersV
Tracks the number of times and the last time a particular object was accessed. To reset these fields, use one of these macros:
- ClearAllDatabaseUseCount
- ClearDatabaseUseCount
- ClearTVMUseCount
You should occasionally clean out rows or reset the fields in the following tables as needed: