Spool space issue

Database
Enthusiast

Spool space issue

Hi,

I encountered a spool space issue while running the query.

Kindly help me understand the root cause and if there are any specific areas to look for in case of spool space issue.

Thanks in advance

query is as follows:

----------------------------------

select a11.PROD_GRP_ID PROD_GRP_ID,

max(a14.PROD_GRP_DESC) PROD_GRP_DESC,

a11.FWEEK_ID FWEEK_ID,

max(a15.WK_DESC) WK_DESC,

sum(a11.CLR_LDOUT_CASES) WJXBFS1,

sum(a11.CLR_LDOUT_GLNS) WJXBFS2,

sum(a11.CLR_LDOUT_LBS) WJXBFS3,

sum(a11.CLR_LDOUT_UNITS) WJXBFS4

from PRD2_VKPI.V_CLR_LDOUT_DRILL a11

join PRD2_VKPI.V_PLANT a12

on (a11.PLANT_ID = a12.PLANT_ID)

join PRD2_VKPI.V_DAY a13

on (a11.DAY_ID = a13.DAY_ID)

join PRD2_VKPI.V_PROD_GRP a14

on (a11.PROD_GRP_ID = a14.PROD_GRP_ID)

join PRD2_VKPI.V_FWEEK a15

on (a11.FWEEK_ID = a15.FWEEK_ID)

where (a13.DAY_DATE >= DATE '2013-06-01'

and a13.DAY_DATE <= DATE '2013-06-08'

and a12.SBU_ID in (1))

group by a11.PROD_GRP_ID,

a11.FWEEK_ID

------------------------------------------------------------

Explain plan for the same is as follows

------------------------------------------------------------

1) First, we lock PRD2_EDW.AJI_V_CLR_LDOUT_DRILL in view

PRD2_VKPI.V_CLR_LDOUT_DRILL for access, we lock PRD2_EDW.PROD2 in

view PRD2_VKPI.V_CLR_LDOUT_DRILL for access, we lock

PRD2_EDW.PICK_LINE in view PRD2_VKPI.V_CLR_LDOUT_DRILL for access,

we lock PRD2_EDW.PROD_HIER in view PRD2_VKPI.V_CLR_LDOUT_DRILL for

access, we lock PRD2_edw.PROD_GRP in view PRD2_VKPI.V_PROD_GRP for

access, we lock PRD2_EDW.PROD_CONT_CTRL in view

PRD2_VKPI.V_CLR_LDOUT_DRILL for access, we lock PRD2_EDW.PROD in

view PRD2_VKPI.V_CLR_LDOUT_DRILL for access, we lock

PRD2_EDW.PLANT in view PRD2_VKPI.V_CLR_LDOUT_DRILL for access, we

lock PRD2_EDW.ITEM_MASTER for access, and we lock

PRD2_EDW.DATE_TIME in view PRD2_VKPI.V_CLR_LDOUT_DRILL for access.

2) Next, we do an all-AMPs SUM step to aggregate from

PRD2_EDW.ITEM_MASTER in view PRD2_VKPI.V_CLR_LDOUT_DRILL by way of

an all-rows scan with no residual conditions

, grouping by field1 ( PRD2_EDW.ITEM_MASTER.FLAV_DESC). Aggregate

Intermediate Results are computed globally, then placed in Spool

10. The size of Spool 10 is estimated with high confidence to be

1,410 rows (69,090 bytes). The estimated time for this step is

0.06 seconds.

3) We do an all-AMPs RETRIEVE step from Spool 10 (Last Use) by way of

an all-rows scan into Spool 3 (used to materialize view, derived

table or table function DISTINCT_VALUES) (all_amps), which is

built locally on the AMPs. The size of Spool 3 is estimated with

high confidence to be 1,410 rows (54,990 bytes). The estimated

time for this step is 0.03 seconds.

4) We do an all-AMPs STAT FUNCTION step from Spool 3 (Last Use) by

way of an all-rows scan into Spool 15 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 13 (all_amps), which is built locally on the AMPs. The

size is estimated with high confidence to be 1,410 rows (122,670

bytes).

5) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from Spool 13 (Last Use) by

way of an all-rows scan into Spool 7 (used to materialize

view, derived table or table function F) (all_amps), which is

built locally on the AMPs. The size of Spool 7 is estimated

with high confidence to be 1,410 rows (60,630 bytes). The

estimated time for this step is 0.03 seconds.

