Is this explain plan normal?

General

Is this explain plan normal?

I am noticing some strange behavior in the viewpoint explain. Data is being inserted in the same spool number multiple times. Taking the example of the few steps of an explain given below, we can see that data is put in spool 44455 multiple times. According to my understanding this should not be happening. Data should only be placed in a spool once, this can be later used in joins and other calculations but no other result should be put in it. This also seems to be messing up the optimizer estimates in later steps where this spool is being used. I was going to have the DBAs create a ticket with the CS over it but after being asked multiple times if im sure this is an issue and not some new teradata feature I am starting to doubt myself. So I wanted to confirm is this something new and normal or a bug?




We do an All-AMPs JOIN step from Spool 44462 (Last Use) by way of an all-rows scan, which is joined to table F. Spool 44462 and table F are joined using a product join . The result goes into Spool 44455, which is built locally on the AMPs. This step begins a parallel block of steps.
We do an All-AMPs RETRIEVE step from FS accessing a single partition into Spool 44464, which is redistributed by hash code to all AMPs. This step is performed in parallel.
We do an All-AMPs RETRIEVE step from S by way of an all-rows scan into Spool 44465, which is built locally on the AMPs. This step ends a parallel block of steps.
We do an All-AMPs JOIN step from Spool 44464 (Last Use) by way of an all-rows scan, which is joined to Spool 44465. Spool 44464 and Spool 44465 are left outer joined using ahash join of 12 partitions. The result goes into Spool 44455, which is built locally on the AMPs.
We do an All-AMPs RETRIEVE step from FL by way of an all-rows scan into Spool 44455, which is built locally on the AMPs.

Thanks

Zarrar

5 REPLIES
Senior Apprentice

Re: Is this explain plan normal?

Hi Zarrar,

this is usually the optimizer's plan for UNIONing two SELECTs.

Re: Is this explain plan normal?

Hi Dieter,

Thanks for the answer. I still have some doubts about how optimizer is dealing with this and its estimates/actual rows. If its a union (and it probably is) shouldnt that actual rows be increasing, but in the last step(step 14) when data is inserted in 44455 the actual rows returns to 0. And later on when this spool is used in a redistribution step (step 15) the optimizer estimates 1 row while the actual rows are same as in step 13, as they should be. But shouldnt the estimated rows also be 2465861, same as that from step 13 or from step 14 which are almost similer.






Step Number Confidence Est. Rows Actual Rows Est. Time Actual Time Step Text





11 3 624 624 0.010242864 0 We do an All-AMPs RETRIEVE step from Spool 44461 (Last Use) by way of an all-rows scan into Spool 44462, which is duplicated on all AMPs.
12 0 1617663 0 1.9377666 1.29 We do an All-AMPs JOIN step from Spool 44462 (Last Use) by way of an all-rows scan, which is joined to table FC. Spool 44462 and table FC are joined using a product join . The result goes into Spool 44455, which is built locally on the AMPs. This step begins a parallel block of steps.
12 0 848198 1055566 2.2752848 0.44 We do an All-AMPs RETRIEVE step from FS accessing a single partition into Spool 44464, which is redistributed by hash code to all AMPs. This step is performed in parallel.
12 3 1.32E+07 1.32E+07 0.80656 0.46 We do an All-AMPs RETRIEVE step from S by way of an all-rows scan into Spool 44465, which is built locally on the AMPs. This step ends a parallel block of steps.
13 0 2465861 1055566 0.7298949 1.44 We do an All-AMPs JOIN step from Spool 44464 (Last Use) by way of an all-rows scan, which is joined to Spool 44465. Spool 44464 and Spool 44465 are left outer joined using ahash join of 12 partitions. The result goes into Spool 44455, which is built locally on the AMPs.
14 0 2465870 0 0.010106113 0.08 We do an All-AMPs RETRIEVE step from FL by way of an all-rows scan into Spool 44455, which is built locally on the AMPs.
15 0 1 1055566 0.1706193 0.23 We do an All-AMPs RETRIEVE step from Spool 44455 (Last Use) by way of an all-rows scan into Spool 44460, which is redistributed by hash code to all AMPs. This step begins a parallel block of steps.

Zarrar

Senior Apprentice

Re: Is this explain plan normal?

Hi Zarrar,

EXPLAIN's "Estimated Rows"  for UNIONs are a cumulative sum, but the "Actual Rows" show the number of rows returned by this step. So 1055566 in step 15 is correct, 0 + 1055566 + 0

But the estimated 1 is actually a bit strange, IMHO this should be 2465870. Are there any additional conditions in the actual query? Is this estimation causing a problem?

Re: Is this explain plan normal?

Hi Dieter, 

Thanks for the quick replies, its becoming a lot more clear to me now. I shall have to get back to you on this, as all the union part is probably happening in a view used in the query and I cant see its definition till I go back to office after the weekend. I can however see a few where conditions on the suspected view in the query. But does the optimizer also filter out results during the redistribution step? Which would inturn cause it to give the 1 row extimate. 

Zarrar

Re: Is this explain plan normal?

So I checked and the view did contcain union between 3 tables there were no conditions inside the view but in the whole query we have additional condition on the view, its something along the lines

sel colA from Hist_View

where colB = 123

AND  colD = '1'

AND  ColC IS NULL OR 

(colC = 'val1' OR 

 colC  = 'val2' OR 

 ColC  = 'val3')

Do you think these additional conditions are responsible for the optimizer estimating the 1 row during the redistribution step?