Xplain pln ridiculuous

Database
Enthusiast

Xplain pln ridiculuous

Hi al!

I have a qry whihc not only runs for long but the xplain pln too show ridiculously high run times. I collected recommend ed stats, but did not help. Can you plz help with this one?

SELECT             A1.c1 AS c1 , A1.c2 AS c2 , A1.c3 AS c3 , A1.c4 AS c4 ,                    A1.c5 AS c5 , A1.c6 AS c6 , A1.c7 AS c7 , A1.c8 AS c8 , A1.c9 AS c9 ,                          A1.c10 AS c10 , A1.c11 AS c11 , A1.c12 AS c12 , A1.c13 AS c13 , A1.c14 AS c14 , A1.c15 AS c15 , A1.c16 AS c16 , A1.c17 AS c17 ,A1.c18 AS c18 , A1.c19 AS c19 , A1.c20 AS c20 , A1.c21 AS c21 ,A1.c22 AS c22 , A1.c23 AS c23 , A1.c24 AS c24 , A1.c25 AS c25

FROM  (                                SELECT             DISTINCT tImEsTaMp'2011-11-10 11:30:18' AS c1 , A1.c8 AS c2 ,                              A1.c9 AS c3 , A1.c10 AS c4 , A1.c11 AS c5 , A1.c12 AS c6 , A1.c13 AS c7 ,                        A1.c14 AS c8 , A1.c15 AS c9 , A1.c16 AS c10 , A1.c7 AS c11 ,

                                A1.c6 AS c12 , A1.c17 AS c13 , A1.c4 / ( NULLIF( A1.c2 + A1.c4 ,                    0 )  ) * 100 AS c14 , ( A1.c5 + A1.c4 - A1.c3  ) / ( NULLIF( A1.c2 + A1.c4 + A1.c5 - A1.c3 ,                 0 )  ) * 100 AS c15 , A1.c4 AS c16 , A1.c5 + A1.c4 - A1.c3 AS c17 ,

                                A1.c2 AS c18 , A1.c1 AS c19 , A1.c18 AS c20 , A1.c19 AS c21 ,      A1.c20 AS c22 , A1.c21 AS c23 , A1.c22 AS c24 , A1.c23 AS c25

                                FROM  (SELECT             SUM (

CASE   WHEN ^ I.TOT_HLD_AST IS NULL THEN I.TOT_HLD_AST

ELSE   0

END     ) AS c1 , SUM ( I.EST_HLD_AST ) AS c2 , SUM ( I.OUT_AFT_OUT_BL ) AS c3 ,

                                SUM ( I.OPN_BL ) AS c4 , SUM ( I.IN_AFT_OUT_BL ) AS c5 ,

                                I.NP_CLT_ID AS c6 , I.NP_PLN_ID AS c7 , D.DR_LGIN AS c8 ,

                                D.DR_REG AS c9 , D.EM_LGIN AS c10 , B.X_PIN AS c11 ,

                                A.LST_NM AS c12 , A.FST_NM AS c13 , G.X_PIN AS c14 ,

                                F.LST_NM AS c15 , F.FST_NM AS c16 , H.DAY_DT AS c17 ,

                                CASE

                WHEN B.X_ACT_FLG = 'Y' THEN 'ACT'

ELSE   'InACT'

END     AS c18 ,

CASE   WHEN B.M_SG_CD = 'a' THEN 'High Net Worth'

ELSE   CASE

                WHEN B.M_SG_CD = 'b' THEN 'Affluent I'

ELSE   CASE

                WHEN B.M_SG_CD = 'c' THEN 'Affluent II'

END     END

END     AS c19 ,

CASE   WHEN B.AR_RSK_CAT = 'h' THEN 'High'

ELSE   CASE

                WHEN B.AR_RSK_CAT = 'l' THEN 'Low'

ELSE   CASE

                WHEN B.AR_RSK_CAT = 'm' THEN 'Medium'

ELSE   CASE

                WHEN B.AR_RSK_CAT = 'U' THEN 'Unspecified'

END     END

END     END AS c20 , B.X_IM_TOTAL_AST AS c21 , H.ROW AS c22 ,

                                F.ROW AS c23

FROM  TABLEA A , TABLEB B LEFT OUTER JOIN TABLEC  C

                ON         B.FNX_ASN_UPD_BY_ID  = C.INTGN_ID ,

                                TABLED  D , TABLEE  E , TABLEA F ,

                                TABLEB G , TABLEH D H , TABLEI  I

WHERE             ( A.ROW = B.ROW

                AND     A.ROW = I.CNTCT

                AND     A.VIS_PR_POS_ID = D.INTGN_ID

                AND     D.ROW = E.ROW

                AND     H.ROW = I.PLANDT

                AND     F.ROW = G.ROW

                AND     F.ROW = I.CO_CLT )

GROUP              BY c13,c12 , c11 ,             c21 , D.EM_LGIN , D.DR_LGIN ,  D.DR_REG , H.ROW , H.DAY_DT , F.FST_NM ,

                                F.LST_NM , F.ROW , G.X_PIN , I.NP_PLN_ID ,                      I.NP_CLT_ID ,

                                CASE   WHEN B.M_SG_CD = 'a' THEN 'High Net Worth'

ELSE   CASE

                WHEN B.M_SG_CD = 'b' THEN 'Affluent I'

ELSE   CASE

                WHEN B.M_SG_CD = 'c' THEN 'Affluent II'

END     END

END     ,

CASE   WHEN B.AR_RSK_CAT = 'h' THEN 'High'

ELSE   CASE

                WHEN B.AR_RSK_CAT = 'l' THEN 'Low'

ELSE   CASE

                WHEN B.AR_RSK_CAT = 'm' THEN 'Medium'

ELSE   CASE

                WHEN B.AR_RSK_CAT = 'U' THEN 'Unspecified'

END     END

END     END ,

CASE   WHEN B.X_ACT_FLG = 'Y' THEN 'ACT'

ELSE   'InACT'

END     ) A1 ) A1

