Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 07 Jan 2011 @ 15:11:10 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


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



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023