Performance issue in join

Database

Performance issue in join

Hi,

 

Please find the query

 

SELECT MSR2.BOOKINGS_MEASURE_KEY , MSR2.BOOKINGS_PROCESS_DATE ,
MSR2.BKGS_MEASURE_TRANS_TYPE_CODE , NBM.SALES_REP_NUMBER , NBM.SALES_TERRITORY_KEY ,
MPH.DD_PGTMV_BE_GEO_ID_INT , SHIP_TO.ERP_WIPS_CITY_NAME AS SHIP_TO_CITY ,
SHIP_TO.STATE_OR_PROVINCE_NAME AS SHIP_TO_STATE , SHIP_TO.ERP_WIPS_ISO_COUNTRY_CD AS SHIP_TO_COUNTRY_CD ,
SHIP_TO_CTRY.ISO_COUNTRY_SHORT_CODE AS SHIP_TO_COUNTRY_SHORT_CD ,
SHIP_TO.ERP_WIPS_POSTAL_CD AS SHIP_TO_POSTAL_CD , END_CUST.ERP_WIPS_CITY_NAME AS END_CUST_CITY ,
END_CUST.STATE_OR_PROVINCE_NAME AS END_CUST_STATE , END_CUST.ERP_WIPS_ISO_COUNTRY_CD AS END_CUST_COUNTRY_CD ,
END_CUST_CTRY.ISO_COUNTRY_SHORT_CODE AS END_CUST_COUNTRY_SHORT_CD ,
END_CUST.ERP_WIPS_POSTAL_CD AS END_CUST_POSTAL_CD , RSH.ISO_COUNTRY_CODE AS SHARE_COUNTRY_CD ,
SHARE_CTRY.ISO_COUNTRY_SHORT_CODE AS SHARE_SHORT_CTRY_CD , RSH.SALES_TERRITORY_NAME_CODE ,
NPOS.BK_WIPS_ORIGINATOR_ID_INT AS BKGS_DISTRIBUTOR_ID , MSR2.DV_END_CUST_PARTY_KEY ,
MSR2.REASON_DESC AS REASON_DESC_END_CUST , COALESCE( SYSVIEW.BK_SA_MEMBER_ID_INT ,
- 999 ) AS BK_SA_MEMBER_ID_INT , COALESCE( SYSVIEW.SALES_ACCOUNT_GROUP_PARTY_KEY ,
- 999 ) AS SALES_ACCOUNT_GROUP_PARTY_KEY , COALESCE( SYSVIEW.LINK_CUSTOMER_PARTY_KEY ,
- 999 ) AS LINK_CUSTOMER_PARTY_KEY , COALESCE( SYSVIEW.HIBERNATION_FLG ,
'N' ) AS HIBERNATION_FLG , MPH.BK_PGTMV_PARTNER_ID_INT , SOLD_TO.ERP_WIPS_CITY_NAME AS SOLD_TO_CITY ,
SOLD_TO.STATE_OR_PROVINCE_NAME AS SOLD_TO_STATE , SOLD_TO.ERP_WIPS_ISO_COUNTRY_CD AS SOLD_TO_COUNTRY_CD ,
SOLD_TO_CTRY.ISO_COUNTRY_SHORT_CODE AS SOLD_TO_COUNTRY_SHORT_CD ,
SOLD_TO.ERP_WIPS_POSTAL_CD AS SOLD_TO_POSTAL_CD
FROM (
SELECT *
FROM STGDB.WI_SALES_ACCOUNT_RSTD_MEASURE2 MSR2
WHERE ^ EXISTS (
SELECT 1
FROM STGDB.WI_SALES_ACCOUNT_RSTD_MEASURE3 MSR3
WHERE MSR2.BOOKINGS_MEASURE_KEY = MSR3.BOOKINGS_MEASURE_KEY
AND MSR2.BOOKINGS_PROCESS_DATE = MSR3.BOOKINGS_PROCESS_DATE
AND MSR2.BKGS_MEASURE_TRANS_TYPE_CODE = MSR3.BKGS_MEASURE_TRANS_TYPE_CODE ) ) MSR2 LEFT JOIN (
SELECT *
FROM STGDB.WI_SYSVIEW_PARTY
WHERE SALES_ACCOUNT_GROUP_TYPE_CD = 'NAMED_ACCOUNT'
AND HIBERNATION_FLG = 'Y'
QUALIFY ROW_NUMBER ( ) OVER ( PARTITION BY CUSTOMER_PARTY_KEY ,
SALES_ACCOUNT_GROUP_PARTY_KEY
ORDER BY BK_SA_MEMBER_ID_INT ) = 1 ) SYSVIEW
ON MSR2.DV_END_CUST_PARTY_KEY = SYSVIEW.CUSTOMER_PARTY_KEY
AND MSR2.ISO_COUNTRY_CODE = SYSVIEW.ISO_COUNTRY_CODE INNER JOIN SLSORDVWDB.N_BOOKINGS_MEASURE NBM
ON MSR2.BOOKINGS_MEASURE_KEY = NBM.BOOKINGS_MEASURE_KEY
AND MSR2.BOOKINGS_PROCESS_DATE = NBM.BOOKINGS_PROCESS_DATE
AND MSR2.BKGS_MEASURE_TRANS_TYPE_CODE = NBM.BKGS_MEASURE_TRANS_TYPE_CODE INNER JOIN SLSORDVWDB.N_BOOKINGS_CHANNEL_MEASURE NCBM
ON MSR2.BOOKINGS_MEASURE_KEY = NCBM.BOOKINGS_MEASURE_KEY
AND MSR2.BOOKINGS_PROCESS_DATE = NCBM.BOOKINGS_PROCESS_DATE
AND MSR2.BKGS_MEASURE_TRANS_TYPE_CODE = NCBM.BKGS_MEASURE_TRANS_TYPE_CODE INNER JOIN COMREFVWDB.MT_ERP_POS_SHIPTO SHIP_TO
ON NBM.SHIP_TO_CUSTOMER_KEY = SHIP_TO.SHIP_TO_CUSTOMER_KEY INNER JOIN COMREFVWDB.MT_ERP_POS_ENDCUST END_CUST
ON NBM.END_CUSTOMER_KEY = END_CUST.END_CUSTOMER_KEY INNER JOIN COMREFVWDB.MT_PARTNER_HIERARCHY MPH
ON NCBM.PARTNER_SITE_PARTY_KEY = MPH.PARTNER_SITE_PARTY_KEY INNER JOIN COMREFVWDB.N_ISO_COUNTRY SHIP_TO_CTRY
ON SHIP_TO_CTRY.BK_ISO_COUNTRY_CODE = SHIP_TO.ERP_WIPS_ISO_COUNTRY_CD INNER JOIN COMREFVWDB.N_ISO_COUNTRY END_CUST_CTRY
ON END_CUST_CTRY.BK_ISO_COUNTRY_CODE = END_CUST.ERP_WIPS_ISO_COUNTRY_CD INNER JOIN COMREFVWDB.R_SALES_HIERARCHY RSH
ON NBM.SALES_TERRITORY_KEY = RSH.SALES_TERRITORY_KEY INNER JOIN COMREFVWDB.N_ISO_COUNTRY SHARE_CTRY
ON SHARE_CTRY.BK_ISO_COUNTRY_CODE = RSH.ISO_COUNTRY_CODE LEFT JOIN SLSORDVWDB.N_POS_TRANSACTION_LINE NPOS
ON NBM.BK_POS_TRANSACTION_ID_INT = NPOS.BK_POS_TRANSACTION_ID_INT INNER JOIN COMREFVWDB.MT_ERP_POS_SOLDTO SOLD_TO
ON NBM.SOLD_TO_CUSTOMER_KEY = SOLD_TO.SOLD_TO_CUSTOMER_KEY INNER JOIN COMREFVWDB.N_ISO_COUNTRY SOLD_TO_CTRY
ON SOLD_TO_CTRY.BK_ISO_COUNTRY_CODE = SOLD_TO.ERP_WIPS_ISO_COUNTRY_CD

 

