17.00 - Transient Journal Purge Overhead Columns - Teradata Database

Teradata Vantageā„¢ - Resource Usage Macros and Tables

Advanced SQL Engine
Teradata Database
June 2020
User Guide
Column Name Mode Description Data Type
TJPurges count The number of purge passes in which a block-by-block scan is done. FLOAT
TJDbPurgeReads count The number of blocks actually mapped in during the purge scan. This is a reasonable approximate measure of the I/O load. The system uses full-cylinder read mode, but the block count would still be roughly proportionate to the I/O load. FLOAT
TJDbPurgeDeletes count

The number of blocks mapped in during the scan that were included in the ranges of blocks that were deleted.

Before Write Ahead Logging (WAL), the ratio of deletes to reads would have been a useful measure of the effectiveness of the purge processing. However, with WAL, the ratio cannot be interpreted quite so simply because:
  • The range of deleted blocks could include blocks that were not actually mapped in (and therefore not counted). Blocks that contain only WAL records are not mapped in during the scan, as they are automatically filtered out. Under typical conditions, there are probably relatively few such blocks. TJ and WAL records are typically generated in an interleaved sequence by regular SQL transactions. But during periods when the system workload is dominated by MultiLoad/FastLoad work, there will be relatively few TJ records written, so the proportion of WAL-only blocks would probably be significant.
  • Post-WAL, neither TJDbPurgeReads nor TJDbPurgeDeletes gets incremented during a normal purge pass. Instead of scanning the active data blocks, a pointer to the oldest active transaction is maintained which is a quicker method. Therefore, PurgeTJ() can simply compute the bounds of the range of records that can be deleted in the part of the WAL/TJ that precedes the start of the oldest transaction. This does not require any scanning and the system cannot definitely determine how many blocks actually get deleted.
  • If the oldest transaction remains open for a long time, the quick purge method is not effective. Therefore, the system reverts back to the full scan method.
The TJDbPurgeReads and TJDbPurgeDeletes are only incremented during a full scan.