![]() |
|
![]() |
![]() |
Archives of the TeradataForumMessage Posted: Fri, 11 Apr 2014 @ 16:19:22 GMT
Below query finds top bad queries. When I run this query I get an error, "expected something between the 'DATE' keyword and the word 'QUALIFYRANK'. What should I change? Thank you, ---Query to list Top Bad Queries SELECT RANK(ImpactCPU) AS CPURank ,CAST(a.CollectTimeStamp AS DATE) AS logdate ,a.UserName ,a.ProcId ,a.QueryId /*,a.querytext*/ ,a.expandacctstring ,a.appid ,a.clientid ,a.MaxAmpCPUTime * (HASHAMP()+1) /*no. of Amps*/ AS ImpactCPU , a.AMPCPUTime AS SumCPU ,CAST(EXTRACT(HOUR FROM ((a.firststeptime - a.StartTime) HOUR(2) TO SECOND(2) ) ) * 3600 + EXTRACT(MINUTE FROM ((a.firststeptime - a.StartTime) HOUR(2) TO SECOND(2) ) ) * 60 + EXTRACT(SECOND FROM ((a.firststeptime - a.StartTime) HOUR(2) TO SECOND(2) ) ) AS INTEGER) AS ParseBlockTime ,((firstresptime-firststeptime) DAY(4) TO SECOND) AS QryRespTime , NumofActiveAmps , NumResultRows , a.TotalIOCount AS SumIO , CASE WHEN AMPCPUTime < 1 OR(AMPCPUTime / (HASHAMP()+1)) =0 THEN 0 ELSE MaxAmpCPUTime/(AMPCPUTime / (HASHAMP()+1)) END(DEC(8,2)) AS CPUSKW , CASE WHEN AMPCPUTime < 1 OR(TotalIOCount / (HASHAMP()+1)) =0 THEN 0 ELSE MaxAmpIO/(TotalIOCount / (HASHAMP()+1)) END(DEC(8,2)) AS IOSKW , CASE WHEN AMPCPUTime < 1 OR TotalIOCount = 0 THEN 0 ELSE (a.AMPCPUTime *1000)/a.TotalIOCount END AS PJI , CASE WHEN AMPCPUTime < 1 OR AMPCPUTime = 0 THEN 0 ELSE a.TotalIOCount/(a.AMPCPUTime *1000) END AS UII ,a.acctstring ,a.Starttime ,a.defaultdatabase ,a.SpoolUsage ,a.Statementtype FROM DBC.DBQLogTbl a WHERE logdate BETWEEN (DATE-90) AND DATE QUALIFYRANK(ImpactCPU) <= 70; James Park
| ||||||||||||||||||||||||||||||||||||||||||||||
https: | ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
![]() | ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 24 Jul 2020 | ||||||||||||||||||||||||||||||||||||||||||||||||