Using SUMMARY Query Logging for Tactical Queries - Advanced SQL Engine - Teradata Database

The two query logging tables that are most relevant to tactical queries are the default logging table, DBC.DBQLogTbl, and the summary logging table, DBC.DBQLSummaryTbl.

You can enable query logging for one, a group, all users, or for an application, and you can only view the logged data after it has been written to disk. The summary table cache is written to disk every 10 minutes. Rows to the default and other DBQL tables are written when either of the following conditions occurs.

• The DBQL table caches become full.
• The threshold for DBQLFlushRate is reached.
• You issue an END QUERY LOGGING request.

Avoid enabling query logging for tactical queries at the default logging level beyond their testing period because the overhead can have an impact on the performance of single- or few-AMP queries.

You might want to enable default query logging during the test phase of your tactical queries to validate that your plans are being cached, because the DBC.DBQLogTbl table carries a flag indicating whether the query used a cached plan or not. However, there is rarely any value in enabling default logging for the well-tuned, predictable tactical query application, and if your queries are single- or few-AMP operations, query logging incurs some slight overhead by writing one row to the log for each query run.

A more general purpose use of query logging for tactical queries is to evaluate and record query response time consistency. You can accomplish this by using summary logging, which writes rows only to DBC.DBQLSummaryTbl. When you perform summary logging of a time variable, you must define three time thresholds, expressed in units of seconds. You can request summary logging using either elapsed, CPU, or normalized CPU time. You can also request summary logging of I/O counts.

For example.

     BEGIN QUERY LOGGING LIMIT SUMMARY = 1, 5, 10
     ON cab;

For each session having a user included in this summary logging, counts are kept for each of four defined buckets. Buckets having a zero count for an interval are not logged. For example, if all queries for an interval fall into the same bucket, then only one bucket is logged for that session. These buckets, which contain counts of queries within that time range, are kept independently for each session, and summary rows carry a session ID to identify the session to which they belong.

These counter buckets are flushed to disk at 10-minute intervals. At the end of the first 10-minute interval, you can examine the buckets for that, and earlier, time intervals by querying the DBC.DBQLSummaryTbl table with a request similar to the following example.

     SELECT procid, collecttimestamp, userid, acctstring, sessionid,
            qrycnt, qrysecs, lowhist, highhist
     FROM dbqlsummarytbl
     ORDER BY collecttimestamp, sessionid, lowhist;

This query produces the following report that illustrates some of the summary data collected for a single session.

For ease of analysis, these summary rows are ordered by each 10-minute interval and formatted with a blank line between collection intervals. Based on the value of CollectTimeStamp, you can see that the first grouping was collected several days prior to the others. Information remains in the query log tables until you delete it explicitly.

The primary index of the DBC.DBQLSummaryTbl table is the first two columns, chosen to expedite the write-to-disk time for the rows. Each 10-minute interval grouping is written efficiently in a batch using the same NUPI value.

The following list provides some descriptive information about DBC.DBQLSummaryTbl table information.

• The LowHist and HighHist columns define the range of that particular bucket. Only buckets with at least one query count for that interval are produced. The third grouping illustrated in the preceding report is the only grouping with query counts in all 4 interval buckets defined by the SUMMARY list of the BEGIN QUERY LOGGING request used to produce the report.

  Interval Number	Interval Time Range (seconds)
  1	0 - 1
  2	1 - 5
  3	5 - 10
  4	10 - 4,294,967,295

  The highest possible value for HighHist in DBC.DBQLSummaryTbl is 4,294,967,295 (232) seconds, which is approximately 1,193,046 hours.

• The QueryCount column (abbreviated in the example report as QryCnt) lists how many queries ran with a response time within the range of that interval. For example, in the first row of output, 8 queries ran in less than 1 second, and 2 queries ran in greater than 10 seconds.
• The QuerySeconds column (abbreviated in the example report as QrySecs) is not strictly the total execution time of all queries in that bucket. A query response time must be greater than 0.5 seconds to be accumulated into that total, and a query that ran in 0.5 or more seconds counts as 1 second. Fractions of a second are rounded up or down in the accumulation.
• The values for UserID are expressed with a BYTE data type because that is how the information appears to the system. One way to view the summary table with an easily recognizable user ID is to join to the DBC.databases2V view, as the query below illustrates. The same details are contained in the summary table as when the previous example query was run.

       SELECT st.collecttimestamp, db.databasename, st.sessionID,
              st.querycount, st.queryseconds, st.lowhist, st.highhist
       FROM DBC.databases2V AS db,DBC.dbqlsummarytbl AS st
       WHERE db.databaseid = st.userid
       ORDER BY collecttimestamp, lowhist;

Some additional considerations when using DBQL for tactical queries include the following issues.

• Query log tables are designed for efficient inserts into the log tables rather than for ease of querying. For that reason, accessing log tables with high row counts can be a slow process. Keeping the tables well-maintained aids viewing, space management, and ease of analyzing the log information.
• For users whose queries are primarily single- or few-AMP operations, enable logging on the default table, DBC.DBQLogTbl, during testing, and then log those queries in production only when necessary.
• DBQL summary information is useful for evaluating the extent of response time anomalies for tactical applications.
• DBQL summary information is not suitable for establishing accurate query rates for short-running queries because of its subsecond rounding. Because of the 10-minute lag between logging and being able to view the data, summary logging is not likely to match the needs of real time performance alerting.
• An additional alternative to summary logging is threshold logging, in which only queries running longer than a specified threshold times (either elapsed time, CPU time, or normalized CPU time, all expressed in units of seconds) or for more than a specified number of I/O counts, are logged in DBC.DBQLogtbl. All queries with an execution time below the threshold value are logged in DBC.DBQLSummaryTbl.