2) We do an all-AMPs SUM step to aggregate from

PRD2_EDW.ITEM_MASTER in view PRD2_VKPI.V_CLR_LDOUT_DRILL by

way of an all-rows scan with no residual conditions

, grouping by field1 ( PRD2_EDW.ITEM_MASTER.LABEL_DESC).

Aggregate Intermediate Results are computed globally, then

placed in Spool 19. The size of Spool 19 is estimated with

high confidence to be 1,887 rows (92,463 bytes). The

estimated time for this step is 0.06 seconds.

6) We do an all-AMPs RETRIEVE step from Spool 19 (Last Use) by way of

an all-rows scan into Spool 2 (used to materialize view, derived

table or table function DISTINCT_VALUES) (all_amps), which is

built locally on the AMPs. The size of Spool 2 is estimated with

high confidence to be 1,887 rows (73,593 bytes). The estimated

time for this step is 0.03 seconds.

7) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by

way of an all-rows scan into Spool 24 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 22 (all_amps), which is built locally on the AMPs. The

size is estimated with high confidence to be 1,887 rows (164,169

bytes).

8) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from Spool 22 (Last Use) by

way of an all-rows scan into Spool 6 (used to materialize

view, derived table or table function L) (all_amps), which is

built locally on the AMPs. The size of Spool 6 is estimated

with high confidence to be 1,887 rows (81,141 bytes). The

estimated time for this step is 0.03 seconds.

2) We do an all-AMPs SUM step to aggregate from

PRD2_EDW.ITEM_MASTER in view PRD2_VKPI.V_CLR_LDOUT_DRILL by

way of an all-rows scan with no residual conditions

, grouping by field1 ( PRD2_EDW.ITEM_MASTER.LABEL_SEG_CD).

Aggregate Intermediate Results are computed globally, then

placed in Spool 28. The size of Spool 28 is estimated with

high confidence to be 9 rows (441 bytes). The estimated time

for this step is 0.05 seconds.

9) We do an all-AMPs RETRIEVE step from Spool 28 (Last Use) by way of

an all-rows scan into Spool 1 (used to materialize view, derived

table or table function DISTINCT_VALUES) (all_amps), which is

built locally on the AMPs. The size of Spool 1 is estimated with

high confidence to be 9 rows (351 bytes). The estimated time for

this step is 0.03 seconds.

10) We do an all-AMPs STAT FUNCTION step from Spool 1 (Last Use) by

way of an all-rows scan into Spool 33 (Last Use), which is

redistributed by hash code to all AMPs. The result rows are put

into Spool 31 (all_amps), which is built locally on the AMPs. The

size is estimated with high confidence to be 9 rows (783 bytes).

11) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from Spool 31 (Last Use) by

way of an all-rows scan into Spool 5 (used to materialize

view, derived table or table function S) (all_amps), which is

built locally on the AMPs. The size of Spool 5 is estimated

with high confidence to be 9 rows (387 bytes). The estimated

time for this step is 0.03 seconds.

2) We do an all-AMPs RETRIEVE step from

PRD2_EDW.AJI_V_CLR_LDOUT_DRILL in view

PRD2_VKPI.V_CLR_LDOUT_DRILL by way of an all-rows scan with a

condition of ("NOT (PRD2_EDW.AJI_V_CLR_LDOUT_DRILL in view

PRD2_VKPI.V_CLR_LDOUT_DRILL.EDW_PLANT_ID IS NULL)") into Spool

4 (used to materialize view, derived table or table function

PL) (all_amps), which is built locally on the AMPs. The size

of Spool 4 is estimated with low confidence to be 22,851,936

rows (1,851,006,816 bytes). The estimated time for this step

is 3.45 seconds.

3) We do an all-AMPs JOIN step from PRD2_EDW.PCC in view

PRD2_VKPI.V_CLR_LDOUT_DRILL by way of a RowHash match scan

with no residual conditions, which is joined to PRD2_EDW.PROD2

in view PRD2_VKPI.V_CLR_LDOUT_DRILL by way of a RowHash match

scan with no residual conditions. PRD2_EDW.PCC and

PRD2_EDW.PROD2 are joined using a merge join, with a join

condition of ("PRD2_EDW.PROD2.PROD_CONT_CTRL_ID =

PRD2_EDW.PCC.PROD_CONT_CTRL_ID"). The result goes into Spool

41 (all_amps), which is duplicated on all AMPs. The size of

Spool 41 is estimated with low confidence to be 433,920 rows (

12,583,680 bytes). The estimated time for this step is 0.05

seconds.

4) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way

