Degraded Query performance while performing insertion

Database
Enthusiast

Degraded Query performance while performing insertion

Hi Friends,

I have one query as below where we are performing an insertion in one empty table. The insertion statement is as below.The below insertion query is currently taking more than 20 hrs for its insertion. The tables which are used in the join i.e.JEDI_CDW_DB.CALL_HIST has UPI defined on columns used in join query and also table JEDI_AGR_DB.CDR_COST_HIST_PRE has UPI defined on columns used in join. But still there is issue with query performance.

Query:

INSERT

INTO JEDI_AGR_DB.CALL_DAY_IMAGE_1

     ( call_start_dt

     , accs_meth_id

     , a_phone_no_elemt_id

     , b_phone_no_elemt_id

     , call_class_id

     , incoming_party_id

     , outgoing_party_id

     , call_rate_per_cd

     , co_party_id

     , subsctn_id

     , acct_id

     , prod_id

     , call_svc_type_cd

     , call_accs_type_cd

     , net_accs_type_in

     , src_id

     , call_no

     , call_no_no_dup_svc

     , call_dur

     , call_dur_no_dup_svc

     , call_bill_dur

     , call_gross_rvnu_am

     , call_estimated_rvnu_am

/*--------------------------------------*/

     , call_inc_chrg_am

     , call_out_chrg_am

     , call_kckbck_chrg_am

     , call_trnsit_chrg_am

     , call_inslv_chrg_am

/*--------------------------------------*/     

     , call_act_hist_in

     , itr_call_rate_per_cd

/*--------------------------------------*/

     , orig_b_phone_no_elemt_id

/*--------------------------------------*/

     , call_inc_chrg_no_dup_am

     , call_out_chrg_no_dup_am

     , call_kckbck_chrg_no_dup_am

     , call_trnsit_chrg_no_dup_am

     , call_inslv_chrg_no_dup_am

/*--------------------------------------*/

     , call_inc_chrg_adj_am

     , call_out_chrg_adj_am

     , call_kckbck_chrg_adj_am

     , call_trnsit_chrg_adj_am

     , call_inslv_chrg_adj_am

/*--------------------------------------*/

     , call_inc_chrg_no_dup_adj_am

     , call_out_chrg_no_dup_adj_am

     , call_kckbck_chrg_no_dup_adj_am

     , call_trnsit_chrg_no_dup_adj_am

     , call_inslv_chrg_no_dup_adj_am

/*----------- N° Byte/Eventi MVNO -----*/

     , call_ip_qn 

     , call_evt_qn

     ,call_stat_id

     )

SELECT

     a.call_start_dt

   , a.accs_meth_id

   , a.origtg_phone_no_elemt_id

   , a.termntg_phone_no_elemt_id

   , a.call_class_id

   , b.party_id_inc                     AS incoming_party_id

   , b.party_id_out                     AS outgoing_party_id

   , a.call_rate_per_cd

   , a.co_party_id

   , a.subsctn_id

   , COALESCE(a.acct_id, 0)             AS SITO

   , COALESCE(a.prod_id, 0)             AS PRODOTTO

   , a.call_svc_type_cd

   , a.call_accs_type_cd

  ,

CASE

WHEN a.src_id In (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 114)

          THEN TRIM(a.call_b_subscriber_no)

ELSE NULL

END                                AS net_accs_type_in

   ,

CASE

WHEN a.src_id In (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

          THEN 1 

ELSE a.src_id 

END                                AS source

   , SUM (COALESCE(call_record_no,1))                     /* call_no */

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN (Coalesce(call_record_no, 1))

WHEN ' ' THEN (Coalesce(call_record_no, 1))

WHEN 'N' THEN (Coalesce(call_record_no, 1))

ELSE 0 end)                                      /* call_no_no_dup_svc */

   , SUM(COALESCE(a.call_dur,0))                          /* call_dur */

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN (Coalesce(a.call_dur,0))

WHEN ' ' THEN (Coalesce(a.call_dur,0))

WHEN 'N' THEN (Coalesce(a.call_dur,0))

