Get query information from DBQLogTBL in Teradata

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. 

Comments