DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Help needed to Tune this sql script

  1. #1
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    I have pasted a query below.Its taking me 28 minutes to execute this query.Your help and suestions are appreciated.

    There are 3 synonyms
    SY_SN_9478ORDER_HIER -10 lakh records
    SY_TB_9296BOOKING_TRANSACTION -20 lakh records
    SY_SN_6419FCST_ACCESS_PERM -1000 records

    Present Indexes
    ------------------
    SY_TB_9296BOOKING_TRANSACTION
    ========================
    sales_order_key , sales_line_key(compound index)
    SY_SN_6419FCST_ACCESS_PERM
    ---------------------------------------
    securing_attribute_name,securing_attribute_value
    application_cmpnt_code,application_module_code,application_system_code,user_login

    Pls. help me what to do now.

    SELECT
    sum(nvl(SY_TB_9296BOOKING_TRANSACTION.BOOKING_AMT,0) *
    nvl(SY_SN_9478ORDER_HIER.SPLIT_CREDIT_PCT,100) * (0.01))
    FROM
    SY_SN_9478ORDER_HIER,
    SY_TB_9296BOOKING_TRANSACTION,
    SY_SN_6419FCST_ACCESS_PERM
    WHERE
    (
    SY_TB_9296BOOKING_TRANSACTION.SALES_ORDER_KEY=SY_SN_9478ORDER_HIER.SALES_ORDER_KEY AND
    SY_TB_9296BOOKING_TRANSACTION.SALES_LINE_KEY=SY_SN_9478ORDER_HIER.SALES_LINE_KEY
    )
    AND
    (
    (SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_NAME = 'DISTRICT_PRIMARY_POS_ORG' AND
    SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_VALUE=SY_SN_9478ORDER_HIER.DISTRICT_PRIMARY_POS_ORG)
    OR
    (SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_NAME = 'PARENT_CUSTOMER_KEY' AND
    SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_VALUE=to_char(SY_SN_9478ORDER_HIER.PARENT_CUSTOMER_KEY ))
    OR
    (SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_NAME = 'PARENT_ULTIMATE_CUSTOMER_KEY' AND
    SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_VALUE=to_char(SY_SN_9478ORDER_HIER.PARENT_ULTIMATE_CUS TOMER_KEY))
    )
    AND
    ( SY_SN_6419FCST_ACCESS_PERM.APPLICATION_CMPNT_CODE = 'BUS_OBJ'
    AND SY_SN_6419FCST_ACCESS_PERM.APPLICATION_MODULE_CODE = 'BUS_OBJ'
    AND SY_SN_6419FCST_ACCESS_PERM.APPLICATION_SYSTEM_CODE = 'SALES_RESULTS' AND
    SY_SN_6419FCST_ACCESS_PERM.USER_LOGIN = upper('MEIGP')
    )
    SELECT
    sum(nvl(SY_TB_9296BOOKING_TRANSACTION.BOOKING_AMT,0) *
    nvl(SY_SN_9478ORDER_HIER.SPLIT_CREDIT_PCT,100) * (0.01))
    FROM
    SY_SN_9478ORDER_HIER,
    SY_TB_9296BOOKING_TRANSACTION,
    SY_SN_6419FCST_ACCESS_PERM
    WHERE
    (
    SY_TB_9296BOOKING_TRANSACTION.SALES_ORDER_KEY=SY_SN_9478ORDER_HIER.SALES_ORDER_KEY AND
    SY_TB_9296BOOKING_TRANSACTION.SALES_LINE_KEY=SY_SN_9478ORDER_HIER.SALES_LINE_KEY
    )
    AND
    (
    (SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_NAME = 'DISTRICT_PRIMARY_POS_ORG' AND
    SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_VALUE=SY_SN_9478ORDER_HIER.DISTRICT_PRIMARY_POS_ORG)
    OR
    (SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_NAME = 'PARENT_CUSTOMER_KEY' AND
    SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_VALUE=to_char(SY_SN_9478ORDER_HIER.PARENT_CUSTOMER_KEY ))
    OR
    (SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_NAME = 'PARENT_ULTIMATE_CUSTOMER_KEY' AND
    SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_VALUE=to_char(SY_SN_9478ORDER_HIER.PARENT_ULTIMATE_CUS TOMER_KEY))
    )
    AND
    ( SY_SN_6419FCST_ACCESS_PERM.APPLICATION_CMPNT_CODE = 'BUS_OBJ'
    AND SY_SN_6419FCST_ACCESS_PERM.APPLICATION_MODULE_CODE = 'BUS_OBJ'
    AND SY_SN_6419FCST_ACCESS_PERM.APPLICATION_SYSTEM_CODE = 'SALES_RESULTS' AND
    SY_SN_6419FCST_ACCESS_PERM.USER_LOGIN = upper('MEIGP')
    )

  2. #2
    Join Date
    Mar 2001
    Posts
    63
    Great oogley moogley! That's one heck of a query.

    Seriously though, have you done an explain plan on this yet? Probably a good place to start.

  3. #3
    Join Date
    Mar 2001
    Posts
    63
    Try setting up 2 indexes:

    SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_NAME
    SY_SN_6419FCST_ACCESS_PERM.SECURING_ATTRIBUTE_VALUE

    -and-

    SY_SN_6419FCST_ACCESS_PERM.APPLICATION_CMPNT_CODE
    SY_SN_6419FCST_ACCESS_PERM.APPLICATION_MODULE_CODE
    SY_SN_6419FCST_ACCESS_PERM.APPLICATION_SYSTEM_CODE
    SY_SN_6419FCST_ACCESS_PERM.USER_LOGIN




    Next, do an analyze table compute statistics on SY_SN_6419FCST_ACCESS_PERM. The query you actually have isn't all that bad processing wise, just a lot of logic.

    Did you hit paste twice or do you really repeat the same query twice?


  4. #4
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    The indexes you mentioned are already there and all the tables are analyzed.Still the same problem.

  5. #5
    Join Date
    Mar 2001
    Posts
    63
    So what does the explain plan say then?

  6. #6
    Join Date
    Mar 2001
    Posts
    18
    what type of indexes are you using?
    rule-based or cost-based optimization?

    how often do you run this query?

Posting Permissions

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


Click Here to Expand Forum to Full Width