of an all-rows scan into Spool 42 (all_amps), which is

duplicated on all AMPs. Then we do a SORT to order Spool 42

by the hash code of (PRD2_EDW.ITEM_MASTER.LABEL_DESC). The

size of Spool 42 is estimated with high confidence to be

181,152 rows (6,340,320 bytes). The estimated time for this

step is 0.02 seconds.

12) We do an all-AMPs JOIN step from Spool 41 (Last Use) by way of an

all-rows scan, which is joined to PRD2_EDW.IM in view

PRD2_VKPI.V_CLR_LDOUT_DRILL by way of an all-rows scan with no

residual conditions. Spool 41 and PRD2_EDW.IM are joined using a

single partition hash_ join, with a join condition of (

"PRD2_EDW.IM.PROD_ID = PROD_ID"). The result goes into Spool 43

(all_amps), which is built locally on the AMPs. Then we do a SORT

to order Spool 43 by the hash code of (PRD2_EDW.IM.LABEL_DESC).

The size of Spool 43 is estimated with low confidence to be 47,357

rows (4,120,059 bytes). The estimated time for this step is 0.04

seconds.

13) We do an all-AMPs RETRIEVE step from PRD2_EDW.PN in view

PRD2_VKPI.V_CLR_LDOUT_DRILL by way of an all-rows scan with a

condition of ("(PRD2_EDW.PN in view

PRD2_VKPI.V_CLR_LDOUT_DRILL.PLANT_TYPE_ID >= 11) OR (PRD2_EDW.PN

in view PRD2_VKPI.V_CLR_LDOUT_DRILL.PLANT_TYPE_ID <= 9)") into

Spool 44 (all_amps), which is duplicated on all AMPs. The size of

Spool 44 is estimated with high confidence to be 11,328 rows (

237,888 bytes). The estimated time for this step is 0.03 seconds.

14) We do an all-AMPs JOIN step from Spool 44 (Last Use) by way of an

all-rows scan, which is joined to Spool 4 (Last Use) by way of an

all-rows scan with a condition of ("NOT (PL.EDW_ITEM_ID IS NULL)").

Spool 44 and Spool 4 are joined using a single partition hash_

join, with a join condition of ("EDW_PLANT_ID = PLANT_ID"). The

result goes into Spool 45 (all_amps), which is redistributed by

the hash code of (PRD2_EDW.AJI_V_CLR_LDOUT_DRILL.DATE_ID) to all

AMPs. Then we do a SORT to order Spool 45 by row hash. The size

of Spool 45 is estimated with low confidence to be 158 rows (

12,166 bytes). The estimated time for this step is 1.06 seconds.

15) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 42 (Last Use) by way of

a RowHash match scan, which is joined to Spool 43 (Last Use)

by way of a RowHash match scan. Spool 42 and Spool 43 are

joined using a merge join, with a join condition of (

"LABEL_DESC = LABEL_DESC"). The result goes into Spool 46

(all_amps), which is built locally on the AMPs. Then we do a

SORT to order Spool 46 by the hash code of (

PRD2_EDW.IM.EDW_ITEM_ID). The size of Spool 46 is estimated

with low confidence to be 47,357 rows (4,309,487 bytes). The

estimated time for this step is 0.03 seconds.

2) We do an all-AMPs JOIN step from PRD2_EDW.T in view

PRD2_VKPI.V_CLR_LDOUT_DRILL by way of a RowHash match scan

with a condition of ("(NOT (PRD2_EDW.T in view

PRD2_VKPI.V_CLR_LDOUT_DRILL.DAY_ID IS NULL )) AND (NOT

(PRD2_EDW.T in view PRD2_VKPI.V_CLR_LDOUT_DRILL.FWEEK_ID IS

NULL ))"), which is joined to Spool 45 (Last Use) by way of a

RowHash match scan. PRD2_EDW.T and Spool 45 are joined using

a merge join, with a join condition of ("DATE_ID =

PRD2_EDW.T.DATE_ID"). The result goes into Spool 47

(all_amps), which is redistributed by the hash code of (

PRD2_EDW.AJI_V_CLR_LDOUT_DRILL.EDW_ITEM_ID) to all AMPs. Then

we do a SORT to order Spool 47 by row hash. The size of Spool

47 is estimated with index join confidence to be 158 rows (

13,430 bytes). The estimated time for this step is 0.03

seconds.

16) We do an all-AMPs JOIN step from Spool 46 (Last Use) by way of a

