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