Tune SQL Urgent

[alison]

Hi there,

I was wondering if anyone could help me tune the following query.

As it stands this query never produces any results, it takes so long that it has to be canceled.

I have included the explain plan for the query and a list of the row counts for each table included.

The user is urgently requiring this information

Many thanks

Allie

SELECT DISTINCT
B.BUSINESS_UNIT,
A.TRANSACTION_GROUP,
TO_CHAR(B.TRANSACTION_DATE,'YYYY-MM-DD'),
TO_CHAR(A.ACCOUNTING_DT,'YYYY-MM-DD'),
A.ACCTG_LINE_NO,
B.INV_ITEM_ID,
B.QTY,
A.ACCOUNT,
A.DEPTID,
A.MONETARY_AMOUNT,
B.STORAGE_AREA,
B.STOR_LEVEL_1,
A.DISTRIB_TYPE,
A.ORDER_NO,
A.RECEIVER_ID,
E.RECEIVER_ID,
A.RECV_LN_NBR,
A.JOURNAL_ID,
D.RTV_ID,
D.RTV_LN_NBR,
E.REJECT_ACTION,
E.REJECT_REASON,
E.RMA_ID,
E.RMA_LINE_NBR,
C.SHIP_TO_CUST_ID,
E.RTV_LN_STATUS
FROM PS_TRANSACTION_INV B, PS_CM_ACCTG_LINE A, PS_MSR_HDR_INV C, PS_SHIP_INF_INV D, PS_RTV_LN E
WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT
AND B.INV_ITEM_ID = A.INV_ITEM_ID
AND B.DT_TIMESTAMP = A.DT_TIMESTAMP
AND B.SEQ_NBR = A.SEQ_NBR
AND A.BUSINESS_UNIT = '00240'
AND A.TRANSACTION_GROUP = '012'
AND A.ACCOUNT = '2210GRN03'
AND A.JOURNAL_ID BETWEEN 'IN00003801' AND 'IN00003952'
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.ORDER_NO = C.ORDER_NO
AND A.DISTRIB_TYPE = C.DISTRIB_TYPE
AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND B.INV_ITEM_ID = D.INV_ITEM_ID
AND A.ORDER_NO = D.ORDER_NO
AND A.ORDER_INT_LINE_NO = D.ORDER_INT_LINE_NO
AND D.BUSINESS_UNIT = E.BUSINESS_UNIT
AND D.INV_ITEM_ID = E.INV_ITEM_ID
AND D.RTV_ID = E.RTV_ID
AND D.RTV_LN_NBR = E.RTV_LN_NBR

SQL> @EXPLAIN_PLAN

STATEMENT_| ID|PARENT_ID|ACCESS_PLAN |OPTIONS |OBJECT_NAME
----------|------|---------|----------------------------------------|--------------------|--------------------
ALISON | 0| | SELECT STATEMENT | |
ALISON | 1| 0| SORT |UNIQUE |
ALISON | 2| 1| NESTED LOOPS | |
ALISON | 3| 2| NESTED LOOPS | |
ALISON | 4| 3| NESTED LOOPS | |
ALISON | 5| 4| HASH JOIN | |
ALISON | 6| 5| TABLE ACCESS |FULL |PS_RTV_LN
ALISON | 7| 5| TABLE ACCESS |FULL |PS_SHIP_INF_INV
ALISON | 8| 4| TABLE ACCESS |BY INDEX ROWID |PS_TRANSACTION_INV
ALISON | 9| 8| INDEX |RANGE SCAN |PS_TRANSACTION_INV
ALISON | 10| 3| TABLE ACCESS |BY INDEX ROWID |PS_CM_ACCTG_LINE
ALISON | 11| 10| INDEX |RANGE SCAN |PSDCM_ACCTG_LINE
ALISON | 12| 2| TABLE ACCESS |BY INDEX ROWID |PS_MSR_HDR_INV
ALISON | 13| 12| INDEX |RANGE SCAN |PS_MSR_HDR_INV