RowHash match scan, which is joined to Spool 47 (Last Use) by way

of a RowHash match scan. Spool 46 and Spool 47 are joined using a

merge join, with a join condition of ("EDW_ITEM_ID = EDW_ITEM_ID").

The result goes into Spool 48 (all_amps), which is redistributed

by the hash code of (PRD2_EDW.PROD2.PROD_HIER_ID) to all AMPs.

Then we do a SORT to order Spool 48 by row hash. The size of

Spool 48 is estimated with index join confidence to be 158 rows (

25,122 bytes). The estimated time for this step is 0.03 seconds.

17) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from PRD2_EDW.PH in view

PRD2_VKPI.V_CLR_LDOUT_DRILL by way of a RowHash match scan

with no residual conditions, which is joined to Spool 48 (Last

Use) by way of a RowHash match scan. PRD2_EDW.PH and Spool 48

are joined using a merge join, with a join condition of (

"(((PRD2_EDW.PH.PROD_GRP_ID >= 5) OR (PRD2_EDW.PH.PROD_GRP_ID

<= 3 )) OR ((PLANT_TYPE_ID = 9) OR (PLANT_TYPE_ID = 7 ))) AND

(PROD_HIER_ID = PRD2_EDW.PH.PROD_HIER_ID)"). The result goes

into Spool 49 (all_amps), which is redistributed by the hash

code of (PRD2_EDW.IM.FLAV_DESC) to all AMPs. Then we do a

SORT to order Spool 49 by row hash. The size of Spool 49 is

estimated with index join confidence to be 119 rows (19,397

bytes). The estimated time for this step is 0.03 seconds.

2) We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by way

of an all-rows scan into Spool 50 (all_amps), which is

redistributed by the hash code of (

PRD2_EDW.ITEM_MASTER.FLAV_DESC) to all AMPs. Then we do a

SORT to order Spool 50 by row hash. The size of Spool 50 is

estimated with high confidence to be 1,410 rows (49,350 bytes).

The estimated time for this step is 0.02 seconds.

3) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way

of an all-rows scan into Spool 51 (all_amps), which is

duplicated on all AMPs. Then we do a SORT to order Spool 51

by the hash code of (PRD2_EDW.ITEM_MASTER.LABEL_SEG_CD). The

size of Spool 51 is estimated with high confidence to be 864

rows (30,240 bytes). The estimated time for this step is 0.01

seconds.

18) We do an all-AMPs JOIN step from Spool 49 (Last Use) by way of a

RowHash match scan, which is joined to Spool 50 (Last Use) by way

of a RowHash match scan. Spool 49 and Spool 50 are joined using a

merge join, with a join condition of ("FLAV_DESC = FLAVOR_DESC").

The result goes into Spool 52 (all_amps), which is built locally

on the AMPs. Then we do a SORT to order Spool 52 by the hash code

of (PRD2_EDW.IM.LABEL_SEG_CD). The size of Spool 52 is estimated

with index join confidence to be 119 rows (19,873 bytes). The

estimated time for this step is 0.03 seconds.

19) We do an all-AMPs JOIN step from Spool 51 (Last Use) by way of a

RowHash match scan, which is joined to Spool 52 (Last Use) by way

of a RowHash match scan. Spool 51 and Spool 52 are joined using a

merge join, with a join condition of ("LABEL_SEG_CD =

LABEL_SEG_DESC"). The result goes into Spool 40 (all_amps), which

is built locally on the AMPs. The size of Spool 40 is estimated

with index join confidence to be 119 rows (20,587 bytes). The

estimated time for this step is 0.04 seconds.

20) We do an all-AMPs SUM step to aggregate from Spool 40 (Last Use)

by way of an all-rows scan , grouping by field1 ( EDW_PLANT_ID

,MTH_ID ,FWEEK_ID ,DAY_ID ,EDW_ITEM_ID ,LOCAL_ITEM_NUM ,PROD_ID

,PROD_GRP_ID ,PROD_TYPE_ID ,PROD_LINE_ID ,CONT_TYPE_ID

,CONT_SIZE_ID ,LABEL_SEG_ID ,LABEL_SEG_CD ,LABEL_ID ,LABEL_DESC

,FLAVOR_ID ,PRD2_EDW.IM.FLAV_DESC

,PRD2_EDW.AJI_V_CLR_LDOUT_DRILL.PICK_UOM). Aggregate Intermediate

