Performance issue. Please help

Database
Enthusiast

Performance issue. Please help

 

Hi, 

 

I am facing issue with one of the query. It is not completing and keeps running. 

This was an existing query which will take 2-3 mins to complete.

I have added one inner join to this existing query in order to update the records that are only present in my temp table.

Existing query and modified query with its explain plan i have pasted below. 

Could some one please help me finding the issue here.

 

Old Query: 

 

UPDATE target
FROM promoexst_store_tot_trad_perc target,
(SELECT offer_number
, base_product_number_std
, retail_outlet_number
, MAX(total_cycle_trading_perc) AS final_total_cycle_trading_perc
FROM ( SELECT offer_number
, base_product_number_std
, base_product_number
, retail_outlet_number
, NULLIFZERO(SUM(wk_lvl_total_sf)) / 100 AS total_cycle_trading_perc
FROM ( SELECT pk.offer_number
, pk.base_product_number_std
, pd.base_product_number
, prf.retail_outlet_number
/* groups sum of ptps at week level */
, ((cal.calendar_date - pk.offer_start_Date) / 7 ) + 1 AS group_wk_lvl

/* sum of ptps at week level only if we have full week ptps else 100 */
, CASE WHEN COUNT(drv_dtp.total_sf) = 7
OR (pk.offer_start_date - pk.offer_end_Date - 1) + (group_wk_lvl * 7) + COUNT(drv_dtp.total_sf) = 7
THEN CAST(SUM(drv_dtp.total_sf) AS DECIMAL(9,3))
ELSE CAST(COUNT(cal.calendar_date) AS DECIMAL(9,3)) * 100 / 7
END AS wk_lvl_total_sf
FROM promoexst_promos_to_research rt
INNER JOIN promoexst_promotions_key pk
ON rt.offer_number = pk.offer_number
AND rt.base_product_number_std = pk.base_product_number_std

INNER JOIN DXWI_PROD_VIEW_ACCESS.VWI0CAL_CALENDAR cal
ON cal.calendar_date BETWEEN pk.offer_start_Date
AND pk.offer_end_date

INNER JOIN promoexst_sh_promo_range_fcast prf
ON pk.offer_number = prf.offer_number
AND pk.base_product_number_std = prf.base_product_number_std

 

LEFT JOIN ( SELECT dtp.retail_outlet_number
, dtp.base_product_number
, drv_ptr.base_product_number_std
, dtp.calendar_date
, drv_ptr.offer_number
, dtp.total_sf
FROM promoexst_rslvd_cr_adjustments dtp
INNER JOIN ( SELECT pk.offer_number
, pk.base_product_number_std
, pd.base_product_number
, prf.retail_outlet_number
, pk.offer_start_date
, pk.offer_end_date
FROM promoexst_promos_to_research ptr
INNER JOIN promoexst_promotions_key pk
ON ptr.offer_number = pk.offer_number
AND ptr.base_product_number_std = pk.base_product_number_std
INNER JOIN promoexst_product_data pd
ON pk.base_product_number_std = pd.base_product_number_std
INNER JOIN promoexst_sh_promo_range_fcast prf
ON pk.offer_number = prf.offer_number
AND pk.base_product_number_std = prf.base_product_number_std


GROUP BY pk.offer_number
, pk.base_product_number_std
, pd.base_product_number
, pk.offer_start_date
, pk.offer_end_date
, prf.retail_outlet_number
) drv_ptr
ON dtp.retail_outlet_number = drv_ptr.retail_outlet_number
AND dtp.base_product_number = drv_ptr.base_product_number
AND dtp.calendar_date BETWEEN drv_ptr.offer_start_date
AND drv_ptr.offer_end_date
AND dtp.total_sf IS NOT NULL
) drv_dtp
ON drv_dtp.calendar_date = cal.calendar_date
AND drv_dtp.offer_number = pk.offer_number
AND drv_dtp.base_product_number_std = pk.base_product_number_std
AND drv_dtp.retail_outlet_number = prf.retail_outlet_number

INNER JOIN promoexst_product_data pd
ON pk.base_product_number_std = pd.base_product_number_std

GROUP BY pk.offer_number
, pk.base_product_number_std
, pd.base_product_number
, prf.retail_outlet_number
, pk.offer_start_Date
, pk.offer_end_Date
, group_wk_lvl

) AS ctpwl /* cycle_trading_perc_wk_level */

GROUP BY offer_number
, base_product_number_std
, base_product_number
, retail_outlet_number

) AS fctp /* full_cycle_trading_perc */

GROUP BY offer_number
, base_product_number_std
, retail_outlet_number

) AS ffctp /* final_full_cycle_trading_perc */

