Tune qry

Database
Enthusiast

Tune qry

Explain SELECT  
AA.REPORT_DATE,
 BB.PD_DT AS FILE_DATE,
AA.ACE_PRM_LG_HRY_NUM  AS PRM_UPID,
AA.ACE_FNL_LG_HRY_NUM AS UPID,
AA.ACE_PRM_LG_HRY_NM  AS PRM_UPNM,
AA.ACE_FNL_LG_HRY_NM AS UPNM,
AA.ACE_PRM_ECNMC_ENT_NUM  AS PRM_EEID,
AA.ACE_FNL_ECNMC_ENT_NUM  AS EEID, 
AA.ACE_PRM_ECNMC_ENT_NM  AS PRM_EENM,
AA.ACE_FNL_ECNMC_ENT_NM  AS EENM, 
AA.ACE_PRM_LG_ENT_NUM  AS PRM_LEID,
AA.ACE_FNL_LG_ENT_NUM  AS LEID,
AA.ACE_PRM_LG_ENT_NM  AS PRM_LENM,
AA.ACE_FNL_LG_ENT_NM  AS LENM,
AA.PRM_UPID_W_IS,
AA.UPID_W_IS,
AA.PRM_UPNM_W_IS,
AA.UPNM_W_IS,
BB.G_OG_AU_GN_ID, 
BB.G_LB_GN_ID AS LB,
B.G_LB_TYP_8_DESC AS LB_DESCRIPTION, 
BB.RECORD_TYP,
BB.FK_S_ID_CD,,
BB.S_ID_CD,,
BB.FK_BK_NUM,
BB.BK_NUM, 
BB.FK_OGR_ID,
BB.OGR_ID,
BB.FK_COMM_NUM,
.OBGN_NUM,
BB.CUST_NM ,
BB.OGR_NM,
BB.CR_TYP_IND ,
BB.OSTD_AMT ,
BB.CMT_AMT ,
BB.BQR_SRCE,
BB.BQR_DRVD,
BB.NS_CD ,
BB.IND_INDST_DESC ,
BB.CML_GN_ID,
BB.DATA_FEED_CD,
AA.PD_DT ,
AA.ACE_LG_ENT_NUM_WHSL  ,
AA.ACE_ECON_ETY_NUM_WHSL ,
AA.ICIS_PD_DT ,
AA.PRM_ICIS_PD_DT ,  
AA.ACE_ULT_PRT_NUM_WHSL ,
BB.CQR_SRCE,
BB.CQR_DRVD,
BB.AQR_SRCE,
BB.AQR_DRVD,
BB.G_ETY_GN_ID,
BB.G_ETY_DESC,
CASE
      WHEN BB.G_ETY_IS_NA = 'Y'
     THEN ', NA'
      ELSE NULL           
END AS G_ETY_IS_NA,
CASE
      WHEN BB.G_ETY_IS_SC = 'Y'
     THEN ' South Central Cons'
      ELSE NULL       
END AS G_ETY_IS_SC,
CASE
      WHEN BB.G_ETY_IS_NW = 'Y'
     THEN  ' NorthWest Cons'
      ELSE NULL          
END AS  G_ETY_IS_NW,
CASE
      WHEN BB.G_ETY_IS_WFF = 'Y'
     THEN 'National Bk'
      ELSE  NULL          
END AS G_ETY_IS_WFF,
CASE
      WHEN BB.G_ETY_IS_WREIC = 'Y'
            THEN Preferred Fund Hold Corp Summary'
      ELSE  NULL        
END AS G_ETY_IS_WREIC,
CASE
      WHEN BB.G_ETY_IS_RUBY = 'Y'
      THEN 'A M CMPNY'
      ELSE  NULL          
END AS G_ETY_IS_RUBY,
BB.MTRY_DT,
BB.G_ACCT_LOAN_CAT_5,
BB.PRPS_CD,
BB.PRPS_CD_DESC,
BB.CUST_ZIP_CD,
ECN.ECN,
ECN.CUST_CO_NUM as CUST_CO_ID,
CLNT.CLNT_ID as WCIS_ID

