Archives of the TeradataForum
Message Posted: Wed, 15 Dec 1999 @ 22:51:30 GMT
Sigh. (for those of you that remember it as a Chuck Eubanks-ism). Another view to find out who is running that hot amp query.
This one is called FastHotSpool. It is implemented as an underlying view for those that want to do their own constraints and order bys. The uncommon metrics are: the standard deviation (full population) of the current spool (STDevP), the percentage of the Max vproc to Avg vproc current spool (PctMaxAvg), and the number of devations between the Avg and max vprocs (NumDev). Between these metrics you should be able to establish who done it.
At least on the system I'm on, even with 20 to 30 concurrent sessions, performance is in the 10 to 30 second range (an improvement over the 2-3 minute version using the dbc views). This one directly calls the dbc tables, avoiding some extra joins in the dbc views. Please, let me know if something is wrong or can be improved. Also: it is expensive to run, and is not something you want to run 2 times a minute or all day long. I also recommend sorting by PctMaxAvg Desc...
I would be interested in seeing other's implementations of this problem...
create view FastHotSpool as select DBase. databasename as UserDB, sqrt(((count(css) * sum(css*css))- (sum(css)*sum(css)))/ (count(css)*count(css))) (format 'zzz,zzz,zzz,zz9.99') as STDevP, (maxspool - avgspool ) / nullifzero(stdevp) (format 'zz9.99') as NumDev, ((maxspool - avgspool) / maxspool * 100) (format 'zzz.9999') as PctMaxAvg, count(*) (format 'zz9') as VCnt, avg(css) (format 'zzz,zzz,zzz,zz9') as AvgSpool, max(css) (format 'zzz,zzz,zzz,zz9') as MaxSpool, sum(css) (format 'zzz,zzz,zzz,zz9') as SumSpool from DBC.Dbase , (select DataBaseSpace.DatabaseId , DataBaseSpace.VProc , DataBaseSpace.CurrentSpoolSpace as css FROM DBC.DataBaseSpace WHERE DataBaseSpace.CurrentSpoolSpace <> 0) DBS where DBase.DatabaseID = DBS.DatabaseID group by UserDB;
For those of you that hate that queryman cannot deal with numerics...
replace macro m_fasthotspool as ( select UserDB, STDevP (char(20)), NumDev (char(6)), PctMaxAvg (char(8)), VCnt (char(3)), AvgSpool (char(15)), MaxSpool (char(15)), SumSpool (char(15)) from fasthotspool order by PctMaxAvg desc;);
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|