All Forums Database
ap90792 11 posts Joined 08/14
20 Feb 2015
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** ***********************

dnoeth 4628 posts Joined 11/04
23 Feb 2015

Did you check actual vs. estimated row counts from Viewpoint or DBQL?
A huge difference might indicate wrong stats...

Dieter

You must sign in to leave a comment.