ELSE 0 END)                                      /* call_dur_no_dup_svc */

   , SUM(Coalesce(a.call_bill_dur,0))                     /* call_bill_dur */

   , SUM(CAST((COALESCE(   a.call_gross_rvnu_am     , 0.000000)) AS DECIMAL(18, 6)))

   , SUM(CAST((COALESCE(   a.call_estimated_rvnu_am , 0.000000)) AS DECIMAL(18, 6)))

   /*----------------- campi di costo DUP NON Rettificati ---------------------*/

   , SUM(CAST((COALESCE(CCHP.raccolta_am            , 0.000000)) AS DECIMAL(18, 6)))

   , SUM(CAST((COALESCE(CCHP.interconn_am           , 0.000000)) AS DECIMAL(18, 6)))

   , SUM(CAST((COALESCE(CCHP.kick_back_am           , 0.000000)) AS DECIMAL(18, 6)))

   , SUM(CAST((COALESCE(CCHP.transito_am            , 0.000000)) AS DECIMAL(18, 6)))

   , SUM(CAST((COALESCE(CCHP.insolvenza_am          , 0.000000)) AS DECIMAL(18, 6)))

   , 'A'                                    AS indi

   , CCHP.itr_call_rate_per_cd Fascia_itr   /* Viene valorizzata solo nel caso sia presente un costo */

   /*----------------- prefisso originale portato in caso di MNP -----------------*/

   , CAST(a.wirels_call_roamer_surchrg_am AS INTEGER)       AS orig_b_phone_no_elemt_id

   /*----------------- campi di costo NO DUP NON Rettificati ---------------------*/

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.raccolta_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.raccolta_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.raccolta_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.interconn_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.interconn_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.interconn_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.kick_back_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.kick_back_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.kick_back_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.transito_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.transito_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.transito_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.insolvenza_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.insolvenza_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.insolvenza_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

   /*----------------- campi di costo DUP Rettificati ---------------------*/

   , SUM(CAST((COALESCE(CCHP.raccolta_am            , 0.000000)) AS DECIMAL(18, 6)))

   , SUM(CAST((COALESCE(CCHP.interconn_am           , 0.000000)) AS DECIMAL(18, 6)))

   , SUM(CAST((COALESCE(CCHP.kick_back_am           , 0.000000)) AS DECIMAL(18, 6)))

   , SUM(CAST((COALESCE(CCHP.transito_am            , 0.000000)) AS DECIMAL(18, 6)))

   , SUM(CAST((COALESCE(CCHP.insolvenza_am          , 0.000000)) AS DECIMAL(18, 6)))

   /*----------------- campi di costo NO DUP Rettificati ---------------------*/

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.raccolta_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.raccolta_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.raccolta_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.interconn_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.interconn_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.interconn_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.kick_back_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.kick_back_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.kick_back_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.transito_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.transito_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.transito_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

   , SUM(

CASE (COALESCE(a.call_dup_svc_in, '0'))

WHEN '0' THEN CAST((Coalesce(CCHP.insolvenza_am,0.000000)) AS DECIMAL(18,6))

WHEN ' ' THEN CAST((Coalesce(CCHP.insolvenza_am,0.000000)) AS DECIMAL(18,6))

WHEN 'N' THEN CAST((Coalesce(CCHP.insolvenza_am,0.000000)) AS DECIMAL(18,6))

ELSE 0 END)

  /*-------------------- N° Byte/Eventi MVNO -------------*/

  ,SUM(CAST((COALESCE(a.call_ip_qn,0))                              AS DECIMAL(18,0)))

  ,SUM(COALESCE(a.call_evt_qn,0))

  ,a.call_stat_id   

FROM               JEDI_CDW_DB.CALL_HIST A

LEFT OUTER JOIN JEDI_AGR_DB.CDR_COST_HIST_PRE CCHP

ON CCHP.tt2_call_start_dt = a.call_start_dt

AND CCHP.tt2_call_start_tm = a.call_start_tm

AND CCHP.tt2_switch_id     = a.switch_id

AND CCHP.tt2_recseqnum     = a.call_record_seq_no

LEFT OUTER JOIN JEDI_AGR_DB.NET_CROSS B

ON a.incoming_rt_cd = b.rt_cd_inc

AND a.outgoing_rt_cd = b.rt_cd_out

GROUP BY  1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 29, 30, 31, 49

;

Also, I am mentioning below the explain plan for reference. Can anyone please suggest me any ways that I can use to improve the query performance.

