Teradata Query Logging
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.
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;
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.
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;
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
Stores workload management events
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
Turn off query logging for the specific user, account or application
Log DBC.DBQLOGTBL plus EXPLAIN, OBJECTS, SQL, STEPINFO
Log DBC.DBQLOGTBL plus the EXPLAIN text
Log DBC.DBQLOGTBL plus the objects accessed
Log DBC.DBQLOGTBL plus the entire SQL text
Log DBC.DBQLOGTBL plus all steps of the query
Log DBC.DBQLOGTBL plus the Explain Plan as XML
Log DBC.DBQLOGTBL plus statistics usage
The LIMIT Option
The LIMIT option determines how much information is stored:
Determines the length of the SQL text which is stored. The default is 200 characters. It can be between 0 and 10,000 characters
It does not log into DBC.DBQLOGTBL and only writes counts of queries each 10 minutes
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