To comprehend 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.
When executing multiple queries sequentially in Teradata, it is not always accurate to assume that the query taking the longest time is the weakest. However, utilizing three specific parameters makes it possible to determine the worst query in most cases. This information can be obtained from two tables in DBC: DBQLOGTBL and DBQLSQLTBL. To obtain SQL Query Stats, use the following query:
SEL
TB1.queryband,TB1.NumResultRows,TB1.NumSteps,TB1.TotalIOCount,
TB1.AMPCPUTime,TB1.ParserCPUTime,TB1.NumOfActiveAMPs,TB1.MaxCPUAmpNumber,
TB1.MinAmpIO,TB1.MAxAmPIO,TB1.MaxIOAmpNumber,TB1.SpoolUsage,
SUBSTR(TB2.SqlTextInfo,1,1000) AS SqlTextInfo
FROM
DBC.DBQLOGTBL TB1
INNER JOIN
DBC.DBQLSQLTBL TB2
ON
TB1.QueryID = TB2.QueryID
AND
TB1.ProcID = TB2.ProcID
AND
TB1.SessionID=12345;
The columns can be adjusted according to your needs. However, the ones highlighted are crucial in determining Query Performance in Teradata. If the AMPCPUTIME is high, it is necessary to fine-tune your query for optimal performance.
Consider these three factors when executing the aforementioned query:
SQL query results may not be immediately visible due to a brief delay in transferring query information to DBQL tables.
The aforementioned query may require a significant duration to produce results due to inadequate indexing in two 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 unviable, 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 that had the longest execution time as the worst. Inspect the Query DBQL statistics to personally identify the query with the poorest performance.
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?
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.
“
You will need to start query logging then end it to flush the cache, or you will get nothing using your query