Need Help with DBQL query to find top 10 queries with high cpu usage

General

Need Help with DBQL query to find top 10 queries with high cpu usage

Could you please help with providing DBQL query to find the top 10 queries with high cpu usage and how many times those queries were run in a day? Can you please let me know what DBQL tables to be used and specific columns?
1 REPLY
Enthusiast

Re: Need Help with DBQL query to find top 10 queries with high cpu usage

Hi Sangeetha,

The below query gives the top 50 queries;  please modify it according to your needs;  (courtesy Akos Levoi)

select  top 50
ProcID
, QueryID
, AMPCPUTime
, MaxAMPCPUTime * (hashamp () + 1) CPUImpact
, CAST (100 - ((AmpCPUTime / (hashamp () + 1)) * 100 / NULLIFZERO (MaxAMPCPUTime)) AS INTEGER) "CPUSkew%"
, TotalIOCount
, MaxAMPIO * (hashamp () + 1) IOImpact
, CAST (100 - ((TotalIOCount / (hashamp () + 1)) * 100 / NULLIFZERO (MaxAMPIO) ) AS INTEGER) "IOSkew%"
, AMPCPUTime * 1000 / nullifzero (TotalIOCount) LHR
, TotalIOCount / nullifzero (AMPCPUTime * 1000) RHL
, ParserCPUTime
, Queryband
, Substr(QueryText,1,2000) QueryText
from
/* For archived DBQL
dbql_arch.dbqlogtbl_hst where logdate=1131201
and ampcputime>0
*/
/* For online DBQL*/
dbc.dbqlogtbl where
cast(cast(starttime as char(10)) as date) = '2013-12-18' (date)
and ampcputime>0
order by CPUImpact desc