About Object Use Counts
The purpose of collecting object use counts is to maximize the system management capabilities by providing an efficient way for DBAs to determine the usage frequencies of various database objects. Object use counts record information about how often database objects such as tables, views, macros, columns, indexes, and statistics are used at the system level. This includes the total access count for update, delete, and insert operations (collectively known as UDI counts) and the last access timestamp. This information is collectively referred to as object use data, or OUD.
Unlike the DBQL feature, object use count data measure system-level object use, while DBQL records similar object-related data at the request level.
- Database accesses
- Table accesses
- Column accesses
- Index accesses This includes all of the following index accesses:
- Primary
- Secondary
- Join
- Hash
- Delete, insert, and update accesses
- Types of usage associated with each database object access This includes all of the following accesses:
- Macros
- Views
- User-defined procedures
- Triggers
- User-defined functions
- User-defined methods
- User-defined types
- Statistics usage
The Optimizer uses object use counts in DBC.ObjectUsage to track the access usage of various database objects. Vantage tracks two versions of use counts: user and system. The two counts are identical, with the only differentiating characteristic being the ability for Vantage to reset user-level use counts by users and to reset system-level use counts.
- You can reset user object use counts using the DBC.ClearUserUseCount and DBC.ClearUserStatCount system macros, and you can reset these use counts at self-determined intervals. See
Teradata Vantage™ - Data Dictionary, B035-1092 for more information about these macros.
This type of information is useful within the context of system monitoring, analysis of system performance, and tuning of frequently used objects.
- Only Vantage can reset system object use counts. The reset interval depends on the optimizations for which these counts are used.
System object use counts are useful within the context of optimizing system database objects, such as statistics. The ability to accurately track the usage of statistics is critical for prioritizing the ability of the THRESHOLD option of the COLLECT STATISTICS statement to accurately recollect statistics.
About UDI Counts
UDI counts are an important level of object use counts tracked by Vantage. Effective interpretation of the THRESHOLD option for COLLECT STATISTICS requires a fine level of tracking granularity, so Vantage tracks the number of update, delete, and insert operations performed against database objects in DBC.ObjectUsage. These sums are referred to as UDI counts. Using the last reset timestamp, UDI counts provide an accurate measure of the changes in the cardinalities of tables.
This type of information is useful for internal database optimizations, for DBAs, and for application users. As is true for object use counts, Vantage tracks two versions of UDI counts: user and system. The two counts are identical, with the only differentiating characteristic being the ability to reset UDI counts by either users or by Vantage.
The collection of UDI counts is disabled by default, and you can only enable their collection by specifying the USECOUNT option for a database as part of a BEGIN QUERY LOGGING request.
For more information about the USECOUNT option, see “BEGIN QUERY LOGGING” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- Tables This does not include counts for the following table types:
- Data Dictionary
- Error
- Log
- Queue
- Join indexes
- Hash indexes
- DELETE
- INSERT
- MERGE (including merge, merge-delete, and merge-update operations)
- SELECT
- UPDATE (including the Upsert form of UPDATE)
- General
This category includes accesses by DML-invoked operations such as user-defined functions and user-defined procedures.
- Statistics
Only Vantage can reset system UDI counts.
Vantage uses system UDI counts to estimate table cardinalities, to extrapolate statistics, and to track thresholds for recollecting statistics.
Optimizer Use of UDI Counts
The Optimizer uses UDI counts to estimate table cardinalities and when it applies THRESHOLD options determined when statistics are initially collected.
To estimate table cardinalities, the Optimizer retrieves insert and delete counts from DBC.ObjectUsage and then applies them to cardinality estimation.
Whenever statistics are collected or recollected, the Optimizer draws on UDI counts to apply the THRESHOLD options specified for the original COLLECT STATISTICS request. Vantage first retrieves the delete and insert UDI counts from the master record for the table and the update UDI counts from the column and then combines them with the latest UDI counts from DBC.ObjectUsage to make a final cardinality estimate.
Accuracy of Object Use Counts
The accuracy of object use counts must be acute to ensure that the Optimizer can make effective decisions about query optimization and the recollection of statistics. There are two common scenarios in which use count inaccuracies can occur.
| Type of use count inaccuracy | Explanation |
|---|---|
| Underreporting | Use counts can be underreported when they are not collected because of system restarts. When a restart occurs, use count information in the memory-resident OUC cache is lost and not written to disk. As a result, the Optimizer can make poor decisions such as not recollecting statistics when current statistics are not recognized as being stale. To lessen the risk of underreporting use counts, the OUC cache also uses a persistent memory segment that lives across restarts, making use count information that might otherwise have been lost kept intact, and the collection of use count data can resume without interruption. |
| Overreporting | Use counts can be overreported when they are collected and then retained after a transaction abort. When an abort occurs, use count information in the memory-resident OUC cache does not get spoiled because the object use count collection operates independently of any query or transaction logic. This presents a risk of overreporting counts because information is committed to disk when it should not have been. The Optimizer can make unnecessary decisions such as recollecting statistics unnecessarily based on these overreported counts. |