Sometime user or developer might want to know why a query takes longer time. To investigate the issue, you can use the following sql to check if there was any delay, Query execution time, CPU time, Parsing time, CPU Skew, PJI, Spool Usage, Error, query text etc to investigate the matter.
Select
dbql.LogDate
,dbql.QueryId
,dbql.SessionID
,dbql.StartTime
,dbql.delaytime
,dbql.lockdelay
,dbql.FirstRespTime
,((dbql.FirstRespTime - dbql.StartTime) hour(4) to second) (Named ElapsedTimeInDB)
,cast(
extract(hour from ElapsedTimeInDB) * 60 * 60 +
extract(minute from ElapsedTimeInDB) * 60 +
extract(second from ElapsedTimeInDB)
as decimal(10,1)) (Named ElapsedSecondsInDB)
,dbql.LastRespTime
,((dbql.LastRespTime - dbql.StartTime) hour(4) to second) (Named ElapsedTimeTotal)
,cast(
extract(hour from ElapsedTimeTotal) * 60 * 60 +
extract(minute from ElapsedTimeTotal) * 60 +
extract(second from ElapsedTimeTotal)
as decimal(10,1)) (Named ElapsedSecondsTotal)
,(dbql.AmpCPUTime+dbql.ParserCPUTime) as CPU
,QUANTILE(10, CPU) as QCPU
,COALESCE(CPU/NULLIFZERO(ElapsedSecondsInDB),0) as CPUI
,case
when CPU between 0.0 and 3.9 then 3
when CPU between 3.0 and 10.0 then 10
else 11
end as CPUSpan
,dbql.ParserCPUTime
,COALESCE(100-(NULLIFZERO(dbql.Ampcputime/NULLIFZERO(dbql.NumOfActiveAMPs))/(NULLIFZERO(dbql.Maxampcputime))*100),0) AS CPUSkew
,COALESCE(NULLIFZERO(dbql.AMPCPUTime) / NULLIFZERO(dbql.NumOfActiveAMPS),0) AS CPUSkewImpact
,(dbql.AMPCPUTime *1000)/NULLIFZERO(dbql.TotalIOCount) AS PJI
,COALESCE(dbql.TotalIOCount/(NULLIFZERO(dbql.AMPCPUTime) * 1000),0) AS UII
,dbql.MaxAMPCPUTime*dbql.NumOfActiveAMPs ImpactCPU
,dbql.spoolusage/1024/1024 spoolusage_MB
,dbql.ERRORCODE
,dbql.errortext
,dbql.WDName
,dbql.FinalWDName
,dbql.QueryBand
,dbql.UserName
,dbql.QueryText
From PDCRINFO.DBQLogTbl_Hst as dbql
Where
dbql.LogDate = Date '2018-09-06'
and dbql.username='A83844'
-- and dbql.sessionid=74119319
-- and dbql.queryid=307121259674786486
order by starttime
;
If the data is not moved to PDCRINFO.DBQLogTbl_Hst table from PDCRINFO.DBQLogTbl_DBC table then replace PDCRINFO.DBQLogTbl_Hst with PDCRINFO.DBQLogTbl_DBC in the above query to get the information.