Explain Plan:

 

1) First, we lock COMREFDB.MT_PARTNER_HIERARCHY in view
COMREFVWDB.MT_PARTNER_HIERARCHY for access, we lock
COMREFDB.R_SALES_HIERARCHY in view COMREFVWDB.R_SALES_HIERARCHY
for access, we lock COMREFDB.MT_ERP_POS_SOLDTO in view
COMREFVWDB.MT_ERP_POS_SOLDTO for access, we lock
COMREFDB.MT_ERP_POS_SHIPTO in view COMREFVWDB.MT_ERP_POS_SHIPTO
for access, we lock COMREFDB.MT_ERP_POS_ENDCUST in view
COMREFVWDB.MT_ERP_POS_ENDCUST for access, we lock
COMREFDB.N_ISO_COUNTRY in view COMREFVWDB.N_ISO_COUNTRY for access,
we lock SLSORDDB.N_BOOKINGS_MEASURE in view
SLSORDVWDB.N_BOOKINGS_MEASURE for access, we lock
SLSORDDB.N_BOOKINGS_CHANNEL_MEASURE in view
SLSORDVWDB.N_BOOKINGS_CHANNEL_MEASURE for access, we lock
SLSORDDB.N_POS_TRANSACTION_LINE in view
SLSORDVWDB.N_POS_TRANSACTION_LINE for access, we lock STGDB.MSR3
for access, we lock STGDB.MSR2 for access, and we lock
STGDB.WI_SYSVIEW_PARTY for access.
2) Next, we do an all-AMPs RETRIEVE step from STGDB.WI_SYSVIEW_PARTY
by way of an all-rows scan with a condition of (
"(STGDB.WI_SYSVIEW_PARTY.HIBERNATION_FLG = 'Y') AND
(STGDB.WI_SYSVIEW_PARTY.SALES_ACCOUNT_GROUP_TYPE_CD =
'NAMED_ACCOUNT')") into Spool 2 (all_amps) (compressed columns
allowed), which is built locally on the AMPs. The size of Spool 2
is estimated with no confidence to be 9,270,634 rows (324,472,190
bytes). The estimated time for this step is 0.50 seconds.
3) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by
way of an all-rows scan into Spool 5 (Last Use), which is
redistributed by hash code to all AMPs. The result rows are put
into Spool 3 (all_amps) (compressed columns allowed), which is
built locally on the AMPs. The size is estimated with no
confidence to be 9,270,634 rows (565,508,674 bytes).
4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by
way of an all-rows scan with a condition of ("Field_8 = 1")
into Spool 1 (used to materialize view, derived table, table
function or table operator SYSVIEW) (all_amps) (compressed
columns allowed), which is built locally on the AMPs. The
size of Spool 1 is estimated with no confidence to be
9,270,634 rows (380,095,994 bytes). The estimated time for
this step is 0.07 seconds.
2) We do an all-AMPs RETRIEVE step from
SLSORDDB.N_BOOKINGS_CHANNEL_MEASURE in view
SLSORDVWDB.N_BOOKINGS_CHANNEL_MEASURE by way of an all-rows
scan with a condition of (
"SLSORDDB.N_BOOKINGS_CHANNEL_MEASURE in view
SLSORDVWDB.N_BOOKINGS_CHANNEL_MEASURE.PARTNER_SITE_PARTY_KEY
>= -999999") into Spool 9 (all_amps) (compressed columns
allowed), which is built locally on the AMPs. Then we do a
SORT to order Spool 9 by the hash code of (
SLSORDDB.N_BOOKINGS_CHANNEL_MEASURE.BKGS_MEASURE_TRANS_TYPE_CODE,
SLSORDDB.N_BOOKINGS_CHANNEL_MEASURE.BOOKINGS_PROCESS_DATE,
SLSORDDB.N_BOOKINGS_CHANNEL_MEASURE.BOOKINGS_MEASURE_KEY).
The size of Spool 9 is estimated with high confidence to be
1,077,575,653 rows (34,482,420,896 bytes). The estimated
time for this step is 15.68 seconds.
5) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of a
RowHash match scan, which is joined to STGDB.MSR2 by way of a
RowHash match scan with no residual conditions. Spool 9 and
STGDB.MSR2 are joined using a merge join, with a join condition of
("(STGDB.MSR2.BOOKINGS_MEASURE_KEY = BOOKINGS_MEASURE_KEY) AND
((STGDB.MSR2.BOOKINGS_PROCESS_DATE = BOOKINGS_PROCESS_DATE) AND
(STGDB.MSR2.BKGS_MEASURE_TRANS_TYPE_CODE =
BKGS_MEASURE_TRANS_TYPE_CODE ))"). The result goes into Spool 10
(all_amps) (compressed columns allowed), which is built locally on
the AMPs. The size of Spool 10 is estimated with low confidence
to be 172,432,005 rows (15,346,448,445 bytes). The estimated time
for this step is 4.55 seconds.
6) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way
of an all-rows scan, which is joined to STGDB.MSR3 by way of
an all-rows scan with no residual conditions. Spool 10 and
STGDB.MSR3 are joined using an exclusion merge join, with a
join condition of ("((BOOKINGS_MEASURE_KEY =
STGDB.MSR3.BOOKINGS_MEASURE_KEY) AND (BOOKINGS_PROCESS_DATE =
STGDB.MSR3.BOOKINGS_PROCESS_DATE )) AND
(BKGS_MEASURE_TRANS_TYPE_CODE =
STGDB.MSR3.BKGS_MEASURE_TRANS_TYPE_CODE)") where unknown
comparison will be ignored. The result goes into Spool 11
(all_amps) (compressed columns allowed) fanned out into 28
hash join partitions, which is built locally on the AMPs.
The size of Spool 11 is estimated with index join confidence
to be 172,432,005 rows (15,346,448,445 bytes). The estimated
time for this step is 3.59 seconds.
2) We do an all-AMPs RETRIEVE step from
COMREFDB.MT_PARTNER_HIERARCHY in view
COMREFVWDB.MT_PARTNER_HIERARCHY by way of an all-rows scan
with a condition of ("(COMREFDB.MT_PARTNER_HIERARCHY in view
COMREFVWDB.MT_PARTNER_HIERARCHY.PARTNER_SITE_PARTY_KEY >
-1000000) AND (COMREFDB.MT_PARTNER_HIERARCHY in view
COMREFVWDB.MT_PARTNER_HIERARCHY.PARTNER_SITE_PARTY_KEY >=
-999999)") into Spool 12 (all_amps) (compressed columns
allowed) fanned out into 28 hash join partitions, which is
duplicated on all AMPs. The size of Spool 12 is estimated
with high confidence to be 1,189,218,240 rows (
29,730,456,000 bytes). The estimated time for this step is
6.89 seconds.
7) 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 a hash join
of 28 partitions, with a join condition of (
"PARTNER_SITE_PARTY_KEY = PARTNER_SITE_PARTY_KEY"). The result
goes into Spool 13 (all_amps) (compressed columns allowed), which
is built locally on the AMPs. Then we do a SORT to partition
Spool 13 by rowkey. The size of Spool 13 is estimated with index
join confidence to be 172,432,005 rows (16,036,176,465 bytes).
The estimated time for this step is 8.28 seconds.
8) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from COMREFDB.N_ISO_COUNTRY
in view COMREFVWDB.N_ISO_COUNTRY by way of an all-rows scan
with no residual conditions into Spool 14 (all_amps)
(compressed columns allowed), which is duplicated on all AMPs.
The size of Spool 14 is estimated with high confidence to be
265,680 rows (4,782,240 bytes). The estimated time for this
step is 0.03 seconds.
2) We do an all-AMPs JOIN step from SLSORDDB.N_BOOKINGS_MEASURE
in view SLSORDVWDB.N_BOOKINGS_MEASURE by way of a RowHash
match scan with a condition of ("SLSORDDB.N_BOOKINGS_MEASURE
in view SLSORDVWDB.N_BOOKINGS_MEASURE.SALES_TERRITORY_KEY >=
-999999"), which is joined to Spool 13 (Last Use) by way of a
RowHash match scan. SLSORDDB.N_BOOKINGS_MEASURE and Spool 13
are joined using a rowkey-based merge join, with a join
condition of ("(BOOKINGS_MEASURE_KEY =
SLSORDDB.N_BOOKINGS_MEASURE.BOOKINGS_MEASURE_KEY) AND
((BOOKINGS_PROCESS_DATE =
SLSORDDB.N_BOOKINGS_MEASURE.BOOKINGS_PROCESS_DATE) AND
((BKGS_MEASURE_TRANS_TYPE_CODE =
SLSORDDB.N_BOOKINGS_MEASURE.BKGS_MEASURE_TRANS_TYPE_CODE) AND
((BKGS_MEASURE_TRANS_TYPE_CODE =
SLSORDDB.N_BOOKINGS_MEASURE.BKGS_MEASURE_TRANS_TYPE_CODE) AND
((BOOKINGS_PROCESS_DATE =
SLSORDDB.N_BOOKINGS_MEASURE.BOOKINGS_PROCESS_DATE) AND
(BOOKINGS_MEASURE_KEY =
SLSORDDB.N_BOOKINGS_MEASURE.BOOKINGS_MEASURE_KEY )))))").
The result goes into Spool 15 (all_amps) (compressed columns
allowed) fanned out into 34 hash join partitions, which is
built locally on the AMPs. The size of Spool 15 is estimated
with index join confidence to be 172,432,005 rows (
18,967,520,550 bytes). The estimated time for this step is
25.38 seconds.
3) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by
way of an all-rows scan with a condition of ("NOT
(SYSVIEW.ISO_COUNTRY_CODE IS NULL)") into Spool 16 (all_amps)
(compressed columns allowed) fanned out into 34 hash join
partitions, which is duplicated on all AMPs. The size of
Spool 16 is estimated with no confidence to be 10,012,284,720
rows (330,405,395,760 bytes). The estimated time for this
step is 1 minute and 14 seconds.
9) We do an all-AMPs JOIN step from Spool 14 by way of an all-rows
scan, which is joined to COMREFDB.MT_ERP_POS_SOLDTO in view
COMREFVWDB.MT_ERP_POS_SOLDTO by way of an all-rows scan with no
residual conditions. Spool 14 and COMREFDB.MT_ERP_POS_SOLDTO are
joined using a dynamic hash join, with a join condition of (
"BK_ISO_COUNTRY_CODE =
COMREFDB.MT_ERP_POS_SOLDTO.ERP_WIPS_ISO_COUNTRY_CD"). The result
goes into Spool 19 (all_amps) (compressed columns allowed) fanned
out into 7 hash join partitions, which is built locally on the
AMPs. The size of Spool 19 is estimated with low confidence to be
9,401,940 rows (3,770,177,940 bytes). The estimated time for this
step is 1.11 seconds.
10) 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 left outer joined using
a hash join of 34 partitions, with condition(s) used for
non-matching on left table ("NOT (ISO_COUNTRY_CODE IS NULL)"),
with a join condition of ("(ISO_COUNTRY_CODE = ISO_COUNTRY_CODE)
AND (DV_END_CUST_PARTY_KEY = CUSTOMER_PARTY_KEY)"). The result
goes into Spool 20 (all_amps) (compressed columns allowed) fanned
out into 7 hash join partitions, which is redistributed by the
hash code of (SLSORDDB.N_BOOKINGS_MEASURE.SOLD_TO_CUSTOMER_KEY) to
all AMPs. The size of Spool 20 is estimated with no confidence to
be 172,432,005 rows (20,691,840,600 bytes). The estimated time
for this step is 6 minutes and 53 seconds.
11) We do an all-AMPs JOIN step from Spool 14 by way of an all-rows
scan, which is joined to COMREFDB.MT_ERP_POS_SHIPTO in view
COMREFVWDB.MT_ERP_POS_SHIPTO by way of an all-rows scan with no
residual conditions. Spool 14 and COMREFDB.MT_ERP_POS_SHIPTO are
joined using a dynamic hash join, with a join condition of (
"BK_ISO_COUNTRY_CODE =
COMREFDB.MT_ERP_POS_SHIPTO.ERP_WIPS_ISO_COUNTRY_CD"). The result
goes into Spool 21 (all_amps) (compressed columns allowed) fanned
out into 20 hash join partitions, which is built locally on the
AMPs. The size of Spool 21 is estimated with low confidence to be
26,943,460 rows (10,804,327,460 bytes). The estimated time for
this step is 3.41 seconds.
12) 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 7 partitions, with a join condition of ("SOLD_TO_CUSTOMER_KEY =
SOLD_TO_CUSTOMER_KEY"). The result goes into Spool 22 (all_amps)
(compressed columns allowed) fanned out into 20 hash join
partitions, which is redistributed by the hash code of (
SLSORDDB.N_BOOKINGS_MEASURE.SHIP_TO_CUSTOMER_KEY) to all AMPs.
The size of Spool 22 is estimated with no confidence to be
172,432,005 rows (86,216,002,500 bytes). The estimated time for
this step is 26 minutes and 49 seconds.
13) 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 joined using a hash join
of 20 partitions, with a join condition of ("SHIP_TO_CUSTOMER_KEY
= SHIP_TO_CUSTOMER_KEY"). The result goes into Spool 23
(all_amps) (compressed columns allowed), which is built locally on
the AMPs. Then we do a SORT to order Spool 23 by the hash code of
(SLSORDDB.N_BOOKINGS_MEASURE.BK_POS_TRANSACTION_ID_INT). The size
of Spool 23 is estimated with no confidence to be 172,432,005 rows
(151,740,164,400 bytes). The estimated time for this step is 1
minute and 8 seconds.
14) We do an all-AMPs RETRIEVE step from
SLSORDDB.N_POS_TRANSACTION_LINE in view
SLSORDVWDB.N_POS_TRANSACTION_LINE by way of an all-rows scan with
no residual conditions into Spool 24 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. Then we do a
SORT to order Spool 24 by the hash code of (
SLSORDDB.N_POS_TRANSACTION_LINE.BK_POS_TRANSACTION_ID_INT). The
size of Spool 24 is estimated with high confidence to be
96,743,103,840 rows (2,031,605,180,640 bytes). The estimated time
for this step is 24 minutes and 30 seconds.