Explanation

  1) First, we lock a distinct JEDI_AGR_DB."pseudo table" for write on

     a RowHash to prevent global deadlock for

     JEDI_AGR_DB.CALL_DAY_IMAGE_1. 

  2) Next, we lock a distinct JEDI_AGR_DB."pseudo table" for read on a

     RowHash to prevent global deadlock for JEDI_AGR_DB.CCHP. 

  3) We lock a distinct JEDI_AGR_DB."pseudo table" for read on a

     RowHash to prevent global deadlock for JEDI_AGR_DB.B. 

  4) We lock a distinct jedi_cdw_db."pseudo table" for read on a

     RowHash to prevent global deadlock for jedi_cdw_db.call_hist. 

  5) We lock JEDI_AGR_DB.CALL_DAY_IMAGE_1 for write, we lock

     JEDI_AGR_DB.CCHP for read, we lock JEDI_AGR_DB.B for read, and we

     lock jedi_cdw_db.call_hist for read. 

  6) We execute the following steps in parallel. 

       1) We do an all-AMPs RETRIEVE step from jedi_cdw_db.call_hist by

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

          Spool 4 (all_amps), which is redistributed by hash code to

          all AMPs.  Then we do a SORT to partition Spool 4 by rowkey. 

          The result spool file will not be cached in memory.  The size

          of Spool 4 is estimated with high confidence to be

          915,225,392 rows.  The estimated time for this step is 1 hour

          and 53 minutes. 

       2) We do an all-AMPs RETRIEVE step from JEDI_AGR_DB.B by way of

          an all-rows scan with no residual conditions into Spool 5

          (all_amps), which is duplicated on all AMPs.  Then we do a

          SORT to order Spool 5 by row hash.  The result spool file

          will not be cached in memory.  The size of Spool 5 is

          estimated with high confidence to be 210,412,800 rows.  The

          estimated time for this step is 4 minutes and 58 seconds. 

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

     RowHash match scan, which is joined to all partitions of

     JEDI_AGR_DB.CCHP by way of a RowHash match scan with a condition

     of ("(NOT (JEDI_AGR_DB.CCHP.tt2_call_start_tm IS NULL )) AND ((NOT

     (JEDI_AGR_DB.CCHP.tt2_switch_id IS NULL )) AND (NOT

     (JEDI_AGR_DB.CCHP.tt2_recseqnum IS NULL )))").  Spool 4 and

     JEDI_AGR_DB.CCHP are left outer joined using a rowkey-based merge

     join, with a join condition of ("(JEDI_AGR_DB.CCHP.tt2_recseqnum =

     call_record_seq_no) AND ((JEDI_AGR_DB.CCHP.tt2_switch_id =

     switch_id) AND ((JEDI_AGR_DB.CCHP.tt2_call_start_tm =

     call_start_tm) AND (JEDI_AGR_DB.CCHP.tt2_call_start_dt =

     call_start_dt )))").  The input table JEDI_AGR_DB.CCHP will not be

     cached in memory.  The result goes into Spool 6 (all_amps), which

     is built locally on the AMPs.  Then we do a SORT to order Spool 6

     by row hash.  The result spool file will not be cached in memory. 

     The size of Spool 6 is estimated with low confidence to be

     915,225,392 rows.  The estimated time for this step is 1 hour and

     53 minutes. 

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

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

     of a RowHash match scan.  Spool 5 and Spool 6 are right outer

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

     "(outgoing_rt_cd = rt_cd_out) AND (incoming_rt_cd = rt_cd_inc)"). 

     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 low confidence to be

     1,500,179,332 rows.  The estimated time for this step is 42

     minutes and 14 seconds. 

  9) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by

     way of an all-rows scan, and the grouping identifier in field 1. 

     Aggregate Intermediate Results are computed globally, then placed

     in Spool 11.  The aggregate spool file will not be cached in

     memory.  The size of Spool 11 is estimated with low confidence to

     be 1,125,134,499 rows.  The estimated time for this step is 14

     hours and 6 minutes. 

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

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

     by hash code to all AMPs.  Then we do a SORT to partition Spool 1

     by rowkey.  The result spool file will not be cached in memory. 

     The size of Spool 1 is estimated with low confidence to be

     1,125,134,499 rows.  The estimated time for this step is 3 hours

     and 7 minutes. 

 11) We do an all-AMPs MERGE into JEDI_AGR_DB.CALL_DAY_IMAGE_1 from

     Spool 1 (Last Use). 

 12) We spoil the parser's dictionary cache for the table. 

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

     in processing the request.

  -> No rows are returned to the user as the result of statement 1. 

