Archives of the TeradataForum

Message Posted: Wed, 28 Aug 2002 @ 15:12:56 GMT


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

Subj:		Re: User query to list running jobs in teradata?

From:		Michael McIntire

I had a spool space problem last night while running a pass thru SAS job to teradata and was thinking maybe the query were not getting terminated on the teradata side after canceling the job in SAS.

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

Michael McIntire


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

Attachments

Library

Quick Reference