SQL> SELECT COUNT(*) FROM PS_TRANSACTION_INV;

COUNT(*)
--------------------------------------------------
1147734

SQL> SELECT COUNT(*) FROM PS_CM_ACCTG_LINE;

COUNT(*)
--------------------------------------------------
11647712

SQL> SELECT COUNT(*) FROM PS_MSR_HDR_INV;

COUNT(*)
--------------------------------------------------
5462

SQL> SELECT COUNT(*) FROM PS_SHIP_INF_INV;

COUNT(*)
--------------------------------------------------
67835

SQL> SELECT COUNT(*) FROM PS_RTV_LN;

COUNT(*)
--------------------------------------------------
1717

[gansen]

Try the query by keeping these 4 lines first in the where clause

AND A.BUSINESS_UNIT = '00240'
AND A.TRANSACTION_GROUP = '012'
AND A.ACCOUNT = '2210GRN03'
AND A.JOURNAL_ID BETWEEN 'IN00003801' AND 'IN00003952'

Change the table order in such a way like

the higest rows returned table as the last in the list

Check for indexes properly used

[kris109]

Can you print the cost column in the explain plan statement?

[alison]

As requested I have included the cost in the explain plan.

Thanks for your help.

Allie

STATEMENT_ ID PARENT_ID ACCESS_PLAN OPTIONS OBJECT_NAME COST
---------- ------ --------- ---------------------------------------- -------------------- -------------------- --------
ALISON 0 SELECT STATEMENT 548
ALISON 1 0 SORT UNIQUE 548
ALISON 2 1 NESTED LOOPS 546
ALISON 3 2 NESTED LOOPS 545
ALISON 4 3 NESTED LOOPS 265
ALISON 5 4 HASH JOIN 163
ALISON 6 5 TABLE ACCESS FULL PS_RTV_LN 2
ALISON 7 5 TABLE ACCESS FULL PS_SHIP_INF_INV 158
ALISON 8 4 TABLE ACCESS BY INDEX ROWID PS_TRANSACTION_INV 51
ALISON 9 8 INDEX RANGE SCAN PS_TRANSACTION_INV 1
ALISON 10 3 TABLE ACCESS BY INDEX ROWID PS_CM_ACCTG_LINE 1
ALISON 11 10 INDEX RANGE SCAN PSDCM_ACCTG_LINE 1
ALISON 12 2 TABLE ACCESS BY INDEX ROWID PS_MSR_HDR_INV 1
ALISON 13 12 INDEX RANGE SCAN PS_MSR_HDR_INV

[anandkl]

Make sure you table is not fragmented.If yes try to taken a export of your tables and create the tables by setting proper storage parameters so that when u import it gets inserted into few number of blocks.Also if there were heavy insert,update,delete on the tables then the index on these tables needs to be rebuild as they will still be holding the info of those records delete or updated.

Create index with decereasing order of selectivity.Selectivity is calculated based on no. of distinct values in the column to the total no. of values in the column.So if the selectivity is 1 or close to 1 then it must be the first column to be included in creating a complex index followed by the next high selectivity column.

cheese
anandkl

[kris109]

Your resources wasted by unique( eliminting duplicates) and nested loops (joins). Also PS_SHIP_INF_INV can use an index.

As the previous poster mentioned, you need to put the most restrictive join conditions first. And since you are comparing Business_id in so many tables, A.BUSINESS_UNIT = '00240' should come first. Also you need to create an index on PS_SHIP_INF_INV business_unit.

Indexes on these columns should improve performance:

B.BUSINESS_UNIT
A.BUSINESS_UNIT
B.INV_ITEM_ID
A.INV_ITEM_ID
B.SEQ_NBR
A.SEQ_NBR

A composites index (business_unit, inv_item_id, seq_nbr) on both A and B would improve the performance of your query.