Archives of the TeradataForum

Message Posted: Thu, 03 Apr 2003 @ 14:17:42 GMT

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

<-- Anonymously Posted: Thursday, April 03, 2003 06:10 -->

You wrote ;

Encourage users to sign off when idle / overnight or implement a policy to kill all sessions idle for say more than 1 hour, obviously business needs must be considered here also. Having said that this does keep all session reletively small and totals more applicable to the queries running at the time. Frequent checks of the CPU and Disk IO totals will soon give an idea of who culprits are from PMon.

If this isn't practical in the short term try querying the DBC.DISKSPACE table to get an idea of user spool usage at the current time, spool usage in the ideal world will be even accross all AMPs if not grab the code from PMon, very high spool usage may indicate large redistribution or product join taking place again check user code running currently, may also indicate a large proportion of a table being updated via UPDATE statement.

Care ~ this will give current total spool usage / AMP for all sessions active under a user ID at current time!

SELECT DATABASENAME, VPROC, CURRENTSPOOL, PEAKSPOOL,MAXSPOOL
FROM DBC.DISKSPACE
WHERE DATABASENAME IN (SELECT DATABASENAME FROM DBC.DISKSPACE
                        WHERE CURRENTSPOOL > 0)
AND   DATABASENAME IN (SEL USERNAME FROM DBC.SESSIONINFO)
ORDER BY 1,3 DESC
;

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