DWHPro Insights

How to get SQL Query Stats in Teradata

By Roland Wenzlofsky ·

How to get SQL Query Stats in Teradata

To understand SQL query performance in Teradata, various parameters come into play. I believe AMPCPUTime, TotalIOCount, and SpoolUsage are the primary indicators to assess SQL query performance.

Use DBQL resource metrics together with workload importance and comparable runs. Query logging, including SQL text when needed, must be enabled for the measured requests. The query below returns one row per SQL fragment, so its metrics repeat and must not be summed across fragments.

SELECT q.ProcID, q.QueryID, q.StartTime, q.SessionID,
 q.QueryBand, q.TotalIOCount, q.AMPCPUTime, q.SpoolUsage,
 s.SQLRowNo, s.SQLTextInfo
FROM DBC.DBQLogTbl q
LEFT JOIN DBC.DBQLSQLTbl s
 ON q.ProcID=s.ProcID AND q.QueryID=s.QueryID
WHERE q.SessionID=12345
 AND q.StartTime >= CURRENT_TIMESTAMP - INTERVAL '1' HOUR
ORDER BY q.StartTime, q.ProcID, q.QueryID, s.SQLRowNo;

Replace the session placeholder and identify the exact ProcID/QueryID and time range. Reassemble SQL fragments in SQLRowNo order, without truncating each fragment. High CPU is a candidate for investigation, not proof of an inefficient query. The bounded audit query returned no rows, so end-to-end logging was not demonstrated.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Consider these three factors when executing the query above:

SQL query results may not be immediately visible due to a brief delay in transferring query information to DBQL tables.

The query above may take a significant amount of time to produce results due to inadequate indexing on both tables. Upon inspecting both tables’ primary index columns, we note that the ProcID and CollectTimeStamp are identical. However, the CollectTimeStamp value may differ for the same query in either table, making joining by the second column unwise. Consequently, leveraging the primary index is not possible, resulting in an extended query time.

To obtain the SessionID, execute the SEL SESSION command within the session where you execute your queries.

Avoid labeling the query with the longest execution time as the worst. Inspect the query DBQL statistics to personally identify the query with the poorest performance.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

3 thoughts on “How to get SQL Query Stats in Teradata”

  1. Query logging has indeed to be turned on, but as far as I know, the cache is automatically flushed regularly, or am I wrong on this?

    Reply
    • FROM The docs:
      ”
      Before Teradata Database writes DBQL data to Data Dictionary tables, it holds the data in cache until either:
      1.The cache is full.
      2.You end query logging.
      3.The number of seconds has elapsed that you define in the DBS Control utility field DBQLFlushRate.
      Note: You can select a rate from 1 to 3,600 seconds.However,Teradata recommends a flush rate of at least 10 minutes (600 seconds), which is the default. Less than 10 minutes can impact performance.
      4.You flush the DBQL cache manually.
      “

      Reply
  2. You will need to start query logging then end it to flush the cache, or you will get nothing using your query

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam