Archives of the TeradataForum

Message Posted: Wed, 15 Dec 1999 @ 22:51:30 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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...

Michael McIntire

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>