Tune qry

Database
Enthusiast

Tune qry

Hi!

Trying to tune thsi qry with stats collection and creating secdry/join indices but it does not help. Can you please take a look at it? Alll suggestions are appreciated.

SELECT            

COALESCE(D.clnt_nbr,'')            CLIENT_NUMBER, COALESCE(TRIM(B1.p_nbr) || '/' || TRIM(B1.pln_nbr) || '/' || TRIM(B1.subpln_nbr),'')    CNTRCT_UNQ_KY,

COALESCE(TRIM(B1.pln_nbr),'')              PLAN_NUMBER,COALESCE(TRIM(B1.subpln_nbr),'')        SUBPLAN_NUMBER,COALESCE(oreplace(oreplace(oreplace(oreplace(oreplace(TRIM(B1.fl_nm),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'<','&gt;'),'')  PPNT_NAME,

COALESCE(oreplace(oreplace(oreplace(oreplace(oreplace(TRIM(B1.FRST_NM),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'<','&gt;'),'')      PPNT_FIRST_NAME,

COALESCE(oreplace(oreplace(oreplace(oreplace(oreplace(TRIM(B1.LST_NM),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'<','&gt;'),'')    PPNT_LAST_NAME,

COALESCE(TRIM(B1.p_nbr),'')        SSN,COALESCE(TRIM(B1.PIN_NBR),'')         PIN,COALESCE(oreplace(oreplace(oreplace(oreplace(TRIM(B1.pln_nm),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'')                                                   PLAN_NAME,

COALESCE(oreplace(oreplace(oreplace(oreplace(TRIM(C.INVSMT_VEH_LGL_DESC_TXT),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'')       INVSMT_NAME,

COALESCE(TRIM(C.INVSMT_SRC_CD),'')                            FUND_SOURCE,COALESCE(oreplace(oreplace(oreplace(oreplace(TRIM(C.INVSMT_SRC_CD_DESC_TXT),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'')        SOURCE_NAME,

COALESCE(TRIM(''),'')                           LN_STAT,COALESCE(TRIM(''),'')                    LNS_PAST_DUE_30,COALESCE(TRIM(''),'')               LNS_PAST_DUE_60,

COALESCE(TRIM(''),'')         LNS_PAST_DUE_90,COALESCE(TRIM(''),'')    DY_PST_DUE,COALESCE(TRIM(''),'')               DSTRBTN_TYP,COALESCE(TRIM(''),'')         DSTRBTN_REASON_CD,

COALESCE(TRIM(''),'')              DSTRBTN_TYP_CD,COALESCE(TRIM(''),'')                                   DSTRBTN_TYP_KEY,

CASE WHEN TRIM(C.INVSMT_tickr_sym_cd)='LN#' THEN ‘Otstdng LN Bal’

                                                WHEN TRIM(C.INVSMT_tickr_sym_cd)='DMLN#' THEN ‘Otstdng LN Bal’

WHEN TRIM(asset_TableH_desc_txt)='' THEN ‘Ast Cls - NA’  ELSE COALESCE(oreplace(oreplace(oreplace(oreplace

(oreplace(TRIM(asset_TableH_desc_txt) ,'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'<','&gt;'),'')   END         CATEGORY_CLASS_DESC,

COALESCE(TRIM(''),'')           NUMBER_OF_INVSMT,

CASE WHEN TRIM(B1.DF_ENR_TYP_CD) IN ('1', '2') THEN  'Y' ELSE               'N'

END DF_ALCN_IND,

case when TableH_cd = ' ' then 'P' else TableH_cd end ACCOUNT_TYP,

COALESCE(TRIM(''),'')       LNS_BAL_MORE_THAN_50K,

CASE  WHEN  (CNTRBN_MTY_DT BETWEEN DATE '2011-05-31' -80 AND  DATE '2011-05-31') AND CNTRBN_SUM_MTY <> 0  THEN   COALESCE(TRIM('Y'),'') ELSE COALESCE(TRIM('N'),'') END  ACTIVE_CNTRBTN_IND,

(CASE WHEN COALESCE(UPPER(TRIM(C.INVSMT_veh_lgl_desc_txt)),'')  LIKE 'TIAA TRADITIONAL%'

   THEN 'TL'  ELSE TRIM(C.src_sys_veh_cd)  

   ENDAS FUND_INVESTMENT,

  CASE WHEN UPPER(TRIM(B1.gndr_nm)) = 'UNKNOWN' THEN 'Not Supplied'

ELSE   COALESCE(TRIM(B1.gndr_nm),'')  END GENDER,

CASE   WHEN UPPER(TRIM(B1.pln_elgbl_sts_nm)) = 'UNKNOWN'   THEN 'Not Supplied'

ELSE   COALESCE(oreplace(oreplace(oreplace(oreplace(TRIM(B1.pln_elgbl_sts_nm),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'')

END          PPNT_STAT,

CASE   WHEN UPPER(TRIM(B1.marl_sts_nm) ) = 'UNKNOWN'   THEN 'Not Supplied'

ELSE   COALESCE(oreplace(oreplace(oreplace(oreplace(TRIM(B1.marl_sts_nm),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'') 

END           MARITAL_STAT,

CASE   WHEN B1.dob <=  '1900-01-01'  THEN ''

ELSE   COALESCE((B1.dob  (FORMAT 'YYYYMMDD')  (CHAR(8)) ),'')

END           DOB,  

CASE   WHEN TRANSLATE_CHK(B1.Addr_ln_1_nm using latin_to_uniCD) = 0 THEN

COALESCE(oreplace(oreplace(oreplace(oreplace(TRIM(B1.Addr_ln_1_nm),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'')  ELSE   ''

END     PPNT_ADDRESS_LINE1,

CASE   WHEN TRANSLATE_CHK(B1.city_nm USING latin_to_uniCD) = 0 THEN

COALESCE(oreplace(oreplace(oreplace(oreplace(TRIM(B1.city_nm),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;'),'')  ELSE                ''

END       PPNT_ADDRESS_CITY,

CASE   WHEN TRANSLATE_CHK(B1.st_cd USING latin_to_uniCD) = 0 THEN

COALESCE(TRIM(B1.st_cd),'') ELSE      ''

END       PPNT_ADDRESS_STATE,

B1.pstcd_area_cd        PPNT_ADDRESS_ZIP,

TRIM(B1.vest_sts_nm)         VESTING_STAT,

CASE   WHEN TRIM(B1.hce_ind) IN ('5', '6') THEN  'YES'  ELSE             'NO'

END     HIGHLY_COMPENSATED,

CASE   WHEN B1.frst_cntrbn_pd_dt <=  '1900-01-01'  THEN ''  

ELSE     COALESCE((B1.frst_cntrbn_pd_dt (FORMAT  'YYYYMMDD')  (CHAR(8)) ),'')

END             FIRST_PREMIUM_PAID_DATE,

CASE   WHEN cpf.lst_cntrbn_pd_dt  <=  '1900-01-01'  THEN ''

ELSE COALESCE((cpf.lst_cntrbn_pd_dt (FORMAT  'YYYYMMDD')  (CHAR(8)) ),'') 

END                          LAST_PREMIUM_PAID_DATE,

TRIM( substr( (substr( B1.sub_pln_nm, 0, index(B1.sub_pln_nm,

                                ')')) ||  ' ' || substr(B1.sub_pln_nm,  index(B1.sub_pln_nm,

                                ')') + 1)) , 0, index((substr(B1.sub_pln_nm, 0, index(B1.sub_pln_nm,

                                ')')) ||  ' ' || substr(B1.sub_pln_nm,  index(B1.sub_pln_nm,

                                ')') + 1)), '('))

       ||  ' '

       || substr((substr(B1.sub_pln_nm, 0, index(B1.sub_pln_nm,

                                ')')) ||  ' ' || substr(B1.sub_pln_nm,  index(B1.sub_pln_nm,

                                ')') + 1)),  index((substr(B1.sub_pln_nm, 0, index(B1.sub_pln_nm,

                                ')')) ||  ' ' || substr(B1.sub_pln_nm,  index(B1.sub_pln_nm,

                                ')') + 1)), '(') + 1) ) SUB_PLAN_NAME,

TRIM(B1.empm_lfcysts_typ_nm)      EMPLOYEE_STAT,

oreplace(oreplace(oreplace(oreplace(TRIM(B1.empm_lfcysts_rsn_typ_nm),'&', '&amp;'),'"','&quot;'),'>','&gt;'),'''','&apos;')      EMPLOYEE_STAT_REASON,

CASE   WHEN TRIM(B1.DF_ENR_TYP_CD) IN ('1') OR TRIM(B1.ENRL_TYP_NM) IN

(‘Int/Adm Enr’ , ‘ES Enr’, ‘Remt Enr’) THEN  ‘Df’

WHEN B1.ENRL_TYP_NM IN ('Int/Ind Enr') THEN ‘Ol’

WHEN  B1.ENRL_TYP_NM IN ('Paper Enr' , 'Int/Ira Enr' ) THEN ‘Ppr’

WHEN TRIM(B1.DF_ENR_TYP_CD) IN ('2') OR B1.ENRL_TYP_NM IN

('Int/Bulk' , 'Rgr Enr' , 'ERS/ODE Enr' , 'LNs Enr' , 'Negative Enr','UnKnown') THEN 'Other' ELSE 'Other'

END        ENR_TYP_NAME,

(CASE WHEN B1.DTH_DT IN('1700-01-01','2100-12-31') then (B1.EFF_DT - B1.DOB)/365

 ELSE (B1.DTH_DT - B1.DOB)/365

   ENDAS AGE

FROM .TABLEA  A

JOIN        TableB B

on A.p_pln_prdim_id = B.p_pln_prdim_id

JOIN TABLEC C

ON A.INVSMT_FND_DIM_ID = C.INVSMT_FUND_DIM_ID

join TableD D

on D.instn_pln_prdim_id = A.instn_pln_prdim_id

 and D.clnt_nbr in  (sel trim(client_id) from TableE where Trim(SHARD_ID)= 's01')

join (sel * from TableB where end_dt = '2100-12-31') B1

  on B.prd_arngmanch_id = B1.prd_arngmanch_id

  join  (sel TableH_cd, prd_arngmanch_id

                                                                from TableF p

                                                                 join TableH c

                                                                on c.TableH_id = p.P_RECRD_CD_ID

                                                                where c.end_dt = '2100-12-31'

                                                                and p.end_dt = '2100-12-31'

                                                                ) ppa

 on ppa.prd_arngmanch_id = B1.prd_arngmanch_id

LEFT OUTER JOIN (SELECT P_PLN_PRDIM_ID, lst_cntrbn_pd_dt

                                                                                                                                                                                                FROM  TABLEG G

                                                                                                                                                                                                WHERE             G.end_dt = '2100-12-31' group by 1,2

                                                                                                                                                                                                ) cpf

ON         cpf.P_PLN_PRDIM_ID = B1.P_PLN_PRDIM_ID

WHERE  A.CNTRBN_MTY_DT between  add_months ( date '2011-05-31', -18) - extract  (day from add_months ( date '2011-05-31', -18))+1 and   add_months(add_months ( date '2011-05-31', -0) - extract  (day from add_months ( date '2011-05-31', -0))+1,1)-1

QUALIFY           ROW_NUMBER() OVER (PARTITION BY TRIM(ssn), TRIM(B1.pln_nbr) ,TRIM(B1.subpln_nbr) , Trim(FUND_INVESTMENT), Trim( FUND_SOURCE)

ORDER              BY  B1.p_pln_prdim_id DESC,  C.INVSMT_FUND_DIM_ID) = 1

1) First, we lock TableE for access, we lock A

     for access, we lock TABLEB for access, we lock

     TABLEF for access, we lock

     TABLEG for access, we lock

     TABLEC for access, we lock

     TABLED for access, and we lock TABLEH for

     access.

  2) Next, we do an all-AMPs SUM step to aggregate from

     TABLEG by way of an all-rows scan with a

     condition of ("TABLEG.END_DT = DATE

     '2100-12-31'"), and the grouping identifier in field 1.  Aggregate

     Intermediate Results are computed locally, then placed in Spool 3.

     The input table will not be cached in memory, but it is eligible

     for synchronized scanning.  The size of Spool 3 is estimated with

     low confidence to be 7,130,459 rows.  The estimated time for this

     step is 42.60 seconds.

  3) 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 into Spool 1 (all_amps), which is

          built locally on the AMPs.  The size of Spool 1 is estimated

          with low confidence to be 7,130,459 rows.  The estimated time

          for this step is 0.60 seconds.

       2) We do an all-AMPs RETRIEVE step from TABLED

          by way of an all-rows scan with no residual conditions into

          Spool 7 (all_amps), which is built locally on the AMPs.  Then

          we do a SORT to order Spool 7 by row hash.  The size of Spool

          7 is estimated with high confidence to be 1,498,180 rows.

          The estimated time for this step is 0.70 seconds.

       3) We do an all-AMPs RETRIEVE step from TableE by

          way of an all-rows scan with a condition of ("(TRIM(BOTH FROM

          TableE.SHARD_ID ))= 's01'") into Spool 9

          (all_amps), which is redistributed by hash CD to all AMPs.

          Then we do a SORT to order Spool 9 by the sort key in spool

          field1 eliminating duplicate rows.  The size of Spool 9 is

          estimated with no confidence to be 49 rows.  The estimated

          time for this step is 0.01 seconds.

  4) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way of

     an all-rows scan into Spool 8 (all_amps), which is duplicated on

     all AMPs.  Then we do a SORT to order Spool 8 by row hash.  The

     size of Spool 8 is estimated with no confidence to be 3,675 rows.

  5) We execute the following steps in parallel.

       1) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of

          an all-rows scan, which is joined to Spool 8 (Last Use) by

          way of an all-rows scanSpool 7 and Spool 8 are joined

          using an inclusion merge join, with a join condition of (

          "CLNT_NBR = Field_2").  The result goes into Spool 10

          (all_amps), which is duplicated on all AMPs.  The size of

          Spool 10 is estimated with index join confidence to be

          325,875 rows.  The estimated time for this step is 0.09

          seconds.

       2) We do an all-AMPs RETRIEVE step from A by way of an

          all-rows scan with a condition of (

          "(A.CNTRBN_MTY_DT <= DATE '2011-05-31') AND

          ((A.CNTRBN_MTY_DT >= DATE '2009-11-01') AND ((NOT

          (A.INSTN_PLN_PRDIM_ID IS NULL )) AND (NOT

          (A.INVSMT_FND_DIM_ID IS NULL ))))") into Spool 11

          (all_amps), which is built locally on the AMPs.  The input

          table will not be cached in memory, but it is eligible for

          synchronized scanning.  The size of Spool 11 is estimated

          with high confidence to be 55,008,485 rows.  The estimated

          time for this step is 27.69 seconds.

  6) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an

     all-rows scan, which is joined to Spool 11 (Last Use) by way of an

     all-rows scanSpool 10 and Spool 11 are joined using a single

     partition hash join, with a join condition of (

     "INSTN_PLN_PRDIM_ID = INSTN_PLN_PRDIM_ID").  The result goes

     into Spool 12 (all_amps), which is redistributed by hash CD to

     all AMPs.  Then we do a SORT to order Spool 12 by row hash.  The

     size of Spool 12 is estimated with index join confidence to be

     5,640,132 rows.  The estimated time for this step is 9.70 seconds.

  7) We execute the following steps in parallel.

       1) We do an all-AMPs RETRIEVE step from TABLEH by way of an

          all-rows scan with a condition of ("TABLEH.END_DT = DATE

          '2100-12-31'") into Spool 13 (all_amps), which is duplicated

          on all AMPs.  The size of Spool 13 is estimated with high

          confidence to be 194,775 rows.  The estimated time for this

          step is 0.04 seconds.

       2) We do an all-AMPs RETRIEVE step from

          TABLEF by way of an all-rows scan with

          a condition of ("(TABLEF.END_DT = DATE

          '2100-12-31') AND (NOT

          (TABLEF.P_RECRD_CD_ID IS NULL ))")

          into Spool 14 (all_amps), which is built locally on the AMPs.

          The input table will not be cached in memory, but it is

          eligible for synchronized scanning.  The size of Spool 14 is

          estimated with high confidence to be 9,502,093 rows.  The

          estimated time for this step is 17.63 seconds.

       3) We do an all-AMPs JOIN step from TABLEC by

          way of a RowHash match scan with no residual conditions,

          which is joined to Spool 12 (Last Use) by way of a RowHash

          match scan.  TABLEC and Spool 12 are joined

          using a merge join, with a join condition of (

          "INVSMT_FND_DIM_ID =

          TABLEC.INVSMT_FUND_DIM_ID").  The result

          goes into Spool 15 (all_amps), which is redistributed by hash

          CD to all AMPs.  Then we do a SORT to order Spool 15 by row

          hash.  The size of Spool 15 is estimated with index join

          confidence to be 5,640,132 rows.  The estimated time for this

          step is 12.66 seconds.

  8) We do an all-AMPs JOIN step from TABLEB by way

     of a RowHash match scan with no residual conditions, which is

     joined to Spool 15 (Last Use) by way of a RowHash match scan.

     TABLEB and Spool 15 are joined using a merge

     join, with a join condition of ("P_PLN_PRDIM_ID =

     TABLEB.P_PLN_PRDIM_ID").  The input table

     TABLEB will not be cached in memory, but it is

     eligible for synchronized scanning.  The result goes into Spool 16

     (all_amps), which is redistributed by hash CD to all AMPs into

     35 hash join partitions.  The size of Spool 16 is estimated with

     index join confidence to be 5,640,132 rows.  The estimated time

     for this step is 1 minute and 34 seconds.

  9) We do an all-AMPs RETRIEVE step from TABLEB by

     way of an all-rows scan with a condition of (

     "TABLEB.END_DT = DATE '2100-12-31'") locking for

     access into Spool 17 (all_amps) fanned out into 35 hash join

     partitions, which is redistributed by hash CD to all AMPs.  The

     input table will not be cached in memory, but it is eligible for

     synchronized scanning.  The size of Spool 17 is estimated with

     high confidence to be 9,502,093 rows.  The estimated time for this

     step is 2 minutes and 3 seconds.

 10) 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 scanSpool 13 and Spool 14 are joined using a single

     partition hash join, with a join condition of ("(TABLEH_ID =

     P_RECRD_CD_ID) AND (END_DT = END_DT)").  The result goes into

     Spool 18 (all_amps), which is built locally on the AMPs into 8

     hash join partitions.  The size of Spool 18 is estimated with low

     confidence to be 9,502,093 rows.  The estimated time for this step

     is 3.23 seconds.

 11) We do an all-AMPs JOIN step from Spool 16 (Last Use) by way of an

     all-rows scan, which is joined to Spool 17 (Last Use) by way of an

     all-rows scanSpool 16 and Spool 17 are joined using a hash join

     of 35 partitions, with a join condition of ("PRD_ARNGMANCH_ID =

     PRD_ARNGMANCH_ID").  The result goes into Spool 19 (all_amps),

     which is built locally on the AMPs into 8 hash join partitions.

     The size of Spool 19 is estimated with index join confidence to be

     5,640,132 rows.  The estimated time for this step is 14.81 seconds.

 12) We do an all-AMPs JOIN step from Spool 18 (Last Use) by way of an

     all-rows scan, which is joined to Spool 19 (Last Use) by way of an

     all-rows scanSpool 18 and Spool 19 are joined using a hash join

     of 8 partitions, with a join condition of ("PRD_ARNGMANCH_ID =

     PRD_ARNGMANCH_ID").  The result goes into Spool 20 (all_amps),

     which is redistributed by hash CD to all AMPs.  Then we do a

     SORT to order Spool 20 by row hash.  The size of Spool 20 is

     estimated with index join confidence to be 5,640,132 rows.  The

     estimated time for this step is 32.33 seconds.

 13) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of

     an all-rows scan into Spool 21 (all_amps), which is redistributed

     by hash CD to all AMPs.  Then we do a SORT to order Spool 21 by

     row hash.  The size of Spool 21 is estimated with low confidence

     to be 7,130,459 rows.  The estimated time for this step is 2.59

     seconds.

 14) We do an all-AMPs JOIN step from Spool 20 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 21 (Last Use) by way

     of a RowHash match scan.  Spool 20 and Spool 21 are left outer

     joined using a merge join, with a join condition of (

     "P_PLN_PRDIM_ID = P_PLN_PRDIM_ID").  The result goes

     into Spool 6 (all_amps), which is built locally on the AMPs.  The

     size of Spool 6 is estimated with index join confidence to be

     5,347,845 rows.  The estimated time for this step is 4.79 seconds.

 15) We do an all-AMPs STAT FUNCTION step from Spool 6 (Last Use) by

     way of an all-rows scan into Spool 24 (Last Use), which is assumed

     to be redistributed by value to all AMPs.  The result rows are put

     into Spool 5 (group_amps), which is built locally on the AMPs.

 16) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

5 REPLIES
Enthusiast

Re: Tune qry

Dieter , can you help me please?

Enthusiast

Re: Tune qry

Anyone else?

Senior Apprentice

Re: Tune qry

Hard to tell, just some remarks:

Why do you use TRIM all the time, is your data quality that bad?

It's a large overhead and the optimizer can't use existing stats.

What steps (according to DBQL) could be optimized?

There's no direct join on a PI and any table access is a Full Table Scan, so there are no matching SIs/JIs or partitioning, too.

Especially tables which "will not be cached in memory" should have a more efficient access path.

Which SI/JI didn't help, what are the existing indexes?

Dieter

Enthusiast

Re: Tune qry

Thanks for replying. Given are the recommended indexs which do not help and the existing ones in table format. I tried removing the TRim in the Qualify function but did not reduce the processing time. Are you saying that the TRIM in the sleect also does not allow stats to be used?

CREATE JOIN INDEX JITableF AS SELECT END_DT, ROWID FROM TableF PRIMARY INDEX(END_DT)

CREATE JOIN INDEX JITableH AS SELECT END_DT, ROWID FROM TableH PRIMARY INDEX(END_DT)

CREATE INDEX(prd_arngmanch_id) ON TableB

 Table G is the largest with  900 million rows, followed by table B with 120 million rows.Table H is the smallest with 3K rows.

TABLEH

Index

Cls_ID

Primary Index

TABLEA

Index

CLNT_NBR, CMPS_ID, CNTRBN_MTY_DT, CNTR_UNQ_KY, FND_INV, FND_SRC

Primary Index

TABLED

Index

instn_pln_prdim_id

Primary Index

TABLED

Index

LN_ALW_CD

Secondary Index

TABLED

Index

PLN_NBR

Secondary Index

TABLEC

Index

INVSMT_FUND_DIM_ID

Primary Index

TABLEG

Index

P_PLN_PRDIM_ID

Primary Index

TABLEF

Index

prd_arngmanch_id

Primary Index

TABLEB

Index

P_PLN_PRDIM_ID

Primary Index

TABLEB

Index

PLN_NBR

Secondary Index

TABLEB

Index

PRTCPT_NBR

Secondary Index

 Thanks!!

Enthusiast

Re: Tune qry

Dieter ...need yr help plz...Will changing the indices help? If yes, to what?