Very Long Running SQL - Urgent

[alison]

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

[julian]

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?

[alison]

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

[anandkl]

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

[julian]

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?

[alison]

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

[helpme]

Is column PS_SHIP_INF_INV.DEMAND_SOURCE indexed?
if yes,try forcing that index in the query?

[alison]

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

[helpme]

You can force an index like:

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

[alison]

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