16.10 - Object Use and UDI Counts - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

About Object Use Counts

The purpose of collecting object use counts is to maximize the system management capabilities of Teradata Database 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.

Object use counts track the following types of information:

  • 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 to track the access usage of various database objects in Teradata Database. Teradata Database tracks two versions of use counts: user and system. The two counts are identical, with the only differentiating characteristic being the ability to reset user-level use counts by users and to reset system-level use counts only by Teradata Database.

Object access use count data is obtained from DBQL. DBQL inserts a row into DBC.ObjectUsage for each object used by a query.

The following list defines the similarities and differences between user object use counts and system object 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 Data Dictionary 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 Teradata Database 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 Teradata Database. Effective interpretation of the THRESHOLD option for COLLECT STATISTICS requires a fine level of tracking granularity, so Teradata Database tracks the number of update, delete, and insert operations performed against database objects. 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, Teradata Database 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 Teradata Database.

Unlike access use counts data, UDI counts are not obtained from DBQL. 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 SQL Data Definition Language.

Teradata Database logs UDI counts for the following database objects:

  • Tables

    This does not include counts for the following table types:

    • Data Dictionary
    • Error
    • Log
    • Queue
  • Join indexes
  • Hash indexes

UDI counts are logged for the following SQL DML statements and accesses:

  • 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 Teradata Database can reset system UDI counts.

Teradata Database 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 using the following equation.

where:

Equation element Specifies
cardinality cardinality estimate determined from UDI count data.
residual_statistics_cardinality histogram cardinality from the most recent collection of statistics.
insert_UDI_count number of insert UDI operations recorded in DBC.ObjectUsage.
delete_UDI_count number of delete UDI operations recorded in DBC.ObjectUsage.

Whenever statistics are collected or recollected, the Optimizer draws on UDI counts to apply the THRESHOLD options specified for the original COLLECT STATISTICS request. Teradata Database 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 2 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.

Incrementing UDI Counts in DBC.ObjectUsage

This topic indicates how Teradata Database updates UDI counts in DBC.ObjectUsage. The example data is based on various DML request accesses to the following table:

     CREATE TABLE db1.t1, NO FALLBACK, NO BEFORE JOURNAL,
                          NO AFTER JOURNAL, CHECKSUM=DEFAULT (
       c1 INTEGER,
       c2 INTEGER)
     PRIMARY INDEX (c1)
     UNIQUE INDEX (c2);

Assume that DBC.ObjectUsage contains no rows before you submit Request 1.

Request 1

     SELECT 100
     FROM db1.t1;
  • Database db1 and table t1 are accessed for the first time.
  • Teradata Database creates the entries in rows 1 and 2 of DBC.ObjectUsage to record this activity.

Request 2

     SELECT c1
     FROM db1.t1;

The entries in DBC.ObjectUsage are updated or created as follows:

  • Teradata Database accesses database db1, table t1, and column c1.
  • The access counts for rows 1 and 2 are incremented by 1.
  • Teradata Database creates Row 3 to record the first time access to column c1.

Request 3

   SELECT 100
   FROM db1.t1
   WHERE c2 = 10;
  • Teradata Database accesses database db1, table t1, column c2, and the USI on column c2.
  • Entries in DBC.ObjectUsage are updated or created as follows:
    • Teradata Database increments the access counts for rows 1 and 2 by 1.
    • Row 4 is created to record the first time access on column c2.
    • Row 5 is created to record the first time USI index access on column c2.
    • Row 6 is created to record the statistics usage, indicated by STA, on column c2.

The following table shows how Teradata Database updates the UDI counts for selected columns of DBC.ObjectUsage for each of these requests.

Column Row 1 Row 2 Row 3 Row 4 Row 5 Row 6
DatabaseId db1 id db1 id db1 id db1 id db1 id db1 id
ObjectId null t1 id t1 id t1 id t1 id t1 id
FieldId null null c1 id c2 id null c2 stats id
IndexNumber null null null null c2 index num null
UsageType DML DML DML DML DML STA
UserAccessCnt 1+1+1 1+1+1 1 1 1 1
SysAccessCnt 1+1+1 1+1+1 1 1 1 1
UserUpdateCnt 0 0 0 0 0 0
UserDeleteCnt 0 0 0 0 0 0
UserInsertCnt 0 0 0 0 0 0
SysUpdateCnt 0 0 0 0 0 0
SysDeleteCnt 0 0 0 0 0 0
SysInsertCnt 0 0 0 0 0 0
LastAccessTimeStamp request 3 timestamp request 3 timestamp request 2 timestamp request 3 timestamp request 2 timestamp request 3 timestamp
LastUsrAccessCntReseTimeStamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp
LastSysAccessCntResetTimeStamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp
LastUsrUDIResetTimeStamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp
LastSysUDIResetTimeStamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp last reset timestamp

The following UDI operations update the usage counts in DBC.ObjectUsage for the existing rows 1, 2, and 3, which were created by Request 1 and Request 2. You can see the updated contents of DBC.ObjectUsage in the table that follows the requests.

Request 4

     INSERT INTO db1.t1
     VALUES (…);
  • Teradata Database accesses database db1 and table t1 and inserts rows into db1.t1.
  • The entries in DBC.ObjectUsage are updated as follows.
    • The access counts for rows 1 and 2 are incremented by 1.
    • The insert access count for row 2 is incremented by 1.

Request 5

     DELETE db1.t1
     WHERE c1 > 10;
  • Teradata Database accesses database db1, table t1, and column c1 and deletes a set of rows from db1.t1.
  • The entries in DBC.ObjectUsage are updated as follows:
    • The access counts for rows 1 and 2 are incremented by 1.
    • The delete access count for row 2 is incremented by 5, which is the number of rows deleted.

Request 6

     UPDATE db1.t1
     SET c1 = 100;
  • Teradata Database accesses database db1, table t1, and column c1 and updates a set of rows in db1.t1.
  • The entries in DBC.ObjectUsage are updated as follows:
    • The access counts for rows 1, 2, and 3 are incremented by 1.
    • The update access count for row 3 is incremented by 10, which is the number of rows updated.

After requests 4, 5, and 6 complete, the contents of DBC.ObjectUsage look like the following table, where previous totals are grouped within parentheses so that (3) + 1 + 1 + 1 indicates a User Access Count of 6, for example.

Column Row 1 Row 2 Row 3
DatabaseId db1 id db1 id db1 id
ObjectId null t1 id t1 id
FieldId null null c1 id
IndexNumber null null null
UsageType DML DML DML
UserAccessCnt (3) + 1+1+1 (3) + 1+1+1 (1) + 1
SysAccessCnt (3) + 1+1+1 (3) + 1+1+1 (1) + 1
UserUpdateCnt 0 0 10
UserDeleteCnt 0 5 0
UserInsertCnt 0 1 0
SysUpdateCnt 0 0 10
SysDeleteCnt 0 5 0
SysInsertCnt 0 1 0
LastAccessTimeStamp request 6 timestamp request 6 timestamp request 6 timestamp
LastUsrAccessCntResetTimeStamp last reset timestamp last reset timestamp last reset timestamp
LastSysAccessCntResetTimeStamp last reset timestamp last reset timestamp last reset timestamp
LastUsrUDIResetTimeStamp last reset timestamp last reset timestamp last reset timestamp
LastSysUDIResetTimeStamp last reset timestamp last reset timestamp last reset timestamp