Spool Space Error

Database
Enthusiast

Spool Space Error

When running "SEL * FROM WEB_S_V" recieving spool space error.WEB_S_V is a complex view and underlying tables has the latest stats.

Diagnostic help stats is not showing any recommendations for the query.When monitoring the sql from Viewpoint,it is stuck at last but one step.

We do an All-AMPs JOIN step from Spool 59510 (Last Use) by way of an all-rows scan, which is joined to Spool 59511. Spool 59510 and Spool 59511 are right outer joined using ahash join of 3 partitions. The result goes into Spool 59508, which is built locally on the AMPs.

Based on the active explain plan and provided explain,please suggest at which step issue lies.

 *********************************************EXPLAIN**************************

 Explain SEL * FROM WEB_S_V

 This query is optimized using type 2 profile DR148674, profileid 10001.

  1) First, we lock W_D_T.prt_drg_vendr_contry_d_x in view

     WEB_S_V for access, we lock

     W_D_T.prt_drg_vendr_mapng_a_x in view

     WEB_S_V for access, we lock

     W_D_T.PRT_DRG_VENDR_D_X in view

     WEB_S_V for access, we lock

     O_C_D.PRT_VENDR_D_X in view

     WEB_S_V for access, we lock

     O_C_D.GCD_DAT_SORC_D_X in view

     WEB_S_V for access, we lock

     O_C_D.PRT_VENDR_REL_A_X in view

     WEB_S_V for access, we lock

     O_C_D.PRT_VENDR_RECRD_TYP_D_X in view

     WEB_S_V for access, we lock

     O_C_D.PRT_VENDR_LOKUP_CD_D_X in view

     WEB_S_V for access, we lock

     O_C_D.PRT_VENDR_DX2_X in view

     WEB_S_V for access, we lock

     O_C_D.GEO_TYP_D_X in view

     WEB_S_V for access, we lock

     O_C_D.GEO_MAIN_D_X in view

     WEB_S_V for access, and we lock

     E_D.PRT_VENDOR_D_y in view

     WEB_S_V for access.

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

     W_D_T.prt_drg_vendr_mapng_a_x in view

     WEB_S_V by way of an all-rows

     scan with no residual conditions , grouping by field1 (

     W_D_T.prt_drg_vendr_mapng_a_x.Prt_Drg_Vendr_Idn) locking

     for read.  Aggregate Intermediate Results are computed globally,

     then placed in Spool 6.  The size of Spool 6 is estimated with

     high confidence to be 602,181 rows (22,280,697 bytes).  The

     estimated time for this step is 0.35 seconds.

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

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

     table or table function MAPNG) (all_amps), which is built locally

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

     confidence to be 602,181 rows (22,280,697 bytes).  The estimated

     time for this step is 0.02 seconds.

  4) We execute the following steps in parallel.

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

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

          redistributed by the hash code of (

          W_D_T.prt_drg_vendr_mapng_a_x.Prt_Drg_Vendr_Idn) to

          all AMPs.  Then we do a SORT to order Spool 8 by row hash.

          The size of Spool 8 is estimated with high confidence to be

          602,181 rows (17,463,249 bytes).  The estimated time for this

          step is 0.14 seconds.

       2) We do an all-AMPs RETRIEVE step from

          W_D_T.prt_drg_vendr_mapng_a_x in view

          WEB_S_V by way of an

          all-rows scan with no residual conditions into Spool 9

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

          W_D_T.prt_drg_vendr_mapng_a_x.Prt_Drg_Vendr_Idn) to

          all AMPs.  Then we do a SORT to order Spool 9 by row hash.

          The size of Spool 9 is estimated with high confidence to be

          893,131 rows (25,900,799 bytes).  The estimated time for this

          step is 0.29 seconds.

  5) We do an all-AMPs JOIN step from W_D_T.PRT_DRG_VENDR_D_X

     in view WEB_S_V by way of a

     RowHash match scan with a condition of ("NOT

     (W_D_T.PRT_DRG_VENDR_D_X in view

     WEB_S_V.Princpl_Prt_Drg_Vendr_Idn

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

     RowHash match scan.  W_D_T.PRT_DRG_VENDR_D_X and Spool 9

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

     "W_D_T.PRT_DRG_VENDR_D_X.Prt_Drg_Vendr_Idn =

     Prt_Drg_Vendr_Idn").  The result goes into Spool 10 (all_amps),

     which is redistributed by the hash code of (

     W_D_T.prt_drg_vendr_mapng_a_x.Prt_Vendr_Idn) to all AMPs.

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

     Spool 10 is estimated with low confidence to be 893,128 rows (

     40,190,760 bytes).  The estimated time for this step is 0.48

     seconds.

  6) We execute the following steps in parallel.

       1) We do an all-AMPs JOIN step from O_C_D.PRT_VENDR_D_X

          in view WEB_S_V by way of a

          RowHash match scan with no residual conditions, which is

          joined to Spool 10 (Last Use) by way of a RowHash match scan.

          O_C_D.PRT_VENDR_D_X and Spool 10 are joined using a

          merge join, with a join condition of (

          "O_C_D.PRT_VENDR_D_X.PRT_VENDR_IDN = Prt_Vendr_Idn").

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

          locally on the AMPs.  Then we do a SORT to order Spool 11 by

          the hash code of (

          O_C_D.PRT_VENDR_D_X.PRT_VENDR_RECRD_TYP_IDN).  The

          size of Spool 11 is estimated with low confidence to be

          893,128 rows (47,335,784 bytes).  The estimated time for this

          step is 1.35 seconds.

       2) We do an all-AMPs RETRIEVE step from

          O_C_D.PRT_VENDR_RECRD_TYP_D_X in view

          WEB_S_V by way of an

          all-rows scan with a condition of (

          "(O_C_D.PRT_VENDR_RECRD_TYP_D_X in view

          WEB_S_V.PRT_VENDR_TYP_ID =

          'UNKNOWN') OR (O_C_D.PRT_VENDR_RECRD_TYP_D_X in view

          WEB_S_V.PRT_VENDR_TYP_ID =

          'VENDOR RECORD')") locking for read into Spool 12 (all_amps),

          which is duplicated on all AMPs.  Then we do a SORT to order

          Spool 12 by the hash code of (

          O_C_D.PRT_VENDR_RECRD_TYP_D_X.PRT_VENDR_RECRD_TYP_IDN).

          The size of Spool 12 is estimated with high confidence to be

          420 rows (8,820 bytes).  The estimated time for this step is

          0.01 seconds.

  7) We execute the following steps in parallel.

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

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

          by way of an all-rows scan.  Spool 11 and Spool 12 are joined

          using an inclusion merge join, with a join condition of (

          "PRT_VENDR_RECRD_TYP_IDN = PRT_VENDR_RECRD_TYP_IDN").  The

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

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

          code of (W_D_T.PRT_DRG_VENDR_D_X.Prt_Drg_Vendr_Idn,

          W_D_T.prt_drg_vendr_mapng_a_x.Prt_Drg_Vendr_Idn).

          The size of Spool 13 is estimated with low confidence to be

          595,419 rows (26,793,855 bytes).  The estimated time for this

          step is 0.05 seconds.

       2) We do an all-AMPs RETRIEVE step from

          W_D_T.prt_drg_vendr_contry_d_x in view

          WEB_S_V by way of an

          all-rows scan with no residual conditions into Spool 14

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

          SORT to order Spool 14 by the hash code of (

          W_D_T.prt_drg_vendr_contry_d_x.Prt_Drg_Vendr_Idn,

          W_D_T.prt_drg_vendr_contry_d_x.Prt_Drg_Vendr_Idn).

          The size of Spool 14 is estimated with high confidence to be

          1,680 rows (36,960 bytes).  The estimated time for this step

          is 0.01 seconds.

  8) We execute the following steps in parallel.

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

          of a RowHash match scan, which is joined to Spool 14 (Last

          Use) by way of a RowHash match scan.  Spool 13 and Spool 14

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

          used for non-matching on left table ("(Prt_Drg_Vendr_Idn =

          Prt_Drg_Vendr_Idn) AND (NOT (Prt_Drg_Vendr_Idn IS NULL ))"),

          with a join condition of ("(Prt_Drg_Vendr_Idn =

          Prt_Drg_Vendr_Idn) AND (Prt_Drg_Vendr_Idn = Prt_Drg_Vendr_Idn)").

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

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

          low confidence to be 595,419 rows (17,267,151 bytes).  The

          estimated time for this step is 0.04 seconds.

       2) We do an all-AMPs RETRIEVE step from

          O_C_D.PRT_VENDR_REL_A_X in view

          WEB_S_V by way of an

          all-rows scan with a condition of (

          "O_C_D.PRT_VENDR_REL_A_X in view

          WEB_S_V.PRT_VENDR_REL_TYP_IDN

          = 3.") into Spool 16 (all_amps), which is redistributed by

          the hash code of (

          O_C_D.PRT_VENDR_REL_A_X.PRT_VENDR_PRNT_IDN) to all

          AMPs.  The size of Spool 16 is estimated with low confidence

          to be 1,540,497 rows (44,674,413 bytes).  The estimated time

          for this step is 0.49 seconds.

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

          a RowHash match scan, which is joined to

          W_D_T.PRT_DRG_VENDR_D_X in view

          WEB_S_V by way of a RowHash

          match scan with a condition of ("((( CASE WHEN (NOT

          (W_D_T.PRT_DRG_VENDR_D_X in view

          WEB_S_V.Princpl_Flg IS NULL

          )) THEN (W_D_T.PRT_DRG_VENDR_D_X in view

          WEB_S_V.Princpl_Flg) ELSE

          ('P') END ))= 'P')OR ((( CASE WHEN (NOT

          (W_D_T.PRT_DRG_VENDR_D_X in view

          WEB_S_V.Princpl_Flg IS NULL

          )) THEN (W_D_T.PRT_DRG_VENDR_D_X in view

          WEB_S_V.Princpl_Flg) ELSE

          ('P') END ))= 'U')") locking W_D_T.PRT_DRG_VENDR_D_X

          for access.  Spool 8 and W_D_T.PRT_DRG_VENDR_D_X are

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

          "W_D_T.PRT_DRG_VENDR_D_X.Prt_Drg_Vendr_Idn =

          PRT_DRG_VENDR_IDN").  The result goes into Spool 17

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

          Spool 17 is estimated with no confidence to be 136,589 rows (

          5,326,971 bytes).  The estimated time for this step is 0.29

          seconds.

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

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

     all-rows scan.  Spool 15 and Spool 16 are joined using a single

     partition hash join, with a join condition of (

     "PRT_VENDR_PRNT_IDN = PRT_VENDR_IDN").  The result goes into Spool

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

     W_D_T.PRT_DRG_VENDR_D_X.Princpl_Prt_Drg_Vendr_Idn) to all

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

     of Spool 18 is estimated with low confidence to be 794,048 rows (

     35,732,160 bytes).  The estimated time for this step is 0.23

     seconds.

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

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

     of a RowHash match scan.  Spool 17 and Spool 18 are joined using a

     merge join, with a join condition of ("(Princpl_Prt_Drg_Vendr_Idn

     = PRT_DRG_VENDR_IDN) AND (Princpl_Prt_Drg_Vendr_Idn =

     Prt_Drg_Vendr_Idn)").  The result goes into Spool 19 (all_amps),

     which is redistributed by the hash code of (

     W_D_T.prt_drg_vendr_mapng_a_x.Prt_Vendr_Idn) to all AMPs.

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

     Spool 19 is estimated with no confidence to be 182,155 rows (

     8,561,285 bytes).  The estimated time for this step is 0.14

     seconds.

 11) We do an all-AMPs JOIN step from O_C_D.PRT_VENDR_D_X in

     view WEB_S_V by way of a RowHash

     match scan with no residual conditions, which is joined to Spool

     19 (Last Use) by way of a RowHash match scan locking

     O_C_D.PRT_VENDR_D_X for access.  O_C_D.PRT_VENDR_D_X

     and Spool 19 are joined using a merge join, with a join condition

     of ("O_C_D.PRT_VENDR_D_X.PRT_VENDR_IDN = PRT_VENDR_IDN").

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

     by the hash code of (

     O_C_D.PRT_VENDR_REL_A_X.PRT_VENDR_PRNT_IDN) to all AMPs.

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

     Spool 20 is estimated with no confidence to be 182,155 rows (

     62,661,320 bytes).  The estimated time for this step is 1.49

     seconds.

 12) We do an all-AMPs JOIN step from O_C_D.PRT_VENDR_DX2_X in

     view WEB_S_V by way of a RowHash

     match scan with no residual conditions, which is joined to Spool

     20 (Last Use) by way of a RowHash match scan.

     O_C_D.PRT_VENDR_DX2_X and Spool 20 are joined using a merge

     join, with a join condition of ("(PRT_VENDR_PRNT_IDN =

     O_C_D.PRT_VENDR_DX2_X.PRT_VENDR_IDN) AND

     (O_C_D.PRT_VENDR_DX2_X.PRT_VENDR_IDN = PRT_VENDR_IDN)").

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

     by the hash code of (

     O_C_D.PRT_VENDR_REL_A_X.PRT_VENDR_CHLD_IDN) to all AMPs.

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

     Spool 21 is estimated with no confidence to be 182,155 rows (

     61,204,080 bytes).  The estimated time for this step is 2.43

     seconds.

 13) We execute the following steps in parallel.

      1) We do an all-AMPs JOIN step from E_D.PRT_VENDOR_D_y in

         view WEB_S_V by way of a

         RowHash match scan with no residual conditions, which is

         joined to Spool 21 (Last Use) by way of a RowHash match scan

         locking E_D.PRT_VENDOR_D_y for read.

         E_D.PRT_VENDOR_D_y and Spool 21 are joined using a merge

         join, with a join condition of ("PRT_VENDR_CHLD_IDN =

         E_D.PRT_VENDOR_D_y.VENDOR_IDN").  The result goes into

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

         function PRIN_SUP) (all_amps), which is built locally on the

         AMPs.  The size of Spool 4 is estimated with no confidence to

         be 182,155 rows (62,661,320 bytes).  The estimated time for

         this step is 1.63 seconds.

      2) We do an all-AMPs RETRIEVE step from

         O_C_D.PRT_VENDR_LOKUP_CD_D_X in view

         WEB_S_V by way of an all-rows

         scan with no residual conditions locking for read into Spool

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

         Spool 22 is estimated with high confidence to be 1,395,240

         rows (147,895,440 bytes).  The estimated time for this step is

         0.13 seconds.

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

     all-rows scan, which is joined to O_C_D.PRT_VENDR_D_X in

     view WEB_S_V by way of an

     all-rows scan with no residual conditions locking

     O_C_D.PRT_VENDR_D_X for access.  Spool 22 and

     O_C_D.PRT_VENDR_D_X are right outer joined using a dynamic

     hash join, with condition(s) used for non-matching on right table

     ("NOT (O_C_D.PRT_VENDR_D_X.VENDR_TYP_LOKUP_CD_IDN IS NULL)"),

     with a join condition of (

     "O_C_D.PRT_VENDR_D_X.VENDR_TYP_LOKUP_CD_IDN =

     PRT_VENDR_LOKUP_CD_IDN").  The result goes into Spool 23

     (all_amps), which is built locally on the AMPs into 50 hash join

     partitions.  The size of Spool 23 is estimated with low confidence

     to be 2,725,718 rows (1,567,287,850 bytes).  The estimated time

     for this step is 2.03 seconds.

 15) We execute the following steps in parallel.

      1) We do an all-AMPs RETRIEVE step from

         O_C_D.PRT_VENDR_RECRD_TYP_D_X in view

         WEB_S_V by way of an all-rows

         scan with a condition of (

         "(O_C_D.PRT_VENDR_RECRD_TYP_D_X in view

         WEB_S_V.PRT_VENDR_TYP_ID =

         'UNKNOWN') OR (O_C_D.PRT_VENDR_RECRD_TYP_D_X in view

         WEB_S_V.PRT_VENDR_TYP_ID =

         'VENDOR RECORD')") into Spool 26 (all_amps) fanned out into 50

         hash join partitions, which is duplicated on all AMPs.  The

         size of Spool 26 is estimated with high confidence to be 420

         rows (8,820 bytes).  The estimated time for this step is 0.02

         seconds.

      2) We do an all-AMPs RETRIEVE step from

         W_D_T.prt_drg_vendr_mapng_a_x in view

         WEB_S_V by way of an all-rows

         scan with no residual conditions locking for read into Spool

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

         W_D_T.prt_drg_vendr_mapng_a_x.Prt_Drg_Vendr_Idn) to

         all AMPs.  Then we do a SORT to order Spool 27 by row hash.

         The size of Spool 27 is estimated with high confidence to be

         893,131 rows (25,900,799 bytes).  The estimated time for this

         step is 0.29 seconds.

 16) We execute the following steps in parallel.

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

         a RowHash match scan, which is joined to

         W_D_T.PRT_DRG_VENDR_D_X in view

         WEB_S_V by way of a RowHash

         match scan with no residual conditions locking

         W_D_T.PRT_DRG_VENDR_D_X for access.  Spool 27 and

         W_D_T.PRT_DRG_VENDR_D_X are left outer joined using a

         merge join, with a join condition of (

         "W_D_T.PRT_DRG_VENDR_D_X.Prt_Drg_Vendr_Idn =

         Prt_Drg_Vendr_Idn").  The result goes into Spool 28 (all_amps),

         which is redistributed by the hash code of (

         W_D_T.prt_drg_vendr_mapng_a_x.Prt_Vendr_Idn) to all

         AMPs into 4 hash join partitions.  The size of Spool 28 is

         estimated with low confidence to be 1,219,764 rows (

         362,269,908 bytes).  The estimated time for this step is 1.26

         seconds.

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

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

         way of an all-rows scan.  Spool 23 and Spool 26 are joined

         using a inclusion hash join of 50 partitions, with a join

         condition of ("PRT_VENDR_RECRD_TYP_IDN =

         PRT_VENDR_RECRD_TYP_IDN").  The result goes into Spool 29

         (all_amps), which is built locally on the AMPs into 4 hash

         join partitions.  The size of Spool 29 is estimated with low

         confidence to be 1,817,146 rows (1,030,321,782 bytes).  The

         estimated time for this step is 0.48 seconds.

      3) We do an all-AMPs RETRIEVE step from

         W_D_T.prt_drg_vendr_contry_d_x in view

         WEB_S_V by way of an all-rows

         scan with no residual conditions locking for read into Spool

         31 (all_amps) fanned out into 50 hash join partitions, which

         is duplicated on all AMPs.  The size of Spool 31 is estimated

         with high confidence to be 1,680 rows (50,400 bytes).  The

         estimated time for this step is 0.02 seconds.

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

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

     all-rows scan.  Spool 28 and Spool 29 are right outer joined using

     a hash join of 4 partitions, with a join condition of (

     "PRT_VENDR_IDN = Prt_Vendr_Idn").  The result goes into Spool 32

     (all_amps), which is built locally on the AMPs into 50 hash join

     partitions.  The size of Spool 32 is estimated with low confidence

     to be 2,143,779 rows (1,807,205,697 bytes).  The estimated time

     for this step is 1.11 seconds.

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

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

     all-rows scan.  Spool 31 and Spool 32 are right outer joined using

     a hash join of 50 partitions, with condition(s) used for

     non-matching on right table ("(Prt_Drg_Vendr_Idn =

     Prt_Drg_Vendr_Idn) AND (NOT (Prt_Drg_Vendr_Idn IS NULL ))"), with

     a join condition of ("(Prt_Drg_Vendr_Idn = Prt_Drg_Vendr_Idn) AND

     (Prt_Drg_Vendr_Idn = Prt_Drg_Vendr_Idn)").  The result goes into

     Spool 35 (all_amps), which is built locally on the AMPs.  Then we

     do a SORT to order Spool 35 by the hash code of (

     O_C_D.PRT_VENDR_D_X.PRT_VENDR_IDN).  The size of Spool 35

     is estimated with low confidence to be 2,143,779 rows (

     1,790,055,465 bytes).  The estimated time for this step is 1.85

     seconds.

 19) We do an all-AMPs JOIN step from O_C_D.PRT_VENDR_DX2_X in

     view WEB_S_V by way of a RowHash

     match scan with no residual conditions, which is joined to Spool

     35 (Last Use) by way of a RowHash match scan locking

     O_C_D.PRT_VENDR_DX2_X for read.

     O_C_D.PRT_VENDR_DX2_X and Spool 35 are joined using a merge

     join, with a join condition of (

     "O_C_D.PRT_VENDR_DX2_X.PRT_VENDR_IDN = PRT_VENDR_IDN").

     The result goes into Spool 2 (used to materialize view, derived

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

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

     be 2,143,779 rows (1,809,349,476 bytes).  The estimated time for

     this step is 3.61 seconds.

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

     an all-rows scan into Spool 39 (all_amps), which is redistributed

     by the hash code of (O_C_D.PRT_VENDR_D_X.PRT_VENDR_IDN) to

     all AMPs.  The size of Spool 39 is estimated with low confidence

     to be 2,143,779 rows (70,744,707 bytes).  The estimated time for

     this step is 0.30 seconds.

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

     RowHash match scan, which is joined to O_C_D.PRT_VENDR_D_X

     in view WEB_S_V by way of a

     RowHash match scan with no residual conditions locking

     O_C_D.PRT_VENDR_D_X for access.  Spool 39 and

     O_C_D.PRT_VENDR_D_X are joined using a single partition

     hash join, with a join condition of (

     "O_C_D.PRT_VENDR_D_X.PRT_VENDR_IDN = PRT_VENDR_IDN").  The

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

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

     to be 2,143,779 rows (87,894,939 bytes).  The estimated time for

     this step is 1.39 seconds.

 22) We do an all-AMPs RETRIEVE step from O_C_D.GEO_TYP_D_X in

     view WEB_S_V by way of an

     all-rows scan with a condition of ("(O_C_D.GEO_TYP_D_X in

     view WEB_S_V.GEO_TYP_DESC =

     'UNKNOWN') OR (O_C_D.GEO_TYP_D_X in view

     WEB_S_V.GEO_TYP_DESC = 'COUNTRY')")

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

     Then we do a SORT to order Spool 41 by the sort key in spool

     field1 (O_C_D.GEO_TYP_D_X.GEO_TYP_IDN).  The size of Spool

     41 is estimated with high confidence to be 2 rows (58 bytes).  The

     estimated time for this step is 0.03 seconds.

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

     an all-rows scan into Spool 40 (all_amps), which is duplicated on

     all AMPs.  The size of Spool 40 is estimated with high confidence

     to be 420 rows (12,180 bytes).

 24) We execute the following steps in parallel.

      1) We do an all-AMPs JOIN step from O_C_D.GEO_MAIN_D_X in

         view WEB_S_V by way of an

         all-rows scan with no residual conditions, which is joined to

         Spool 40 (Last Use) by way of an all-rows scan.

         O_C_D.GEO_MAIN_D_X and Spool 40 are joined using a

         inclusion dynamic hash join, with a join condition of (

         "O_C_D.GEO_MAIN_D_X.GEO_TYP_IDN = GEO_TYP_IDN").  The

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

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

         code of (O_C_D.GEO_MAIN_D_X.SRC_IDN).  The size of

         Spool 42 is estimated with low confidence to be 11,446 rows (

         1,980,158 bytes).  The estimated time for this step is 0.06

         seconds.

      2) We do an all-AMPs RETRIEVE step from

         O_C_D.GCD_DAT_SORC_D_X in view

         WEB_S_V by way of a traversal

         of index # 4 without accessing the base table with a residual

         condition of ("(O_C_D.GCD_DAT_SORC_D_X in view

         WEB_S_V.SORC_NM = 'UNKNOWN')

         OR (O_C_D.GCD_DAT_SORC_D_X in view

         WEB_S_V.SORC_NM = 'GLPROD')")

         into Spool 44 (all_amps), which is duplicated on all AMPs.

         Then we do a SORT to order Spool 44 by the hash code of (

         O_C_D.GCD_DAT_SORC_D_X.DATA_SORC_IDN).  The size of

         Spool 44 is estimated with high confidence to be 420 rows (

         8,820 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 Spool 42 (Last Use) by way of

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

         way of an all-rows scan.  Spool 42 and Spool 44 are joined

         using an inclusion merge join, with a join condition of (

         "SRC_IDN = DATA_SORC_IDN").  The result goes into Spool 45

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

         SORT to order Spool 45 by the hash code of (

         O_C_D.GEO_MAIN_D_X.GEO_MAIN_IDN).  The size of Spool 45

         is estimated with low confidence to be 165,900 rows (

         27,373,500 bytes).  The estimated time for this step is 0.04

         seconds.

      2) We do an all-AMPs RETRIEVE step from Spool 38 by way of an

         all-rows scan 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 (

         W_D_T.prt_drg_vendr_contry_d_x.Vendr_Contry_Idn).

         The size of Spool 46 is estimated with low confidence to be

         2,143,779 rows (87,894,939 bytes).  The estimated time for

         this step is 0.08 seconds.

 26) We execute the following steps in parallel.

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

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

         by way of a RowHash match scan.  Spool 45 and Spool 46 are

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

         "(GEO_MAIN_IDN = VENDR_CONTRY_IDN) AND (NOT (VENDR_CONTRY_IDN

         IS NULL ))").  The result goes into Spool 47 (all_amps), which

         is duplicated on all AMPs.  The size of Spool 47 is estimated

         with low confidence to be 1,680 rows (277,200 bytes).  The

         estimated time for this step is 0.04 seconds.

      2) We do an all-AMPs RETRIEVE step from E_D.PRT_VENDOR_D_y

         in view WEB_S_V by way of an

         all-rows scan with no residual conditions locking for read

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

         The size of Spool 48 is estimated with high confidence to be

         1,490,221 rows (31,294,641 bytes).  The estimated time for

         this step is 1.67 seconds.

      3) We do an all-AMPs RETRIEVE step from

         O_C_D.PRT_VENDR_REL_A_X in view

         WEB_S_V by way of an all-rows

         scan with a condition of ("O_C_D.PRT_VENDR_REL_A_X in

         view

         WEB_S_V.PRT_VENDR_REL_TYP_IDN

         = 3.") locking for read into Spool 49 (all_amps), which is

         redistributed by the hash code of (

         O_C_D.PRT_VENDR_REL_A_X.PRT_VENDR_CHLD_IDN) to all AMPs.

         The size of Spool 49 is estimated with low confidence to be

         1,540,497 rows (44,674,413 bytes).  The estimated time for

         this step is 0.76 seconds.

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

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

     all-rows scan.  Spool 48 and Spool 49 are joined using a single

     partition hash join, with a join condition of (

     "PRT_VENDR_CHLD_IDN = VENDOR_IDN").  The result goes into Spool 50

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

     O_C_D.PRT_VENDR_REL_A_X.PRT_VENDR_PRNT_IDN) to all AMPs.

     The size of Spool 50 is estimated with low confidence to be

     1,519,585 rows (44,067,965 bytes).  The estimated time for this

     step is 0.37 seconds.

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

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

     all-rows scan.  Spool 47 and Spool 38 are right outer joined using

     a dynamic hash join, with a join condition of ("Field_1 = Field_2").

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

     by the hash code of (O_C_D.PRT_VENDR_D_X.PRT_VENDR_IDN) to

     all AMPs.  The size of Spool 51 is estimated with low confidence

     to be 2,143,779 rows (379,448,883 bytes).  The estimated time for

     this step is 1.06 seconds.

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

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

     all-rows scan.  Spool 50 and Spool 51 are joined using a single

     partition hash join, with a join condition of (

     "PRT_VENDR_PRNT_IDN = PRT_VENDR_IDN").  The result goes into Spool

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

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

     of Spool 3 is estimated with low confidence to be 1,792,732 rows (

     331,655,420 bytes).  The estimated time for this step is 0.27

     seconds.

 30) We execute the following steps in parallel.

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

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

         redistributed by the hash code of (

         E_D.PRT_VENDOR_D_y.VENDOR_IDN) to all AMPs.  The size of

         Spool 55 is estimated with no confidence to be 182,155 rows (

         61,204,080 bytes).  The estimated time for this step is 0.27

         seconds.

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

         of an all-rows scan into Spool 56 (all_amps) fanned out into 3

         hash join partitions, which is redistributed by the hash code

         of (E_D.PRT_VENDOR_D_y.VENDOR_IDN) to all AMPs.  The size

         of Spool 56 is estimated with low confidence to be 1,792,732

         rows (317,313,564 bytes).  The estimated time for this step is

         0.94 seconds.

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

     RowHash match scan, which is joined to E_D.PRT_VENDOR_D_y in

     view WEB_S_V by way of a RowHash

     match scan with no residual conditions.  Spool 55 and

     E_D.PRT_VENDOR_D_y are right outer joined using a single

     partition hash join, with a join condition of (

     "E_D.PRT_VENDOR_D_y.VENDOR_IDN = PRT_VENDR_IDN").  The result

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

     into 3 hash join partitions.  The size of Spool 57 is estimated

     with no confidence to be 1,490,221 rows (5,492,954,606 bytes).

     The estimated time for this step is 4.09 seconds.

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

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

     all-rows scan.  Spool 56 and Spool 57 are right outer joined using

     a hash join of 3 partitions, with a join condition of (

     "VENDOR_IDN = SUPLYR_IDN").  The result goes into Spool 54

     (group_amps), which is built locally on the AMPs.  The size of

     Spool 54 is estimated with no confidence to be 1,792,732 rows (

     7,022,131,244 bytes).  The estimated time for this step is 4.33

     seconds.

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

     in processing the request.

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

     of statement 1.

 *********************************************EXPLAIN*************************

1 REPLY
Junior Contributor

Re: Spool Space Error

Did you check actual vs. estimated row counts from Viewpoint or DBQL?

A huge difference might indicate wrong stats...