Archives of the TeradataForum

Message Posted: Fri, 18 May 2012 @ 17:25:39 GMT

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

Hi Tomas,

You make a good point that Christer said "explain takes longer". I have to admit that I'd assumed that he meant that the explain showed a longer time (and we know what happens when you assume...)

Christer, it would be good to get clarification as to whether the query runtime is longer or the parsing time is longer.

I'd also like to follow up on a couple of points that Tomas made below. They don't tie up with my experience.

1) You refer to locks being visible in ParserCPUTime or AMPCpuTime. I wouldn't have thought so. These columns record the amount of CPU time (not elapsed time) being used by PE or AMP respectively. If a query is blocked then it is not using any CPU time, so that symptom (being blocked) wouldn't show up in these columns.

2) You also said " Locks are placed when the execution plan is beign executed, not when it is beign built ". This is mainly true. The one exception being that the parser process may involve requests (called express requests) to the dictionary. These requests can get blocked. I've seen many customer systems where many requests are showing as in "Parsing" state for many seconds. This has always been due to a dictionary block - usually caused by a DBA running something inappropriate during the normal working day !

Thinking about your comment as to whether the parser time is too long or whether the query execution time is too long, this can be done using the StartTime, FirstStepTime and FirstRespTime columns.

Starttime is when the query starts

FirstStepTime - StartTime = parsing elapsed time FirstrespTime - FirstStepTime = dbms time to build the answer set/complete the query

Cheers,

Dave

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

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