Results are computed globally, then placed in Spool 53. The size

of Spool 53 is estimated with low confidence to be 119 rows (

44,149 bytes). The estimated time for this step is 0.04 seconds.

21) We do an all-AMPs RETRIEVE step from Spool 53 (Last Use) by way of

an all-rows scan into Spool 8 (used to materialize view, derived

table or table function a11) (all_amps), which is built locally on

the AMPs. The size of Spool 8 is estimated with low confidence to

be 119 rows (8,211 bytes). The estimated time for this step is

0.03 seconds.

22) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from Spool 8 (Last Use) by way

of an all-rows scan with a condition of ("(NOT (a11.DAY_ID IS

NULL )) AND (NOT (a11.FWEEK_ID IS NULL ))") into Spool 58

(all_amps), which is redistributed by hash code to all AMPs.

Then we do a SORT to order Spool 58 by row hash. The size of

Spool 58 is estimated with low confidence to be 119 rows (

7,259 bytes). The estimated time for this step is 0.01

seconds.

2) We do an all-AMPs RETRIEVE step from PRD2_edw.DATE_TIME in

view PRD2_VKPI.V_DAY by way of an all-rows scan with a

condition of ("(NOT (PRD2_edw.DATE_TIME in view

PRD2_VKPI.V_DAY.DAY_ID IS NULL )) AND ((PRD2_edw.DATE_TIME in

view PRD2_VKPI.V_DAY.CAL_DT >= DATE '2013-06-01') AND

((PRD2_edw.DATE_TIME in view PRD2_VKPI.V_DAY.CAL_DT <= DATE

'2013-06-08') AND (PRD2_edw.DATE_TIME in view

PRD2_VKPI.V_DAY.TIME_TYPE = 'DAY')))") into Spool 59

(all_amps), which is duplicated on all AMPs. Then we do a

SORT to order Spool 59 by row hash. The size of Spool 59 is

estimated with low confidence to be 480 rows (8,160 bytes).

The estimated time for this step is 0.01 seconds.

23) We do an all-AMPs JOIN step from PRD2_EDW.PLANT in view

PRD2_VKPI.V_PLANT by way of a RowHash match scan with a condition

of ("((PRD2_EDW.PLANT in view PRD2_VKPI.V_PLANT.PLANT_TYPE_ID >=

11) OR (PRD2_EDW.PLANT in view PRD2_VKPI.V_PLANT.PLANT_TYPE_ID <=

9 )) AND (PRD2_EDW.PLANT in view PRD2_VKPI.V_PLANT.SBU_ID = 1)"),

which is joined to Spool 58 (Last Use) by way of a RowHash match

scan. PRD2_EDW.PLANT and Spool 58 are joined using a merge join,

with a join condition of ("PLANT_ID = PRD2_EDW.PLANT.PLANT_ID").

The result goes into Spool 60 (all_amps), which is built locally

on the AMPs. Then we do a SORT to order Spool 60 by row hash.

The size of Spool 60 is estimated with low confidence to be 105

rows (5,985 bytes). The estimated time for this step is 0.03

seconds.

24) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 59 (Last Use) by way of

a RowHash match scan, which is joined to Spool 60 (Last Use)

by way of a RowHash match scan. Spool 59 and Spool 60 are

joined using a merge join, with a join condition of ("DAY_ID =

DAY_ID"). The result goes into Spool 61 (all_amps), which is

redistributed by hash code to all AMPs. Then we do a SORT to

order Spool 61 by row hash. The size of Spool 61 is estimated

with low confidence to be 5 rows (265 bytes). The estimated

time for this step is 0.03 seconds.

2) We do an all-AMPs RETRIEVE step from PRD2_edw.a in view

PRD2_VKPI.V_FWEEK by way of an all-rows scan with a condition

of ("(PRD2_edw.a in view PRD2_VKPI.V_FWEEK.TIME_TYPE =

'FWEEK') AND (NOT (PRD2_edw.a in view

PRD2_VKPI.V_FWEEK.FWEEK_ID IS NULL ))") into Spool 62

(all_amps), which is redistributed by hash code to all AMPs.

Then we do a SORT to order Spool 62 by row hash. The size of

