Archives of the TeradataForum

Message Posted: Fri, 09 Apr 2010 @ 12:13:43 GMT

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

Hi David,

Some thoughts...

FirstStepTime is when the first amp step is dispatched by the PE to the AMPs. So "FirstStepTime - StartTime" is effectively 'parse time' - i.e. how long it took the parser to do it's stuff (technical description !). This will include waiting on dictionary tables to get locks for resolving, security checking etc. This time also includes the amount of time the query was delayed by TDWM. (assuming no delays by TDWM then think of this as the time it takes a query to be explained).

From your query below it looks like on some days you're getting an average parse time of 12 or 18 seconds. IMHO that is very long.

I doubt if it's the plans themselves, so the fact that your plans look good is not really surprising. If you had bad plans then FirstRespTime - FirstStepTime would show that (this is how long the DBMS took to build the result set).

I'd change your query below to look at max, min and average parse time. For those queries with long parse times, see if they're happening at around the same time. If they are then I'd look to see what was actually running at that time. If it's the dictionary being locked then look for DDL commands. Teradata is pretty good at minimising the dictionary locks now, but I think it's worth a check.

Note that the DelayTime column is only for delays caused by TDWM and depending on your release of TD it might be showing in seconds (V2R6) or hundredths of seconds (TD 12) - see NTA2025 for details.

Cheers,

Dave

Ward Analytics Ltd - Information in motion ( www.ward-analytics.com )

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