After secondary index creation

1) First, we lock TABLEA for access, we lock

     TABLEE  for access, we lock CFM for access, we

     lock TABLED  for access, we lock TABLEB

     for access, we lock TABLEC  for access, we lock

     TABLEH D for access, and we lock T1 for access.

  2) Next, we execute the following steps in parallel.

       1) We do an all-AMPs RETRIEVE step from CFM by way of an

          all-rows scan with a condition of ((CFM.VAL =

          'QUALIFIED') AND (CFM.TYPE = 'NP_QUAL_FLG')) into

          Spool 4 (all_amps), which is duplicated on all AMPs.  The

          size of Spool 4 is estimated with low confidence to be 150

          rows.  The estimated time for this step is 0.01 seconds.

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

          all-rows scan with no residual conditions into Spool 5

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

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

          Spool 5 is estimated with no confidence to be 711,434 rows.

  3) We execute the following steps in parallel.

       1) We do an all-AMPs RETRIEVE step from Spool 5 by way of an

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

          all AMPs.  The result spool file will not be cached in memory.

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

          106,715,100 rows.  The estimated time for this step is 16.31

          seconds.

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

          all-rows scan with no residual conditions locking for access

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

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

          711,434 rows.  The estimated time for this step is 0.18

          seconds.

  4) We execute the following steps in parallel.

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

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

          way of an all-rows scanSpool 7 and Spool 6 are left outer

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

          (1=0)).  The result goes into Spool 8 (all_amps), which is

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

          order Spool 8 by row hash.  The result spool file will not be

          cached in memory.  The size of Spool 8 is estimated with high

          confidence to be 506,138,336,356 rows.  The estimated time

          for this step is 1,517 hours and 33 minutes.

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

          an all-rows scan, which is joined to CFM by way of an

          all-rows scan with a condition of ((CFM.VAL =

          'NON_QUALIFIED') AND (CFM.TYPE = 'NP_NON_QUAL_FLG'))

          locking CFM for accessSpool 4 and CFM are

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

          (1=1)).  The result goes into Spool 9 (all_amps), which is

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

          with low confidence to be 150 rows.  The estimated time for

          this step is 0.01 seconds.

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

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

     of a RowHash match scan.  Spool 8 and Spool 5 are full outer

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

     Field_1).  The result goes into Spool 10 (all_amps), which is

     built locally on the AMPs.  The result spool file will not be

     cached in memory.  The size of Spool 10 is estimated with high

     confidence to be 506,138,336,356 rows.  The estimated time for

     this step is 27 hours and 3 minutes.

  6) We execute the following steps in parallel.

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

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

          way of an all-rows scanSpool 9 and Spool 10 are joined

          using a product join, with a join condition of ((1=1)).

          The result goes into Spool 1 (all_amps), which is

          redistributed by hash code to all AMPs.  The result spool

          file will not be cached in memory.  The size of Spool 1 is

          estimated with low confidence to be 506,138,336,356 rows.

          The estimated time for this step is 97 hours and 24 minutes.

       2) We do an all-AMPs RETRIEVE step from TABLEC  by

          way of a traversal of index # 4 without accessing the base

          table with no residual conditions into Spool 15 (all_amps),

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

          Spool 15 by row hash.  The size of Spool 15 is estimated with

          high confidence to be 5,461,650 rows.  The estimated time for

          this step is 0.47 seconds.

       3) We do an all-AMPs JOIN step from TABLEA by way of a

          RowHash match scan with a condition of (NOT

          (TABLEA.VIS_PR_POS_ID IS NULL)), which is joined

          to TABLEB by way of a RowHash match scan with no

          residual conditions.  TABLEA and TABLEB

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

          TABLEA.ROW = TABLEB.ROW).  The

          input tables TABLEA and TABLEB will not

          be cached in memory, but TABLEA is eligible for

          synchronized scanning.  The result goes into Spool 16

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

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

          estimated with low confidence to be 8,281,174 rows.  The

          estimated time for this step is 33.71 seconds.

       4) We do an all-AMPs JOIN step from TABLED  by

          way of a RowHash match scan with no residual conditions,

          which is joined to TABLEE  by way of a RowHash

          match scan with no residual conditions.

          TABLED  and TABLEE  are joined

          using a merge join, with a join condition of (

          TABLED .ROW = TABLEE .ROW).

          The result goes into Spool 17 (all_amps), which is duplicated

          on all AMPs into 2 hash join partitions.  The size of Spool

          17 is estimated with low confidence to be 1,815,150 rows.

          The estimated time for this step is 0.26 seconds.

  7) We execute the following steps in parallel.

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

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

          Use) by way of a RowHash match scan.  Spool 15 and Spool 16

          are right outer joined using a merge join, with a join

          condition of (FNX_ASN_UPD_BY_ID = INTGN_ID).

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

          locally on the AMPs into 2 hash join partitions.  The size of

          Spool 18 is estimated with low confidence to be 8,281,629

          rows.  The estimated time for this step is 2.12 seconds.

       2) We do an all-AMPs RETRIEVE step from TABLEH D by way of

          an all-rows scan with no residual conditions into Spool 21

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

          Spool 21 is estimated with high confidence to be 3,962,100

          rows.  The estimated time for this step is 0.12 seconds.

       3) We do an all-AMPs JOIN step from TABLEA by way of a

          RowHash match scan with no residual conditions, which is

          joined to TABLEB by way of a RowHash match scan

          with no residual conditions locking TABLEA for

          access and TABLEB for accessTABLEA

          and TABLEB are joined using a merge join, with a

          join condition of (TABLEA.ROW =

          TABLEB.ROW).  The input tables

          TABLEA and TABLEB will not be cached in

          memory, but TABLEA is eligible for synchronized

          scanning.  The result goes into Spool 22 (all_amps), which is

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

          with low confidence to be 12,511,148 rows.  The estimated

          time for this step is 32.66 seconds.

  8) We execute the following steps in parallel.

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

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

          by way of an all-rows scanSpool 21 and Spool 22 are joined

          using a product join, with a join condition of ((1=1)).

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

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

          row hash.  The result spool file will not be cached in memory.

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

          330,469,463,272 rows.  The estimated time for this step is 41

          hours and 3 minutes.

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

          way of an all-rows scan with a condition of ((NOT

          (CO_CLT IS NULL )) AND ((NOT (PLANDT IS NULL ))

          AND (NOT (CNTCT IS NULL )))) into Spool 24 (all_amps),

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

          a SORT to order Spool 24 by row hash.  The result spool file

          will not be cached in memory.  The size of Spool 24 is

          estimated with low confidence to be 506,138,336,356 rows.

          The estimated time for this step is 121 hours and 42 minutes.

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

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

     all-rows scanSpool 17 and Spool 18 are joined using a hash join

     of 2 partitions, with a join condition of (VIS_PR_POS_ID =

     INTGN_ID).  The result goes into Spool 25 (all_amps), which

     is duplicated on all AMPs.  Then we do a SORT to order Spool 25 by

     row hash.  The result spool file will not be cached in memory.

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

     1,242,244,350 rows.  The estimated time for this step is 9 minutes

     and 33 seconds.

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

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

     of a RowHash match scan.  Spool 23 and Spool 24 are joined using a

     merge join, with a join condition of ((ROW = CO_CLT)

     AND ((CO_CLT = ROW) AND (ROW = PLANDT ))).

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

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

     The result spool file will not be cached in memory.  The size of

     Spool 26 is estimated with index join confidence to be

     506,138,336,356 rows.  The estimated time for this step is 123

     hours and 57 minutes.

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

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

     of a RowHash match scan.  Spool 25 and Spool 26 are joined using a

     merge join, with a join condition of ((CNTCT = ROW) AND

     (ROW = CNTCT)).  The result goes into Spool 14

     (all_amps), which is built locally on the AMPs.  The result spool

     file will not be cached in memory.  The size of Spool 14 is

     estimated with index join confidence to be 506,138,336,356 rows.

     The estimated time for this step is 35 hours and 21 minutes.

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

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

     Aggregate Intermediate Results are computed globally, then placed

     in Spool 27.  The aggregate spool file will not be cached in

     memory.  The size of Spool 27 is estimated with no confidence to

     be 379,603,752,267 rows.  The estimated time for this step is

     1,400 hours and 27 minutes.

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

     an all-rows scan into Spool 2 (all_amps), which is built locally

     on the AMPs.  The result spool file will not be cached in memory.

     The size of Spool 2 is estimated with no confidence to be

     379,603,752,267 rows.  The estimated time for this step is 25

     hours and 57 minutes.

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

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

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

     the sort key in spool fielA1 eliminating duplicate rows.  The

     result spool file will not be cached in memory.  The size of Spool

     3 is estimated with no confidence to be 379,603,752,267 rows.  The

     estimated time for this step is 232 hours and 56 minutes.

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

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

     locally on the AMPs.  The result spool file will not be cached in

     memory.  The size of Spool 29 is estimated with no confidence to

     be 379,603,752,267 rows.  The estimated time for this step is 28

     hours and 3 minutes.

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

     in processing the request.

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

     of statement 1.

3 REPLIES
Senior Apprentice

Re: Xplain pln ridiculuous

There are several product joins with (1=1), which indicate missing join conditions.

Additionally query and explain don't match:

there are 3 outer joins in explain, but only 1 in your query,

Dieter

Enthusiast

Re: Xplain pln ridiculuous

You are right. TableI is actually a view defined as below which cause the prdct join. Any suggestions on how to chang the view def without impacting functionality?

Sel …. FROM  

(SELECT  C.ROW  FROM  IND_D C WHERE C.TYP='NP_NON_FLG' AND C.VAL ='NON_QLFD') AB,

(SELECT  C.ROW FROM  IND_D C WHERE C.TYP='NP_FLG' AND C.VAL ='QLFD') EF,

PLN_MTHLY_F T1 FULL JOIN PLN_MTHLY_F T2  ON 1=2) D;

Senior Apprentice

Re: Xplain pln ridiculuous

Which functionality?

I don't have a clue what this view is supposed to return:

Cross joins plus a strange outer join...

Dieter