-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|