SET total_cycle_trading_perc = ffctp.final_total_cycle_trading_perc

WHERE target.offer_number = ffctp.offer_number
AND target.base_product_number_std = ffctp.base_product_number_std
AND target.retail_outlet_number = ffctp.retail_outlet_number

 

Modified quey: Highlighted peice of code is my change

UPDATE target
FROM promoexst_store_tot_trad_perc target,
(SELECT offer_number
, base_product_number_std
, retail_outlet_number
, MAX(total_cycle_trading_perc) AS final_total_cycle_trading_perc
FROM ( SELECT offer_number
, base_product_number_std
, base_product_number
, retail_outlet_number
, NULLIFZERO(SUM(wk_lvl_total_sf)) / 100 AS total_cycle_trading_perc
FROM ( SELECT pk.offer_number
, pk.base_product_number_std
, pd.base_product_number
, prf.retail_outlet_number
/* groups sum of ptps at week level */
, ((cal.calendar_date - pk.offer_start_Date) / 7 ) + 1 AS group_wk_lvl

/* sum of ptps at week level only if we have full week ptps else 100 */
, CASE WHEN COUNT(drv_dtp.total_sf) = 7
OR (pk.offer_start_date - pk.offer_end_Date - 1) + (group_wk_lvl * 7) + COUNT(drv_dtp.total_sf) = 7
THEN CAST(SUM(drv_dtp.total_sf) AS DECIMAL(9,3))
ELSE CAST(COUNT(cal.calendar_date) AS DECIMAL(9,3)) * 100 / 7
END AS wk_lvl_total_sf
FROM promoexst_promos_to_research rt
INNER JOIN promoexst_promotions_key pk
ON rt.offer_number = pk.offer_number
AND rt.base_product_number_std = pk.base_product_number_std

INNER JOIN DXWI_PROD_VIEW_ACCESS.VWI0CAL_CALENDAR cal
ON cal.calendar_date BETWEEN pk.offer_start_Date
AND pk.offer_end_date

INNER JOIN promoexst_sh_promo_range_fcast prf
ON pk.offer_number = prf.offer_number
AND pk.base_product_number_std = prf.base_product_number_std

INNER JOIN promoexst_temp_sttp_upd_range tmp_sttp
ON prf.offer_number = tmp_sttp.offer_number
AND prf.base_product_number_std = tmp_sttp.base_product_number_std
AND prf.retail_outlet_number = tmp_sttp.retail_outlet_number


LEFT JOIN ( SELECT dtp.retail_outlet_number
, dtp.base_product_number
, drv_ptr.base_product_number_std
, dtp.calendar_date
, drv_ptr.offer_number
, dtp.total_sf
FROM promoexst_rslvd_cr_adjustments dtp
INNER JOIN ( SELECT pk.offer_number
, pk.base_product_number_std
, pd.base_product_number
, prf.retail_outlet_number
, pk.offer_start_date
, pk.offer_end_date
FROM promoexst_promos_to_research ptr
INNER JOIN promoexst_promotions_key pk
ON ptr.offer_number = pk.offer_number
AND ptr.base_product_number_std = pk.base_product_number_std
INNER JOIN promoexst_product_data pd
ON pk.base_product_number_std = pd.base_product_number_std
INNER JOIN promoexst_sh_promo_range_fcast prf
ON pk.offer_number = prf.offer_number
AND pk.base_product_number_std = prf.base_product_number_std
INNER JOIN promoexst_temp_sttp_upd_range tmp_sttp
ON prf.offer_number = tmp_sttp.offer_number
AND prf.base_product_number_std = tmp_sttp.base_product_number_std
AND prf.retail_outlet_number = tmp_sttp.retail_outlet_number


GROUP BY pk.offer_number
, pk.base_product_number_std
, pd.base_product_number
, pk.offer_start_date
, pk.offer_end_date
, prf.retail_outlet_number
) drv_ptr
ON dtp.retail_outlet_number = drv_ptr.retail_outlet_number
AND dtp.base_product_number = drv_ptr.base_product_number
AND dtp.calendar_date BETWEEN drv_ptr.offer_start_date
AND drv_ptr.offer_end_date
AND dtp.total_sf IS NOT NULL
) drv_dtp
ON drv_dtp.calendar_date = cal.calendar_date
AND drv_dtp.offer_number = pk.offer_number
AND drv_dtp.base_product_number_std = pk.base_product_number_std
AND drv_dtp.retail_outlet_number = prf.retail_outlet_number

INNER JOIN promoexst_product_data pd
ON pk.base_product_number_std = pd.base_product_number_std

GROUP BY pk.offer_number
, pk.base_product_number_std
, pd.base_product_number
, prf.retail_outlet_number
, pk.offer_start_Date
, pk.offer_end_Date
, group_wk_lvl

) AS ctpwl /* cycle_trading_perc_wk_level */

GROUP BY offer_number
, base_product_number_std
, base_product_number
, retail_outlet_number

) AS fctp /* full_cycle_trading_perc */

GROUP BY offer_number
, base_product_number_std
, retail_outlet_number

) AS ffctp /* final_full_cycle_trading_perc */

