Very Long Running SQL - Urgent

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

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?

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

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

Quote:

---

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?

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

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

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

You can force an index like:

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

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

Yes, you should use /*+ ... */ if you want the hint to realy be a hint to database engine.

Your explain plan probably didn't show any difference as the provided syntax for the hint was wrong (there might be other reasons, though), so Oracle treated it as a normal comment. The correct syntax is:

SELECT /*+ INDEX(table_name index_name) */ .....

Note that there is no comma between table name and index name inside the hint.

Oracle 8.1.6 NT

Thanks for all your replies,

We have now discovered other queries failing to run correctly and have identified that the problem lies with PS_SHIP_INF_INV table.

All things being equal, and with no impact on other queries, can anyone suggest what columns should be in an index on that table to assist it to run more successfully.

We are using a cost based optimizer so I would rather not hint the index.

Many thanks in advance

Allie

Quote:

---

Originally posted by helpme
You can force an index like:

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

---

Your hint syntax is wrong. It's /*+ INDEX(table_alias index_name) */

(note the space between you table alias and your index name)

Quote:

---

Originally posted by jmodic
Yes, you should use /*+ ... */ if you want the hint to realy be a hint to database engine.

Your explain plan probably didn't show any difference as the provided syntax for the hint was wrong (there might be other reasons, though), so Oracle treated it as a normal comment. The correct syntax is:

SELECT /*+ INDEX(table_name index_name) */ .....

Note that there is no comma between table name and index name inside the hint.

---

Doh - I should be the head of the office of redundancy office.

Thanks,

I got that to work.

But I would rather not hint the index if possible, I would like to create a new index that would speed up this query specifically.

I was wondering based on the query above and the execution plan what would the best columns and in which order should they be in the index.

Kind thanks

Alison