Query taking very long and very high spool usage

Database
Fan

Query taking very long and very high spool usage


EXPLAIN SELECT
REIH.Lg_Ctr_K4_Key
, C.Pk_Date
, C.L_Pk_G_Name
, C.Ct_TNum
, C.Br_MNum
, C.Lp_Crnum
, C.Ct_TNum
, C.Oc_TypeName
, C.Ln_Of_CName

, Case when(C.B_To_Date - C.Lp_VntgDate) <30
Then 'NewtoMonth' Else 'Exist'
End as Acc_status

, C.F_B_APd_Id
, Sum ( GP.F_Entrd_Avg_BAmt )
, Sum ( C.Prm_T_Entrd_Amt )
, Sum ( C.Prm_T_Entrd_Amt )
, Sum ( C.Prm_Ovl_Entrd_Amt )
, Sum ( C.Prm_Alc_Entrd_Amt) 

FROM   AbTbla.Mg_Ctr REIH
INNER JOIN PQTbla.Z_TP_GP_EOM GP

ON REIH.Lg_CtrId = GP.Acc_ctrId

INNER JOIN PQTbla.RPT_CAcct_Om_Mth C
ON C.Pk_Date=GP.GL_Effect_Date

WHERE Lg_Ctr_K4_Key in ('70000','BR1290','BR2514','BR2984','BR1290')
AND C.Pk_Date = CAST('20160630' AS DATE)
AND GP.GL_Effect_Date = CAST('20160630' AS DATE)
AND C.Pk_Date_Run_Num=GP.Prcs_Date_Rn_Num
--AND C.Pv_Ss_Cod=GP.Pv_Ss_Cod   
AND C.Ldp_Name=GP.Ldp_Name
AND C.PT_SB_Act_Ety_Id=GP.PT_SB_Act_Ety_Id
AND C.PT_SB_Act_Ctr_Id=GP.PT_SB_Act_Ctr_Id
AND C.PT_SB_Act_Act_Id=GP.PT_SB_Act_Act_Id
AND C.F_B_APd_Id=GP.PT_SB_Act_Pdct_Id
AND C.PT_SB_Act_Ccy_Id=GP.PT_SB_Act_Ccy_Id

GROUP BY HIER.Lg_Ctr_K4_Key
, C.Pk_Date
, C.L_Pk_G_Name
, C.Ct_TNum
, C.Br_MNum
, C.Lp_Crnum
, C.Ct_TNum
, C.Oc_TypeName
, C.Ln_Of_CName
, Acc_status
, C.F_B_APd_Id;

 