Thanks in advance.

- Sachin

8 REPLIES
Enthusiast

Re: Degraded Query performance while performing insertion

Can you post the ddls of the lookup and the source tables...

Enthusiast

Re: Degraded Query performance while performing insertion

Hi,

Please find below the DDLs of the source and target tables.

1) Source Tables(JEDI_CDW_DB.CALL_HIST and JEDI_AGR_DB.CDR_COST_HIST_PRE)

2) Target table - JEDI_AGR_DB.CALL_DAY_IMAGE_1

DDL of JEDI_CDW_DB.CALL_HIST: 

CREATE SET TABLE JEDI_cdw_db.call_hist ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      accs_meth_id INTEGER TITLE 'ACCESS METHOD ID' NOT NULL,

      switch_id INTEGER TITLE 'SWITCH ID' NOT NULL,

      call_start_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL START DATE' NOT NULL,

      call_start_tm FLOAT FORMAT '99:99:99' TITLE 'CALL START TIME' NOT NULL,

      call_record_seq_no INTEGER TITLE 'CALL RECORD SEQUENCE NO' NOT NULL,

      subsctn_id INTEGER TITLE 'SUBSCRIPTION ID' COMPRESS ,

      offrng_id INTEGER TITLE 'OFFERING ID' COMPRESS ,

      prod_id INTEGER TITLE 'PRODUCT ID' COMPRESS ,

      bill_stmt_id INTEGER TITLE 'BILLING STATEMENT ID' COMPRESS ,

      bill_stmt_ref_id INTEGER TITLE 'BILLING STATEMENT REF ID' COMPRESS ,

      bill_stmt_ref_rsts_id SMALLINT TITLE 'BILLING STATEMENT REF RSTS ID' COMPRESS ,

      call_end_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL END DATE' COMPRESS ,

      call_end_tm FLOAT FORMAT '99:99:99' TITLE 'CALL END TIME' COMPRESS ,

      call_dur INTEGER TITLE 'CALL DURATION' COMPRESS ,

      call_bill_dur INTEGER TITLE 'CALL BILL DURATION' COMPRESS ,

      dur_unit_of_measure_id INTEGER TITLE 'DURATION UNIT OF MEASURE ID' COMPRESS ,

      call_type_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL TYPE CD' COMPRESS ,

      call_svc_type_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL SERVICE TYPE CD' COMPRESS ,

      call_enhncd_svc_type_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ENHANCED SERVICE TYPE CD' COMPRESS ,

      call_spec_svc_type_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL SPECIAL SERVICE TYPE CD' COMPRESS ,

      call_rate_per_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL RATE PERIOD CD' COMPRESS ,

      call_gross_rvnu_am DECIMAL(18,6) TITLE 'CALL GROSS REVENUE AMT' COMPRESS ,

      call_gross_rvnu_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL GROSS REVENUE DATE' COMPRESS ,

      call_compl_type_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL COMPLETION TYPE CD' COMPRESS ,

      call_fault_cd DECIMAL(5,0) TITLE 'CALL FAULT CD' COMPRESS ,

      call_inbnd_outbnd_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INBOUND OUTBOUND IND' COMPRESS ,

      call_curr_cust_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL CURRENT CUSTOMER IND' COMPRESS ,

      call_inter_intra_st_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INTER/INTRA STATE IND' COMPRESS ,

      call_inter_intra_lata_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INTER/INTRA LATA IND' COMPRESS ,

      call_intnl_dom_call_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INTERNATIONAL/DOMESTIC CALL IND' COMPRESS ,

      call_incollect_tot_chrg_am DECIMAL(18,6) TITLE 'CALL INCOLLECT TOTAL CHARGE AMT' COMPRESS ,

      call_outcollect_tot_chrg_am DECIMAL(18,6) TITLE 'CALL OUTCOLLECT TOTAL CHARGE AMT' COMPRESS ,

      monty_unit_of_measure_id INTEGER TITLE 'MONETARY UNIT OF MEASURE ID' COMPRESS ,

      termntg_phone_no_elemt_id INTEGER TITLE 'TERMINATING PHONE NO ELEMENT ID' COMPRESS ,

      call_termntg_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL TERMINATING NO',

      origtg_phone_no_elemt_id INTEGER TITLE 'ORIGINATING PHONE NO ELEMENT ID' COMPRESS ,

      call_origtg_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ORIGINATING NO',

      call_dialed_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL DIALED NO',

      call_wirels_call_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL WIRELESS CALL IND' COMPRESS ,

      airtm_chrg_am DECIMAL(18,6) TITLE 'AIRTIME CHARGE AMT' COMPRESS ,

      wirels_airtm_per_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'WIRELESS AIRTIME PERIOD CD' COMPRESS ,

      wirels_landtm_per_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'WIRELESS LANDTIME PERIOD CD' COMPRESS ,

      wirels_call_landtm_chrg_am DECIMAL(18,6) TITLE 'WIRELESS CALL HISTORY LANDTIME CHARGE AMT' COMPRESS ,

      wirels_call_handoff_cnt INTEGER TITLE 'WIRELESS CALL HISTORY HANDOFF COUNT' COMPRESS ,

      wirels_roamer_type_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'WIRELESS ROAMER TYPE CD' COMPRESS ,

      wirels_call_roamer_surchrg_am DECIMAL(18,6) TITLE 'WIRELESS CALL HISTORY  ROAMER SURCHARGE AMT' COMPRESS ,

      wirels_call_incolct_rom_chg_am DECIMAL(18,6) TITLE 'WIRELESS CALL HISTORY INCOLLECT ROAMER CHARGE AMT' COMPRESS ,

      orig_cell_site_id INTEGER TITLE 'ORIGINAL CELL SITE ID' COMPRESS ,

      term_cell_site_id INTEGER TITLE 'TERMATING CELL SITE ID' COMPRESS ,

      call_origtg_crypt_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ORIGINATING CRYPT NO',

      call_termntg_crypt_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL TERMINATING CRYPT NO',

      call_dup_svc_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL DUP SERVICE IND' COMPRESS ,

      call_dup_local_loop_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL DUP LOCAL LOOP IND' COMPRESS ,

      call_estimated_rvnu_am DECIMAL(18,6) TITLE 'CALL ESTIMATED REVENUE AMT' COMPRESS ,

      call_estimated_rvnu_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL ESTIMATED REVENUE DATE' COMPRESS ,

      acct_id INTEGER TITLE 'ACCT ID' COMPRESS ,

      co_party_id INTEGER TITLE 'COMPANY PARTY ID' COMPRESS ,

      call_accs_type_cd BYTEINT TITLE 'CALL ACCESS TYPE CD' COMPRESS ,

      call_egrs_type_cd BYTEINT TITLE 'CALL EGRS TYPE CD' COMPRESS ,

      call_tele_svc_cd BYTEINT TITLE 'CALL TELEPHONE SERVICE CD' COMPRESS ,

      call_call_id INTEGER TITLE 'CALL CALL ID' COMPRESS ,

      call_record_no SMALLINT TITLE 'CALL RECORD NO' DEFAULT 1  COMPRESS ,

      call_tt_no SMALLINT TITLE 'CALL TT Number' COMPRESS ,

      call_cost_cd BYTEINT TITLE 'CALL COST CD' COMPRESS ,

      call_a_subscriber_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL A SUBSCRIBER NO',

      call_b_subscriber_no VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL B SUBSCRIBER NO',

      outgoing_rt_cd CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'OUTGOING ROUTE CD' COMPRESS ,

      call_origin_outgoing_rt_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ORIGIN OUTGOING ROUTE IND' COMPRESS ,

      incoming_rt_cd CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'INCOMING ROUTE CD' COMPRESS ,

      call_origin_incoming_rt_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ORIGIN INCOMING ROUTE IND' COMPRESS ,

      call_cause_out BYTEINT TITLE 'CALL Cause Output' COMPRESS ,

      call_jurisdiction_cd INTEGER TITLE 'CALL JURISDICTION CD' COMPRESS ,

      call_internal_rt_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL INTERNAL ROUTE CD' COMPRESS ,

      call_elemt_id SMALLINT TITLE 'CALL ELEMENT ID' COMPRESS ,

      call_subscr_no INTEGER TITLE 'CALL SUBSCR NO' COMPRESS ,

      call_subscr_no_resets SMALLINT TITLE 'CALL SUBSCR NO RESETS' COMPRESS ,

      call_file_id INTEGER TITLE 'CALL FILE ID' COMPRESS ,

      call_class_id BYTEINT TITLE 'CALL CLASSIFICATION ID' COMPRESS ,

      call_stat_id BYTEINT TITLE 'CALL STATUS ID' COMPRESS ,

      incollect_call_stat_id BYTEINT TITLE 'INCOLLECT CALL STATUS ID' COMPRESS ,

      outcollect_call_stat_id BYTEINT TITLE 'OUTCOLLECT CALL STATUS ID' COMPRESS ,

      call_record_eff_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL RECORD EFFECTIVE DATE' COMPRESS ,

      call_record_eff_tm FLOAT FORMAT '99:99:99' TITLE 'CALL RECORD EFFECTIVE TIME' COMPRESS ,

      src_id BYTEINT TITLE 'SOURCE ID' COMPRESS ,

      arbor_instnc_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE _UNICODE '004100520042004F005200200049004E005300540041004E004300450020000D0020002000200020002000430044'XC,

      call_auth_cd VARCHAR(24) CHARACTER SET LATIN NOT CASESPECIFIC,

      call_cli_type_in CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE _UNICODE '00430041004C004C00200043004C0049002000540059005000450020000D002000200020002000200049004E0044'XC COMPRESS ,

      hstd_carr_pslctn_no SMALLINT TITLE 'HOSTED CARRIER PRESELECTION NO' NOT NULL DEFAULT 0 ,

      call_ip_qn DECIMAL(18,0) TITLE 'BYTES QTY (es. GPRS, BBerry,ecc.)' COMPRESS ,

      call_evt_qn INTEGER TITLE 'EVENTS QTY (es. SMS, MMS, ecc.)' COMPRESS )

