Teradata - report for top "stats hoggers"

Question

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 does not "Split" the CPU on a given query by some proportion of weights to table. So if in a stats operation - the most expensive CPU was on FACT.BILLION_DOLLAR table but there was also a DIMENSION.DWARF table , DIMENSION.DWARF will spuriously show up on the chart -which makes the report misleading.
  I am also trying to compile another report where I want the TOP CPU by TABLE. Its not "Strictly" poss because the CPU is for a query not object but inside a query I want to "split" the CPU in proportion ( I guess the count(*) would be 1 criteria ). So HOW do I get this done

• 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 ;

Answer 1

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.