Query runs slow after database upgrade
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Query runs slow after database upgrade

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Query runs slow after database upgrade

    Hi,

    We did database upgrade from 11.1.0.7 to 11.2.0.3 over the last weekend. After that following query is running extremely slow.

    SELECT
    NULL,
    LTRIM(RTRIM(A.ATTRIBUTE2)),
    F.SEGMENT3,
    DECODE(F.SEGMENT3,'52321','350',DECODE(F.SEGMENT3,'52324','350','300')),
    SUBSTR(C.SEGMENT1,1,10),
    A.accounting_date,
    DECODE(SUBSTR(A.DESCRIPTION,1,3),'SPA',B.DESCRIPTION,A.DESCRIPTION),
    NULL,
    A.AMOUNT,
    NULL,
    NULL,
    B.INVOICE_NUM,
    DECODE(A.REVERSAL_FLAG,NULL,A.LINE_TYPE_LOOKUP_CODE,'REVERSAL'),
    NULL,
    NULL,
    NULL,
    SUBSTR(C.VENDOR_NAME,1,20),
    UPPER(B.VOUCHER_NUM) AS SPA_ID,
    A.INVOICE_ID,
    A.DISTRIBUTION_LINE_NUMBER,
    A.INVOICE_LINE_NUMBER
    FROM
    APPS.AP_INVOICE_DISTRIBUTIONS_all A,
    APPS.AP_INVOICES_all B,
    APPS.PO_VENDORS C,
    APPS.GL_CODE_COMBINATIONS F
    WHERE
    A.ATTRIBUTE2 IS NOT NULL AND
    A.INVOICE_ID = B.INVOICE_ID AND
    B.VENDOR_ID = C.VENDOR_ID AND
    A.POSTED_FLAG='Y' AND
    A.accounting_date between TRUNC(SYSDATE-360) AND TRUNC(SYSDATE) AND
    A.DIST_CODE_COMBINATION_ID = F.CODE_COMBINATION_ID AND
    (F.SEGMENT3 LIKE '52%' OR F.SEGMENT3 LIKE '68%' OR F.SEGMENT3 LIKE '69%')
    AND
    A.INVOICE_ID||A.INVOICE_LINE_NUMBER||A.DISTRIBUTION_LINE_NUMBER NOT IN
    (SELECT BILLS_INVOICE_ID||BILLS_LINE_NUMBER||BILLS_DISTRIBUTION_LINE_NUMBER
    FROM KFORM.BILLS WHERE
    A.INVOICE_ID = BILLS_INVOICE_ID AND
    A.INVOICE_LINE_NUMBER = BILLS_LINE_NUMBER AND
    A.DISTRIBUTION_LINE_NUMBER = BILLS_DISTRIBUTION_LINE_NUMBER)


    Here is the explain plan difference.

    PLAN BEFORE UPGRADE
    ----------------------------------------------
    Plan
    SELECT STATEMENT ALL_ROWSCost: 23,467 Bytes: 315,592 Cardinality: 1,532
    16 FILTER
    14 FILTER
    13 HASH JOIN Cost: 21,934 Bytes: 315,592 Cardinality: 1,532
    11 NESTED LOOPS
    9 NESTED LOOPS Cost: 20,677 Bytes: 307,932 Cardinality: 1,532
    7 NESTED LOOPS Cost: 17,610 Bytes: 245,120 Cardinality: 1,532
    4 HASH JOIN Cost: 14,544 Bytes: 142,476 Cardinality: 1,532
    1 TABLE ACCESS FULL TABLE GL.GL_CODE_COMBINATIONS Cost: 594 Bytes: 17,472 Cardinality: 1,456
    3 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_INVOICE_DISTRIBUTIONS_ALL Cost: 13,950 Bytes: 1,054,620 Cardinality: 13,020
    2 INDEX SKIP SCAN INDEX AP.AP_INVOICE_DISTRIBUTIONS_N31 Cost: 796 Cardinality: 140,348
    6 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_INVOICES_ALL Cost: 2 Bytes: 67 Cardinality: 1
    5 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_INVOICES_U1 Cost: 1 Cardinality: 1
    8 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIERS_U1 Cost: 1 Cardinality: 1
    10 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 2 Bytes: 41 Cardinality: 1
    12 INDEX FULL SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1,252 Bytes: 2,685,490 Cardinality: 537,098
    15 INDEX UNIQUE SCAN INDEX (UNIQUE) KFORM.AP_BILLS Cost: 2 Bytes: 14 Cardinality: 1


    PLAN AFTER UPGRADE
    ----------------------------------------------

    Plan
    SELECT STATEMENT ALL_ROWSCost: 78,428 Bytes: 326,098 Cardinality: 1,583
    16 FILTER
    14 FILTER
    13 HASH JOIN Cost: 76,844 Bytes: 326,098 Cardinality: 1,583
    11 NESTED LOOPS
    9 NESTED LOOPS Cost: 75,540 Bytes: 318,183 Cardinality: 1,583
    7 NESTED LOOPS Cost: 72,372 Bytes: 253,280 Cardinality: 1,583
    4 NESTED LOOPS Cost: 69,203 Bytes: 147,219 Cardinality: 1,583
    1 TABLE ACCESS FULL TABLE GL.GL_CODE_COMBINATIONS Cost: 607 Bytes: 17,988 Cardinality: 1,499
    3 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_INVOICE_DISTRIBUTIONS_ALL Cost: 85 Bytes: 81 Cardinality: 1
    2 INDEX RANGE SCAN INDEX AP.AP_INVOICE_DISTRIBUTIONS_N3 Cost: 2 Cardinality: 259
    6 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_INVOICES_ALL Cost: 2 Bytes: 67 Cardinality: 1
    5 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_INVOICES_U1 Cost: 1 Cardinality: 1
    8 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIERS_U1 Cost: 1 Cardinality: 1
    10 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 2 Bytes: 41 Cardinality: 1
    12 INDEX FULL SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1,298 Bytes: 2,782,340 Cardinality: 556,468
    15 INDEX UNIQUE SCAN INDEX (UNIQUE) KFORM.AP_BILLS Cost: 2 Bytes: 14 Cardinality: 1


    Please help.

    Thanks in advance.

    - Sam
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    HASH_JOIN below was converted to nested loops...

    HASH JOIN Cost: 14,544 Bytes: 142,476 Cardinality: 1,532

    ...be sure you gather fresh stats on all tables and indexes, also be sure you have gathered SYSTEM stats after upgrade.

    If in doubt, first backup statistics just in case you have to go back.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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