Tune SQL Urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Tune SQL Urgent

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  2. #2
    Join Date
    Feb 2002
    Posts
    7
    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
    Ganesan S.

  3. #3
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    Can you print the cost column in the explain plan statement?
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  4. #4
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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
    What's the sound of one hand clapping - "CL"

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  6. #6
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    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.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

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