Trying to compile a "statistics Hoggers" report . All those users who hogged away CPU running statistics
On what "table.cols" ( or col1,col2 etc) , did they run stats and when they ran it.
I wrote the below report but I can see its far from real
It "pulls over the wrong guy"- the username against running the stats operation shows up incorrectly. Our production ID that runs stats is SWPRDUSR but the top stats user shows up as SYSPRDUSR who is the system wide prod. user and he really does'nt mess with our stuff- so I know something is amiss here.
Here's what I am running
I am running this report not system wide BUT only for my My database , cascaded
sel
a.username,
s.ObjectTableName,
s.objectdatabasename,
--s.ObjectColumnName,
cast ( s.CollectTimeStamp as date ) ,
CAST( SUM((((a.AmpCPUTime(DEC(18,3)))+
ZEROIFNULL(a.ParserCPUTime)) )) AS DECIMAL(18,3)) as Total_CPU
from
DBC.DBQLogtbl a join DBC.DBQLoBJTBL s
on ( s.ProcID = a.ProcID
and cast ( s.CollectTimeStamp as date ) = cast ( a.CollectTimeStamp as date ) )
where objectdatabasename in (
sel child
from dbc.children
where parent ='FINDB'
group by 1 )
and ObjectType='tab'
and statementType='collect statistics'
group by 1,2,3,4
UNION ALL
sel
a.username,
s.ObjectTableName,
s.objectdatabasename,
s.Logdate,
--s.ObjectColumnName,
CAST( SUM((((a.AmpCPUTime(DEC(18,3)))+
ZEROIFNULL(a.ParserCPUTime)) )) AS DECIMAL(18,3)) as Total_CPU
from
PDCRinfo.DBQLogtbl a join PDCRinfo.dbqlobjtbl_hst s
on ( s.queryID = a.queryID
and s.Logdate = a.Logdate )
where objectdatabasename in (
sel child
from dbc.children
where parent ='FINDB'
group by 1 )
and ObjectType='tab'
and statementType='collect statistics'
group by 1,2,3,4
order by 5 desc , 3 asc, 2 asc, 1 asc
;
In the 1st Select there's a missing join condition: s.queryID = a.queryID
Collect Stats is always single table, no need to split CPU.