15.00 - Performance Issues - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Performance Issues

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 such downward adjustments are accompanied by negative system performance, so they are 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. The data inconsistencies scenario described in the second paragraph of this topic is a good candidate for using FLUSH QUERY LOGGING requests.

Related Topics

See the following for more information about query logging.

  • Database Administration
  • “BEGIN QUERY LOGGING” on page 157
  • “BEGIN QUERY LOGGING” in SQL Data Definition Language Syntax and Examples
  • “END QUERY LOGGING” on page 822
  • “END QUERY LOGGING” in SQL Data Definition Language Syntax and Examples
  • “FLUSH QUERY LOGGING” in SQL Data Definition Language Syntax and Examples
  • “REPLACE QUERY LOGGING” on page 847
  • “REPLACE QUERY LOGGING” in SQL Data Definition Language Syntax and Examples