Poor Performing Sql#3
(This sql was formatted for readability)
SELECT NEW.INVOICE_ID P_INT_INVOICE_ID , NEW.DUE_DATE P_ACCOUNTING_DATE , PV.VENDOR_ID P_VENDOR_ID , ORIG.INVOICE_NUM P_OLD_INVOICE_NUM , NEW.INVOICE_NUM P_INT_INVOICE_NUM , NEW.PAYMENT_AMOUNT P_INTEREST_AMOUNT , DECODE(ORIG.INVOICE_CURRENCY_CODE, :B3 , NULL, DECODE(BASE.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND(NEW.PAYMENT_AMOUNT / ORIG.PAYMENT_CROSS_RATE * NVL(NEW.PAYMENT_EXCHANGE_RATE,1), BASE.PRECISION), ROUND( ((NEW.PAYMENT_AMOUNT / ORIG.PAYMENT_CROSS_RATE * NVL(NEW.PAYMENT_EXCHANGE_RATE,1)) / BASE.MINIMUM_ACCOUNTABLE_UNIT) * BASE.MINIMUM_ACCOUNTABLE_UNIT ) ) ) P_INTEREST_BASE_AMOUNT , ORIG.SET_OF_BOOKS_ID P_SET_OF_BOOKS_ID , ORIG.PAYMENT_CROSS_RATE P_PAYMENT_CROSS_RATE , NEW.PAYMENT_EXCHANGE_RATE P_EXCHANGE_RATE , NEW.PAYMENT_EXCHANGE_RATE_TYPE P_EXCHANGE_RATE_TYPE , IBYDOCS.PAYMENT_DATE P_EXCHANGE_DATE , ORIG.INVOICE_ID P_INVOICE_ID , ORIG.INVOICE_CURRENCY_CODE P_INVOICE_CURRENCY_CODE , ORIG.ORG_ID P_ORG_ID FROM PO_VENDORS PV , AP_INVOICES_ALL ORIG , AP_SELECTED_INVOICES_ALL NEW , FND_CURRENCIES BASE , FND_CURRENCIES FCINV , IBY_FD_DOCS_PAYABLE_V IBYDOCS WHERE NEW.ORIGINAL_INVOICE_ID = ORIG.INVOICE_ID AND NEW.VENDOR_ID = PV.VENDOR_ID AND NEW.CHECKRUN_NAME = :B5 AND NEW.CHECKRUN_ID = :B4 AND BASE.CURRENCY_CODE = :B3 AND FCINV.CURRENCY_CODE = ORIG.INVOICE_CURRENCY_CODE AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 = NEW.CHECKRUN_ID AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 = NEW.INVOICE_ID AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 = NEW.PAYMENT_NUM AND IBYDOCS.COMPLETED_PMTS_GROUP_ID = :B2 AND IBYDOCS.ORG_ID = :B1 AND NEW.ORG_ID = :B1
This sql had following plan at our site
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3suxb7k1zzxmr, child number 0
-------------------------------------
SELECT NEW.INVOICE_ID P_INT_INVOICE_ID, NEW.DUE_DATE P_ACCOUNTING_DATE,
PV.VENDOR_ID P_VENDOR_ID, ORIG.INVOICE_NUM P_OLD_INVOICE_NUM,
NEW.INVOICE_NUM P_INT_INVOICE_NUM, NEW.PAYMENT_AMOUNT
P_INTEREST_AMOUNT, DECODE(ORIG.INVOICE_CURRENCY_CODE, :B3 , NULL,
DECODE(BASE.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND(NEW.PAYMENT_AMOUNT /
ORIG.PAYMENT_CROSS_RATE * NVL(NEW.PAYMENT_EXCHANGE_RATE,1),
BASE.PRECISION), ROUND( ((NEW.PAYMENT_AMOUNT / ORIG.PAYMENT_CROSS_RATE
* NVL(NEW.PAYMENT_EXCHANGE_RATE,1)) / BASE.MINIMUM_ACCOUNTABLE_UNIT) *
BASE.MINIMUM_ACCOUNTABLE_UNIT ) ) ) P_INTEREST_BASE_AMOUNT,
ORIG.SET_OF_BOOKS_ID P_SET_OF_BOOKS_ID, ORIG.PAYMENT_CROSS_RATE
P_PAYMENT_CROSS_RATE, NEW.PAYMENT_EXCHANGE_RATE P_EXCHANGE_RATE,
NEW.PAYMENT_EXCHANGE_RATE_TYPE P_EXCHANGE_RATE_TYPE,
IBYDOCS.PAYMENT_DATE P_EXCHANGE_DATE, ORIG.INVOICE_ID P_INVOICE_ID,
ORIG.INVOICE_CURRENCY_CODE P_INVOICE_CURRENCY_CODE, ORIG.ORG_ID
P_ORG_ID FROM PO_VENDORS PV, AP_INVOICES_ALL ORIG,
AP_SELECTED_INVOICES_ALL NEW, FND_CURRENCIES BAS
Plan hash value: 902921929
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 403 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 207 | 403 (1)| 00:00:05 |
| 3 | NESTED LOOPS | | 1 | 176 | 9 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 171 | 9 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 134 | 7 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 129 | 6 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 119 | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| FND_CURRENCIES | 1 | 10 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | FND_CURRENCIES_U1 | 1 | | 0 (0)| |
|* 10 | TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL | 1 | 109 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | AP_SELECTED_INVOICES_N7 | 1 | | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 1 | 10 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | 5 | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | AP_INVOICES_ALL | 1 | 37 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | AP_INVOICES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | FND_CURRENCIES_U1 | 1 | 5 | 0 (0)| |
|* 18 | INDEX RANGE SCAN | IBY_DOCS_PAYABLE_ALL_N16 | 6787 | | 15 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | IBY_DOCS_PAYABLE_ALL | 1 | 31 | 394 (1)| 00:00:05 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("BASE"."CURRENCY_CODE"=:B3)
10 - filter(("NEW"."CHECKRUN_NAME"=:B5 AND "NEW"."ORG_ID"=:B1))
11 - access("NEW"."CHECKRUN_ID"=:B4)
13 - access("NEW"."VENDOR_ID"="PAV"."VENDOR_ID")
14 - access("PAV"."PARTY_ID"="HP"."PARTY_ID")
16 - access("ORIG"."INVOICE_ID"=TO_NUMBER("NEW"."ORIGINAL_INVOICE_ID"))
17 - access("FCINV"."CURRENCY_CODE"="ORIG"."INVOICE_CURRENCY_CODE")
18 - access("IBY_DOCS"."COMPLETED_PMTS_GROUP_ID"=:B2)
19 - filter((TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1")=:B4 AND "IBY_DOCS"."ORG_ID"=:B1 AND
"NEW"."CHECKRUN_ID"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1") AND
"NEW"."INVOICE_ID"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2") AND
"NEW"."PAYMENT_NUM"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3")))
62 rows selected.
Q:Why this plan is bad?A:This plan is using index IBY_DOCS_PAYABLE_ALL_N16 access record in iby_docs_payable_all table. Suppose ( as in my case) ap_selected_invoices_all has 100,000 rows and for each record in ap_selected_invoices_all, it is getting 100000 rows in iby_docs_payable_all table and then applying filter on calling_app_doc_unique_ref column. this does not make any sense. it should us use index IBY_DOCS_PAYABLE_ALL_N13 and get just one record from iby_docs_payable_all table.
I added following hint the sql
/*+ leading (IBYDOCS) use_nl (IBYDOCS new pv orig ) */
Now do the following
- Execute query with hint so that a better plan is available to create it as baseline plan
- Load bad plan the sql
- Disable bad plan for sql
- Load good plan for sql generated by sql with hints
You can see this post for how to perform above steps.
After making these changes, we got following plan
Plan hash value: 3515255252
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 232 | 536 (1)| 00:00:07 |
| 1 | NESTED LOOPS | | 1 | 232 | 536 (1)| 00:00:07 |
| 2 | NESTED LOOPS | | 1 | 227 | 536 (1)| 00:00:07 |
| 3 | NESTED LOOPS | | 1 | 190 | 534 (1)| 00:00:07 |
| 4 | NESTED LOOPS | | 1 | 185 | 533 (1)| 00:00:07 |
| 5 | NESTED LOOPS | | 1 | 175 | 531 (1)| 00:00:07 |
| 6 | NESTED LOOPS | | 25 | 1050 | 456 (1)| 00:00:06 |
| 7 | TABLE ACCESS BY INDEX ROWID| FND_CURRENCIES | 1 | 10 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | FND_CURRENCIES_U1 | 1 | | 0 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| IBY_DOCS_PAYABLE_ALL | 25 | 800 | 455 (1)| 00:00:06 |
|* 10 | INDEX RANGE SCAN | IBY_DOCS_PAYABLE_ALL_N16 | 7867 | | 19 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | AP_SELECTED_INVOICES_ALL | 1 | 133 | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | AP_SELECTED_INVOICES_N1 | 1 | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 1 | 10 | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | 5 | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | AP_INVOICES_ALL | 1 | 37 | 2 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | AP_INVOICES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | FND_CURRENCIES_U1 | 1 | 5 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("BASE"."CURRENCY_CODE"=:B3)
9 - filter(TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1")=TO_NUMBER(:B4) AND
"IBY_DOCS"."ORG_ID"=TO_NUMBER(:B1))
10 - access("IBY_DOCS"."COMPLETED_PMTS_GROUP_ID"=TO_NUMBER(:B2))
11 - filter("NEW"."CHECKRUN_NAME"=:B5 AND "NEW"."CHECKRUN_ID"=TO_NUMBER(:B4) AND
"NEW"."ORG_ID"=TO_NUMBER(:B1) AND "NEW"."CHECKRUN_ID"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF
1"))
12 - access("NEW"."INVOICE_ID"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2") AND
"NEW"."PAYMENT_NUM"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"))
14 - access("NEW"."VENDOR_ID"="PAV"."VENDOR_ID")
15 - access("PAV"."PARTY_ID"="HP"."PARTY_ID")
17 - access("ORIG"."INVOICE_ID"=TO_NUMBER("NEW"."ORIGINAL_INVOICE_ID"))
18 - access("FCINV"."CURRENCY_CODE"="ORIG"."INVOICE_CURRENCY_CODE")
Posts
No comments:
Post a Comment