FROM
 (
 SELECT
 ADD_MONTHS((A.PD_Dt- EXTRACT(DAY FROM A.PD_Dt)+1),1)-1  AS REPORT_DATE, --Data as of date
A.ACE_PRM_LG_HRY_NUM , --prelim Ultimate PRT ID
A.ACE_FNL_LG_HRY_NUM , --final Ultimate PRT ID
A.ACE_PRM_LG_HRY_NM  , --prelim Ultimate PRT Name
A.ACE_FNL_LG_HRY_NM , --final Ultimate PRT Name
A.ACE_PRM_ECNMC_ENT_NUM , --prelim Economic ETY ID
A.ACE_FNL_ECNMC_ENT_NUM  ,  --final Economic ETY ID
A.ACE_PRM_ECNMC_ENT_NM , --prelim Economic ETY Name
A.ACE_FNL_ECNMC_ENT_NM  ,  --final Economic ETY Name
A.ACE_PRM_LG_ENT_NUM , --prelim Legal ETY ID
A.ACE_FNL_LG_ENT_NUM  , --final Legal ETY ID
A.ACE_PRM_LG_ENT_NM  , --prelim Legal ETY Name
A.ACE_FNL_LG_ENT_NM  , --final Legal ETY Name
PRM_ICIS.ICIS_UPID AS PRM_UPID_W_IS , --prelim Wholesale ICIS ID
ICIS.ICIS_UPID AS UPID_W_IS , --final Wholesale ICIS ID
PRM_ICIS.ICIS_UPID_NM AS PRM_UPNM_W_IS , --prelim Wholesale ICIS Name
ICIS.ICIS_UPID_NM AS UPNM_W_IS, --final Wholesale ICIS Name
A.PD_DT , -- ACE PD date current month (not asked for)
A.ACE_LG_ENT_NUM_WHSL, --legal ETY WHSL ID (not asked for)
A.ACE_ECON_ETY_NUM_WHSL, --economic ETY ID (not asked for)
ICIS.ICIS_PD_DT , --ICIS PD_dt current (not asked for)
PRM_ICIS.PRM_ICIS_PD_DT ,  --ICIS PD_dt prior (not asked for)
A.ACE_ULT_PRT_NUM_WHSL  ,
a.CML_GN_id

FROM
               
  DB1_V3.D_ACE  A
 
  LEFT OUTER JOIN --current month ICIS data
  (
  SELECT
  PD_DT AS ICIS_PD_DT,
  TRIM(ICIS_UPID) AS ICIS_UPID,
  ICIS_UPID_NM
  FROM DB1_V3.D_ACE_WHSL
   GROUP BY 1,2,3
  ) ICIS
    ON     
     A.ACE_ULT_PRT_NUM_WHSL = ICIS.ICIS_UPID            
    AND A.PD_DT = ICIS.ICIS_PD_DT     
    LEFT OUTER JOIN  --prior month ICIS data
  (
  SELECT
  PD_DT AS PRM_ICIS_PD_DT ,
  ADD_MONTHS(PD_DT,+1)  AS PREV_PD_DT,
 TRIM( ICIS_UPID) AS  ICIS_UPID,
  ICIS_UPID_NM
  FROM DB1_V3.D_ACE_WHSL
  GROUP BY 1,2,3,4
  ) PRM_ICIS
    ON     
      A.ACE_ULT_PRT_NUM_WHSL = PRM_ICIS.ICIS_UPID              
        AND A.PD_DT =PRM_ICIS.PREV_PD_DT    ) AA
       
         INNER JOIN
          
