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