Very Long Running SQL - Urgent
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Very Long Running SQL - Urgent

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi there,

    I'm having lots of problems with a sqr job which is running the following code.

    UPDATE PS_ORD_SCHEDULE
    SET ORD_SCH_STATUS = 'C'
    WHERE ORD_SCH_STATUS <> 'C'
    AND ORD_SCH_STATUS <> 'X'
    AND EXISTS (SELECT 'X' FROM PS_ORD_LN_PRTYP_VW
    WHERE PS_ORD_LN_PRTYP_VW.BUSINESS_UNIT = PS_ORD_SCHEDULE.BUSINESS_UNIT
    AND PS_ORD_LN_PRTYP_VW.ORDER_NO = PS_ORD_SCHEDULE.ORDER_NO
    AND PS_ORD_LN_PRTYP_VW.ORDER_INT_LINE_NO = PS_ORD_SCHEDULE.ORDER_INT_LINE_NO
    AND PS_ORD_LN_PRTYP_VW.PRODUCT_KIT_FLAG = 'N')
    AND ( (BCKORDR_CNCL_FLAG = 'Y'
    AND EXISTS (SELECT 'X' FROM PS_SHIP_INF_INV
    WHERE PS_SHIP_INF_INV.SOURCE_BUS_UNIT = PS_ORD_SCHEDULE.BUSINESS_UNIT
    AND PS_SHIP_INF_INV.ORDER_NO = PS_ORD_SCHEDULE.ORDER_NO
    AND PS_SHIP_INF_INV.ORDER_INT_LINE_NO = PS_ORD_SCHEDULE.ORDER_INT_LINE_NO
    AND PS_SHIP_INF_INV.SCHED_LINE_NO = PS_ORD_SCHEDULE.SCHED_LINE_NBR
    AND PS_SHIP_INF_INV.DEMAND_SOURCE = 'OM'))
    OR
    ( QTY_SCHEDULED = (SELECT SUM(QTY_SHIPPED) FROM PS_SHIP_INF_INV
    WHERE PS_SHIP_INF_INV.SOURCE_BUS_UNIT = PS_ORD_SCHEDULE.BUSINESS_UNIT
    AND PS_SHIP_INF_INV.ORDER_NO = PS_ORD_SCHEDULE.ORDER_NO
    AND PS_SHIP_INF_INV.ORDER_INT_LINE_NO = PS_ORD_SCHEDULE.ORDER_INT_LINE_NO
    AND PS_SHIP_INF_INV.SCHED_LINE_NO = PS_ORD_SCHEDULE.SCHED_LINE_NBR
    AND PS_SHIP_INF_INV.DEMAND_SOURCE = 'OM')))

    Sorry it's so long.

    This part of the job is sitting for about 6 hours before it moves on.

    I have ran an explain plan for it, which gives me the following results:-

    ID|PARENT_ID|ACCESS_PLAN |OPTIONS |OBJECT_NAME
    ------|---------|------------------------------|------------------------------|----------------------
    0| | UPDATE STATEMENT | |
    1| 0| UPDATE | |PS_ORD_SCHEDULE
    2| 1| FILTER | |
    3| 2| TABLE ACCESS |FULL |PS_ORD_SCHEDULE
    4| 2| NESTED LOOPS | |
    5| 4| NESTED LOOPS | |
    6| 5| TABLE ACCESS |BY INDEX ROWID |PS_ORD_LINE
    7| 6| INDEX |UNIQUE SCAN |PS_ORD_LINE
    8| 5| INDEX |RANGE SCAN |PSASET_CNTRL_REC
    9| 4| TABLE ACCESS |BY INDEX ROWID |PS_PROD_ITEM
    10| 9| INDEX |RANGE SCAN |PSAPROD_ITEM
    11| 2| INDEX |FULL SCAN |PS_SHIP_INF_INV
    12| 2| SORT |AGGREGATE |
    13| 12| TABLE ACCESS |FULL |PS_SHIP_INF_INV

    I haven't done any sql tuning beforeso I'm not really sure what I'm looking for.

    Any help would be a life saver - I have users on my back and my senior DBA is on holiday.

    Kind regards

    Alison

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Try

    analyze table PS_SHIP_INF_INV estimate statistics;
    analyze table PS_ORD_SCHEDULE estimate statistics;
    analyze table PS_ORD_LN_PRTYP_VW estimate statistics;

    Could be that the statistics are old or somebody has droped and rebuilt an index without analyzing it first. You said it takes 6hours, how long is expected for the script to run?

    How big are these tables?


  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi thanks for your reply,

    I have analyzed the tables and rebuilt the indexes,

    I have re-ran the explain plan and got the following results:-

    ID|PARENT_ID|ACCESS_PLAN |OPTIONS |OBJECT_NAME
    ------|---------|------------------------------|------------------------------|-----------------------
    0| | UPDATE STATEMENT | |
    1| 0| UPDATE | |PS_ORD_SCHEDULE
    2| 1| FILTER | |
    3| 2| TABLE ACCESS |FULL |PS_ORD_SCHEDULE
    4| 2| NESTED LOOPS | |
    5| 4| NESTED LOOPS | |
    6| 5| TABLE ACCESS |BY INDEX ROWID |PS_ORD_LINE
    7| 6| INDEX |UNIQUE SCAN |PS_ORD_LINE
    8| 5| INDEX |RANGE SCAN |PSASET_CNTRL_REC
    9| 4| TABLE ACCESS |BY INDEX ROWID |PS_PROD_ITEM
    10| 9| INDEX |RANGE SCAN |PSAPROD_ITEM
    11| 2| INDEX |FULL SCAN |PS_SHIP_INF_INV
    12| 2| SORT |AGGREGATE |
    13| 12| TABLE ACCESS |FULL |PS_SHIP_INF_INV

    The tables sizes involved are
    PS_SHIP_INF_INV = 51235
    PS_ORD_SCHEDULE = 72211
    PA_ORD_LN_PRTYP_VW is a view (I don't think its causing any problems)

    The query would normally take anything from minutes to about an hour depending on how many orders it's using.

    I'm not sure why rebuilding the indexes without running an analyze would cause a problem, could you please explain.

    Kind thanks

    Alison


  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Try to create a concatnated index on the primary key columns with the first colum with highest selectivity followed by the next and so on and then run the sql

    hope this helps

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I'm not sure why rebuilding the indexes without running an analyze would cause a problem, could you please explain.
    I meant if someone has rebuild the index with drop+create, then you should reanalyze.

    Did you have any improvement in performance after analyze?


  6. #6
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    No, nothing was better,

    I do think that it's a problem with the indexes, I beleve one was changed,

    I'm not sure what I need the index to be though, what columns ect, there are already 13 indexes on the SHIP_INF_INV table

    Could you help

    Kind Regards

    Alison

  7. #7
    Join Date
    Jun 2001
    Posts
    316
    Is column PS_SHIP_INF_INV.DEMAND_SOURCE indexed?
    if yes,try forcing that index in the query?

  8. #8
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Yes demand source is indexed, along with

    BUSINESS_UNIT
    DEMAND_SOURCE
    SOURCE_BUS_UNIT
    ORDER_NO
    ORDER_INT_LINE_NO
    SCHED_LINE_NO
    INV_ITEM_ID
    DEMAND_LINE_NO

    In that order.
    Index name PS_SHIP_INF_INV

    How can I tell if the query is using it? or how do I force the query to use it?

    Many thanks for all your help

    A

  9. #9
    Join Date
    Jun 2001
    Posts
    316
    You can force an index like:

    SELECT /*+ INDEX(table_name, index _name) */ column1,column2 FROM table WHERE column LIKE '%SAM%'

  10. #10
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Thanks Helpme,

    When I use the force index should I put it in the /*..*/ like in the example you gave me.

    I ran an explain plan with this in but it didn't make any difference (not sure if it should)

    I also tried to run the code (with a select instead of an update) but it also didn't make any difference, I eventually had to cancel it.

    Thanks for your help

    A

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