(SELECT
  B.PD_DT, --Date of file
B.G_OG_AU_GN_ID, --AU
B.G_LB_GN_ID , --LOB
GL.G_LB_TYP_8_DESC , --LOB level 8 description
B.RECORD_TYP, -- record TYP
B.FK_S_ID_CD,, --facility system
B.S_ID_CD,, --system
B.FK_BK_NUM, --facility
B.BK_NUM, --
B.FK_OGR_ID, --facility OGR (not asked for)
B.OGR_ID, --OGR (not asked for)
B.FK_COMM_NUM, --facility number
B.OBGN_NUM, --instrument number
E.CUST_NM , --CMR Name
E.OGR_NM, --OGR name (not asked for)
C.CR_TYP_IND , --Risk TYP
B.OSTD_AMT , --Book Balance
B.CMT_AMT, --CMT Balance
CCS.BQR_SRCE, --BQR
C.BQR_DRVD, --DRVD BQR
C.NS_CD , --NS code
D.IND_INDST_DESC , --INDST description
B.CML_GN_ID, --CML Gen Id
B.DATA_FEED_CD, --Data Feed Code
 CCS.CQR_SRCE,
C.CQR_DRVD,
CCS.AQR_SRCE,
C.AQR_DRVD,
GLD.G_ETY_GN_ID,
GLD.G_ETY_DESC,
GLD. G_ETY_IS_NA,
GLD. G_ETY_IS_SC,
GLD.G_ETY_IS_NW,
GLD. G_ETY_IS_WFF,
GLD. G_ETY_IS_WREIC,
GLD. G_ETY_IS_RUBY  ,
C.MTRY_DT,
GLD.G_ACCT_LOAN_CAT_5,
PC.PRPS_CD,
PC.PRPS_CD_DESC,
E.CUST_ZIP_CD

FROM
                DB1_V3.F_CML B

INNER JOIN
  DB1_V3.D_ACT_COML C
    ON
      B.CML_GN_ID = C.CML_GN_ID
      AND B.PD_dt = C.PD_dt
INNER JOIN
  DB1_V3.COML_CODE_SRCE CCS
    ON
      B.CML_GN_ID = CCS.CML_GN_ID
      AND B.PD_dt = CCS.PD_dt
      INNER JOIN
  DB1_V3.D_CMR_COML E
    ON
      B.CML_GN_ID = E.CML_GN_ID
      AND B.PD_dt = e.PD_dt

JOIN  DB1_V3.G_LB_D GL
         ON   GL.G_LB_GN_ID = B.G_LB_GN_ID

LEFT  JOIN  DB2_V1.G_DATA_F_LG_ENT GLD
      ON  B.G_DATA_GN_ID = GLD.G_DATA_GN_ID
      -- AND B.G_ETY_GN_ID = GLD.G_ETY_GN_ID
     AND B.PD_DT = GLD.PD_DT

LEFT JOIN DB1_V3.D_PRPS_CODE PC
ON  B.PRPS_GN_ID = PC.PRPS_GN_ID

JOIN
 DB1_V3.D_INDST D
    ON
      B.INDST_DRVD_GN_ID = D.IND_GN_ID
 
 WHERE   CR_TYP_IND IN( 'DIRECT CREDIT','EXPIRED') 

) BB
    ON
      aa.CML_GN_ID = bb.CML_GN_ID
      AND AA.PD_DT = BB.PD_DT

      ----------------------------------------------------------------------------------------------------------------------------
     
      LEFT JOIN DB3_V1.LKP_CML_ECN  ECN
          ON
         AA.CML_GN_ID = ECN.CML_GN_ID
          AND AA.PD_DT= ECN.PD_DT
          AND ECN.OBLGR_ECN_ROW_NUM='1'
         
          LEFT JOIN DB3_V1.LKP_CML_CLNT CLNT
          ON
          AA.CML_GN_ID = CLNT.CML_GN_ID
          AND AA.PD_DT = CLNT.PD_DT
          AND CLNT.OBLGR_CLNT_ROW_NUM='1';

  1) First, we lock DB2_OPRS.G_LB_D for access, we
     lock DB4.D_ACE_WHSL for access, we lock
     DB5.PRPS_CODE_D for access, we lock
     DB5.INDST_D for access, we lock
     DB6.D_ACT_COML for access, we lock
     DB6.G_P3_ACT_XREF_R for access, we lock
     DB6.G_ORG_D for access, we lock DB6.G_ETY_D for
     access, we lock DB6.G_ACT_D for access, we lock
     DB6.LKP_CML_ECN in view
     DB3_V1.LKP_CML_ECN for access, we lock
     DB6.LKP_CML_CLNT in view
     DB3_V1.LKP_CML_CLNT for access, we lock
     DB6.G_DATA_F for access, we lock DB6.D_ACE for access,
     we lock DB6.f for access, we lock DB6.D_CMR_COML
     for access, and we lock DB6.CODE for access.
  2) Next, we do an all-AMPs SUM step to aggregate from
     DB4.D_ACE_WHSL by way of an all-rows scan
     with a condition of ("NOT (TRIM(BOTH FROM
     DB4.D_ACE_WHSL.ICIS_UPID )IS NULL)")
     , grouping by field1 (
     DB4.D_ACE_WHSL.PD_DT ,TRIM(BOTH FROM
     DB4.D_ACE_WHSL.ICIS_UPID)
     ,DB4.D_ACE_WHSL.ICIS_UPID_NM).  Aggregate
     Intermediate Results are computed globally, then placed in Spool 4.
     The size of Spool 4 is estimated with high confidence to be
     7,272,483 rows (1,687,216,056 bytes).  The estimated time for this
     step is 4.25 seconds.
  3) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by
          way of an all-rows scan into Spool 2 (used to materialize
          view, DRVD table or table function ICIS) (all_amps)
          (compressed columns allowed), which is built locally on the
          AMPs.  The size of Spool 2 is estimated with high confidence
          to be 7,272,483 rows (567,253,674 bytes).  The estimated time
          for this step is 0.26 seconds.
       2) We do an all-AMPs SUM step to aggregate from
          DB4.D_ACE_WHSL by way of an all-rows
          scan with a condition of ("(NOT (TRIM(BOTH FROM
          DB4.D_ACE_WHSL.ICIS_UPID )IS NULL ))
          AND (NOT
          (ADD_MONTHS((DB4.D_ACE_WHSL.PD_DT
          ),(1 ))IS NULL ))") , grouping by field1 (
          DB4.D_ACE_WHSL.PD_DT
          ,ADD_MONTHS((DB4.D_ACE_WHSL.PD_DT
          ),(1 )) ,TRIM(BOTH FROM
          DB4.D_ACE_WHSL.ICIS_UPID)
          ,DB4.D_ACE_WHSL.ICIS_UPID_NM) locking
          for access.  Aggregate Intermediate Results are computed
          globally, then placed in Spool 7.  The size of Spool 7 is
          estimated with high confidence to be 7,272,483 rows (
          1,774,485,852 bytes).  The estimated time for this step is
          5.22 seconds.
  4) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by
          way of an all-rows scan into Spool 1 (used to materialize
          view, DRVD table or table function PRM_ICIS) (all_amps)
          (compressed columns allowed), which is built locally on the
          AMPs.  The size of Spool 1 is estimated with high confidence
          to be 7,272,483 rows (596,343,606 bytes).  The estimated time
          for this step is 0.29 seconds.
       2) We do an all-AMPs RETRIEVE step from
          DB2_OPRS.G_LB_D by way of an all-rows scan
          with a condition of (
          "DB2_OPRS.G_LB_D.G_LB_CUR_IND = 'Y'") into
          Spool 10 (all_amps) (compressed columns allowed), which is
          duplicated on all AMPs.  The size of Spool 10 is estimated
          with high confidence to be 46,800 rows (3,229,200 bytes).
          The estimated time for this step is 0.03 seconds.
       3) We do an all-AMPs RETRIEVE step from
          DB2_OPRS.G_LB_D by way of an all-rows scan
          with no residual conditions locking for access into Spool 11
          (all_amps) (compressed columns allowed), which is duplicated
          on all AMPs.  The size of Spool 11 is estimated with high
          confidence to be 136,080 rows (5,579,280 bytes).  The
          estimated time for this step is 0.05 seconds.
  5) We do an all-AMPs JOIN step from Spool 10 by way of an all-rows
     scan, which is joined to DB6.G_DATA_F by way of an all-rows
     scan with no residual conditions.  Spool 10 and
     DB6.G_DATA_F are joined using a dynamic hash join, with a
     join condition of ("DB6.G_DATA_F.G_LB_GN_ID =
     G_LB_GN_ID").  The result goes into Spool 12 (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 12 is estimated with low confidence to be
     7,234,170 rows (651,075,300 bytes).  The estimated time for this
     step is 0.52 seconds.
  6) We do an all-AMPs RETRIEVE step from
     DB6.G_P3_ACT_XREF_R by way of an all-rows scan with no
     residual conditions into Spool 13 (all_amps) (compressed columns
     allowed), which is redistributed by the hash code of (
     DB6.G_P3_ACT_XREF_R.PD_DT,
     DB6.G_P3_ACT_XREF_R.G_P3_OLD_ACCT_GN_ID) to all AMPs.
     The size of Spool 13 is estimated with high confidence to be
     1,754,505 rows (42,108,120 bytes).  The estimated time for this
     step is 0.16 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 right outer joined using
     a single partition hash join, with condition(s) used for
     non-matching on right table ("NOT (G_INITIAL_LB_GN_ID IS NULL)"),
     with a join condition of ("(PD_DT <= G_LB_EXP_DT) AND
     ((PD_DT >= G_LB_EFF_DT) AND (G_INITIAL_LB_GN_ID =
     G_LB_GN_ID ))").  The result goes into Spool 14 (all_amps)
     (compressed columns allowed), which is redistributed by the hash
     code of (DB6.G_DATA_F.PD_DT,
     DB6.G_DATA_F.G_ACCT_GN_ID) to all AMPs.  The size of
     Spool 14 is estimated with low confidence to be 7,234,170 rows (
     506,391,900 bytes).  The estimated time for this step is 0.85
     seconds.
  8) We do an all-AMPs RETRIEVE step from DB6.G_ACT_D by way
     of an all-rows scan with no residual conditions into Spool 17
     (all_amps) (compressed columns allowed), which is redistributed by
     the hash code of (DB6.G_ACT_D.PD_DT,
     DB6.G_ACT_D.G_ACCT_GN_ID) to all AMPs.  The size of
     Spool 17 is estimated with high confidence to be 448,921 rows (
     26,486,339 bytes).  The estimated time for this step is 0.25
     seconds.
  9) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of an
     all-rows scan, which is joined to Spool 14 (Last Use) by way of an
     all-rows scan.  Spool 13 and Spool 14 are right outer joined using
     a single partition hash join, with condition(s) used for
     non-matching on right table ("(NOT (G_ACCT_GN_ID IS NULL )) AND
     (NOT (PD_DT IS NULL ))"), with a join condition of (
     "(G_ACCT_GN_ID = G_P3_OLD_ACCT_GN_ID) AND (PD_DT =
     PD_DT)").  The result goes into Spool 18 (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 18 is estimated with low confidence to be
     7,234,170 rows (506,391,900 bytes).  The estimated time for this
     step is 0.28 seconds.
 10) We execute the following steps in parallel.
      1) We do an all-AMPs RETRIEVE step from
         DB6.LKP_CML_CLNT in view
         DB3_V1.LKP_CML_CLNT by way of an all-rows scan
         with a condition of ("DB6.LKP_CML_CLNT in view
         DB3_V1.LKP_CML_CLNT.OBLGR_CLNT_ROW_NUM = 1")
         into Spool 20 (all_amps) (compressed columns allowed), which
         is redistributed by the hash code of (
         DB6.LKP_CML_CLNT.CML_GN_ID) to all AMPs.
         The size of Spool 20 is estimated with low confidence to be
         552,737 rows (33,164,220 bytes).  The estimated time for this
         step is 0.12 seconds.
      2) We do an all-AMPs RETRIEVE step from
         DB5.PRPS_CODE_D by way of an all-rows scan with a
         condition of ("DB5.PRPS_CODE_D.PRPS_CUR_IND =
         'Y'") into Spool 21 (all_amps) (compressed columns allowed),
         which is duplicated on all AMPs.  The size of Spool 21 is
         estimated with high confidence to be 212,400 rows (12,319,200
         bytes).  The estimated time for this step is 0.05 seconds.
 11) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of an
     all-rows scan, which is joined to Spool 18 (Last Use) by way of an
     all-rows scan.  Spool 17 and Spool 18 are joined using a single
     partition hash join, with a join condition of ("(G_ACCT_GN_ID =
     G_ACCT_GN_ID) AND (PD_DT = PD_DT)").  The result goes
     into Spool 22 (all_amps) (compressed columns allowed), which is
     redistributed by the hash code of (
     DB6.G_DATA_F.G_ORG_GN_ID) to all AMPs.  Then we do a SORT
     to order Spool 22 by row hash.  The size of Spool 22 is estimated
     with low confidence to be 7,234,170 rows (708,948,660 bytes).  The
     estimated time for this step is 1.27 seconds.
 12) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from DB6.CODE by way of a
         RowHash match scan with no residual conditions, which is
         joined to DB6.COML_CODE_SRCE by way of a RowHash match
         scan with no residual conditions locking
         DB6.COML_CODE_SRCE for access.  DB6.CODE and
         DB6.COML_CODE_SRCE are joined using a rowkey-based merge
         join, with a join condition of ("(DB6.CODE.PD_DT =
         DB6.COML_CODE_SRCE.PD_DT) AND
         (DB6.CODE.CML_GN_ID =
         DB6.COML_CODE_SRCE.CML_GN_ID)").  The result
         goes into Spool 23 (all_amps) (compressed columns allowed),
         which is built locally on the AMPs.  Then we do a SORT to
         partition Spool 23 by rowkey.  The size of Spool 23 is
         estimated with low confidence to be 7,511,362 rows (
         788,693,010 bytes).  The estimated time for this step is 0.94
         seconds.
      2) We do an all-AMPs RETRIEVE step from
         DB6.LKP_CML_ECN in view
         DB3_V1.LKP_CML_ECN by way of an all-rows scan
         with a condition of ("DB6.LKP_CML_ECN in view
         DB3_V1.LKP_CML_ECN.OBLGR_ECN_ROW_NUM = 1")
         into Spool 24 (all_amps) (compressed columns allowed), which
         is redistributed by the hash code of (
         DB6.LKP_CML_ECN.CML_GN_ID) to all AMPs.
         The size of Spool 24 is estimated with low confidence to be
         757,282 rows (46,951,484 bytes).  The estimated time for this
         step is 0.13 seconds.
 13) We do an all-AMPs JOIN step from Spool 20 (Last Use) by way of a
     RowHash match scan, which is joined to DB6.D_ACE by way of a
     RowHash match scan with no residual conditions.  Spool 20 and
     DB6.D_ACE are right outer joined using a single partition
     hash join, with a join condition of ("(DB6.D_ACE.PD_DT =
     PD_DT) AND (DB6.D_ACE.CML_GN_ID =
     CML_GN_ID)").  The result goes into Spool 25 (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 25 is estimated with low confidence to be
     5,003,433 rows (1,921,318,272 bytes).  The estimated time for this
     step is 1.37 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 DB6.f by way of an
     all-rows scan with no residual conditions.  Spool 21 and
     DB6.f are right outer joined using a dynamic hash join, with
     condition(s) used for non-matching on right table ("NOT
     (DB6.f.PRPS_GN_ID IS NULL)"), with a join condition of (
     "DB6.f.PRPS_GN_ID = PRPS_GN_ID").  The result goes
     into Spool 28 (all_amps) (compressed columns allowed), which is
     built locally on the AMPs.  The size of Spool 28 is estimated with
     low confidence to be 10,923,069 rows (2,479,536,663 bytes).  The
     estimated time for this step is 1.68 seconds.
 15) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
     an all-rows scan with a condition of ("NOT (ICIS.ICIS_UPID IS NULL)")
     into Spool 30 (all_amps) (compressed columns allowed) fanned out
     into 2 hash join partitions, which is redistributed by the hash
     code of (DB4.D_ACE_WHSL.ICIS_PD_DT,
     DB4.D_ACE_WHSL.ICIS_UPID) to all AMPs.  The
     size of Spool 30 is estimated with high confidence to be 7,272,483
     rows (509,073,810 bytes).  The estimated time for this step is
     0.65 seconds.
 16) We do an all-AMPs JOIN step from Spool 24 (Last Use) by way of an
     all-rows scan, which is joined to Spool 25 (Last Use) by way of an
     all-rows scan.  Spool 24 and Spool 25 are right outer joined using
     a single partition hash join, with a join condition of (
     "(PD_DT = PD_DT) AND (CML_GN_ID =
     CML_GN_ID)").  The result goes into Spool 31 (all_amps)
     (compressed columns allowed), which is redistributed by the hash
     code of (DB6.D_ACE.PD_DT,
     DB6.D_ACE.ACE_ULT_PRT_NUM_WHSL) to all AMPs into 2 hash
     join partitions.  The size of Spool 31 is estimated with low
     confidence to be 5,410,639 rows (2,131,791,766 bytes).  The
     estimated time for this step is 3.21 seconds.
 17) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
     an all-rows scan with a condition of ("NOT (PRM_ICIS.ICIS_UPID
     IS NULL)") into Spool 33 (all_amps) (compressed columns allowed)
     fanned out into 2 hash join partitions, which is redistributed by
     the hash code of (
     DB4.D_ACE_WHSL.PREV_PD_DT,
     DB4.D_ACE_WHSL.ICIS_UPID) to all AMPs.  The
     size of Spool 33 is estimated with high confidence to be 7,272,483
     rows (538,163,742 bytes).  The estimated time for this step is
     0.71 seconds.
 18) We do an all-AMPs JOIN step from Spool 30 (Last Use) by way of an
     all-rows scan, which is joined to Spool 31 (Last Use) by way of an
     all-rows scan.  Spool 30 and Spool 31 are right outer joined using
     a hash join of 2 partitions, with condition(s) used for
     non-matching on right table ("NOT (ACE_ULT_PRT_NUM_WHSL IS NULL)"),
     with a join condition of ("(PD_DT = ICIS_PD_DT) AND
     (ACE_ULT_PRT_NUM_WHSL = ICIS_UPID)").  The result goes into
     Spool 34 (all_amps) (compressed columns allowed), which is built
     locally on the AMPs into 2 hash join partitions.  The size of
     Spool 34 is estimated with low confidence to be 5,410,639 rows (
     2,440,198,189 bytes).  The estimated time for this step is 1.29
     seconds.
 19) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an
     all-rows scan, which is joined to Spool 28 (Last Use) by way of an
     all-rows scan.  Spool 10 and Spool 28 are joined using a single
     partition hash join, with a join condition of ("G_LB_GN_ID =
     G_LB_GN_ID").  The result goes into Spool 37 (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     Then we do a SORT to order Spool 37 by the hash code of (
     DB6.f.CML_GN_ID).  The size of Spool 37 is estimated
     with low confidence to be 10,860,533 rows (2,856,320,179 bytes).
     The estimated time for this step is 2.71 seconds.
 20) We do an all-AMPs JOIN step from DB6.D_CMR_COML by way
     of a RowHash match scan with no residual conditions, which is
     joined to Spool 23 (Last Use) by way of a RowHash match scan.
     DB6.D_CMR_COML and Spool 23 are joined using a
     rowkey-based merge join, with a join condition of ("(PD_DT =
     DB6.D_CMR_COML.PD_DT) AND ((CML_GN_ID =
     DB6.D_CMR_COML.CML_GN_ID) AND ((PD_DT =
     DB6.D_CMR_COML.PD_DT) AND (CML_GN_ID =
     DB6.D_CMR_COML.CML_GN_ID )))").  The result
     goes into Spool 38 (all_amps) (compressed columns allowed), which
     is built locally on the AMPs.  Then we do a SORT to order Spool 38
     by the hash code of (DB6.D_CMR_COML.CML_GN_ID).
     The size of Spool 38 is estimated with low confidence to be
     11,918,557 rows (2,920,046,465 bytes).  The estimated time for
     this step is 2.66 seconds.
 21) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from Spool 37 (Last Use) by way of
         a RowHash match scan, which is joined to Spool 38 (Last Use)
         by way of a RowHash match scan.  Spool 37 and Spool 38 are
         joined using a merge join, with a join condition of (
         "(PD_DT = PD_DT) AND ((CML_GN_ID =
         CML_GN_ID) AND ((PD_DT = PD_DT) AND
         ((CML_GN_ID = CML_GN_ID) AND
         ((CML_GN_ID = CML_GN_ID) AND (PD_DT =
         PD_DT )))))").  The result goes into Spool 39 (all_amps)
         (compressed columns allowed), which is built locally on the
         AMPs.  The size of Spool 39 is estimated with low confidence
         to be 18,803,344 rows (9,307,655,280 bytes).  The estimated
         time for this step is 4.23 seconds.
      2) We do an all-AMPs RETRIEVE step from DB5.INDST_D
         by way of an all-rows scan with a condition of (
         "DB5.INDST_D.IND_CUR_IND = 'Y'") into Spool 40
         (all_amps) (compressed columns allowed), which is duplicated
         on all AMPs.  The size of Spool 40 is estimated with high
         confidence to be 756,000 rows (28,728,000 bytes).  The
         estimated time for this step is 0.05 seconds.
 22) We do an all-AMPs JOIN step from Spool 39 (Last Use) by way of a
     RowHash match scan, which is joined to DB6.D_ACT_COML by
     way of a RowHash match scan with a condition of (
     "(DB6.D_ACT_COML.CR_TYP_IND = 'EXPIRED ') OR
     (DB6.D_ACT_COML.CR_TYP_IND = 'DIRECT CREDIT ')").
     Spool 39 and DB6.D_ACT_COML are joined using a
     sliding-window merge join, with a join condition of (
     "(CML_GN_ID = DB6.D_ACT_COML.CML_GN_ID)
     AND ((PD_DT = DB6.D_ACT_COML.PD_DT) AND
     ((CML_GN_ID = DB6.D_ACT_COML.CML_GN_ID)
     AND ((PD_DT = DB6.D_ACT_COML.PD_DT) AND
     ((PD_DT = DB6.D_ACT_COML.PD_DT) AND
     ((CML_GN_ID = DB6.D_ACT_COML.CML_GN_ID)
     AND ((CML_GN_ID =
     DB6.D_ACT_COML.CML_GN_ID) AND (PD_DT =
     DB6.D_ACT_COML.PD_DT )))))))").  The result goes
     into Spool 41 (all_amps) (compressed columns allowed), which is
     built locally on the AMPs.  The size of Spool 41 is estimated with
     low confidence to be 28,665,492 rows (14,189,418,540 bytes).  The
     estimated time for this step is 7.56 seconds.
 23) We do an all-AMPs JOIN step from Spool 33 (Last Use) by way of an
     all-rows scan, which is joined to Spool 34 (Last Use) by way of an
     all-rows scan.  Spool 33 and Spool 34 are right outer joined using
     a hash join of 2 partitions, with condition(s) used for
     non-matching on right table ("NOT (ACE_ULT_PRT_NUM_WHSL IS NULL)"),
     with a join condition of ("(PD_DT = PREV_PD_DT) AND
     (ACE_ULT_PRT_NUM_WHSL = ICIS_UPID)").  The result goes into
     Spool 42 (all_amps) (compressed columns allowed), which is
     redistributed by the hash code of (
     DB6.D_ACE.CML_GN_ID) to all AMPs into 19 hash join
     partitions.  The size of Spool 42 is estimated with low confidence
     to be 6,733,393 rows (3,420,563,644 bytes).  The estimated time
     for this step is 3.85 seconds.
 24) We do an all-AMPs JOIN step from Spool 40 (Last Use) by way of an
     all-rows scan, which is joined to Spool 41 (Last Use) by way of an
     all-rows scan.  Spool 40 and Spool 41 are joined using a single
     partition hash join, with a join condition of ("(IND_CUR_IND =
     G_LB_CUR_IND) AND ((( CASE WHEN (((SIC_CD_SRCE >= '0001 ') AND
     (SIC_CD_SRCE <= '9999 ')) AND ((PD_DT <= DATE '2011-07-01')
     AND (INDST_DRVD_GN_ID = '0 '))) THEN (SIC_CD_SRCE) ELSE
     (INDST_DRVD_GN_ID) END ))= IND_GN_ID)").  The result goes
     into Spool 45 (all_amps) (compressed columns allowed), which is
     built locally on the AMPs into 19 hash join partitions.  The size
     of Spool 45 is estimated with low confidence to be 28,665,492 rows
     (14,332,746,000 bytes).  The estimated time for this step is 7.28
     seconds.
 25) We execute the following steps in parallel.
      1) We do an all-AMPs JOIN step from DB6.G_ORG_D by way of
         a RowHash match scan with no residual conditions, which is
         joined to Spool 22 (Last Use) by way of a RowHash match scan.
         DB6.G_ORG_D and Spool 22 are joined using a
         sliding-window merge join, with a join condition of (
         "(DB6.G_ORG_D.G_ORG_GN_ID = G_ORG_GN_ID) AND
         (DB6.G_ORG_D.PD_DT = PD_DT)").  The result goes
         into Spool 46 (all_amps) (compressed columns allowed), which
         is built locally on the AMPs into 2 hash join partitions.  The
         size of Spool 46 is estimated with low confidence to be
         7,234,170 rows (658,309,470 bytes).  The estimated time for
         this step is 1.38 seconds.
      2) We do an all-AMPs RETRIEVE step from DB6.G_ETY_D by
         way of an all-rows scan with a condition of (
         "(DB6.G_ETY_D.G_ETY_GEN02_MEMBER > 'GLCC') OR
         (DB6.G_ETY_D.G_ETY_GEN02_MEMBER < 'GLCC')") into
         Spool 47 (all_amps) (compressed columns allowed) fanned out
         into 2 hash join partitions, which is duplicated on all AMPs.
         The size of Spool 47 is estimated with low confidence to be
         16,818,480 rows (1,059,564,240 bytes).  The estimated time for
         this step is 0.54 seconds.
 26) We do an all-AMPs JOIN step from Spool 46 (Last Use) by way of an
     all-rows scan, which is joined to Spool 47 (Last Use) by way of an
     all-rows scan.  Spool 46 and Spool 47 are joined using a hash join
     of 2 partitions, with a join condition of ("(PD_DT = PD_DT)
     AND ((G_ETY_GN_ID = G_ETY_GN_ID) AND ((PD_DT =
     PD_DT) AND (PD_DT = PD_DT )))").  The result goes into
     Spool 48 (all_amps) (compressed columns allowed), which is
     redistributed by the hash code of (DB6.G_DATA_F.PD_DT,
     DB6.G_DATA_F.G_DATA_GN_ID) to all AMPs into 5 hash join
     partitions.  The size of Spool 48 is estimated with low confidence
     to be 7,234,170 rows (911,505,420 bytes).  The estimated time for
     this step is 1.19 seconds.
 27) We do an all-AMPs JOIN step from Spool 42 (Last Use) by way of an
     all-rows scan, which is joined to Spool 45 (Last Use) by way of an
     all-rows scan.  Spool 42 and Spool 45 are joined using a hash join
     of 19 partitions, with a join condition of ("(PD_DT =
     PD_DT) AND (CML_GN_ID = CML_GN_ID)").  The
     result goes into Spool 49 (all_amps) (compressed columns allowed),
     which is redistributed by the hash code of (DB6.f.PD_DT,
     DB6.f.G_DATA_GN_ID) to all AMPs into 5 hash join
     partitions.  The size of Spool 49 is estimated with low confidence
     to be 63,424,349 rows (60,887,375,040 bytes).  The estimated time
     for this step is 5 minutes and 31 seconds.
 28) We do an all-AMPs JOIN step from Spool 48 (Last Use) by way of an
     all-rows scan, which is joined to Spool 49 (Last Use) by way of an
     all-rows scan.  Spool 48 and Spool 49 are right outer joined using
     a hash join of 5 partitions, with condition(s) used for
     non-matching on right table ("NOT (G_DATA_GN_ID IS NULL)"), with
     a join condition of ("(PD_DT = PD_DT) AND (G_DATA_GN_ID
     = G_DATA_GN_ID)").  The result goes into Spool 9 (group_amps),
     which is built locally on the AMPs.  The size of Spool 9 is
     estimated with low confidence to be 63,424,349 rows (
     69,322,813,457 bytes).  The estimated time for this step is 32.53
     seconds.
 29) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 9 are sent back to the user as the result of
     statement 1.  The total estimated time is 6 minutes and 58 seconds.

Experts, any suggestion on how to reduce CPU and IO skw?

4 REPLIES
Enthusiast

Re: Tune qry

Dieter,

Need your expert help..Any suggestions on how this query can be written to improve performance?

Enthusiast

Re: Tune qry

Anyone, any suggestions?

Enthusiast

Re: Tune qry

Appreciate any pointers!

Junior Contributor

Re: Tune qry

There's no evident problem, did you check QryLogStepsV for the actual resusage?