SET total_cycle_trading_perc = ffctp.final_total_cycle_trading_perc

WHERE target.offer_number = ffctp.offer_number
AND target.base_product_number_std = ffctp.base_product_number_std
AND target.retail_outlet_number = ffctp.retail_outlet_number

 

Explain plan of modifed query:

  1) First, we lock a distinct DXWI_DEV_PROMO_EXP_PLAY_PEN."pseudo
table" for write on a RowHash to prevent global deadlock for
DXWI_DEV_PROMO_EXP_PLAY_PEN.promoexst_store_tot_trad_perc.
2) Next, we lock DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp for access, we
lock DXWI_DEV_PROMO_EXP_PLAY_PEN.prf for access, we lock
DXWI_DEV_PROMO_EXP_PLAY_PEN.dtp for access, we lock
DXWI_DEV_PROMO_EXP_PLAY_PEN.promoexst_store_tot_trad_perc for
write, we lock DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr for access, we lock
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd for access, we lock
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk for access, and we lock
DXWI_PROD_REF_REFRESH_DB01.TWI0CAL_CALENDAR for access.
3) We do an all-AMPs JOIN step from DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr
by way of a RowHash match scan with no residual conditions, which
is joined to DXWI_DEV_PROMO_EXP_PLAY_PEN.pk by way of a RowHash
match scan with no residual conditions.
DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr and DXWI_DEV_PROMO_EXP_PLAY_PEN.pk
are joined using a merge join, with a join condition of (
"(DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr.offer_number =
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_number) AND
(DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr.base_product_number_std =
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std)"). The
result goes into Spool 7 (all_amps), which is redistributed by the
hash code of (
DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr.base_product_number_std) to all
AMPs. Then we do a SORT to order Spool 7 by row hash. The size
of Spool 7 is estimated with low confidence to be 7,037 rows (
330,739 bytes). The estimated time for this step is 0.08 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
a RowHash match scan, which is joined to
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd by way of a RowHash match scan
with no residual conditions. Spool 7 and
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd are joined using a merge join,
with a join condition of ("(base_product_number_std =
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd.base_product_number_std) AND
(base_product_number_std =
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd.base_product_number_std)").
The result goes into Spool 8 (all_amps), which is
redistributed by the hash code of (
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.ptr.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd.base_product_number_std) to
all AMPs. Then we do a SORT to order Spool 8 by row hash.
The size of Spool 8 is estimated with low confidence to be
7,068 rows (395,808 bytes). The estimated time for this step
is 0.03 seconds.
2) We do an all-AMPs JOIN step from
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf by way of a RowHash match
scan with no residual conditions, which is joined to
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp by way of a RowHash
match scan with no residual conditions.
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf and
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp are joined using a merge
join, with a join condition of (
"(DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.retail_outlet_number =
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.retail_outlet_number)
AND ((DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.base_product_number_std
=
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.base_product_number_std)
AND (DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.offer_number =
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.offer_number ))"). The
result goes into Spool 9 (all_amps), which is redistributed
by the hash code of (
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.base_product_number_std) to
all AMPs. Then we do a SORT to order Spool 9 by row hash.
The size of Spool 9 is estimated with low confidence to be
845,285 rows (37,192,540 bytes). The estimated time for this
step is 0.70 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 9 (Last Use) by way
of a RowHash match scan. Spool 8 and Spool 9 are joined using a
merge join, with a join condition of ("(base_product_number_std =
base_product_number_std) AND ((offer_number = offer_number) AND
((base_product_number_std = base_product_number_std) AND
((offer_number = offer_number) AND ((base_product_number_std =
base_product_number_std) AND ((offer_number = offer_number) AND
((base_product_number_std = base_product_number_std) AND
((offer_number = offer_number) AND ((base_product_number_std =
base_product_number_std) AND (base_product_number_std =
base_product_number_std )))))))))"). The result goes into Spool 6
(all_amps), which is built locally on the AMPs. The size of Spool
6 is estimated with low confidence to be 28,839 rows (1,268,916
bytes). The estimated time for this step is 0.04 seconds.
6) We do an all-AMPs SUM step to aggregate from Spool 6 (Last Use) by
way of an all-rows scan , grouping by field1 (
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_number
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pd.base_product_number
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_start_date
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_end_date
,DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.retail_outlet_number). Aggregate
Intermediate Results are computed globally, then placed in Spool
10. The size of Spool 10 is estimated with low confidence to be
28,839 rows (2,653,188 bytes). The estimated time for this step
is 0.05 seconds.
7) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 10 (Last Use) by
way of an all-rows scan into Spool 1 (used to materialize
view, derived table, table function or table operator drv_ptr)
(all_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with low confidence to be 28,839 rows (
1,441,950 bytes). The estimated time for this step is 0.03
seconds.
2) We do an all-AMPs RETRIEVE step from
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp by way of an all-rows
scan with no residual conditions locking for access into
Spool 14 (all_amps), which is redistributed by the hash code
of (DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.base_product_number_std)
to all AMPs. Then we do a SORT to order Spool 14 by row hash.
The size of Spool 14 is estimated with high confidence to be
845,285 rows (25,358,550 bytes). The estimated time for this
step is 0.09 seconds.
8) We do an all-AMPs JOIN step from DXWI_DEV_PROMO_EXP_PLAY_PEN.pk by
way of a RowHash match scan with no residual conditions, which is
joined to Spool 14 (Last Use) by way of a RowHash match scan
locking DXWI_DEV_PROMO_EXP_PLAY_PEN.pk for access.
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk and Spool 14 are joined using a
merge join, with a join condition of (
"(DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_number = offer_number) AND
(DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std =
base_product_number_std)"). The result goes into Spool 15
(all_amps), which is built locally on the AMPs. The size of Spool
15 is estimated with index join confidence to be 845,285 rows (
43,109,535 bytes). The estimated time for this step is 0.11
seconds.
9) We do an all-AMPs JOIN step from DXWI_DEV_PROMO_EXP_PLAY_PEN.rt by
way of a RowHash match scan with no residual conditions, which is
joined to Spool 15 (Last Use) by way of a RowHash match scan
locking DXWI_DEV_PROMO_EXP_PLAY_PEN.rt for access.
DXWI_DEV_PROMO_EXP_PLAY_PEN.rt and Spool 15 are joined using a
merge join, with a join condition of (
"(DXWI_DEV_PROMO_EXP_PLAY_PEN.rt.base_product_number_std =
base_product_number_std) AND
((DXWI_DEV_PROMO_EXP_PLAY_PEN.rt.offer_number = offer_number) AND
((DXWI_DEV_PROMO_EXP_PLAY_PEN.rt.base_product_number_std =
base_product_number_std) AND
((DXWI_DEV_PROMO_EXP_PLAY_PEN.rt.offer_number = offer_number) AND
((DXWI_DEV_PROMO_EXP_PLAY_PEN.rt.base_product_number_std =
base_product_number_std) AND (offer_number =
DXWI_DEV_PROMO_EXP_PLAY_PEN.rt.offer_number )))))"). The result
goes into Spool 16 (all_amps), which is redistributed by the hash
code of (DXWI_DEV_PROMO_EXP_PLAY_PEN.rt.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.rt.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.tmp_sttp.retail_outlet_number) to all
AMPs. Then we do a SORT to order Spool 16 by row hash. The size
of Spool 16 is estimated with index join confidence to be 7,037
rows (450,368 bytes). The estimated time for this step is 0.03
seconds.
10) We do an all-AMPs JOIN step from DXWI_DEV_PROMO_EXP_PLAY_PEN.prf
by way of a RowHash match scan with no residual conditions, which
is joined to Spool 16 (Last Use) by way of a RowHash match scan
locking DXWI_DEV_PROMO_EXP_PLAY_PEN.prf for access.
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf and Spool 16 are joined using a
merge join, with a join condition of ("(offer_number =
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.offer_number) AND
((base_product_number_std =
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.base_product_number_std) AND
((base_product_number_std =
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.base_product_number_std)
AND((offer_number = DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.offer_number)
AND ((base_product_number_std =
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.base_product_number_std) AND
((offer_number = DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.offer_number) AND
((DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.offer_number = offer_number) AND
((DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.base_product_number_std =
base_product_number_std) AND
(DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.retail_outlet_number =
retail_outlet_number ))))))))"). The result goes into Spool 17
(all_amps), which is redistributed by the hash code of (
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std) to all
AMPs. Then we do a SORT to order Spool 17 by row hash. The size
of Spool 17 is estimated with index join confidence to be 7,037
rows (267,406 bytes). The estimated time for this step is 0.14
seconds.
11) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of
a RowHash match scan, which is joined to
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd by way of a RowHash match scan
with no residual conditions locking
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd for access. Spool 17 and
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd are joined using a merge join,
with a join condition of ("base_product_number_std =
DXWI_DEV_PROMO_EXP_PLAY_PEN.pd.base_product_number_std"). The
result goes into Spool 18 (all_amps), which is duplicated on
all AMPs. The size of Spool 18 is estimated with index join
confidence to be 4,071,168 rows (170,989,056 bytes). The
estimated time for this step is 0.18 seconds.
2) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way
of an all-rows scan into Spool 19 (all_amps) fanned out into 2
hash join partitions, which is duplicated on all AMPs. The
size of Spool 19 is estimated with low confidence to be
16,611,264 rows (697,673,088 bytes). The estimated time for
this step is 0.39 seconds.
3) We do an all-AMPs RETRIEVE step from
DXWI_DEV_PROMO_EXP_PLAY_PEN.dtp by way of an all-rows scan
with a condition of ("(NOT
(DXWI_DEV_PROMO_EXP_PLAY_PEN.dtp.total_sf IS NULL )) AND ((NOT
(DXWI_DEV_PROMO_EXP_PLAY_PEN.dtp.base_product_number IS NULL
)) AND ((NOT (DXWI_DEV_PROMO_EXP_PLAY_PEN.dtp.calendar_date IS
NULL )) AND (NOT
(DXWI_DEV_PROMO_EXP_PLAY_PEN.dtp.retail_outlet_number IS NULL
))))") into Spool 20 (all_amps) fanned out into 2 hash join
partitions, which is built locally on the AMPs. The size of
Spool 20 is estimated with low confidence to be 806,814,082
rows (23,397,608,378 bytes). The estimated time for this step
is 8.54 seconds.
12) We do an all-AMPs JOIN step from
DXWI_PROD_REF_REFRESH_DB01.TWI0CAL_CALENDAR by way of a traversal
of index # 4 without accessing the base table with no residual
conditions, which is joined to Spool 18 (Last Use) by way of an
all-rows scan locking DXWI_PROD_REF_REFRESH_DB01.TWI0CAL_CALENDAR
for access. DXWI_PROD_REF_REFRESH_DB01.TWI0CAL_CALENDAR and Spool
18 are joined using a product join, with a join condition of (
"(DXWI_PROD_REF_REFRESH_DB01.TWI0CAL_CALENDAR.Calendar_Date <=
offer_end_date) AND
(DXWI_PROD_REF_REFRESH_DB01.TWI0CAL_CALENDAR.Calendar_Date >=
offer_start_date)"). The result goes into Spool 21 (all_amps)
fanned out into 6 hash join partitions, which is built locally on
the AMPs. The size of Spool 21 is estimated with index join
confidence to be 33,359,077 rows (1,534,517,542 bytes). The
estimated time for this step is 0.50 seconds.
13) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an
all-rows scan, which is joined to Spool 20 (Last Use) by way of an
all-rows scan. Spool 19 and Spool 20 are joined using a hash join
of 2 partitions, with a join condition of ("(calendar_date <=
OFFER_END_DATE) AND ((calendar_date >= OFFER_START_DATE) AND
((base_product_number = BASE_PRODUCT_NUMBER) AND
(retail_outlet_number = RETAIL_OUTLET_NUMBER )))"). The result
goes into Spool 22 (all_amps) fanned out into 6 hash join
partitions, which is redistributed by the hash code of (
DXWI_DEV_PROMO_EXP_PLAY_PEN.dtp.calendar_date) to all AMPs. The
size of Spool 22 is estimated with no confidence to be 807,430,035
rows (30,682,341,330 bytes). The estimated time for this step is
19.64 seconds.
14) 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 scan. Spool 21 and Spool 22 are left outer joined using
a hash join of 6 partitions, with a join condition of (
"(BASE_PRODUCT_NUMBER_STD = base_product_number_std) AND
((OFFER_NUMBER = offer_number) AND ((calendar_date = Calendar_Date)
AND (retail_outlet_number = retail_outlet_number )))"). The
result goes into Spool 13 (all_amps), which is built locally on
the AMPs. The size of Spool 13 is estimated with no confidence to
be 33,537,057 rows (1,743,926,964 bytes). The estimated time for
this step is 3.97 seconds.
15) We do an all-AMPs SUM step to aggregate from Spool 13 (Last Use)
by way of an all-rows scan , grouping by field1 (
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_number
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pd.base_product_number
,DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.retail_outlet_number
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_start_date
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_end_date
,(((DXWI_PROD_REF_REFRESH_DB01.TWI0CAL_CALENDAR.Calendar_Date )-
(DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_start_date ))/ 7 )+ 1).
Aggregate Intermediate Results are computed globally, then placed
in Spool 23. The size of Spool 23 is estimated with no confidence
to be 25,152,793 rows (2,515,279,300 bytes). The estimated time
for this step is 0.65 seconds.
16) We do an all-AMPs RETRIEVE step from Spool 23 (Last Use) by way of
an all-rows scan into Spool 2 (used to materialize view, derived
table, table function or table operator ctpwl) (all_amps), which
is built locally on the AMPs. The size of Spool 2 is estimated
with no confidence to be 25,152,793 rows (1,458,861,994 bytes).
The estimated time for this step is 0.69 seconds.
17) We do an all-AMPs SUM step to aggregate from Spool 2 (Last Use) by
way of an all-rows scan with a condition of ("(NOT
(ctpwl.BASE_PRODUCT_NUMBER_STD IS NULL )) AND ((NOT
(ctpwl.RETAIL_OUTLET_NUMBER IS NULL )) AND (NOT
(ctpwl.OFFER_NUMBER IS NULL )))") , grouping by field1 (
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_number
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pd.base_product_number
,DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.retail_outlet_number). Aggregate
Intermediate Results are computed globally, then placed in Spool
26. The size of Spool 26 is estimated with no confidence to be
18,864,595 rows (1,509,167,600 bytes). The estimated time for
this step is 0.55 seconds.
18) We do an all-AMPs RETRIEVE step from Spool 26 (Last Use) by way of
an all-rows scan into Spool 3 (used to materialize view, derived
table, table function or table operator fctp) (all_amps), which is
built locally on the AMPs. The size of Spool 3 is estimated with
no confidence to be 18,864,595 rows (1,018,688,130 bytes). The
estimated time for this step is 0.29 seconds.
19) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
way of an all-rows scan with a condition of ("(NOT
(fctp.BASE_PRODUCT_NUMBER_STD IS NULL )) AND ((NOT
(fctp.RETAIL_OUTLET_NUMBER IS NULL )) AND (NOT (fctp.OFFER_NUMBER
IS NULL )))") , grouping by field1 (
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_number
,DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std
,DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.retail_outlet_number). Aggregate
Intermediate Results are computed globally, then placed in Spool
29. The size of Spool 29 is estimated with no confidence to be
7,037 rows (506,664 bytes). The estimated time for this step is
0.37 seconds.
20) We do an all-AMPs RETRIEVE step from Spool 29 (Last Use) by way of
an all-rows scan into Spool 4 (used to materialize view, derived
table, table function or table operator ffctp) (all_amps), which
is built locally on the AMPs. The size of Spool 4 is estimated
with no confidence to be 7,037 rows (379,998 bytes). The
estimated time for this step is 0.03 seconds.
21) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
an all-rows scan into Spool 32 (all_amps), which is redistributed
by the hash code of (DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.offer_number,
DXWI_DEV_PROMO_EXP_PLAY_PEN.pk.base_product_number_std,
DXWI_DEV_PROMO_EXP_PLAY_PEN.prf.retail_outlet_number) to all AMPs.
Then we do a SORT to order Spool 32 by row hash. The size of
Spool 32 is estimated with no confidence to be 7,037 rows (
323,702 bytes). The estimated time for this step is 0.02 seconds.
22) We do an all-AMPs JOIN step from
DXWI_DEV_PROMO_EXP_PLAY_PEN.promoexst_store_tot_trad_perc by way
of a RowHash match scan with no residual conditions, which is
joined to Spool 32 (Last Use) by way of a RowHash match scan.
DXWI_DEV_PROMO_EXP_PLAY_PEN.promoexst_store_tot_trad_perc and
Spool 32 are joined using a merge join, with a join condition of (
"(DXWI_DEV_PROMO_EXP_PLAY_PEN.promoexst_store_tot_trad_perc.offer_numb
er = OFFER_NUMBER) AND
((DXWI_DEV_PROMO_EXP_PLAY_PEN.promoexst_store_tot_trad_perc.base_produ
ct_number_std = BASE_PRODUCT_NUMBER_STD) AND
(DXWI_DEV_PROMO_EXP_PLAY_PEN.promoexst_store_tot_trad_perc.retail_outl
et_number = RETAIL_OUTLET_NUMBER ))"). The result goes into Spool
31 (all_amps), which is built locally on the AMPs. Then we do a
SORT to order Spool 31 by the sort key in spool field1 (
DXWI_DEV_PROMO_EXP_PLAY_PEN.promoexst_store_tot_trad_perc.ROWID).
The size of Spool 31 is estimated with no confidence to be 7,037
rows (239,258 bytes). The estimated time for this step is 0.03
seconds.
23) We do a MERGE Update to
DXWI_DEV_PROMO_EXP_PLAY_PEN.promoexst_store_tot_trad_perc from
Spool 31 (Last Use) via ROWID. The size is estimated with no
confidence to be 7,037 rows (323,702 bytes). The estimated time
for this step is 0.04 seconds.
24) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.
The total estimated time is 36.58 seconds.
 I have attached stats for your reference. 

 