UNIQUE PRIMARY INDEX XPKRevenue ( accs_meth_id ,switch_id ,call_start_dt ,

call_start_tm ,call_record_seq_no )

PARTITION BY RANGE_N(call_start_dt  BETWEEN DATE '2001-04-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY ,

DATE '2016-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY )

INDEX nusi1 ( accs_meth_id ,call_start_dt );

DDL of table JEDI_AGR_DB.CDR_COST_HIST_PRE:

CREATE SET TABLE JEDI_agr_db.cdr_cost_hist_pre ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      tt2_call_start_tm FLOAT FORMAT '99:99:99',

      tt2_call_start_dt DATE FORMAT 'DD-MM-YYYY',

      tt2_switch_id INTEGER,

      tt2_recseqnum INTEGER TITLE 'RECORD SEQUENCE NUMBER',

      itr_call_rate_per_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'P',

      kick_back_am DECIMAL(18,6) DEFAULT 0.000000  COMPRESS 0.000000 ,

      raccolta_am DECIMAL(18,6) DEFAULT 0.000000  COMPRESS 0.000000 ,

      interconn_am DECIMAL(18,6) DEFAULT 0.000000  COMPRESS 0.000000 ,

      transito_am DECIMAL(18,6) DEFAULT 0.000000  COMPRESS 0.000000 ,

      insolvenza_am DECIMAL(18,6) DEFAULT 0.000000  COMPRESS 0.000000 ,

      CountQ BYTEINT COMPRESS (1 ,2 ))