Spool 62 is estimated with low confidence to be 858 rows (

32,604 bytes). The estimated time for this step is 0.01

seconds.

3) We do an all-AMPs RETRIEVE step from PRD2_edw.b in view

PRD2_VKPI.V_FWEEK by way of an all-rows scan with a condition

of ("(PRD2_edw.b in view PRD2_VKPI.V_FWEEK.TIME_TYPE =

'FWEEK') AND (NOT (PRD2_edw.b in view PRD2_VKPI.V_FWEEK.WK_NO

IS NULL ))") into Spool 63 (all_amps), which is redistributed

by hash code to all AMPs. Then we do a SORT to order Spool 63

by row hash. The size of Spool 63 is estimated with low

confidence to be 858 rows (24,882 bytes). The estimated time

for this step is 0.01 seconds.

25) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from PRD2_edw.PROD_GRP in view

PRD2_VKPI.V_PROD_GRP by way of a RowHash match scan with no

residual conditions, which is joined to Spool 61 (Last Use) by

way of a RowHash match scan. PRD2_edw.PROD_GRP and Spool 61

are joined using a merge join, with a join condition of (

"PROD_GRP_ID = PRD2_edw.PROD_GRP.PROD_GRP_ID"). The result

goes into Spool 64 (all_amps), which is redistributed by hash

code to all AMPs. Then we do a SORT to order Spool 64 by row

hash. The size of Spool 64 is estimated with low confidence

to be 5 rows (305 bytes). The estimated time for this step is

0.03 seconds.

2) We do an all-AMPs JOIN step from Spool 62 (Last Use) by way of

a RowHash match scan, which is joined to Spool 63 (Last Use)

by way of a RowHash match scan. Spool 62 and Spool 63 are

left outer joined using a merge join, with condition(s) used

for non-matching on left table ("(TIME_TYPE = 'FWEEK') AND

(NOT (WK_NO IS NULL ))"), with a join condition of (

"((YEAR_ID - 1 )= YEAR_ID) AND ((TIME_TYPE = TIME_TYPE) AND

(WK_NO = WK_NO ))"). The result goes into Spool 65 (all_amps),

which is redistributed by hash code to all AMPs. Then we do a

SORT to order Spool 65 by row hash. The size of Spool 65 is

estimated with low confidence to be 858 rows (18,018 bytes).

The estimated time for this step is 0.03 seconds.

26) We do an all-AMPs JOIN step from Spool 64 (Last Use) by way of a

RowHash match scan, which is joined to Spool 65 (Last Use) by way

of a RowHash match scan. Spool 64 and Spool 65 are joined using a

merge join, with a join condition of ("FWEEK_ID = FWEEK_ID"). The

result goes into Spool 57 (all_amps), which is built locally on

the AMPs. The size of Spool 57 is estimated with low confidence

to be 5 rows (335 bytes). The estimated time for this step is

0.04 seconds.

27) We do an all-AMPs SUM step to aggregate from Spool 57 (Last Use)

by way of an all-rows scan, and the grouping identifier in field 1.

Aggregate Intermediate Results are computed locally, then placed

in Spool 66. The size of Spool 66 is estimated with low

confidence to be 5 rows (410 bytes). The estimated time for this

step is 0.03 seconds.

28) We do an all-AMPs RETRIEVE step from Spool 66 (Last Use) by way of

an all-rows scan into Spool 55 (group_amps), which is built

locally on the AMPs. The size of Spool 55 is estimated with low

confidence to be 5 rows (365 bytes). The estimated time for this

step is 0.03 seconds.

29) Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

-> The contents of Spool 55 are sent back to the user as the result

of statement 1.

------------------------------------------------------

I believe the spool space is caused when i join the view "PRD2_VKPI.V_CLR_LDOUT_DRILL" with any other view/table.

The view definition of PRD2_VKPI.V_CLR_LDOUT_DRILL is as follows.

REPLACE VIEW PRD2_VKPI.V_CLR_LDOUT_DRILL AS

LOCKING TABLE PRD2_EDW.PICK_LINE FOR ACCESS

LOCKING TABLE PRD2_EDW.DATE_TIME FOR ACCESS

LOCKING TABLE PRD2_EDW.PLANT FOR ACCESS

LOCKING TABLE PRD2_EDW.ITEM_MASTER FOR ACCESS

LOCKING TABLE PRD2_EDW.PROD FOR ACCESS