dtpdtppkpktmp_sttptmp_sttpprfprfptrptrpdpd

 

 

 

 

3 REPLIES
Apprentice

Re: Performance issue. Please help

Hi,

Couple of things:

1) If you have step-level query logging turned on for this query see which steps have longest run-time and which ones have large discrepancy between estimated and actual row counts.

2) The stats for your new table show 7M rows, but step 7.2 is a single table retrieve with no selection criteria says 'estimated row count' is only 845k. I know that the optimiser row counts are not always 100% accurate, but missing 90% of the data seems a lot.

- are the stats up to date?

3) In the step where it is used as a join (step 4.2) the estimated output spool file is about the same number. Is this what you're expecting for this join? Will each row in your new table normally only join to one row in DXWI_DEV_PROMO_EXP_PLAY_PEN.prf?

 

What is the old plan? That might make it easier to shed light on the differences. (having said that if the new query is now meeting the business requirement then the old plan is largely irrelevant).

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Performance issue. Please help

@DaveWellmanThanks for checking.. 

1. Please find the screen shots from viewpoint where query gets struck at step 14

2. Stats are updated. Count seems fine from the new table(845k)

3. Each row in new table join to one row in DXWI_DEV_PROMO_EXP_PLAY_PEN.prf. Records from new table are part of prf.

    Data in new table is less compared to prf.

 

viewpoint1.JPGviewpoint2.JPGviewpoint3.JPGviewpoint4.JPGviewpoint5.JPGviewpoint6.JPGviewpoint7.JPG

 

Thank you !!

Apprentice

Re: Performance issue. Please help

Hi,

 

Before I go any further, I just wanted to say well done on your original post. You included a lot of information that people will need to help you (you'd be surprised how many people do not include basic info). Good job.

 

Thanks for the update, and again the VP output is useful.

 

If you look at the VP plan you can see a number of steps where the Actual row count is an order of magnitude bigger than the Estimated row count. The first such step is 'step 5', est rows = 38k, actual rows = 912k. It is steps like these which can cause the optimiser to generate 'sub optimal' plans.(and I didn't see anything in your new query explain output about IPE - although you might have removed that).

- 'step 5' is a join step between two spool files on columns 'base_product_number_std' and 'offer_number'. Track back through the plan to see which tables these come from and collect stats on both tables on the combination of those columns.

- re-explain the plan to see if the structure changes

 

Another example is step 11. Estimated row count is 5.4M but actual rows = 525M. It is steps like these that can cause problems.

 

Start with that and see what happens.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com