UNIQUE PRIMARY INDEX XPK_ITR_CDR_COST ( tt2_call_start_tm ,tt2_call_start_dt ,

tt2_switch_id ,tt2_recseqnum )

PARTITION BY RANGE_N(tt2_call_start_dt  BETWEEN DATE '2001-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' DAY );

DDL of table- JEDI_AGR_DB.NET_CROSS :

CREATE SET TABLE JEDI_AGR_DB.NET_CROSS ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      rt_cd_inc CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'INCOMING ROUTE CD' NOT NULL,

      party_id_inc INTEGER TITLE 'INCOMING PARTY ID',

      rt_cd_out CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'OUTGOING ROUTE CD' NOT NULL,

      party_id_out INTEGER TITLE 'OUTGOING PARTY ID',

      rt_in_eff_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'ROUTE EFF DATE' NOT NULL,

      rt_in_end_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'ROUTE END DATE' NOT NULL,

      rt_out_eff_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'ROUTE EFF DATE' NOT NULL,

      rt_out_end_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'ROUTE END DATE' NOT NULL)

PRIMARY INDEX XPKROUTE ( rt_cd_inc ,rt_cd_out )

UNIQUE INDEX ( rt_cd_inc ,rt_cd_out ,rt_in_eff_dt ,rt_out_eff_dt );