Query plan:

 

  1) First, we lock AbTabla.Mg_Ctr in view
     AbTbla.Mg_Ctr for access, we lock
     PQTablA.Z_TP_GP_EOM in view
     PQTbla.Z_TP_GP_EOM for access, and we lock
     PQTablA.RPT_CAcct_Om_Mth in view
     PQTbla.RPT_CAcct_Om_Mth for access.
  2) Next, we execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from
          PQTablA.Z_TP_GP_EOM in view
          PQTbla.Z_TP_GP_EOM by way of an all-rows
          scan with a condition of ("(( CASE WHEN (((SUBSTR(ACCOUNT ,19
          ,1 ))= 'Y') OR (PQTablA.Z_TP_GP_EOM in view
          PQTbla.Z_TP_GP_EOM.RowSec19_Prscrb_Emp_GL_Ind
          <> 'Y')) THEN (PQTablA.Z_TP_GP_EOM in view
          PQTbla.Z_TP_GP_EOM.GL_Effect_Date) ELSE
          (DATE '0101-01-01') END ))= DATE '2016-06-30'") into Spool 4
          (all_amps), which is built locally on the AMPs.  The size of
          Spool 4 is estimated with no confidence to be 1,129,127 rows
          (114,041,827 bytes).  The estimated time for this step is
          2.96 seconds.
       2) We do an all-AMPs RETRIEVE step from AbTabla.Mg_Ctr
          in view AbTbla.Mg_Ctr by way of an all-rows scan
          with a condition of ("(AbTabla.Mg_Ctr in view
          AbTbla.Mg_Ctr.Lg_Ctr_K4_Key = 'BR2984 ') OR
          ((AbTabla.Mg_Ctr in view
          AbTbla.Mg_Ctr.Lg_Ctr_K4_Key = 'BR1290 ') OR
          ((AbTabla.Mg_Ctr in view
          AbTbla.Mg_Ctr.Lg_Ctr_K4_Key = 'BR2514 ') OR
          (AbTabla.Mg_Ctr in view
          AbTbla.Mg_Ctr.Lg_Ctr_K4_Key = '70000 ')))")
          into Spool 5 (all_amps), which is duplicated on all AMPs.
          The size of Spool 5 is estimated with no confidence to be
          1,940,976 rows (75,698,064 bytes).  The estimated time for
          this step is 0.22 seconds.
  3) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
     all-rows scan, which is joined to Spool 5 (Last Use) by way of an
     all-rows scan.  Spool 4 and Spool 5 are joined using a single
     partition hash join, with a join condition of ("LG_Centre_Id = ((
     CASE WHEN (((SUBSTR(ACCOUNT ,19 ,1 ))= 'Y') OR
     (RowSec19_Prscrb_Emp_GL_Ind <> 'Y')) THEN (PL_Act_Ctr_Id) ELSE
     ('?') END ))").  The result goes into Spool 6 (all_amps), which is
     redistributed by the hash code of (
     PQTablA.Z_TP_GP_EOM.PT_SB_Act_Ccy_Id,
     PQTablA.Z_TP_GP_EOM.PT_SB_Act_Pdct_Id,
     PQTablA.Z_TP_GP_EOM.PT_SB_Act_Act_Id,
     PQTablA.Z_TP_GP_EOM.PT_SB_Act_Ctr_Id,
     PQTablA.Z_TP_GP_EOM.PT_SB_Act_Ety_Id,
     PQTablA.Z_TP_GP_EOM.Process_Date_Run_Num, (CASE
     WHEN (((SUBSTR(ACCOUNT ,19 ,1 ))= 'Y') OR
     (PQTablA.Z_TP_GP_EOM.RowSec19_Prscrb_Emp_GL_Ind <>
     'Y')) THEN (PQTablA.Z_TP_GP_EOM.Ldp_Name) ELSE
     ('?') END )(VARCHAR(30), CHARACTER SET LATIN, NOT CASESPECIFIC), (
     CASE WHEN (((SUBSTR(ACCOUNT ,19 ,1 ))= 'Y') OR
     (PQTablA.Z_TP_GP_EOM.RowSec19_Prscrb_Emp_GL_Ind <>
     'Y')) THEN (PQTablA.Z_TP_GP_EOM.GL_Effect_Date)
     ELSE (DATE '0101-01-01') END )(DATE)) to all AMPs.  The size of
     Spool 6 is estimated with no confidence to be 733,953 rows (
     81,468,783 bytes).  The estimated time for this step is 0.31
     seconds.
  4) We do an all-AMPs RETRIEVE step from a single partition of
     PQTablA.RPT_CAcct_Om_Mth in view
     PQTbla.RPT_CAcct_Om_Mth with a condition of (
     "PQTablA.RPT_CAcct_Om_Mth in view
     PQTbla.RPT_CAcct_Om_Mth.Process_Date = DATE
     '2016-06-30'") with a residual condition of (
     "PQTablA.RPT_CAcct_Om_Mth in view
     PQTbla.RPT_CAcct_Om_Mth.Process_Date = DATE
     '2016-06-30'") into Spool 7 (all_amps), which is redistributed by
     the hash code of (
     PQTablA.RPT_CAcct_Om_Mth.Process_Date,
     PQTablA.RPT_CAcct_Om_Mth.Process_Date_Run_Num,
     PQTablA.RPT_CAcct_Om_Mth.Ldp_Name,
     PQTablA.RPT_CAcct_Om_Mth.PT_SB_Act_Ety_Id,
     PQTablA.RPT_CAcct_Om_Mth.PT_SB_Act_Ctr_Id,
     PQTablA.RPT_CAcct_Om_Mth.PT_SB_Act_Act_Id,
     PQTablA.RPT_CAcct_Om_Mth.PT_SB_Act_Pdct_Id,
     PQTablA.RPT_CAcct_Om_Mth.PT_SB_Act_Ccy_Id) to
     all AMPs.  The size of Spool 7 is estimated with high confidence
     to be 35,156,918 rows (5,765,734,552 bytes).  The estimated time
     for this step is 18.63 seconds.
  5) 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 scan.  Spool 6 and Spool 7 are joined using a product
     join, with a join condition of ("(Ldp_Name = (( CASE WHEN
     (((SUBSTR(ACCOUNT ,19 ,1 ))= 'Y') OR (RowSec19_Prscrb_Emp_GL_Ind
     <> 'Y')) THEN (Ldp_Name) ELSE ('?') END ))) AND ((Process_Date =
     (( CASE WHEN (((SUBSTR(ACCOUNT ,19 ,1 ))= 'Y') OR
     (RowSec19_Prscrb_Emp_GL_Ind <> 'Y')) THEN (GL_Effect_Date) ELSE
     (DATE '0101-01-01') END ))) AND ((PT_SB_Act_Ccy_Id =
     PT_SB_Act_Ccy_Id) AND ((PT_SB_Act_Pdct_Id =
     PT_SB_Act_Pdct_Id) AND ((PT_SB_Act_Act_Id =
     PT_SB_Act_Act_Id) AND ((PT_SB_Act_Ctr_Id =
     PT_SB_Act_Ctr_Id) AND ((PT_SB_Act_Ety_Id =
     PT_SB_Act_Ety_Id) AND (Prcs_Date_Rn_Num =
     Prcs_Date_Rn_Num )))))))").  The result goes into Spool 3
     (all_amps), which is built locally on the AMPs.  The result spool
     file will not be cached in memory.  The size of Spool 3 is
     estimated with no confidence to be 505,951,479,154 rows (
     81,458,188,143,794 bytes).  The estimated time for this step is 21
     hours and 11 minutes.
  6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
     way of an all-rows scan , grouping by field1 (
     AbTabla.Mg_Ctr.Lg_Ctr_K4_Key
     ,PQTablA.RPT_CAcct_Om_Mth.Process_Date
     ,PQTablA.RPT_CAcct_Om_Mth.LTP_Prod_Grp_Name
     ,PQTablA.RPT_CAcct_Om_Mth.Contrt_Term_Num
     ,PQTablA.RPT_CAcct_Om_Mth.Bhvr_Matury_Num
     ,PQTablA.RPT_CAcct_Om_Mth.LTP_Curve_Name
     ,PQTablA.RPT_CAcct_Om_Mth.Occup_Type_Name
     ,PQTablA.RPT_CAcct_Om_Mth.Line_Of_Cr_Name ,( CASE
     WHEN (((PQTablA.RPT_CAcct_Om_Mth.Bus_To_Date )-
     (PQTablA.RPT_CAcct_Om_Mth.LTP_Vintage_Date ))< 30)
     THEN ('NewtoMonth') ELSE ('Exist') END)
     ,PQTablA.RPT_CAcct_Om_Mth.PT_SB_Act_Pdct_Id).
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 8.  The aggregate spool file will not be cached in memory.
     The size of Spool 8 is estimated with no confidence to be
     379,463,609,366 rows (166,963,988,121,040 bytes).  The estimated
     time for this step is 11 hours and 43 minutes.
  7) We do an all-AMPs RETRIEVE step from Spool 8 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  The result spool file will not be cached in memory.
     The size of Spool 1 is estimated with no confidence to be
     379,463,609,366 rows (80,825,748,794,958 bytes).  The estimated
     time for this step is 26 hours and 20 minutes.
  8) 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 59 hours and 14 minutes.

1 REPLY
Senior Apprentice

Re: Query taking very long and very high spool usage

Two out of three tables are accessed with no confidence, no statistics on AbTabla.Mg_Ctr.Lg_Ctr_K4_Key and a CASE WHEN (((SUBSTR(ACCOUNT ,19,1 ))= 'Y') ... statement in  Z_TP_GP_EOM used for WHERE & JOIN. Of course this might cause totally wrong estimates (check dbc.QryLogStepsV).

You should collect stats on Lg_Ctr_K4_Key and maybe the CASEes, too.