This example detects the longest running query in a particular time frame.
- Run the following command to determine what queryid has the longest query:
SELECT qlog.StartTime ,qlog.QueryId AS QueryId ,((FirstRespTime - StartTime HOUR(4)) (FLOAT)) * 3600. + ((FirstRespTime - ((FirstRespTime - StartTime HOUR(4) )) - StartTime SECOND(4) ) (FLOAT)) as ResponseTime ,qlog.NumResultRows ,qlog.TotalIoCount ,qlog.AmpCpuTime ,qlog.SpoolUsage ,qlog.ERRORCODE ,qlog.ErrorText ,qlog.Username ,qlog.QueryText FROM td_metric_svc.DBQLogV qlog where starttime between '2022-10-31 15:36:21.62' and '2022-10-31 16:07:14.88' order by ResponseTime Desc;Result: In the output, queryid 307160898229181140 has the longest running query with a response time of 262.453597. This is partial output and the output is displayed vertically for readability.StartTime: 2022-10-31 15:40:58 QueryId 307160898229181140 ResponseTime 262.453597 NumResultRows 5 TotalIOCount 1442598 AMPCPUTime 251.908 SpoolUsage 22623870976 Errorcode 0 ErrorText UserName MEDIUM_USER1 QueryText "-- using 6 as a seed to the RNG ... StartTime: 2022-10-31 15:36:22 QueryId 307160898229181080 ResponseTime 260.520936 NumResultRows 20122 TotalIOCount 481829 AMPCPUTime 2106.304 SpoolUsage 16922267648 Errorcode 0 ErrorText UserName MEDIUM_USER1 QueryText "-- using 6 as a seed to the RNG ... StartTime: 2022-10-31 15:38:29 QueryId 307160898229181121 ResponseTime 255.513717 NumResultRows 19979 TotalIOCount 469941 AMPCPUTime 2094.62 SpoolUsage 16930865152 Errorcode 0 ErrorText UserName MEDIUM_USER1 QueryText "SELECT O_ORDERPRIORITY, COUNT(*) ..." [ ... ] - Determine what step in the query took the longest. To determine this, query the DBQLStepV table for the queryid found in the previous step (307160898229181140):
select QueryID as QueryID ,StepLev1Num ,StepLev2Num ,StepName , StepStartTime ,((StepStopTime - StepStartTime HOUR(4)) (FLOAT)) * 3600. + ((StepStopTime - ((StepStopTime - StepStartTime HOUR(4) )) - StepStartTime SECOND(4) ) (FLOAT)) as StepResponseTime , 'Primary Cluster' as Component , EstProcTime , EstCPUCost , NumOfActiveAMPs , CPUTime , MaxAmpCPUTime , MinAmpCPUTime , CASE WHEN CPUTime < 1 OR (CPUTime / (HASHAMP()+1)) =0 THEN 0 ELSE MaxAmpCPUTime/(CPUTime / (HASHAMP()+1)) END (DEC(8,2)) AS CPUSKW , IOCount , MaxAmpIO , MinAmpIO , CASE WHEN CPUTime < 1 OR (IOCount / (HASHAMP()+1)) =0 THEN 0 ELSE MaxAmpIO/(IOCount / (HASHAMP()+1)) END (DEC(8,2)) AS IOSKW , SpoolUsage , MaxAMPSpool , MinAMPSpool from td_metric_svc.DBQLStepV ql where queryid=307160898229181140 /* this is the queryid of interest */ --and path_component_id like '%pog%' order by StepStartTime;Result: From the output we can see that StepLev1Num 9 has the longest response time. This step is performing an aggregation (SUM in the StepName column). This is partial output and the output is displayed vertically for readability.QueryId 307160898229181140 StepLev1Num 1 StepLev2Num 0 StepName MLK StepStartTime 2022-10-31 15:40:58 StepResponseTime 0.000266 Component Primary Cluster EstProcTime 0 EstCPUCost 0 NumOfActiveAMPs 1 CPUTime 0 MaxAmpCPUTime 0 MinAmpCPUTime 0 CPUSKW 0.00 IOCount 4 MaxAmpIO 4 MinAmpIO 0 IOSKW 0.00 SpoolUsage MaxAMPSpool MinAMPSpool QueryId 307160898229181140 StepLev1Num 2 StepLev2Num 0 StepName MLK StepStartTime 2022-10-31 15:40:58 StepResponseTime 0.000531 Component Primary Cluster EstProcTime 0 EstCPUCost 0 NumOfActiveAMPs 1 CPUTime 0 MaxAmpCPUTime 0 MinAmpCPUTime 0 CPUSKW 0.00 IOCount 4 MaxAmpIO 4 MinAmpIO 0 IOSKW 0.00 SpoolUsage MaxAMPSpool MinAMPSpool [ ... ] QueryId 307160898229181140 StepLev1Num 9 StepLev2Num 0 StepName SUM StepStartTime 2022-10-31 15:41:03 StepResponseTime 234.083157 Component Primary Cluster EstProcTime 96.69273423 EstCPUCost 21776.148969 NumOfActiveAMPs 96 CPUTime 2281.164 MaxAmpCPUTime 36.54 MinAmpCPUTime 14.352 CPUSKW 0.77 IOCount 1235766 MaxAmpIO 18911 MinAmpIO 8238 IOSKW 0.73 SpoolUsage 22623870976 MaxAMPSpool 413634560 MinAMPSpool149807104 [ ... ]