Archives of the TeradataForum
Message Posted: Mon, 15 May 2006 @ 18:47:14 GMT
| Subj: || || Finding spool usage for each STEP in the explain |
| From: || || Anomy Anom |
<-- 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
/** Find the AMP with the highest spool usage**/
sel max(peakspool), vproc
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.