DDL of target table - JEDI_AGR_DB.CALL_DAY_IMAGE_1:

CREATE MULTISET TABLE JEDI_AGR_DB.CALL_DAY_IMAGE_1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      call_start_dt DATE FORMAT 'DD-MM-YYYY' TITLE 'CALL START DATE' NOT NULL,

      accs_meth_id INTEGER TITLE 'ACCESS METHOD ID' NOT NULL,

      a_phone_no_elemt_id INTEGER TITLE 'A PHONE NO ELEMENT ID',

      b_phone_no_elemt_id INTEGER TITLE 'B PHONE NO ELEMENT ID',

      call_class_id BYTEINT TITLE 'CALL CLASSIFICATION ID',

      incoming_party_id INTEGER TITLE 'INCOMING PARTY IDENTIFIER',

      outgoing_party_id INTEGER TITLE 'OUTGOING PARTY IDENTIFIER',

      call_rate_per_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL RATE PERIOD CD',

      call_svc_type_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL SERVICE TYPE CD',

      co_party_id INTEGER TITLE 'COMPANY PARTY ID',

      subsctn_id INTEGER TITLE 'SUBSCRIPTION ID',

      acct_id INTEGER TITLE 'ACCT ID',

      prod_id INTEGER TITLE 'PRODUCT ID',

      call_act_hist_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALL ACTUAL HIST INDICATOR' COMPRESS 'H',

      call_no INTEGER TITLE 'CALL NO' COMPRESS ,

      call_dur INTEGER TITLE 'CALL DURATION' COMPRESS ,

      call_bill_dur INTEGER TITLE 'CALL BILL DURATION' COMPRESS ,

      call_gross_rvnu_am DECIMAL(18,6) TITLE 'CALL GROSS REVENUE AMT' COMPRESS ,

      call_estimated_rvnu_am DECIMAL(18,6) TITLE 'CALL ESTIMATED REVENUE AMT' COMPRESS ,

      call_inc_chrg_am DECIMAL(18,6) TITLE 'CALL INCOLLECT TOTAL CHARGE AMT' DEFAULT 0.000000  COMPRESS ,

      call_out_chrg_am DECIMAL(18,6) TITLE 'CALL OUTCOLLECT TOTAL CHARGE AMT' DEFAULT 0.000000  COMPRESS ,

      call_no_no_dup_svc INTEGER TITLE 'CALL NO NO DUP SERVICE',

      call_dur_no_dup_svc INTEGER TITLE 'CALL DURATION NO DUP SERVICE',

      call_accs_type_cd BYTEINT TITLE 'CALL ACCESS EGRESS TYPE CODE',

      src_id BYTEINT TITLE 'SOURCE IDENTIFIER',

      net_accs_type_in CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'NET ACCESS TYPE CODE',

      call_kckbck_chrg_am DECIMAL(18,6) DEFAULT 0.000000 ,

      call_trnsit_chrg_am DECIMAL(18,6) DEFAULT 0.000000 ,

      call_inslv_chrg_am DECIMAL(18,6) DEFAULT 0.000000 ,

      Itr_call_rate_per_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      orig_b_phone_no_elemt_id INTEGER TITLE 'orig_b_phone_no_elemt_id',

      call_inc_chrg_no_dup_am DECIMAL(18,6) TITLE 'call_inc_chrg_no_dup_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_out_chrg_no_dup_am DECIMAL(18,6) TITLE 'call_out_chrg_no_dup_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_kckbck_chrg_no_dup_am DECIMAL(18,6) TITLE 'call_kckbck_chrg_no_dup_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_trnsit_chrg_no_dup_am DECIMAL(18,6) TITLE 'call_trnsit_chrg_no_dup_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_inslv_chrg_no_dup_am DECIMAL(18,6) TITLE 'call_inslv_chrg_no_dup_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_inc_chrg_adj_am DECIMAL(18,6) TITLE 'call_inc_chrg_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_out_chrg_adj_am DECIMAL(18,6) TITLE 'call_out_chrg_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_kckbck_chrg_adj_am DECIMAL(18,6) TITLE 'call_kckbck_chrg_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_trnsit_chrg_adj_am DECIMAL(18,6) TITLE 'call_trnsit_chrg_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_inslv_chrg_adj_am DECIMAL(18,6) TITLE 'call_inslv_chrg_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_inc_chrg_no_dup_adj_am DECIMAL(18,6) TITLE 'call_inc_chrg_no_dup_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_out_chrg_no_dup_adj_am DECIMAL(18,6) TITLE 'call_out_chrg_no_dup_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_kckbck_chrg_no_dup_adj_am DECIMAL(18,6) TITLE 'call_kckbck_chrg_no_dup_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_trnsit_chrg_no_dup_adj_am DECIMAL(18,6) TITLE 'call_trnsit_chrg_no_dup_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_inslv_chrg_no_dup_adj_am DECIMAL(18,6) TITLE 'call_inslv_chrg_no_dup_adj_am' DEFAULT 0.000000  COMPRESS 0.000000 ,

      call_ip_qn DECIMAL(18,0) TITLE 'CALL IP QUANTITY' COMPRESS 0. ,

      call_evt_qn INTEGER TITLE 'CALL EVENT QUANTITY' COMPRESS 0 ,

      call_stat_id BYTEINT TITLE 'CALL STATUS ID')

