Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: Slow performance with ISO 99 SQL

    We have an Oracle 9.2.0.4.0 database.

    Some queries written using the ISO 99 SQL standard run extremely long/do not finish vs running quickly using the old "Oracle 8" sql (using (+) for outer joins). For example:

    This query using ISO 99 ran for over two hours before we killed it (estimated to run over three hours via Enterprise manager)

    select
    T2."PHYS_DEST_AVAIL_CD" "c25",
    T2."PHYS_SRC_AVAIL_CD" "c26",
    T7."NAME" "c27",
    T5."VERSION" "c28",
    T2."INV_TXN_NUM" "c29",
    T2."COMMIT_FLG" "c30",
    T8."ORDER_NUM" "c31",
    T2."TXN_DT" "c32",
    T2."QTY" "c33",
    T2."ACT_PRDINT_ID" "c34",
    T2."PHYS_DEST_STAT_CD" "c35",
    T7."PART_NUM" "c36",
    T6."LOGIN" "c37",
    T5."SERIAL_NUM" "c38",
    T5."ASSET_NUM" "c39",
    T4."NAME" "c40",
    T3."NAME" "c41",
    T2."PHYS_SRC_STATUS_CD" "c42",
    T1."NAME" "c43"
    from (((((((((
    "SIEBEL"."S_INV_TXN" T2
    LEFT OUTER JOIN "SIEBEL"."S_INV_TXN_TYPE" T1
    on T2."INV_TXN_TYPE_ID"=T1."ROW_ID")
    LEFT OUTER JOIN "SIEBEL"."S_INVLOC" T3
    on T2."PHYS_DEST_INVLC_ID"=T3."ROW_ID")
    LEFT OUTER JOIN "SIEBEL"."S_INVLOC" T4
    on T2."PHYS_SRC_INVLOC_ID"=T4."ROW_ID")
    LEFT OUTER JOIN "SIEBEL"."S_INV_TXN_ASSET" T9
    on T2."ROW_ID"=T9."INV_TXN_ID")
    LEFT OUTER JOIN "SIEBEL"."S_ASSET" T5
    on T9."ASSET_ID"=T5."ROW_ID")
    LEFT OUTER JOIN "SIEBEL"."S_USER" T6
    on T2."CREATED_BY"=T6."ROW_ID")
    LEFT OUTER JOIN "SIEBEL"."S_PROD_INT" T7
    on T2."PHYS_PROD_ID"=T7."ROW_ID")
    LEFT OUTER JOIN "SIEBEL"."S_ORDPART_MVMT" T10
    on T2."ORD_PART_MVMT_ID"=T10."ROW_ID")
    LEFT OUTER JOIN "SIEBEL"."S_ORDER_ITEM" T11 on
    T10."ORDER_ITEM_ID"=T11."ROW_ID")
    LEFT OUTER JOIN "SIEBEL"."S_ORDER" T8
    on T11."ORDER_ID"=T8."ROW_ID"
    where
    T1."NAME" in ('Adjustment', 'Allocate', 'De-Allocate',
    'Exchange Between FSEs', 'Over-the-counter',
    'Receive From TP', 'Receive Internal', 'Receive Other',
    'Ship Internal', 'Ship Other', 'Ship To TP', 'Stock Transfer')
    and T2."TXN_DT" between to_date('2003-01-01 00:00:00', 'YYYY-MM-DD HH24:MIS')
    and to_date('2004-09-22 00:00:00', 'YYYY-MM-DD HH24:MIS')
    and NVL(T4."NAME",' ') like '%'
    and NVL(T3."NAME",' ') like '%'
    and T6."LOGIN" like '%'
    and NVL(T7."PART_NUM",' ') like '1014804-003'
    and NVL(T5."ASSET_NUM",' ') like 'T14969'
    and T2."PHYS_SRC_STATUS_CD" like '%'
    and T2."PHYS_DEST_STAT_CD" like '%'
    order by "c31" asc, "c34" asc, "c33" asc, "c28" asc, "c32" asc

    But this query which is functionally equal using the older style sql runs in 30 seconds.

    select
    T2."PHYS_DEST_AVAIL_CD" "c25",
    T2."PHYS_SRC_AVAIL_CD" "c26",
    T7."NAME" "c27",
    T5."VERSION" "c28",
    T2."INV_TXN_NUM" "c29",
    T2."COMMIT_FLG" "c30",
    T8."ORDER_NUM" "c31",
    T2."TXN_DT" "c32",
    T2."QTY" "c33",
    T2."ACT_PRDINT_ID" "c34",
    T2."PHYS_DEST_STAT_CD" "c35",
    T7."PART_NUM" "c36",
    T6."LOGIN" "c37",
    T5."SERIAL_NUM" "c38",
    T5."ASSET_NUM" "c39",
    T4."NAME" "c40",
    T3."NAME" "c41",
    T2."PHYS_SRC_STATUS_CD" "c42",
    T1."NAME" "c43"
    from
    "SIEBEL"."S_INV_TXN" T2,
    "SIEBEL"."S_INV_TXN_TYPE" T1,
    "SIEBEL"."S_INVLOC" T3,
    "SIEBEL"."S_INVLOC" T4,
    "SIEBEL"."S_INV_TXN_ASSET" T9,
    "SIEBEL"."S_ASSET" T5,
    "SIEBEL"."S_USER" T6,
    "SIEBEL"."S_PROD_INT" T7,
    "SIEBEL"."S_ORDPART_MVMT" T10,
    "SIEBEL"."S_ORDER_ITEM" T11,
    "SIEBEL"."S_ORDER" T8
    where
    T2."INV_TXN_TYPE_ID"=T1."ROW_ID"(+)
    and T2."PHYS_DEST_INVLC_ID"=T3."ROW_ID"(+)
    and T2."PHYS_SRC_INVLOC_ID"=T4."ROW_ID"(+)
    and T2."ROW_ID"=T9."INV_TXN_ID"(+)
    and T9."ASSET_ID"=T5."ROW_ID"(+)
    and T2."CREATED_BY"=T6."ROW_ID"(+)
    and T2."PHYS_PROD_ID"=T7."ROW_ID"(+)
    and T2."ORD_PART_MVMT_ID"=T10."ROW_ID"(+)
    and T10."ORDER_ITEM_ID"=T11."ROW_ID"(+)
    and T11."ORDER_ID"=T8."ROW_ID"(+)
    and T1."NAME" in ('Adjustment', 'Allocate', 'De-Allocate',
    'Exchange Between FSEs', 'Over-the-counter',
    'Receive From TP', 'Receive Internal',
    'Receive Other', 'Ship Internal', 'Ship Other',
    'Ship To TP', 'Stock Transfer')
    and T2."TXN_DT" between to_date('2003-01-01 00:00:00', 'YYYY-MM-DD HH24:MIS')
    and to_date('2004-09-22 00:00:00', 'YYYY-MM-DD HH24:MIS')
    and NVL(T4."NAME",' ') like '%'
    and NVL(T3."NAME",' ') like '%'
    and T6."LOGIN" like '%'
    and NVL(T7."PART_NUM",' ') like '1014804-003'
    and NVL(T5."ASSET_NUM",' ') like 'T14969'
    and T2."PHYS_SRC_STATUS_CD" like '%'
    and T2."PHYS_DEST_STAT_CD" like '%'
    order by "c31" asc, "c34" asc, "c33" asc, "c28" asc, "c32" asc


    Is there a way to tune this? Unfortunately, these queries are generated out of a reporting tool (Cognos Impromptu) so I don't have much control on the syntax. The query that runs fast came from their version 6 tool (which was out before 9I existed), and the one that doesn't finish was created in the new 7.1 version of their tool which creates all sql going to a 9.2 database using the ISO 99 standard.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    enable SQL_TRACE & run the results thru TKPROF.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2004
    Posts
    2

    cost based vs rule

    The dba did a couple explain plans (I will try to get the trace done tomorrow) and the one that runs fast runs using the rule based optimizer, bu the slow one uses cost. The database is set up to use rule based, but the slow one still uses cost based even after we go to sqlplus and alter the session to use rule mode.

Posting Permissions

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