How to calculate the query execution time in Teradata
Contents
DBC Tables in Teradata – Data dictionary of Teradata
Teradata stores the log of each and every SQL query data in the data dictionary tables. Those meta data tables are present in the DBC database. From these tables,We can get the table level details such as column information, index information, table size,database size and so on. Similarly it provides query execution level details such as query run time time, Impact CPU, CPU consumption, execution plan and so on.
DBC.DBQLogTbl ( view name – DBC.QryLogV )
For each query execution, it maintains the log in DBC.DBQLogTble. The corresponding view name for this table is DBC.QryLogv. It provides enough information to investigate the query performance in Teradata. We can fetch those query level details using either query band or the corresponding user name or query id. Few important columns in this table are AMPCPUTime, MaxAMPCPUTime, FirstRespTime, StartTime and QueryText.
Calculate the query run time in Teradata using DBC.QryLogv
In order to find the query execution time , we need to subtract the Start time from the First Response Time in the DBC.QryLogv. Since those columns contains the timestamp, we have applied Extract function and other time calculation to get the query run time in seconds and minutes as below.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT q.QueryText,AMPCPUTime as CPU_consumption, MaxAMPCPUTime, Impact_CPU, CAST(EXTRACT(HOUR FROM ((q.FirstRespTime - q.StartTime) HOUR(3) TO SECOND(6) ) ) * 3600 + EXTRACT(MINUTE FROM ((q.FirstRespTime - q.StartTime) HOUR(3) TO SECOND(6) ) ) * 60 + EXTRACT(SECOND FROM ((q.FirstRespTime - q.StartTime) HOUR(3) TO SECOND(6) ) ) AS DECIMAL(10,2)) AS response_secs ,response_secs/60.0 AS response_mins from DBC.QryLogV q -- Specify your user name and Query band WHERE USERNAME = <USER_NAME> AND QUERYBAND ='UUID=19c1ccc5-ff2a-4d2d-8114-1fb625ad44df;'; |
FirstRespTime:
The timestamp when the first response packet is sent to the client unless the request is held by TASM to meet the minimum response time. In this case, it is the timestamp when the first response packet is ready to be sent to the client.
StartTime:
The timestamp when the query is submitted.
Output : Query execution time and other CPU metrics
Recommended Articles
- Performance tuning using Collect Statistics in Teradata table with examples
- How to create or drop the secondary index in Teradata?
- How to create Partitioned primary index in Teradata?
