 |
 |
Archives of the TeradataForum
Message Posted: Fri, 07 Jan 2011 @ 15:11:10 GMT
| Subj: | | Re: Tune the query - giving spool space error |
| |
| From: | | Shindhe, Naveen |
Hi,
I have collected stats on the columns which are being used in the JOINS, but still ending up with the Spool space issue. Below is the count for
the tables . I am attaching the Explain Plan below.
SELECT COUNT(*) FROM Pln -> 2153
SELECT COUNT(*) FROM ZLZ_Pln_Ln -> 60,998,057 SELECT COUNT(*) FROM
ZLZ_Pln_Ln_Amnt -> 56,451,540 SELECT COUNT(*) FROM ZLZ_PLN_LN_ITM_ASSN ->
4,143,390 SELECT COUNT(*) FROM ITM -> 93,796 SELECT COUNT(*) FROM
ZLZ_PLN_LN_PRTY_ASSN -> 4,256,623 SELECT COUNT(*) FROM ZRTY -> 205,311
SELECT COUNT(*) FROM ZLZ_LN_AMNT_TYP -> 9.547 SELECT COUNT(*) FROM
LN_AMT_TYP_HIER_GRP_ASSN -> 10,256
SELECT
P.ORG_BPTY_ID
P.BPTY_ID
FROM
Pln P
INNER JOIN
ZLZ_Pln_Ln SPL
ON P.PLN_ID = SPL.ZLZ_PLN_ID
AND P.PLN_RVSN_DT = SPL.PLN_RVSN_DT
AND p.ORG_BPTY_ID = 2
LEFT JOIN
ZLZ_Pln_Ln_Amnt SPLA
ON SPLA.ZLZ_PLN_LN_NUM = SPL.ZLZ_PLN_LN_NUM AND SPLA.ZLZ_PLN_ID=
SPL.ZLZ_PLN_ID AND SPLA.PLN_RVSN_DT = SPL.PLN_RVSN_DT AND SPLA.ZLZ_Pln_Ln_Dt
= SPL.ZLZ_PLN_LN_DT
LEFT JOIN
ZLZ_PLN_LN_ITM_ASSN SPLIA
ON SPL.ZLZ_PLN_LN_NUM = SPLIA.ZLZ_PLN_LN_NUM AND SPL.ZLZ_PLN_ID=
SPLIA.ZLZ_PLN_ID AND SPL.PLN_RVSN_DT = SPLIA.PLN_RVSN_DT
LEFT JOIN
ITM ITM
ON ITM.ITM_ID = SPLIA.ITM_ID
LEFT JOIN
ZLZ_PLN_LN_PRTY_ASSN SPLPA
ON SPL.ZLZ_PLN_LN_NUM = SPLPA.ZLZ_PLN_LN_NUM AND SPL.ZLZ_PLN_ID=
SPLPA.ZLZ_PLN_ID AND SPL.PLN_RVSN_DT = SPLPA.PLN_RVSN_DT
LEFT JOIN
ZRTY ZRTY
ON ZRTY.BPTY_ID = SPLPA.BPTY_ID
AND ZRTY.SRC_PRTY_TYP_CD = 'CSTMR'
LEFT JOIN
ZLZ_LN_AMNT_TYP SLAT
ON SPLA.SL_LN_AMT_TYP_NUM = SLAT.SL_LN_AMT_TYP_NUM
AND P.ORG_BPTY_ID = SLAT.ORG_BPTY_ID
LEFT JOIN
LN_AMT_TYP_HIER_GRP_ASSN LATHGA
ON SLAT.SL_LN_AMT_TYP_NUM = LATHGA.LN_AMT_TYP_NUM
AND SLAT.ORG_BPTY_ID = LATHGA.ORG_BPTY_ID
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct DEV_T."pseudo table" for read on a RowHash to prevent global deadlock for DEV_T.SPLPA.
| |
| | 2) | Next, we lock a distinct DEV_T."pseudo table" for read on a RowHash to prevent global deadlock for DEV_T.SPLIA.
| |
| | 3) | We lock a distinct DEV_T."pseudo table" for read on a RowHash to prevent global deadlock for DEV_T.SPLA.
| |
| | 4) | We lock a distinct DEV_T."pseudo table" for read on a RowHash to prevent global deadlock for DEV_T.SPL.
| |
| | 5) | We lock a distinct DEV_T."pseudo table" for read on a RowHash to prevent global deadlock for DEV_T.SLAT.
| |
| | 6) | We lock a distinct DEV_T."pseudo table" for read on a RowHash to prevent global deadlock for DEV_T.ZRTY.
| |
| | 7) | We lock a distinct DEV_T."pseudo table" for read on a RowHash to prevent global deadlock for DEV_T.P.
| |
| | 8) | We lock a distinct DEV_T."pseudo table" for read on a RowHash to prevent global deadlock for DEV_T.LATHGA.
| |
| | 9) | We lock a distinct DEV_T."pseudo table" for read on a RowHash to prevent global deadlock for DEV_T.ITM.
| |
| | 10) | We lock DEV_T.SPLPA for read, we lock DEV_T.SPLIA for read, we lock DEV_T.SPLA for read, we lock DEV_T.SPL for read, we lock DEV_T.SLAT
for read, we lock DEV_T.ZRTY for read, we lock DEV_T.P for read, we lock DEV_T.LATHGA for read, and we lock DEV_T.ITM for read.
| |
| | 11) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from DEV_T.LATHGA by way of an all-rows scan with no residual conditions into Spool 2 (all_amps), which
is redistributed by the hash code of (DEV_T.LATHGA.Ln_Amt_Typ_Num, DEV_T.LATHGA.Org_BPTY_ID) to all AMPs. Then we do a SORT to order Spool 2 by
row hash. The size of Spool 2 is estimated with low confidence to be 8,064 rows (379,008 bytes). The estimated time for this step is 0.04
seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from DEV_T.P by way of an all-rows scan with a condition of ("DEV_T.p.Org_BPTY_ID = 2") into Spool 3
(all_amps), which is duplicated on all AMPs. The size of Spool 3 is estimated with high confidence to be 2,736 rows (93,024 bytes). The
estimated time for this step is 0.03 seconds.
| | |
| | 12) | We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an all-rows scan, which is joined to DEV_T.SPL by way of an all-rows scan
with no residual conditions. Spool 3 and DEV_T.SPL are joined using a single partition hash_ join, with a join condition of ("(Pln_Id =
DEV_T.SPL.ZLZ_Pln_Id) AND (Pln_Rvsn_Dt = DEV_T.SPL.Pln_Rvsn_Dt)"). The result goes into Spool 4 (all_amps), which is redistributed by the hash
code of (DEV_T.SPL.Pln_Rvsn_Dt, DEV_T.SPL.ZLZ_Pln_Id, DEV_T.SPL.ZLZ_Pln_Ln_Num) to all AMPs. Then we do a SORT to order Spool 4 by row hash. The
size of Spool 4 is estimated with low confidence to be 635,288 rows (59,717,072 bytes). The estimated time for this step is 9.02 seconds.
| |
| | 13) | We do an all-AMPs RETRIEVE step from DEV_T.SPLIA by way of an all-rows scan with no residual conditions into Spool 5 (all_amps), which is
redistributed by the hash code of (DEV_T.SPLIA.ZLZ_Pln_Ln_Num, DEV_T.SPLIA.ZLZ_Pln_Id, DEV_T.SPLIA.Pln_Rvsn_Dt) to all AMPs. Then we do a SORT to
order Spool 5 by row hash. The size of Spool 5 is estimated with low confidence to be 4,139,136 rows (120,034,944 bytes). The estimated time for
this step is 1.64 seconds.
| |
| | 14) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 4 (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 4 and Spool 5 are left outer joined using a merge join, with a join condition of ("(Pln_Rvsn_Dt = Pln_Rvsn_Dt) AND
((ZLZ_Pln_Id = ZLZ_Pln_Id) AND (ZLZ_Pln_Ln_Num = ZLZ_Pln_Ln_Num ))"). 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 635,288 rows (62,258,224 bytes). The estimated time for this step is 0.31
seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from DEV_T.SPLPA by way of an all-rows scan with no residual conditions into Spool 7 (all_amps), which is
redistributed by the hash code of (DEV_T.SPLPA.ZLZ_Pln_Ln_Num, DEV_T.SPLPA.ZLZ_Pln_Id, DEV_T.SPLPA.Pln_Rvsn_Dt) 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 4,251,312 rows (123,288,048 bytes). The estimated time for
this step is 1.68 seconds.
| | |
| | 15) | We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of a RowHash match scan, which is joined to Spool 7 (Last Use) by way of a
RowHash match scan. Spool 6 and Spool 7 are left outer joined using a merge join, with a join condition of ("(Pln_Rvsn_Dt = Pln_Rvsn_Dt) AND
((ZLZ_Pln_Id = ZLZ_Pln_Id) AND (ZLZ_Pln_Ln_Num = ZLZ_Pln_Ln_Num ))"). The result goes into Spool 8 (all_amps), which is redistributed by the hash
code of (DEV_T.SPLIA.Itm_Id) 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 651,447 rows (66,447,594 bytes). The estimated time for this step is 1.01 seconds.
| |
| | 16) | We do an all-AMPs JOIN step from DEV_T.ITM by way of a RowHash match scan with no residual conditions, which is joined to Spool 8 (Last
Use) by way of a RowHash match scan. DEV_T.ITM and Spool 8 are right outer joined using a merge join, with a join condition of ("DEV_T.ITM.Itm_Id
= Itm_Id"). The result goes into Spool 9 (all_amps), which is redistributed by the hash code of (DEV_T.P.Org_BPTY_ID, DEV_T.SPLPA.BPTY_ID,
DEV_T.SPL.ZLZ_Pln_Ln_Dt, DEV_T.SPL.Pln_Rvsn_Dt, DEV_T.SPL.ZLZ_Pln_Id, DEV_T.SPL.ZLZ_Pln_Ln_Num, DEV_T.P.Pln_Id, DEV_T.P.Pln_Crt_Dt,
DEV_T.ITM.Itm_Id, DEV_T.SPL.ZLZ_Pln_Ln_Qty, DEV_T.SPL.ZLZ_Pln_Ln_Nt_Wgt_Meas, DEV_T.SPL.ZLZ_Pln_Ln_Grss_Wgt_Meas, DEV_T.P.Pln_SubTyp_Cd) 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 651,447 rows (71,659,170
bytes). The estimated time for this step is 0.10 seconds.
| |
| | 17) | We do an all-AMPs RETRIEVE step from Spool 9 by way of an all-rows scan 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 (DEV_T.SPL.ZLZ_Pln_Ln_Dt, DEV_T.SPL.Pln_Rvsn_Dt, DEV_T.SPL.ZLZ_Pln_Id, DEV_T.SPL.ZLZ_Pln_Ln_Num).
The size of Spool 12 is estimated with low confidence to be 46,904,184 rows (5,159,460,240 bytes). The estimated time for this step is 26.52
seconds.
| |
| | 18) | We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an all-rows scan, which is joined to DEV_T.SPLA by way of a traversal of
index # 8 without accessing the base table extracting row ids only. Spool 12 and DEV_T.SPLA are joined using a nested join, with a join condition
of ("(DEV_T.SPLA.ZLZ_Pln_Ln_Num = ZLZ_Pln_Ln_Num) AND ((DEV_T.SPLA.ZLZ_Pln_Id = ZLZ_Pln_Id) AND ((DEV_T.SPLA.Pln_Rvsn_Dt = Pln_Rvsn_Dt) AND
(DEV_T.SPLA.ZLZ_Pln_Ln_Dt = ZLZ_Pln_Ln_Dt )))"). 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 field Id 1. The size of Spool 13 is estimated with low confidence to be 651,447 rows (78,173,640 bytes). The estimated time
for this step is 7.41 seconds.
| |
| | 19) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of an all-rows scan, which is joined to DEV_T.SPLA by way of an all-rows scan
with no residual conditions. Spool 13 and DEV_T.SPLA are joined using a row id join, with a join condition of ("(1=1)"). The result goes into
Spool 14 (all_amps), which is redistributed by the hash code of (DEV_T.P.Pln_SubTyp_Cd, DEV_T.SPL.ZLZ_Pln_Ln_Grss_Wgt_Meas,
DEV_T.SPL.ZLZ_Pln_Ln_Nt_Wgt_Meas, DEV_T.SPL.ZLZ_Pln_Ln_Qty, DEV_T.ITM.Itm_Id, DEV_T.P.Pln_Crt_Dt, DEV_T.P.Pln_Id, DEV_T.SPL.ZLZ_Pln_Ln_Num,
DEV_T.SPL.ZLZ_Pln_Id, DEV_T.SPL.Pln_Rvsn_Dt, DEV_T.SPL.ZLZ_Pln_Ln_Dt, DEV_T.SPLPA.BPTY_ID, DEV_T.P.Org_BPTY_ID) to all AMPs. Then we do a SORT to
order Spool 14 by row hash. The size of Spool 14 is estimated with low confidence to be 651,447 rows (36,481,032 bytes).
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from DEV_T.ZRTY by way of an all-rows scan with a condition of ("DEV_T.ZRTY.Src_Prty_Typ_Cd = 'CSTMR'")
into Spool 15 (all_amps), which is duplicated on all AMPs. Then we do a SORT to order Spool 15 by the hash code of (DEV_T.ZRTY.BPTY_ID). The
size of Spool 15 is estimated with low confidence to be 6,866,064 rows (116,723,088 bytes). The estimated time for this step is 0.79 seconds.
| | |
| | 20) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 9 (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 9 and Spool 14 are left outer joined using a merge join, with a join condition of ("Field_1 = Field_1"). 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 the hash code of (DEV_T.SPLPA.BPTY_ID).
The size of Spool 16 is estimated with low confidence to be 651,447 rows (78,825,087 bytes). The estimated time for this step is 9.84 seconds.
| | |
| | |
| | 2) | We do an all-AMPs JOIN step from DEV_T.SLAT by way of a RowHash match scan with no residual conditions, which is joined to Spool 2 (Last
Use) by way of a RowHash match scan. DEV_T.SLAT and Spool 2 are left outer joined using a merge join, with a join condition of
("(DEV_T.SLAT.Org_BPTY_ID = Org_BPTY_ID) AND (DEV_T.SLAT.Sl_Ln_Amt_Typ_Num = Ln_Amt_Typ_Num)"). The result goes into Spool 17 (all_amps), which
is duplicated on all AMPs. Then we do a SORT to order Spool 17 by the hash code of (DEV_T.SLAT.Sl_Ln_Amt_Typ_Num, DEV_T.SLAT.Org_BPTY_ID). The
size of Spool 17 is estimated with low confidence to be 687,384 rows (32,307,048 bytes). The estimated time for this step is 0.14 seconds.
| | |
| | 21) | 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 ("BPTY_ID = BPTY_ID"). The result
goes into Spool 18 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 18 by the hash code of
(DEV_T.SPLA.Sl_Ln_Amt_Typ_Num, DEV_T.P.Org_BPTY_ID). The size of Spool 18 is estimated with low confidence to be 651,447 rows (78,825,087 bytes).
The estimated time for this step is 0.45 seconds.
| |
| | 22) | 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 right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(Org_BPTY_ID IS NULL)"), with a join condition of ("(Sl_Ln_Amt_Typ_Num = Sl_Ln_Amt_Typ_Num) AND (Org_BPTY_ID = Org_BPTY_ID)"). The result goes
into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 651,447 rows
(95,762,709 bytes). The estimated time for this step is 0.30 seconds.
| |
| | 23) | 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.
| |
Thank You
| |
