All Forums Database
hyma 33 posts Joined 07/11
29 Jan 2014
Aggregated Join Index on 2 systems

I have an aggregated join idex defined by joining a big table (fact) with some small ones (dim).
Queries running on system 1 (dev) make use of this index. But not on system 2 (prd). Statistics on both systems are the same. 
What could be the reasons why the optimizer didn't use the index on system 2? Thanks.

Raja_KT 1246 posts Joined 07/09
29 Jan 2014

With this much info, it is hard to say. It can be due to many reasons,Data  demography,freshness of stats, though they are same etc. Maybe you can share the explain of queries, masking important infos.
Cheers, 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

M.Saeed Khurram 544 posts Joined 09/12
30 Jan 2014

Hyma,
Optmizer makes its decisions based on many factors; it considers system configurations, stats, sql code, data demographics etc. So on one machine it might consider JI as a faster approach, and on the other hand, on the Prod machine there might be a different system configuration that lead to some other execution plan better than JI. 
 
Can you please share the explain plan?
 

Khurram

hyma 33 posts Joined 07/11
30 Jan 2014

Same Config on 2 systems: TD 14.00; 46AMPs; DDLs on 2 system identical; STATs freshly collected.  
Explain with Aggregate Join Index being used:
1) First, we lock DB_CONTROL.secrole in view
     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we
     lock DB_CONTROL.secrolemap in view
     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we
     lock DB_DWH.AJI_01 in view
     DB_VIEWDWH.FAK_BIG for access, we lock
     DB_DWH.DIM_STD in view
     DB_VIEWDWH.DIM_STD for access, we lock
     DB_DWH.FAK_BIG in view DB_VIEWDWH.FAK_BIG
     for access, we lock DB_DWH.DIM_SPUR in view
     DB_VIEWDWH.DIM_SPUR for access, we lock
     DB_DWH.DIM_MESS in view
     DB_VIEWDWH.DIM_MESS for access, we lock
     DB_DWH.DIM_DAT_MAND in view
     DB_VIEWDWH.DIM_DAT_MAND for access, and we lock
     DB_DWH.MV_DIM_FZGKL_PIVOT in view
     DB_VIEWDWH.MV_DIM_FZGKL_PI for access.
  2) Next, we execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view
          DB_VIEWDWH.MV_DIM_FZGKL_PI by way of an
          all-rows scan with no residual conditions into Spool 4
          (all_amps), which is duplicated on all AMPs.  The size of
          Spool 4 is estimated with high confidence to be 2,806 rows (
          145,912 bytes).  The estimated time for this step is 0.01
          seconds.
       2) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view
          DB_VIEWDWH.DIM_DAT_MAND by way of a
          traversal of index # 4 without accessing the base table with
          a residual condition of ("(DB_DWH.dt in view
          DB_VIEWDWH.DIM_DAT_MAND.YEAR_NR = 2012) AND
          (DB_DWH.dt in view
          DB_VIEWDWH.DIM_DAT_MAND.MAND_BEZ = ‘ZZZ’)")
          into Spool 5 (all_amps), which is duplicated on all AMPs.
          The size of Spool 5 is estimated with no confidence to be
          32,062 rows (1,122,170 bytes).  The estimated time for this
          step is 0.01 seconds.
  3) We do an all-AMPs JOIN step from DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS by way of an all-rows scan
     with a condition of ("(NOT (DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.KT_BEZ IS NULL )) AND
     ((DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.MAND_BEZ = ‘ZZZ’) AND
     ((DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.MESS_TYP_DE_BEZ LIKE
     '%Volumen%') OR ((DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.MESS_TYP_DE_BEZ LIKE
     '%Island%') OR (DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.MESS_NR_BEZ =
     '158'))))"), which is joined to Spool 4 (Last Use) by way of an
     all-rows scanDB_DWH.dt and Spool 4 are joined using a
     product join, with a join condition of (
     "((DB_DWH.dt.MESS_NR_BEZ = '158') OR ((NOT
     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')) OR
     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')))
     AND ((DB_DWH.dt.MESS_NR_BEZ = '158') OR
     (((DB_DWH.dt.MESS_TYP_DE_BEZ LIKE '%Island%') OR (NOT
     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')))
     AND (((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')
     OR (DB_DWH.dt.MESS_TYP_DE_BEZ LIKE '%Volumen%'))))").
     The result goes into Spool 6 (all_amps), which is duplicated on
     all AMPs.  The size of Spool 6 is estimated with no confidence to
     be 20,654 rows (6,733,204 bytes).  The estimated time for this
     step is 0.02 seconds.
  4) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an
     all-rows scan, which is joined to a single partition of
     DB_DWH.AJI_01 in view
     DB_VIEWDWH.FAK_BIG with a condition of (
     "DB_DWH.AJI_01 in view
     DB_VIEWDWH.FAK_BIG.MESSTYP_ID  = 1").  Spool 5
     and DB_DWH.AJI_01 are joined using a dynamic hash
     join, with a join condition of (
     "(DB_DWH.AJI_01.MAND_BEZ = MAND_BEZ) AND
     (DB_DWH.AJI_01.DIM_DAT_MAND = DIMENSION_KEY)").
     The input table DB_DWH.AJI_01 will not be cached in
     memory.  The result goes into Spool 7 (all_amps), which is built
     locally on the AMPs.  The size of Spool 7 is estimated with no
     confidence to be 24,029,188 rows (1,658,013,972 bytes).  The
     estimated time for this step is 34.72 seconds.
  5) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view
     DB_VIEWDWH.DIM_SPUR by way of a traversal of
     index # 4 without accessing the base table with a residual
     condition of ("(DB_DWH.dt in view
     DB_VIEWDWH.DIM_SPUR.STAT_NR = 4) OR
     (DB_DWH.dt in view
     DB_VIEWDWH.DIM_SPUR.STAT_NR = 3)") into
     Spool 8 (all_amps), which is duplicated on all AMPs.  The size of
     Spool 8 is estimated with no confidence to be 874 rows (14,858
     bytes).  The estimated time for this step is 0.01 seconds.
  6) We do an all-AMPs JOIN step from DB_DWH.dt in view
     DB_VIEWDWH.DIM_STD by way of an all-rows scan with
     no residual conditions, which is joined to Spool 8 (Last Use) by
     way of an all-rows scanDB_DWH.dt and Spool 8 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 no confidence to be
     40,204 rows (1,969,996 bytes).  The estimated time for this step
     is 0.01 seconds.
  7) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
     all-rows scan, which is joined to Spool 7 (Last Use) by way of an
     all-rows scanSpool 6 and Spool 7 are joined using a single
     partition hash join, with a join condition of ("(DIMENSION_KEY =
     DIM_FZG) AND ((MAND_BEZ = MAND_BEZ) AND
     ((DIM_MESS = DIMENSION_KEY) AND (MAND_BEZ = MAND_BEZ
     )))").  The result goes into Spool 10 (all_amps), which is built
     locally on the AMPs.  The size of Spool 10 is estimated with no
     confidence to be 1,981,471 rows (685,588,966 bytes).  The
     estimated time for this step is 1.39 seconds.
  8) 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 single
     partition hash join, with a join condition of ("(DIM_SPUR =
     DIMENSION_KEY) AND (DIM_STD = DIMENSION_KEY)").  The result
     goes into Spool 3 (all_amps), which is built locally on the AMPs.
     The size of Spool 3 is estimated with no confidence to be
     3,065,143 rows (1,134,102,910 bytes).  The estimated time for this
     step is 1.45 seconds.
  9) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
     way of an all-rows scan , grouping by field1 (
     DB_DWH.dt.MAND_BEZ ,( CASE WHEN
     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN
     ('boimg://Logi_ZZZ.gif') ELSE
     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')
     END) ,DB_DWH.dt.MESS_NR_BEZ
     ,DB_DWH.dt.MESS_BEZ ,DB_DWH.dt.STR_BEZ
     ,DB_DWH.dt.KT_BEZ ,DB_DWH.dt.RICT_1_BEZ
     ,DB_DWH.dt.RICT_2_BEZ ,DB_DWH.dt.X_COR_NR
     ,DB_DWH.dt.Y_COR_NR ,DB_DWH.dt.YEAR_NR
     ,DB_DWH.dt.STD_BEZ,DB_DWH.dt.STD_NR - 1
     ,(DB_DWH.dt.FKL_HIER_LVL  ||' -
     ')||DB_DWH.dt.FKL_DE_BEZ
     ,DB_DWH.dt.MESS_TYP_NR
     ,DB_DWH.dt.MESS_TYP_DE_BEZ
     ,DB_DWH.AJI_01.DIM_DAT_MAND).  Aggregate
     Intermediate Results are computed globally, then placed in Spool
     12.  The size of Spool 12 is estimated with no confidence to be
     2,298,858 rows (3,388,516,692 bytes).  The estimated time for this
     step is 1.30 seconds.
 10) We do an all-AMPs SUM step to aggregate from Spool 12 (Last Use)
     by way of an all-rows scan , grouping by field1 (
     DB_DWH.dt.MAND_BEZ ,( CASE WHEN
     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN
     ('boimg://Logi_ZZZ.gif') ELSE
     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')
     END) ,DB_DWH.dt.MESS_NR_BEZ
     ,DB_DWH.dt.MESS_BEZ ,DB_DWH.dt.STR_BEZ
     ,DB_DWH.dt.KT_BEZ ,DB_DWH.dt.RICT_1_BEZ
     ,DB_DWH.dt.RICT_2_BEZ ,DB_DWH.dt.X_COR_NR
     ,DB_DWH.dt.Y_COR_NR ,DB_DWH.dt.YEAR_NR
     ,DB_DWH.dt.STD_BEZ,DB_DWH.dt.STD_NR - 1
     ,(DB_DWH.dt.FKL_HIER_LVL  ||' -
     ')||DB_DWH.dt.FKL_DE_BEZ
     ,DB_DWH.dt.MESS_TYP_NR
     ,DB_DWH.dt.MESS_TYP_DE_BEZ).  Aggregate Intermediate
     Results are computed globally, then placed in Spool 14.  The size
     of Spool 14 is estimated with no confidence to be 1,724,144 rows (
     2,527,595,104 bytes).  The estimated time for this step is 1.61
     seconds.
 11) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is redistributed
     by the hash code of (DB_DWH.dt.MESS_TYP_DE_BEZ,
     DB_DWH.dt.MESS_TYP_NR,
     (DB_DWH.dt.FKL_HIER_LVL  ||' -
     ')||DB_DWH.dt.FKL_DE_BEZ,
     COUNT(DB_DWH.AJI_01.DIM_DAT_MAND )(INTEGER),
     SUM(DB_DWH.AJI_01.ANZ_FZG )(BIGINT),
     DB_DWH.dt.STD_NR - 1, DB_DWH.dt.STD_BEZ,
     DB_DWH.dt.YEAR_NR, DB_DWH.dt.Y_COR_NR,
     DB_DWH.dt.X_COR_NR, DB_DWH.dt.RICT_2_BEZ,
     DB_DWH.dt.RICT_1_BEZ, DB_DWH.dt.KT_BEZ,
     DB_DWH.dt.STR_BEZ, DB_DWH.dt.MESS_BEZ,
     DB_DWH.dt.MESS_NR_BEZ, (CASE WHEN
     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN
     ('boimg://Logi_ZZZ.gif') ELSE
     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')
     END), DB_DWH.dt.MAND_BEZ) to all AMPs.  Then we do a
     SORT to order Spool 1 by the sort key in spool field1 eliminating
     duplicate rows.  The size of Spool 1 is estimated with no
     confidence to be 1,724,144 rows (2,218,973,328 bytes).  The
     estimated time for this step is 7.57 seconds.
 12) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 48.08 seconds.
 
Explain with Aggregate Join Index NOT being used:
This query is optimized using type 2 profile T2_Linux64, profileid 21.
  1) First, we lock DB_CONTROL.secrole in view
     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we
     lock DB_CONTROL.secrolemap in view
     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we
     lock DB_DWH.MV_DIM_FZGKL_PIVOT in view
     DB_VIEWDWH.MV_DIM_FZGKL_PI for access, we
     lock DB_DWH.FAK_BIG in view
     DB_VIEWDWH.FAK_BIG for access, we lock
     DB_DWH.DIM_STD in view
     DB_VIEWDWH.DIM_STD for access, we lock
     DB_DWH.DIM_SPUR in view
     DB_VIEWDWH.DIM_SPUR for access, we lock
     DB_DWH.DIM_MESS in view
     DB_VIEWDWH.DIM_MESS for access, and we lock
     DB_DWH.DIM_DAT_MAND  in view
     DB_VIEWDWH.DIM_DAT_MAND  for access.
  2) Next, we execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view
          DB_VIEWDWH.MV_DIM_FZGKL_PI by way of an
          all-rows scan with no residual conditions into Spool 18
          (all_amps), which is duplicated on all AMPs.  The size of
          Spool 18 is estimated with high confidence to be 2,806 rows (
          145,912 bytes).  The estimated time for this step is 0.01
          seconds.
       2) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view
          DB_VIEWDWH.DIM_DAT_MAND  by way of a
          traversal of index # 4 without accessing the base table with
          a residual condition of ("(DB_DWH.dt in view
          DB_VIEWDWH.DIM_DAT_MAND .YEAR_NR = 2012) AND
          (DB_DWH.dt in view
          DB_VIEWDWH.DIM_DAT_MAND .MAND_BEZ = ‘ZZZ’)")
          into Spool 19 (all_amps), which is duplicated on all AMPs.
          Then we do a SORT to partition by rowkey.  The size of Spool
          19 is estimated with low confidence to be 16,836 rows (
          589,260 bytes).  The estimated time for this step is 0.01
          seconds.
       3) We do an all-AMPs RETRIEVE step from DB_DWH.dt in view
          DB_VIEWDWH.DIM_STD by way of an all-rows scan
          with no residual conditions into Spool 20 (all_amps), which
          is duplicated on all AMPs.  The size of Spool 20 is estimated
          with high confidence to be 1,150 rows (51,750 bytes).  The
          estimated time for this step is 0.01 seconds.
  3) We do an all-AMPs JOIN step from DB_DWH.dt in view
     DB_VIEWDWH.DIM_SPUR by way of a traversal of
     index # 4 without accessing the base table with a residual
     condition of ("(DB_DWH.dt in view
     DB_VIEWDWH.DIM_SPUR.STAT_NR = 4) OR
     (DB_DWH.dt in view
     DB_VIEWDWH.DIM_SPUR.STAT_NR = 3)"), which is
     joined to Spool 20 (Last Use) by way of an all-rows scan.
     DB_DWH.dt and Spool 20 are joined using a product join,
     with a join condition of ("(1=1)").  The result goes into Spool 21
     (all_amps), which is duplicated on all AMPs.  The size of Spool 21
     is estimated with high confidence to be 69,000 rows (3,381,000
     bytes).  The estimated time for this step is 0.01 seconds.
  4) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an
     all-rows scan, which is joined to DB_DWH.dt in view
     DB_VIEWDWH.FAK_BIG with a condition of (
     "(DB_DWH.dt in view
     DB_VIEWDWH.FAK_BIG.MESSTYP_ID  = 1) AND
     (DB_DWH.dt in view
     DB_VIEWDWH.FAK_BIG.MAND_BEZ = ‘ZZZ’)").  Spool 19
     and DB_DWH.dt are joined using a dynamic hash join, with a
     join condition of ("(DB_DWH.dt.DIM_DAT_MAND  =
     DIMENSION_KEY) AND (DB_DWH.dt.MAND_BEZ = MAND_BEZ)")
     enhanced by dynamic partition elimination.  The input table
     DB_DWH.dt will not be cached in memory.  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,286,408
     rows (946,053,416 bytes).  The estimated time for this step is
     3.24 seconds.
  5) We do an all-AMPs JOIN step from DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS by way of an all-rows scan
     with a condition of ("(NOT (DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.KT_BEZ IS NULL )) AND
     ((DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.MAND_BEZ = ‘ZZZ’) AND
     ((DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.MESS_TYP_DE_BEZ LIKE
     '%Volumen%') OR ((DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.MESS_TYP_DE_BEZ LIKE
     '%Island%') OR (DB_DWH.dt in view
     DB_VIEWDWH.DIM_MESS.MESS_NR_BEZ =
     '158'))))"), which is joined to Spool 18 (Last Use) by way of an
     all-rows scanDB_DWH.dt and Spool 18 are joined using a
     product join, with a join condition of (
     "((DB_DWH.dt.MESS_NR_BEZ = '158') OR ((NOT
     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')) OR
     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')))
     AND ((DB_DWH.dt.MESS_NR_BEZ = '158') OR
     (((DB_DWH.dt.MESS_TYP_DE_BEZ LIKE '%Island%') OR (NOT
     ((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')))
     AND (((FKL_HIER_LVL  ||' - ')||FKL_DE_BEZ LIKE 'Island 10%')
     OR (DB_DWH.dt.MESS_TYP_DE_BEZ LIKE '%Volumen%'))))").
     The result goes into Spool 23 (all_amps), which is duplicated on
     all AMPs into 3 hash join partitions.  The size of Spool 23 is
     estimated with no confidence to be 184,322 rows (60,088,972 bytes).
     The estimated time for this step is 0.06 seconds.
  6) 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 single
     partition hash join, with a join condition of ("(DIM_STD =
     DIMENSION_KEY) AND (DIM_SPUR = DIMENSION_KEY)").  The
     result goes into Spool 24 (all_amps), which is built locally on
     the AMPs into 3 hash join partitions.  The size of Spool 24 is
     estimated with low confidence to be 19,117,855 rows (
     1,892,667,645 bytes).  The estimated time for this step is 2.95
     seconds.
  7) We do an all-AMPs JOIN step from Spool 23 (Last Use) by way of an
     all-rows scan, which is joined to Spool 24 (Last Use) by way of an
     all-rows scanSpool 23 and Spool 24 are joined using a hash join
     of 3 partitions, with a join condition of ("(DIMENSION_KEY =
     DIM_FZG) AND ((DIM_MESS = DIMENSION_KEY) AND
     ((MAND_BEZ = MAND_BEZ) AND (MAND_BEZ = MAND_BEZ )))").
     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 14,125,991 rows (5,339,624,598 bytes).  The estimated time
     for this step is 5.85 seconds.
  8) We do an all-AMPs SUM step to aggregate from Spool 17 (Last Use)
     by way of an all-rows scan , grouping by field1 (
     DB_DWH.dt.MAND_BEZ ,( CASE WHEN
     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN
     ('boimg://Logi_ZZZ.gif') ELSE
     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')
     END) ,DB_DWH.dt.MESS_NR_BEZ
     ,DB_DWH.dt.MESS_BEZ ,DB_DWH.dt.STR_BEZ
     ,DB_DWH.dt.KT_BEZ ,DB_DWH.dt.RICT_1_BEZ
     ,DB_DWH.dt.RICT_2_BEZ ,DB_DWH.dt.X_COR_NR
     ,DB_DWH.dt.Y_COR_NR ,DB_DWH.dt.YEAR_NR
     ,DB_DWH.dt.STD_BEZ,DB_DWH.dt.STD_NR - 1
     ,(DB_DWH.dt.FKL_HIER_LVL  ||' -
     ')||DB_DWH.dt.FKL_DE_BEZ
     ,DB_DWH.dt.MESS_TYP_NR
     ,DB_DWH.dt.MESS_TYP_DE_BEZ
     ,DB_DWH.dt.DIM_DAT_MAND ).  Aggregate Intermediate
     Results are computed globally, then placed in Spool 26.  The
     aggregate spool file will not be cached in memory.  The size of
     Spool 26 is estimated with no confidence to be 10,594,494 rows (
     15,616,284,156 bytes).  The estimated time for this step is 5.51
     seconds.
  9) We do an all-AMPs SUM step to aggregate from Spool 26 (Last Use)
     by way of an all-rows scan , grouping by field1 (
     DB_DWH.dt.MAND_BEZ ,( CASE WHEN
     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN
     ('boimg://Logi_ZZZ.gif') ELSE
     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')
     END) ,DB_DWH.dt.MESS_NR_BEZ
     ,DB_DWH.dt.MESS_BEZ ,DB_DWH.dt.STR_BEZ
     ,DB_DWH.dt.KT_BEZ ,DB_DWH.dt.RICT_1_BEZ
     ,DB_DWH.dt.RICT_2_BEZ ,DB_DWH.dt.X_COR_NR
     ,DB_DWH.dt.Y_COR_NR ,DB_DWH.dt.YEAR_NR
     ,DB_DWH.dt.STD_BEZ,DB_DWH.dt.STD_NR - 1
     ,(DB_DWH.dt.FKL_HIER_LVL  ||' -
     ')||DB_DWH.dt.FKL_DE_BEZ
     ,DB_DWH.dt.MESS_TYP_NR
     ,DB_DWH.dt.MESS_TYP_DE_BEZ).  Aggregate Intermediate
     Results are computed globally, then placed in Spool 28.  The
     aggregate spool file will not be cached in memory.  The size of
     Spool 28 is estimated with no confidence to be 7,945,871 rows (
     11,648,646,886 bytes).  The estimated time for this step is 8.39
     seconds.
 10) We do an all-AMPs RETRIEVE step from Spool 28 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is redistributed
     by the hash code of (DB_DWH.dt.MESS_TYP_DE_BEZ,
     DB_DWH.dt.MESS_TYP_NR,
     (DB_DWH.dt.FKL_HIER_LVL  ||' -
     ')||DB_DWH.dt.FKL_DE_BEZ,
     COUNT(DB_DWH.dt.DIM_DAT_MAND  )(INTEGER), SUM(( CASE
     WHEN (DB_DWH.dt.MESSTYP_ID  = 1) THEN
     (DB_DWH.dt.ANZ_FZG) ELSE (NULL) END )),
     DB_DWH.dt.STD_NR - 1, DB_DWH.dt.STD_BEZ,
     DB_DWH.dt.YEAR_NR, DB_DWH.dt.Y_COR_NR,
     DB_DWH.dt.X_COR_NR, DB_DWH.dt.RICT_2_BEZ,
     DB_DWH.dt.RICT_1_BEZ, DB_DWH.dt.KT_BEZ,
     DB_DWH.dt.STR_BEZ, DB_DWH.dt.MESS_BEZ,
     DB_DWH.dt.MESS_NR_BEZ, (CASE WHEN
     (DB_DWH.dt.MAND_BEZ = ‘ZZZ’) THEN
     ('boimg://Logi_ZZZ.gif') ELSE
     (('boimg://Logi_'||DB_DWH.dt.MAND_BEZ )||'.GIF')
     END), DB_DWH.dt.MAND_BEZ) to all AMPs.  Then we do a
     SORT to order Spool 1 by the sort key in spool field1 eliminating
     duplicate rows.  The size of Spool 1 is estimated with no
     confidence to be 7,945,871 rows (10,226,335,977 bytes).  The
     estimated time for this step is 45.30 seconds.
 11) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 1 minute and 11 seconds.
 
 

dnoeth 4628 posts Joined 11/04
30 Jan 2014

The stats are freshly collected, but not the same, e.g. step 2.2 (preparation for the join to AJI):
   with low confidence to be 16,836 rows  (AJI not used)
vs.
    no confidence to be 32,062 rows (AJI used)

Dieter

hyma 33 posts Joined 07/11
30 Jan 2014

You're right, Dieter! On system 2 (no AJI) there are 3 small tables having STATS. On system 1 (AJI used) the tables have no STATS.
I dropped STATS on system 2. The Query is now running using AJI!
Curious, isn't it? Having STATS collected didn't help.
 

dnoeth 4628 posts Joined 11/04
30 Jan 2014

Did you compare CPU/IO from DBQL on both systems, did the AJI actually use less/run faster?
There's a lot of "no confidence" in both explains, did you check for missing stats using DIAGNOSTIC HELPSTATS?

Dieter

You must sign in to leave a comment.