LOCKING TABLE PRD2_EDW.PROD_HIER FOR ACCESS

LOCKING TABLE PRD2_EDW.PROD_CONT_CTRL FOR ACCESS

LOCKING VIEW PRD2_VKPI.V_LABEL_SEG FOR ACCESS

LOCKING VIEW PRD2_VKPI.V_LABEL FOR ACCESS

LOCKING VIEW PRD2_VKPI.V_FLAVOR FOR ACCESS

SELECT

PL.EDW_PLANT_ID AS PLANT_ID,

T.MTH_ID, T.FWEEK_ID, T.DAY_ID,

PL.EDW_ITEM_ID AS MASTER_ITEM_ID,

PL.LOCAL_ITEM_NUM AS LOCAL_ITEM_ID,

IM.PROD_ID, PH.PROD_GRP_ID, PH.PROD_TYPE_ID, PH.PROD_LINE_ID,

PCC.CONT_TYPE_ID, PCC.CONT_SIZE_ID,

S.LABEL_SEG_ID, IM.LABEL_SEG_CD,

L.LABEL_ID, IM.LABEL_DESC,

F.FLAVOR_ID, IM.FLAV_DESC,

PL.PICK_UOM AS CLR_LDOUT_MTHD,

SUM(PL.CLR_LDOUT_UNITS) AS CLR_LDOUT_UNITS,

SUM(PL.CLR_LDOUT_GLNS) AS CLR_LDOUT_GLNS,

SUM(PL.CLR_LDOUT_LBS) AS CLR_LDOUT_LBS,

SUM(PL.CLR_LDOUT_CASES) AS CLR_LDOUT_CASES

FROM

(

select

EDW_PLANT_ID,

EDW_ITEM_ID,

LOCAL_ITEM_NUM,

DATE_ID,

PICK_UOM,

SUM(PICK_UNITS) AS CLR_LDOUT_UNITS,

SUM(PICK_GAL) AS CLR_LDOUT_GLNS,

SUM(PICK_LB) AS CLR_LDOUT_LBS,

SUM(PICK_CASE) AS CLR_LDOUT_CASES

from PRD2_EDW.PICK_LINE group by EDW_PLANT_ID,EDW_ITEM_ID, LOCAL_ITEM_NUM,DATE_ID,PICK_UOM

) as PL,

PRD2_EDW.DATE_TIME T,

PRD2_EDW.PLANT PN,

PRD2_EDW.ITEM_MASTER IM,

PRD2_EDW.PROD PR,

PRD2_EDW.PROD_HIER PH,

PRD2_EDW.PROD_CONT_CTRL PCC,

PRD2_VKPI.V_LABEL_SEG S,

PRD2_VKPI.V_LABEL L,

PRD2_VKPI.V_FLAVOR F

WHERE PL.EDW_ITEM_ID = IM.EDW_ITEM_ID

AND PL.DATE_ID = T.DATE_ID

AND IM.PROD_ID = PR.PROD_ID

AND PR.PROD_HIER_ID = PH.PROD_HIER_ID

AND PR.PROD_CONT_CTRL_ID = PCC.PROD_CONT_CTRL_ID

AND PL.EDW_PLANT_ID = PN.PLANT_ID

AND IM.LABEL_SEG_CD = S.LABEL_SEG_DESC

AND IM.LABEL_DESC = L.LABEL_DESC

AND IM.FLAV_DESC = F.FLAVOR_DESC

AND PN.PLANT_TYPE_ID NOT IN (10)

AND NOT (PN.PLANT_TYPE_ID NOT IN (7, 9) AND PH.PROD_GRP_ID IN (4))

GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19;

Tags (1)
3 REPLIES
Junior Contributor

Re: Spool space issue

You need to check dbc.QryLogStepsV (if it's enabled) to find which step failed.

It will include all steps successfully completed, so the first missing step run out of spool.

There's no product join, but some duplicated spools, you should compare actual to estimated rows to see if they're highly different. Might be due to outdated stats (there's a lot of "high confidence")

Dieter

Enthusiast

Re: Spool space issue

Hi Dieter,

Thanks for your suggestion.

Since I am a developer, I donot have access to dbc.QryLogStepsV.

Anyways i tried collecting stats on underlying tables but still i get the same error.

Is there anything else that i can try on this?

Junior Contributor

Re: Spool space issue

Ask your DBA to get access to that information, it's vital for development.

Dieter