January 8

0 comments

Teradata Query Logging

By DWH Pro Admin

January 8, 2020


What is Query Logging?

Query Logging is a feature that allows you to analyze workload and resource usage and perform performance tuning based on this analysis.

How Is Query Logging Implemented?

The Query Logging Framework consists of a set of DBC tables (called DBQL tables) that store the performance indicators of the requests. Which key figures are saved and how detailed this is done can be set.

Does the information in the DBQL tables represent 100% of the workload of a Teradata system?

No, because as mentioned above you can define which information should be stored in which detail. For this purpose, so-called filters are used. Detailed information can also be aggregated and is then only available in aggregated form.
It should also be noted that for performance reasons, the collected information is not available in real-time in the DQBL tables, but is cached in memory and written at intervals (e.g. every 10 minutes). In the case of a system restart, all information that has not yet been written from the cache to the DBQL tables will be lost.

The DBSCOntrol option “DBQLFlushRate” controls in which intervals the cache is written to the DBQL tables.

If the cache is to be written immediately this can be done with this command:

FLUSH QUERY LOGGING WITH ALL;

How is it determined how much information about which objects should be collected?

Query logging is controlled by the following commands:

BEGIN QUERY LOGGING / END QUERY LOGGING

The precisely defined logging rules are stored in table DBC.DBQLRulesV.

How can I determine which rules are activated?

One way is by using this command:

SHOW QUERY LOGGING ON ALL;

Rule UserName “ALL” (From an ALL rule)
Rule UserId 00000000
Rule ZoneId FFFFFFFF
Account (Rule for any Account)
WITH NONE (No DBQL Logging)


Another possibility is to query the table with the rules:

SELECT * FROM DBC.DBQLRulesV;

Which users can enable or disable Query Logging?

Only those who have EXECUTE permissions on the following macro:

GRANT EXECUTE ON DBC.DBQLAccessMacro TO DWHPRO;

The Teradata DBQL Tables

Query logging includes a variety of table/view combinations in the DBC database:

  • DBC.DBQLOGTBL and DBC.QryLogV
    Saves one row per query with the aggregated key figures like the number of CPU seconds, number of IOs and much more.
  • DBC.DBQLStepTbl and DBC.QryLogStepsV
    The individual steps of a query are saved here, including the important key figures for each query system.
  • DBC.DBQLObjTbl and DBC.QryLogObjectsV
    One row per database object used is stored here.
  • DBC.DBQLSqlTbl and DBC.QryLogSQLV
    All SQL text is stored here in the common record for table DBC.DBQLOGTBL. This may require several rows per query.
  • DBC.DBQLSummaryTbl and DBC.QryLogSummaryV
    Certain filters cause that the information about requests is not stored individually in DBC.DBQLOGBTBL, but only aggregated with other queries in this table.
  • DBC.DBQLExplainTbl and DBC.QryLogExplainV
    Saves the individual steps of the Execution Plan
  • DBC.DBQLXMLTbl and DBC.QryLogXMLV
    Saves the Execution Plan of the queries as XML
  • DBC.QryLogEventsV
    Stores workload management events
  • DBC.QryLogExceptionsV
    Stores workload management exceptions

Teradata DBQL Logging Levels

Query logging can be enabled for all users or for specific users:

BEGIN QUERY LOGGING ON ALL;
BEGIN QUERY LOGGING ON DWHPRO;

It can also be restricted to the account or a combination of account and user:

BEGIN QUERY LOGGING ON DWHPRO ACCOUNT = ‘TheAccount';

Logging can also be restricted to certain Teradata applications:

BEGIN QUERY LOGGING ON APPLNAME = ‘Application';

The WITH Option

Which details about an account, user of the application should be logged is determined by the “WITH” option:

BEGIN QUERY LOGGING ON DWHPRO WITH

  • NONE
    Turn off query logging for the specific user, account or application
  • ALL
    Log DBC.DBQLOGTBL plus EXPLAIN, OBJECTS, SQL, STEPINFO
  • EXPLAIN
    Log DBC.DBQLOGTBL plus the EXPLAIN text
  • OBJECTS
    Log DBC.DBQLOGTBL plus the objects accessed
  • SQL
    Log DBC.DBQLOGTBL plus the entire SQL text
  • STEPINFO
    Log DBC.DBQLOGTBL plus all steps of the query
  • XMLPLAN
    Log DBC.DBQLOGTBL plus the Explain Plan as XML
  • STATSUSAGE
    Log DBC.DBQLOGTBL plus statistics usage

The LIMIT Option

The LIMIT option determines how much information is stored:

  • SQLTEXT
    Determines the length of the SQL text which is stored. The default is 200 characters. It can be between 0 and 10,000 characters
  • SUMMARY
    It does not log into DBC.DBQLOGTBL and only writes counts of queries each 10 minutes
  • THRESHOLD
    Rows are generated in DBC.DBQLOGTBL depending on certain thresholds (CPU seconds, etc.) defined. If the measure is below the threshold only a summary row is written into DBC.DBQLSummaryTbl

Difference Between SUMMARY And THRESHOLD

Both types of logging can use additional filters: Seconds, milliseconds, CPU milliseconds, normalized CPU milliseconds, or Number of IOs.

With SUMMARY these filters have the effect that the queries are arranged in a group. The information is only saved in table DBC.DBQLSummaryTbl.

SUMMARY = n1, n2, n3 [ELAPSEDSEC, ELAPSEDTIME, CPUTIME, CPUTIMENORM or IOCOUNT]

With THRESHOLD these filters have the effect that the queries are either written to the DBC.DBQLOGTBL or to DBC.DBQLSummaryTbl.

THRESHOLD = n [ELAPSEDSEC, ELAPSEDTIME, CPUTIME, CPUTIMENORM or IOCOUNT]

Query Logging Examples

BEGIN QUERY LOGGING ON ALL;
Logs default row for all users, accounts, and applications

BEGIN QUERY LOGGING ON ALL;
BEGIN QUERY LOGGING WITH NONE ON DWHPRO;
Logs default row for all users but exclude DWHPRO in a second step

BEGIN QUERY LOGGING WITH NONE ON APPLNAME = ‘FASTLOAD';
Logs all request of all fastloads

BEGIN QUERY LOGGING WITH NONE LIMIT THRESHOLD = 500 CPUTIME ON ALL;

Logs default row for all queries above 100 CPU milliseconds otherwise counts the query in the DBC.DBQLSummaryTbl

BEGIN QUERY LOGGING LIMIT SUMMARY = 100, 1000, 2000 CPUTIME ON ALL;

Logs in DBC.DBQLSummaryTbl in 4 different groups

  • The query takes up to 100 milliseconds
  • The query takes up to 1000 milliseconds
  • The query takes up to 2000 milliseconds
  • The query takes more than 2000 milliseconds

BEGIN QUERY LOGGING LIMIT THRESHOLD = 100 IOCOUNT ON ALL;

Log a default row in DBC.DBLOGTBL if the query needs more than 100 IOs or count the query in DBC.DBQLSummaryTbl if the query needs up to 100 IOs

You can find the exact structure of the DQBL tables here:

Teradata Official Documentation for DQBL

Improve Your Teradata Load Performance(Opens in a new browser tab)

DWH Pro Admin


Teradata and Data Warehouse Specialist with more than 20 years of experience

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!

>