Archives of the TeradataForum
Message Posted: Thu, 03 Apr 2003 @ 14:17:42 GMT
Subj: | | Re: Pinpointing queries affecting performance |
|
From: | | Anomy Anom |
<-- Anonymously Posted: Thursday, April 03, 2003 06:10 -->
You wrote ;
| But what if most of the sessions have been logged on for hours issuing hundreds of queries. Since CPU is accumulative for a session,
what you are suggesting does not pinpoint the current queries affecting performance. | |
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
;
|