Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


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
;


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016