Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: Help is performance Tunning

    Purpose is to tune a DB2 query which is used in our project. These queries take long time to execution around 9 Min on Production (which is 16 partition database) and number of rows returned (which is a very few at max).The tables contain millions of records (e.g table MODEL_SCORES_URN contain 3393035152 records and LNK_ACCT_REL contain 8994896 records) and all the supporting indexes are already created (Attached in this doc). We need to tune this query in such a way that the query execution time will be less than 1 Min.
    Tuning is expected to be performed on the query mentioned below to identify possible solution(s) to improvise performance. Possible outcomes can include creation of MQT, creation of subset or any other solution except index creation to avoid the number of joins and/or conditions checking within the query in case if not present.
    The db2 advisor suggested creating a big Index mention below but we want some alternate way of Tuning
    -- LIST OF RECOMMENDED INDEXES
    -- ===========================
    index[1], 116890.814MB
    CREATE UNIQUE INDEX "DBDBAAM1"."IDX1202171002050" ON "STLPAM” ."MODEL_SCORES_URN" ("MODEL_ID" ASC, "ATTRIB_NAME" ASC, "URN_ACCT" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
    The Exlpain and Advisor result is attached

    Following is the query to be tuned.

    SQL:
    SELECT h.brand,
    h.model_name AS offering,
    max(value_float) as PTB_Max,
    avg(value_float) as PTB_Avg
    FROM amdata.model_scores_urn_am ptb
    JOIN amdata.model_scores_header_am h
    ON h.model_id = ptb.model_id
    JOIN adbam.rdc_cmr_amf urn
    ON urn.urn_acct = ptb.urn_acct
    JOIN adbam.ref_region_am region
    ON region.ctry = urn.ctry
    WHERE urn.client_id = '01156715'
    AND attrib_name = 'PTB_STAR'
    AND ( CASE region.region
    WHEN 'CA' THEN canada_imt
    WHEN 'USE' THEN us_east_imt
    WHEN 'USW' THEN us_west_imt
    WHEN 'US*' THEN us_east_imt
    WHEN 'ALPS' THEN alps_imt
    WHEN 'DE' THEN germany_imt
    WHEN 'NORDIC' THEN nordic_imt
    WHEN 'UKI' THEN uki_imt
    WHEN 'BLX' THEN benelux_imt
    WHEN 'FR' THEN france_imt
    WHEN 'IT' THEN italy_imt
    WHEN 'SPGI' THEN spgi_imt
    WHEN 'JAPAN' THEN japan_imt
    WHEN 'ANZ' THEN anz_gmt
    WHEN 'ASEAN' THEN asean_gmt
    WHEN 'CEE' THEN cee_gmt
    WHEN 'MEA' THEN mea_gmt
    WHEN 'GCG' THEN gcg_gmt
    WHEN 'INDSA' THEN isa_gmt
    WHEN 'KOREA' THEN korea_gmt
    WHEN 'LA' THEN latin_america_gmt
    END ) = '1'
    AND h.expiry_ts >= current timestamp
    AND h.created_ts = (select max(created_ts)
    from amdata.model_scores_urn_am ptb1
    join amdata.model_scores_header_am c on c.model_id = ptb1.model_id and ptb1.urn_acct = ptb.urn_acct and ptb1.attrib_name = 'PTB_STAR'
    where c.abbreviated_name = h.abbreviated_name
    AND ( CASE region.region
    WHEN 'CA' THEN canada_imt
    WHEN 'USE' THEN us_east_imt
    WHEN 'USW' THEN us_west_imt
    WHEN 'US*' THEN us_east_imt
    WHEN 'ALPS' THEN alps_imt
    WHEN 'DE' THEN germany_imt
    WHEN 'NORDIC' THEN nordic_imt
    WHEN 'UKI' THEN uki_imt
    WHEN 'BLX' THEN benelux_imt
    WHEN 'FR' THEN france_imt
    WHEN 'IT' THEN italy_imt
    WHEN 'SPGI' THEN spgi_imt
    WHEN 'JAPAN' THEN japan_imt
    WHEN 'ANZ' THEN anz_gmt
    WHEN 'ASEAN' THEN asean_gmt
    WHEN 'CEE' THEN cee_gmt
    WHEN 'MEA' THEN mea_gmt
    WHEN 'GCG' THEN gcg_gmt
    WHEN 'INDSA' THEN isa_gmt
    WHEN 'KOREA' THEN korea_gmt
    WHEN 'LA' THEN latin_america_gmt
    END ) = '1'
    AND c.expiry_ts >= current timestamp)
    group by brand, model_name
    order by max(value_float) desc, avg(value_float) desc, brand asc, model_name asc
    FOR READ ONLY WITH UR
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish CREATE VIEW staements.
    Because, there are 5 tables in "Optimized Statement" having different names from 4 tables in "Original Statement" in Explain.

  3. #3
    Join Date
    Aug 2012
    Posts
    4

    Create View Statement

    Create View Statement
    create view AMDATA.MODEL_SCORES_URN_AM as select * from STLPAM.MODEL_SCORES_URN
    create view AMDATA.MODEL_SCORES_HEADER_AM as select * from STLPAM.MODEL_SCORES_HEADER
    create view ADBAM.RDC_CMR_AM as SELECT A.URN_ACCT ,B.SAP_CUSNUM ,B.CTRY ,B.GEO ,B.CMR_CTRY_ID ,B.CUST_NUM ,B.MKT_AFFILIATE_NO ,B.CLIENT_ID ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.CLIENT_NAME End As CLIENT_NAME ,B.GLOBAL_CLIENT_ID ,B.MARKED_4_DELETE ,B.ORDER_BLOCK ,B.CUST_TYPE ,B.COVERAGE_TYPE ,B.COVERAGE_ID ,B.DEL_COV_INDC ,B.IND_CD ,B.SUB_IND_CD ,B.QUAD_TIER_CD ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.CUST_NAME End As CUST_NAME ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.CITY End As CITY ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.COUNTY End As COUNTY ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.CUST_STATE_PROV End As CUST_STATE_PROV ,B.ISU ,B.SIC_CODE ,B.FED_FLAG from DB2INST1.RDC_CMR B LEFT OUTER JOIN DB2INST1.LNK_ACCT_REL A on A.SRC_SYS = 'CUS' and A.SRC_KEY1 = COALESCE(B.CTRY, '**') and A.SRC_KEY2 = B.CUST_NUM
    create view ADBAM.REF_REGION_AM as SELECT * from DB2INST1.REF_REGION
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't find a CREATE VIEW statement nor a CREATE TABLE statement for adbam.rdc_cmr_amf which was in your query.

    Is it same as ADBAM.RDC_CMR_AM?
    If so, please confirm that adbam.rdc_cmr_amf was created completely same as ADBAM.RDC_CMR_AM.
    Last edited by tonkuma; 08-31-12 at 05:17. Reason: Add last two staements.

  5. #5
    Join Date
    Aug 2012
    Posts
    4

    ADBAM.RDC_CMR_AMF view

    Hi,

    yes the view is having the same definition but different name

    create view ADBAM.RDC_CMR_AMF as SELECT A.URN_ACCT ,B.SAP_CUSNUM ,B.CTRY ,B.GEO ,B.CMR_CTRY_ID ,B.CUST_NUM ,B.MKT_AFFILIATE_NO ,B.CLIENT_ID ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.CLIENT_NAME End As CLIENT_NAME ,B.GLOBAL_CLIENT_ID ,B.MARKED_4_DELETE ,B.ORDER_BLOCK ,B.CUST_TYPE ,B.COVERAGE_TYPE ,B.COVERAGE_ID ,B.DEL_COV_INDC ,B.IND_CD ,B.SUB_IND_CD ,B.QUAD_TIER_CD ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.CUST_NAME End As CUST_NAME ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.CITY End As CITY ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.COUNTY End As COUNTY ,Case When B.FED_FLAG IS NULL OR B.FED_FLAG='Y' Then 'PROTECTED ' Else B.CUST_STATE_PROV End As CUST_STATE_PROV ,B.ISU ,B.SIC_CODE ,B.FED_FLAG from DB2INST1.RDC_CMR B LEFT OUTER JOIN DB2INST1.LNK_ACCT_REL A on A.SRC_SYS = 'CUS' and A.SRC_KEY1 = COALESCE(B.CTRY, '**') and A.SRC_KEY2 = B.CUST_NUM

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Thugh, it might be too simplified (and have some wrong column qualifiers),
    please try...
    Code:
    SELECT brand
         , model_name       AS offering
         , MAX(value_float) AS PTB_Max
         , AVG(value_float) AS PTB_Avg
     FROM
          (SELECT h  .brand
                , h  .model_name
                , h  .created_ts
                , ptb.value_float
                , MAX(h.created_ts)
                     OVER( PARTITION BY h.abbreviated_name
                                      , ptb.urn_acct
                         ) max_created_ts
            FROM
                  amdata.model_scores_urn_am    ptb
            JOIN
                  amdata.model_scores_header_am h
             ON   h.model_id = ptb.model_id
            JOIN
                  adbam.rdc_cmr_amf             urn
             ON   urn.urn_acct = ptb.urn_acct
            JOIN
                  adbam.ref_region_am           region
             ON   region.ctry = urn.ctry 
            WHERE urn.client_id   = '01156715'
              AND ptb.attrib_name = 'PTB_STAR'
              AND
                  CASE region.region
                  WHEN 'CA'     THEN h.canada_imt
                  WHEN 'USE'    THEN h.us_east_imt
                  WHEN 'USW'    THEN h.us_west_imt
                  WHEN 'US*'    THEN h.us_east_imt
                  WHEN 'ALPS'   THEN h.alps_imt
                  WHEN 'DE'     THEN h.germany_imt
                  WHEN 'NORDIC' THEN h.nordic_imt
                  WHEN 'UKI'    THEN h.uki_imt
                  WHEN 'BLX'    THEN h.benelux_imt
                  WHEN 'FR'     THEN h.france_imt
                  WHEN 'IT'     THEN h.italy_imt
                  WHEN 'SPGI'   THEN h.spgi_imt
                  WHEN 'JAPAN'  THEN h.japan_imt
                  WHEN 'ANZ'    THEN h.anz_gmt
                  WHEN 'ASEAN'  THEN h.asean_gmt
                  WHEN 'CEE'    THEN h.cee_gmt
                  WHEN 'MEA'    THEN h.mea_gmt
                  WHEN 'GCG'    THEN h.gcg_gmt
                  WHEN 'INDSA'  THEN h.isa_gmt
                  WHEN 'KOREA'  THEN h.korea_gmt
                  WHEN 'LA'     THEN h.latin_america_gmt
                  END  = '1'
              AND h.expiry_ts >= current timestamp 
          )
     WHERE created_ts = max_created_ts
     GROUP BY
           brand
         , model_name 
     ORDER BY
           PTB_Max    desc
         , PTB_Avg    desc
         , brand      asc
         , model_name asc 
     FOR READ ONLY WITH UR
    ;

  7. #7
    Join Date
    Aug 2012
    Posts
    4

    Update on tuning

    Hi,

    There is some improvement but still taking 7 Min to complete.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you provide the index definition for the one table are we to assume that there are no indexes on the other tables involved?

    Dave Nance

  9. #9
    Join Date
    Nov 2011
    Posts
    334
    First , I think maybe you need to make sure the tables's statistics are up to date。
    From the explain,I saw many steps is much more under estimated by optimizer。
    So there may be some uncrroect btq,mbtq,nljoin chosed by db2
    ( for example ,to accomplish the nljoin (step 14) i think maybe broadcast the table region is better than sending the resultset of step 16 directly to partition 0 )。
    after collecting the statistics,if performance still is not accepted , then post the explain again。
    BTW,for this sql,i think the distrubited key of table RDC_CMR should be CUST_NUM while not SAP_CUSNUM。and dk of table MODEL_SCORES_URN should be MODEL_ID or urn_acct but not ("MODEL_ID", "URN_ACCT","ATTRIB_NAME")

    and if you could plz post the output for db2look -d <dbname> -t "tables in the query" -e -m here(after runstats)。

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •