Archives of the TeradataForum
Message Posted: Wed, 28 Aug 2002 @ 15:12:56 GMT
Most often, tools such as Pmon and TDAT Manager are not available to developers and users. I built this hack sometime ago to diagnose hot AMP problems. It is dependent on the running job having spool (which may not always be true), and the user having select rights on DBC *tables*... (you could convert this to DBC views).
replace view FastHotSpool as locking DBC.DataBaseSpace for access locking DBC.DBase for access 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;
Because it accesses DataBaseSpace, it requires the system to flush the performance metrics when called, so I would not recommend running it more often than every 10 seconds.
It's use is largely based on checking the count of vprocs that are in use and comparisons of the avg, max, and sum values of spool on the vprocs. Note the calculation of Standard Deviation (P) and the Number of Deviations.
The count of vprocs is simple, if it is less than the number of vprocs on the system, then the query is not distributing to all VPROCS. If the MAX is twice the AVG, then you have an out of balance condition, since a single VPROC has twice the spool of the AVG vproc...
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|