16.20 - Performance Issues - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

The DBQL and TDWM dictionary tables are updated from the caches Teradata Database maintains for each of those tables. Without FLUSH QUERY LOGGING, Teradata Database 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. Teradata Database 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 Teradata Database does not flush frequently and where data inconsistencies occur.