Results 1 to 9 of 9

Thread: help in tuning

  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: help in tuning

    Hi, i need assistance tu tune this query running in db2

    SELECT
    T1.CONFLICT_ID,T1.LAST_UPD,T1.CREATED,
    T1.LAST_UPD_BY,T1.CREATED_BY,T1.MODIFICATION_NUM,
    T1.ROW_ID,T1.UNIT_PRI,4.UOM_CD,T32.PROD_CATG_CD,
    T1.VOL_DISCNT_ID,T1.VOL_DISCNT_ITEM_ID,T25.MAX_QTY ,
    T1.DISCNT_METH_CD,T25.MIN_QTY,T1.VOL_UPSELL_ITEM_I D,
    T1.VOL_UPSELL_MSG_TXT,T1.WAIVED_FLG,T1.PROD_NAME,
    T1.ATP_MSG,T1.ATP_STATUS_CD,T1.ATP_STATUS_DT,
    T3.ACCNT_ID,T1.ACTION_CD,T17.ACTIVITY_ID,T1.ACT_PR DINT_ID,
    T1.ADJ_UNIT_PRI,T1.STATUS_DT,T1.ASSET_ID,T1.ASSET_ INTEG_ID,
    T1.AUC_ITEM_ID,T1.AVAIL_DT,T1.AVAIL_QTY,T1.BASE_UN IT_PRI,
    T1.BILL_ADDR_ID,T1.BILLABLE_FLG,T1.BILL_ACCNT_ID,T 1.BONUS_QTY,
    T1.BONUS_THRESHOLD,T4.ONL_PAGESET_ID,T4.NAME,T1.OR G_PROD_ID,
    T1.CARRIER_CD,T4.CASE_PACK,T26.RELEASED_FLG,T1.CFG _STATE_CD,
    T1.CFG_TYPE_CD,T26.VER_NUM,T48.LAST_PAY_DUE_DT,
    T1.CRSE_OFFR_ID,T1.CRSE_REG_ID,T6.STATUS_CD,
    T47.CMPND_PROD_NUM,T4.CFG_MODEL_ID,T1.CONTACT_ID,
    T19.LAST_NAME,T7.LN_NUM,T8.NAME,T7.ADJ_UNIT_PRI,
    T7.DISCNT_AMT,T7.DISCNT_PERCENT,T1.SVCD_PROD_ID,
    T7.NET_PRI,T7.ROLLUP_PRI,T7.UNIT_PRI,T1.SVCD_ORDER _ITEM_ID,
    T4.CRITICAL_CD,T1.ORDER_ITM_CURCY_CD,T1.DLVRY_STAT US_CD,
    T1.DESC_TEXT,T1.DISCNT_AMT,T1.DISCNT_PERCENT,
    T1.DISCNT_SRC_CD,T1.REQ_SHIP_DT,T23.EFF_START_DT,
    T1.ELIG_REASON,T1.ELIG_STATUS_CD,T1.ENTLMNT_ID,
    T20.NAME,T1.ORDER_ITM_EXCH_DT,T1.EXCL_PRICING_FLG,
    T23.EXPCTD_DLVRY_DT,T1.EXTD_QTY,T1.FRGHT_AMT,
    T1.FRGHT_TERMS_CD,T1.CMPNS_STATUS_CD,
    T1.STATUS_CHANGED_FLG,T1.INTEGRATION_ID,
    T9.NAME,T1.PRI_LST_ID,T1.RESERVE_STATUS_CD,
    T3.ACCNT_ID,T3.PRI_LST_ID,T3.TAX_LIST_ID,
    T11.COST_PER_PT,T10.DEST_AIRPORT_CD,
    T10.DEST_ZONE_CD,T10.ORIG_AIRPORT_CD,
    T10.ORIG_ZONE_CD,T12.INTERNAL_NAME,
    T10.LOY_NUM_POINTS,T11.NUM_POINTS,T10.LOY_PROD_POI NT_ID,
    T10.LOY_TXN_TYPE_CD,T4.DOWN_TIME, T1.LATE_FLG,
    T4.ADDL_LEAD_TIME,T1.LN_NUM,T1.LN_NUM2,
    T1.UNIT_TAX_AMT,T1.PER_MTH_CHG_SUBTOT,
    T23.MAX_PRI,T4.MAX_ORDER_UNITS,T23.MIN_PRI,
    T4.MIN_ORDER_UNITS,T1.ONETIME_CHG_SUBTOT,T1.NET_PR I,
    T4.NET_ELMT_TYPE_CD,T1.LINE_NOTE,T3.ACTIVE_FLG,
    T3.ORDER_DT,T1.ORDER_ID,T3.ORDER_NUM,T3.QUOTE_ID,
    T3.ORDER_TYPE_ID,T3.BU_ID,T3.ACCNT_ID,T1.ITEM_GROU P_NAME,
    T13.TYPE_CD,T14.EXTENDED_QTY,T1.PAR_ORDER_ITEM_ID,
    T14.ITEM_GROUP_NAME,T15.TAX_SUBCOMP_FLG,T4.PART_NU M,
    T1.ALW_PART_SHIP_FLG,T12.DISPLAY_NAME,T4.PRICE_TYP E_CD,
    T1.PRICING_COMMENT, T1.CVRD_ASSET_ID, T1.PROCESSED_FLG,
    T1.PORT_VALID_PROD_ID,T1.PROMOTION_ID,T1.PROM_INTE G_ID,
    T1.PROM_ITEM_ID,T1.PROM_SRC_INTG_ID,T4.NAME,
    T4.PROD_TYPE_CD,T1.PROD_ID,T4.INTEGRATION_ID,T1.PR OD_PORT_ID,
    T4.PR_PROD_LN_ID,T4.CLASS_PARTNUM_ID,T1.PROD_STATU S_CD,
    T4.PROD_CD,T4.UOM_CD,T22.PAR_VOD_ID,T4.NAME,
    T1.PROMO_ITEM_FLG,T1.QTY_MAIN,T1.QTY_MINOR,T1.QTY_ REQ,
    T1.RTRN_ADDR_ID,T1.RTRN_CON_ID,T3.REVISION_DT,T1.R OLLUP_PRI,
    T2.ACTION_CD,T2.ASSET_INTEG_ID,T2.CFG_TYPE_CD,T2.P RI_LST_ID,
    T2.QTY_REQ,T2.REQ_SHIP_DT,T1.ROOT_ORDER_ITEM_ID,
    T2.ITEM_GROUP_NAME,T16.PROD_TYPE_CD,T2.PROD_ID,
    T16.NAME,T27.NAME,T23.SALES_REP_POSTN_ID,T4.SALES_ SRVC_FLG,
    T1.MUST_DLVR_BY_DT,T1.SERV_ACCNT_ID,T1.SVC_CHG_INC _FLG,
    T47.SERVICE_END_DT,T1.SERVICE_NUM,T47.SERVICE_LENG TH,
    T47.SERV_LENGTH_UOM_CD,T1.SVC_PER_PROD_AMT,T1.PRI_ METH_CD,
    T1.SVC_PER_PROD_PCT,T47.SERVICE_START_DT,
    T1.SHIP_COMPLETE_FLG,T29.NAME,T1.SHIP_OU_ID,T29.IN TEGRATION_ID,
    T1.SHIP_ADDR_ID,T30.FST_NAME,T30.LAST_NAME,T1.SHIP _CON_ID,
    T23.SKIP_CFG_VALDN_FLG,T1.SP_NUM,T1.SRC_INVLOC_ID,
    T27.INTEGRATION_ID,T27.NAME,T27.RECV_TO_ADDR_ID,T2 8.ZIPCODE,
    T1.STATUS_CD,T1.EXTND_PRICE,T1.EXTND_TAX,T1.TAX_AM T,
    T1.TAX_EXEMPT_FLG,T1.TAX_INC_FLG,T31.NAME,T4.TAX_S UBCOMP_FLG,
    T1.ROLLUP_FLG,
    T33.PR_HELD_POSTN_ID,
    T34.PAR_PARTY_ID,
    T16.TWOBARCODES_FLG,
    T2.LN_NUM,
    T1.TAX_EXEMPT_REASON,
    T35.LAST_NAME,
    T35.FST_NAME,
    T35.MID_NAME,
    T36.ASSET_NUM,
    T38.CG_FCST_AMT,
    T37.COUNTRY,
    T1.BO_ORDER_NUM,
    T46.ATTRIB_03,
    T46.ATTRIB_04,
    T39.INTEGRATION_ID,
    T19.WORK_PH_NUM,
    T1.X_VAL_FROM_DT,
    T1.X_VAL_THRU_DT,
    T3.AUTO_RECV_FLG,
    T23.X_BOE_PRI,
    T41.DESC_TEXT,
    T1.X_UC_ID,
    T38.NAME,
    T37.X_CO_UGVE,
    T1.X_AGF_ID,
    T39.NAME,
    T23.X_MIL_NAME,
    T1.X_SPCLPROC_FLG,
    T40.DESC_TEXT,
    T1.X_IN_PO_ID,
    T42.ADDR,
    T42.ADDR_NUM,
    T42.X_FLOOR,
    T42.X_LETTER,
    T42.PROVINCE,
    T42.ZIPCODE,
    T42.COUNTRY,
    T42.DESCRIPTOR,
    T42.METRO_AREA,
    T1.X_GALA_COD_HAES,
    T43.PAR_PARTY_ID,
    T1.X_KLCA_DISC_FLG,
    T46.ATTRIB_06,
    T1.X_CIRCUMSTANCIAL_FLG,
    T46.ATTRIB_07,
    T46.ATTRIB_12,
    T1.X_GALA_REL_PROD_NAME,
    T1.X_ORDER_LI_ID,
    T1.X_ASSET_ID,
    T39.VAT_REGN_NUM,
    T45.VAL,
    T1.X_GALA_VENTAS,
    T1.X_STATUS_CD,
    T42.CITY,
    T1.X_VISIBILITY_FLG,
    T49.ADDR,
    T49.CITY,
    T49.COUNTRY,
    T49.STATE,
    T49.ZIPCODE,
    T46.ROW_ID,
    T46.PAR_ROW_ID,
    T46.MODIFICATION_NUM,
    T46.CREATED_BY,
    T46.LAST_UPD_BY,
    T46.CREATED,
    T46.LAST_UPD,
    T46.CONFLICT_ID,
    T46.PAR_ROW_ID,
    T10.ROW_ID,
    T10.PAR_ROW_ID,
    T10.MODIFICATION_NUM,
    T10.CREATED_BY,
    T10.LAST_UPD_BY,
    T10.CREATED,
    T10.LAST_UPD,
    T10.CONFLICT_ID,
    T10.PAR_ROW_ID,
    T47.ROW_ID,
    T47.PAR_ROW_ID,
    T47.MODIFICATION_NUM,
    T47.CREATED_BY,
    T47.LAST_UPD_BY,
    T47.CREATED,
    T47.LAST_UPD,
    T47.CONFLICT_ID,
    T47.PAR_ROW_ID,
    T23.ROW_ID,
    T23.PAR_ROW_ID,
    T23.MODIFICATION_NUM,
    T23.CREATED_BY,
    T23.LAST_UPD_BY,
    T23.CREATED,
    T23.LAST_UPD,
    T23.CONFLICT_ID,
    T23.PAR_ROW_ID,
    T48.ROW_ID,
    T48.PAR_ROW_ID,
    T48.MODIFICATION_NUM,
    T48.CREATED_BY,
    T48.LAST_UPD_BY,
    T48.CREATED,
    T48.LAST_UPD,
    T48.CONFLICT_ID,
    T48.PAR_ROW_ID,
    T49.ROW_ID
    FROM
    SIEBEL.S_ORDER_ITEM T1
    INNER JOIN SIEBEL.S_ORDER_ITEM T2 ON T1.ROOT_ORDER_ITEM_ID = T2.ROW_ID
    INNER JOIN SIEBEL.S_ORDER T3 ON T1.ORDER_ID = T3.ROW_ID
    INNER JOIN SIEBEL.S_PROD_INT T4 ON T1.PROD_ID = T4.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_CTLG_CAT T5 ON T1.PROD_SUB_CAT_ID = T5.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_SRC_EVT_REG T6 ON T1.CRSE_REG_ID = T6.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ORDER_ITEM T7 ON T1.SVCD_ORDER_ITEM_ID = T7.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_PROD_INT T8 ON T1.SVCD_PROD_ID = T8.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_PRI_LST T9 ON T1.PRI_LST_ID = T9.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ORD_ITM_LOYX T10 ON T1.ROW_ID = T10.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_LOY_PROD_PT T11 ON T10.LOY_PROD_POINT_ID = T11.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_LOY_ATTRDEFN T12 ON T11.POINT_TYPE_ID = T12.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_CTLG_CAT T13 ON T5.CG_PAR_CTLG_CAT_ID = T13.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ORDER_ITEM T14 ON T1.PAR_ORDER_ITEM_ID = T14.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_PROD_INT T15 ON T14.PROD_ID = T15.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_PROD_INT T16 ON T2.PROD_ID = T16.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ACT_PRDINT T17 ON T1.ACT_PRDINT_ID = T17.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ASSET T18 ON T1.ASSET_ID = T18.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_CONTACT T19 ON T1.CONTACT_ID = T19.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ENTLMNT T20 ON T1.ENTLMNT_ID = T20.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_VOD T21 ON T4.CFG_MODEL_ID = T21.OBJECT_NUM
    LEFT OUTER JOIN SIEBEL.S_ISS_OBJ_DEF T22 ON T21.ROW_ID = T22.VOD_ID
    LEFT OUTER JOIN SIEBEL.S_ORDER_ITM_PRI T23 ON T1.ROW_ID = T23.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_POSTN T24 ON T23.SALES_REP_POSTN_ID = T24.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_VDISCNT_ITEM T25 ON T1.VOL_DISCNT_ITEM_ID = T25.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_VOD_VER T26 ON T21.ROW_ID = T26.VOD_ID
    LEFT OUTER JOIN SIEBEL.S_INVLOC T27 ON T1.SRC_INVLOC_ID = T27.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ADDR_PER T28 ON T27.RECV_TO_ADDR_ID = T28.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ORG_EXT T29 ON T1.SHIP_OU_ID = T29.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_CONTACT T30 ON T1.SHIP_CON_ID = T30.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_PRI_LST T31 ON T3.TAX_LIST_ID = T31.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_PROD_INT T32 ON T18.PROD_ID = T32.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_CONTACT T33 ON T1.CREATED_BY = T33.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_PARTY T34 ON T33.PR_HELD_POSTN_ID = T34.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_CONTACT T35 ON T24.PR_EMP_ID = T35.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ASSET T36 ON T1.ASSET_INTEG_ID = T36.INTEGRATION_ID
    LEFT OUTER JOIN SIEBEL.S_ADDR_PER T37 ON T18.OU_ADDR_ID = T37.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_DOC_QUOTE T38 ON T1.X_UC_ID = T38.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ORG_EXT T39 ON T1.X_AGF_ID = T39.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_PROD_INT_LANG T40 ON T1.PROD_ID = T40.PAR_ROW_ID AND T40.LANG_ID = ?
    LEFT OUTER JOIN SIEBEL.S_ASSET T41 ON T1.X_IN_PO_ID = T41.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ADDR_PER T42 ON T41.OU_ADDR_ID = T42.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_PARTY T43 ON T23.SALES_REP_POSTN_ID = T43.ROW_ID
    LEFT OUTER JOIN SIEBEL.S_LST_OF_VAL T44 ON T42.PROVINCE = T44.VAL AND T44.TYPE = 'GALA_AGF_PROVINCE_MASTER'
    LEFT OUTER JOIN SIEBEL.S_LST_OF_VAL T45 ON T44.PAR_ROW_ID = T45.ROW_ID AND T45.TYPE = 'GALA_TAX_NAME'
    LEFT OUTER JOIN SIEBEL.S_ORDER_ITEM_X T46 ON T1.ROW_ID = T46.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ORDER_ITEM_OM T47 ON T1.ROW_ID = T47.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ORDERITM_TRMS T48 ON T1.ROW_ID = T48.PAR_ROW_ID
    LEFT OUTER JOIN SIEBEL.S_ADDR_PER T49 ON T1.SHIP_ADDR_ID = T49.ROW_ID

    WHERE
    (T1.PROD_ID IS NULL OR T26.RELEASED_FLG = 'Y' AND T26.START_DT <= CAST(? AS TIMESTAMP) AND (T26.END_DT IS NULL OR T26.END_DT > CAST(? AS TIMESTAMP)) AND T22.FIRST_VERS <= T26.VER_NUM AND T22.LAST_VERS >= T26.VER_NUM) AND
    (T3.ACCNT_ID = ? AND T1.ROW_ID <> ? AND T1.ROOT_ORDER_ITEM_ID <> ? AND T2.ASSET_INTEG_ID <> ? AND T4.NAME IS NOT NULL AND T1.PROCESSED_FLG = ? AND T3.ACTIVE_FLG = ? AND T1.ACTION_CD <> ?)
    ORDER BY
    T1.LN_NUM, T1.LN_NUM2
    Last edited by ad777; 07-02-10 at 06:04.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Moving to DB2 forum

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Wrapping it in [code ] tags would help people help you, and formatting the WHERE clause especially so it is obvious what the conditions are.

    On a more esoteric front, those table aliases are appalling - either alias with meaningful names or not at all.

  4. #4
    Join Date
    Jul 2010
    Posts
    4
    Hi,

    its a dynamic sql generated by siebel

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have you tried db2advis ??
    what is the problem ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jul 2010
    Posts
    4
    hi,

    dont have access to db advisor or any other tool,
    this query takes around 40 seconds to run , which i have to reduce
    can we do something about the logic in where clause
    ?

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    One issue I see right away is that for tables t22 and t26 you have a left outer join, but the where clause turns them back to an inner join after the fact. Is that really what you want? It would be easier to make them inner joins in that case. I suspect it would, also, help the performance of the query. As for which access path would be best, we can't really tell you as we have no idea what is in the tables, what would be the best indexes to use, what should be the start table...etc...
    Dave

  8. #8
    Join Date
    Jul 2010
    Posts
    4
    hi,

    thanks for your suggestion , i will work on it, cant really tell about the data structures as our DBA is not in mood to change any index or structures, he has told us to change predicates or the query for tuning

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Changing predicates means changing the semantics of the query. That can only be done if the query wasn't quite fitting to begin with. Changing the query itself may be an option to pursue, i.e. using uncorrelated subselects instead of joins, etc. But there is a high chance that you need support from your DBA to figure out if additional indexes would be beneficial.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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