PRIMARY INDEX PrimaryKey ( call_start_dt ,accs_meth_id ,acct_id ,

prod_id )

PARTITION BY RANGE_N(call_start_dt  BETWEEN DATE '2001-04-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY ,

DATE '2016-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY );

Please let me know if you need any other information.

Thanks,

Sachin

Junior Supporter

Re: Degraded Query performance while performing insertion

Hi

Is the select exeucting fast ? if true, then is the insert taking time ? This is a Insert sel query with no PI defined for the select part, so the first column will be the PI. If that is skewed, the insert will run longer.

--samir singh

Enthusiast

Re: Degraded Query performance while performing insertion

Hi Samir,

No the select operation is also not executing fast. Both select and insert are taking time.

Can you suggest any ways through which I can improve the query performance. As you can see in my earlier post, the explain plan shows a high number of hrs.

Thanks,

Sachin

Junior Supporter

Re: Degraded Query performance while performing insertion

Few of the basic things that you need to see are :

1. Run : Diagnostic helpstats on for session

2. Run the explain of the query, See if optimizer suggests you some stats. Plese collect the stats with high confidence. This should help if the stats are missing.

3. I see  that a lot of duplication and redistribution is happening in different steps. also, the rows involved are very high here. billions in some cases. So, please check if the joining columns are correct. You must be doing Pi-No Pi join, so a lot of duplication/redistribution is happening

If all of these dont help, then you need to check the logic of the queey, if you joining correct tables on correct columns.

--Samir

Enthusiast

Re: Degraded Query performance while performing insertion

Hi Samir,

I ran the command Diagnostic helpstats on for session on that query last week and found some collect stats as mentioned below required. So, accordingly I ran the collect stats below as suggested :

 COLLECT STATISTICS jedi_cdw_db.call_hist COLUMN (CALL_START_DT  ,CALL_START_TM ,SWITCH_ID ,CALL_RECORD_SEQ_NO); 


COLLECT STATISTICS jedi_cdw_db.call_hist COLUMN (INCOMING_RT_CD,OUTGOING_RT_CD); 

But this operation ran for more than 6hrs and finally I had to abort the session as it was impacting some other parallely running jobs. We are using BTEQ to perform the insertion and collect stats activity.

So, is there any way to reduce the total execution time collect stats is taking currently.

Thanks,

Sachin

Enthusiast

Re: Degraded Query performance while performing insertion

Your table is defined as a SET table, has a UPI defined on it. Maybe it is doing a full row dupe check for each insert? Can you try creating a copy of this table as a MULTISET table and do the insert?

Junior Contributor

Re: Degraded Query performance while performing insertion

First you should check the actual runtime/resource usage for each step using QryLogStepsV.

The UPI on call_hist cannot be used for the join, what are the demographics for accs_meth_id, is it actually needed in the PI?

The stats can probably be collected USING SAMPLE 2 PERCENT instead of full stats, but I doubt it will improve performance.