Archives of the TeradataForum

Message Posted: Mon, 15 May 2006 @ 18:47:14 GMT

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

<-- Anonymously Posted: Monday, May 15, 2006 14:39 -->

Is there a way to find the spool usage for each STEP in the explain? I use dbc.diskspace to identify the total spool usage for the query and spool usage taken by each AMP.

     /** Total Spool Usage **/

     sel sum(d.peakspool) (format 'zzz,zzz,zzz,zz9') (char(15))   ,
            max(d.peakspool)*hashamp() (format 'zzz,zzz,zzz,zz9') (char(15))
         from dbc.diskspace d
     where d.databasename=user;

     /** Find the AMP with the highest spool usage**/

     sel max(peakspool), vproc
     from dbc.diskspace
     where databasename = user
     group by 2 order by 1 desc;

The second query is used to find the AMP with the highest spool usage. I kind of use this as a reference to find which STEP would have caused a skewed AMP by splitting the query into many small SQLs which would get me the exact same STEP. Then I see the distribution of rows based on the hashed columns for each STEP. Now this is a time consuming process especially if the query is huge or number of STEPs in explain are more. I also use DBQL STEPINFO information, to identify which STEP ran longer. But, time taken by a STEP in most cases is not always directly proportional to a skewed AMP. If I know the spool usage in each STEP, it would be easier to straightaway identify the skewed step. Is there a better way to do this? Any thought would be helpful.

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