17.10 - Performance Issues - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

The DBQL and TDWM dictionary tables are updated from the caches the database maintains for each of those tables. Without FLUSH QUERY LOGGING, Vantage flushes the caches either based on a timer that flushes them by default every 10 minutes, or when they become full.

This method of updating these logs can cause various data inconsistencies. For example, the DBQLObjTbl cache might get flushed before the DBQLogTbl cache, which can make it appear as if the rows in DBQLObjTbl are orphaned. Vantage eventually flushes the corresponding row in the DBQLogTbl cache, but the cache might not be flushed for another 10 minutes. You can modify the flush rate to smaller values by adjusting the DBS Control flag DBQLFlushRate, but flushing the the logging caches more frequently can negatively impact system performance, so this practice is not generally recommended.

Using FLUSH QUERY LOGGING requests to flush the logging caches manually has the overhead of adding additional task to the AMPs for updating their associated dictionary tables. You should always keep this extra overhead in mind and use manual query log cache flushing responsibly.

Note that frequent use of FLUSH QUERY LOGGING requests on a very busy system is not recommended. On a busy system with both DBQL and or TDWM enabled, the log tables are often updated as their respective caches fill, which can frequently occur before the default 10 minute timer expires. An optimal strategy is to use FLUSH QUERY LOGGING requests to flush those caches that Vantage does not flush frequently and where data inconsistencies occur.