Usage Recommendations - Teradata Database

Usage Recommendations

When collecting object use counts, remember the following:

Do not enable collection when a Data Dictionary ARCHIVE or RESTORE is in progress.

Note: If the Data Dictionary is being archived and the archive gets blocked by the ObjectUseCount collection, the system will turn off the ObjectUseCount collection. You must enable it again after the archive is complete.

The recommended value of ObjectUseCountCollectRate is 10 minutes or more. Setting ObjectUseCountCollectRate to less than 10 minutes impacts system performance.

Copying the Use Counts of Table

CREATE TABLE AS...WITH DATA AND STATISTICS copies the statistics and data of an original table to a target table. If the USECOUNT option is enabled on the target database, the AND STATISTICS option also copies the use count information. For more information, see “Copying Statistics From a Base to a Target Table” on page 79 or SQL Data Definition Language Detailed Topics, CREATE TABLE (AS Clause).

Renaming Objects Has No Effect on Use Counts

Renaming an object does not reset its use count. That is, neither the access count nor the last access timestamp changes when you rename an object. For example, the following statement:

RENAME TABLE Employee TO Emp;

does not reset the use count for the table “Emp” to 0. The count remains what it was for “Employee” with the timestamp it was last used. When “Emp” is next used, the system continues the tally and updates the timestamp. Similarly, for columns of a table, if you rename a column using the following request:

ALTER TABLE t1 RENAME oldcolumnname TO newcolumnname;

this does not reset the use count information for the column.

Because using the RENAME statement does not affect use count information, if you need to reset the count, manually reset it using the ClearTVMUseCount macro.

Example : Object Use Counts for Tables

Assume two databases, db1 and db2, each contain some tables and other objects. Assume also that count collection is enabled.

Now specify some SQL requests to access the tables in the two databases.

sel *from db1.tab1;

sel *from db1.tab1 where col1=7;

sel *from db1.tab1 where col2=7;

sel *from db1.tab2;

sel *from db2.tab3;

sel *from db2.tab3 where col1=7;

sel *from db2.tab4;

Then use the Data Dictionary view TablesVX to find the use counts for the tables.

sel DatabaseName, TableName, AccessCount, LastAccessTimeStamp

from DBC.TablesVX where TableName IN ('tab1', 'tab2', 'tab3', 'tab4');

The output is:

 *** Query completed. 4 rows found. 4 columns returned.

 *** Total elapsed time was 1 second.

DatabaseName  db1

          TableName  tab2

        AccessCount  1

LastAccessTimeStamp  2006-12-17 14:51:53

       DatabaseName  db2

          TableName  tab4

        AccessCount  1

       DatabaseName  db1

LastAccessTimeStamp  2006-12-17 14:51:53

          TableName  tab1

        AccessCount  3

LastAccessTimeStamp  2006-12-17 14:51:53

       DatabaseName  db2

          TableName  tab3

        AccessCount  2

LastAccessTimeStamp  2006-12-17 14:51:53

=============================================================

Note: If any of the named objects have not been accessed since the last use count reset, the value in the AccessCount column for that object appears as zero, and the LastAccessTimeStamp will appear as a question mark.

You can use account string expansion variables to analyze resource usage trends. See “Logging Resource Usage Data with Account String Variables” on page 160.