We are getting the Issue step 14 

 

14) We do an all-AMPs RETRIEVE step from
SLSORDDB.N_POS_TRANSACTION_LINE in view
SLSORDVWDB.N_POS_TRANSACTION_LINE by way of an all-rows scan with
no residual conditions into Spool 24 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. Then we do a
SORT to order Spool 24 by the hash code of (
SLSORDDB.N_POS_TRANSACTION_LINE.BK_POS_TRANSACTION_ID_INT). The
size of Spool 24 is estimated with high confidence to be
96,743,103,840 rows (2,031,605,180,640 bytes). The estimated time
for this step is 24 minutes and 30 seconds.

 

this step taking more 100k cputime and Impact time

 

Can any one suggest for above issue and Please Provide the your inputs..

1 REPLY
Enthusiast

Re: Performance issue in join

Lot of joins. You may need statistics collected on following:

 

COMREFVWDB.MT_ERP_POS_SOLDTO

COMREFVWDB.MT_ERP_POS_SHIPTO

STGDB.WI_SYSVIEW_PARTY

STGDB.WI_SALES_ACCOUNT_RSTD_MEASURE2

 

Go with the default statistics recommendation maker:

 

"DIAGNOSTIC HELPSTATS ON FOR SESSION" and then see the bottom of explain plan again.

If this doesnt help, you may want to see if you have any flexibility on changing any index or add a partition to some recurring join columns.

 

If this also doesnt help, see if you can have join index but I would recommend break down the query to an MSR.