Poor Performing Sql#4
(This sql was formatted for readability)
SELECT SYSDATE
, :B4
, IBY_AMOUNT_PAID
, IBY_DISCOUNT_AMOUNT_TAKEN
, AP_INVOICES_UTILITY_PKG.GET_PAYMENT_STATUS(INV.INVOICE_ID)
, INV.INVOICE_ID
FROM AP_INVOICES_ALL INV
, AP_SELECTED_INVOICES_ALL SI
, (SELECT
SUM(IBYDOCS.PAYMENT_AMOUNT) IBY_AMOUNT_PAID
, NVL(SUM(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN),0) IBY_DISCOUNT_AMOUNT_TAKEN
, IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 REF1
, IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 REF2
, IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 REF3
FROM IBY_FD_DOCS_PAYABLE_V IBYDOCS
, IBY_FD_PAYMENTS_V IBYPMTS
WHERE IBYPMTS.ORG_TYPE = 'OPERATING_UNIT'
AND IBYPMTS.PAYMENT_ID = IBYDOCS.PAYMENT_ID
AND IBYPMTS.COMPLETED_PMTS_GROUP_ID = :B2
AND IBYPMTS.ORG_ID = :B1
GROUP BY IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1
, IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2
, IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3
) IBYDPM
WHERE INV.INVOICE_ID = SI.INVOICE_ID
AND SI.CHECKRUN_NAME = :B3
AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'INTEREST'
AND IBYDPM.REF2 = TO_CHAR(INV.INVOICE_ID)
AND IBYDPM.REF1 = TO_CHAR(SI.CHECKRUN_ID)
AND IBYDPM.REF2 = TO_CHAR(SI.INVOICE_ID)
AND IBYDPM.REF3 = TO_CHAR(SI.PAYMENT_NUM);
This sql had following plan at our sitePLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SQL_b4d6909efd6427e2
SQL text: SELECT SYSDATE, :B4 , IBY_AMOUNT_PAID, IBY_DISCOUNT_AMOUNT_TAKEN,
AP_INVOICES_UTILITY_PKG.GET_PAYMENT_STATUS(INV.INVOICE_ID),
INV.INVOICE_ID FROM AP_INVOICES_ALL INV, AP_SELECTED_INVOICES_ALL SI,
(SELECT SUM(IBYDOCS.PAYMENT_AMOUNT) IBY_AMOUNT_PAID,
NVL(SUM(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN),0)
IBY_DISCOUNT_AMOUNT_TAKEN, IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 REF1,
IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 REF2,
IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 REF3 FROM IBY_FD_DOCS_PAYABLE_V
IBYDOCS, IBY_FD_PAYMENTS_V IBYPMTS WHERE IBYPMTS.ORG_TYPE =
'OPERATING_UNIT' AND IBYPMTS.PAYMENT_ID = IBYDOCS.PAYMENT_ID AND
IBYPMTS.COMPLETED_PMTS_GROUP_ID = :B2 AND IBYPMTS.ORG_ID = :B1 GROUP BY
IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1,
IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2,
IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3) IBYDPM WHERE INV.INVOICE_ID =
SI.INVOICE_ID AND SI.CHECKRUN_NAME = :B3 AND
INV.INVOICE_TYPE_LOOKUP_CODE <> 'INTEREST' AND IBYDPM.REF2 =
TO_CHAR(INV.INVOICE_ID) AND IBYDPM.REF1 = TO_CHAR(SI.CHECKRUN_ID) AND
IBYDPM.REF2 = TO_CHAR(SI.INVOICE_ID) AND IBYDPM.REF3 =
TO_CHAR(SI.PAYMENT_NUM)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_b9pnhmvyq89z252415c2f Plan id: 1380015151
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 1132729352
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 148 | 390M (1)|999:59:59 |
| 1 | HASH GROUP BY | | 1 | 148 | 390M (1)|999:59:59 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 148 | 390M (1)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 86M| 9762M| 14M (1)| 46:44:50 |
| 5 | NESTED LOOPS | | | | | |
| 6 | NESTED LOOPS | | 58310 | 5238K| 124K (1)| 00:24:50 |
| 7 | TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL | 58310 | 3188K| 7461 (1)| 00:01:30 |
|* 8 | INDEX RANGE SCAN | AP_SELECTED_INVOICES_N5 | 58310 | | 473 (1)| 00:00:06 |
|* 9 | INDEX UNIQUE SCAN | AP_INVOICES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | AP_INVOICES_ALL | 1 | 36 | 2 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 1488 | 38688 | 14M (1)| 46:44:50 |
| 12 | TABLE ACCESS BY INDEX ROWID | IBY_PAYMENTS_ALL | 1488 | 38688 | 238 (0)| 00:00:03 |
|* 13 | INDEX RANGE SCAN | IBY_PAYMENTS_ALL_N10 | 1488 | | 14 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IBY_DOCS_PAYABLE_ALL_N6 | 50 | | 2 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | IBY_DOCS_PAYABLE_ALL | 1 | 30 | 21 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("SI"."CHECKRUN_NAME"=:B3)
9 - access("INV"."INVOICE_ID"="SI"."INVOICE_ID")
10 - filter("INV"."INVOICE_TYPE_LOOKUP_CODE"<>'INTEREST')
13 - access("IBY_PAY"."COMPLETED_PMTS_GROUP_ID"=TO_NUMBER(:B2) AND
"IBY_PAY"."ORG_ID"=TO_NUMBER(:B1) AND "IBY_PAY"."ORG_TYPE"='OPERATING_UNIT')
14 - access("IBY_PAY"."PAYMENT_ID"="IBY_DOCS"."PAYMENT_ID")
15 - filter("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"=TO_CHAR("INVOICE_ID") AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1"=TO_CHAR("SI"."CHECKRUN_ID") AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"=TO_CHAR("INVOICE_ID") AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR("SI"."PAYMENT_NUM"))
Q:Why this plan is bad?
A:This is using Merge Join Cartesian and hash group by. For some reason Hash group by do not perform well at my site. This query too is not written well. Following is completely unnecessary.
- Inner query has group by on IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1, IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2, IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3. CALLING_APP_DOC_UNIQUE_REF2 and CALLING_APP_DOC_UNIQUE_REF3 are invoice id and payment number. This is unique key to ap_payment_schedules_all table and one checkrun can not select one invoice payment schedule more than once, so group by will not do anything except return single row.
- Why is the condition "AND IBYDPM.REF2 = TO_CHAR(INV.INVOICE_ID)" needed? Such unnecessary joins confuse Oracle Optimizer.
In order to get better plan i added following hint to inline view query
/*+ use_nl (ibypmt ibydocs)*/
I added following hint to main query
/*+ leading (ibydpm) use_nl (ibydpm si) index ( si ap_selected_invoices_n5) use_no_hash */
then we did 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.
This is the plan we got
Plan hash value: 3309865005
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 148 | 228K (1)| 00:45:48 |
| 1 | HASH GROUP BY | | 1 | 148 | 228K (1)| 00:45:48 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 148 | 228K (1)| 00:45:48 |
| 4 | NESTED LOOPS | | 229 | 25648 | 228K (1)| 00:45:43 |
| 5 | NESTED LOOPS | | 73175 | 4001K| 6699 (1)| 00:01:21 |
| 6 | TABLE ACCESS BY INDEX ROWID| IBY_PAYMENTS_ALL | 1488 | 38688 | 239 (0)| 00:00:03 |
|* 7 | INDEX RANGE SCAN | IBY_PAYMENTS_ALL_N10 | 1488 | | 15 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| IBY_DOCS_PAYABLE_ALL | 49 | 1470 | 21 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IBY_DOCS_PAYABLE_ALL_N6 | 50 | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | AP_SELECTED_INVOICES_ALL | 1 | 56 | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | AP_SELECTED_INVOICES_N5 | 1 | | 3 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | AP_INVOICES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | AP_INVOICES_ALL | 1 | 36 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("IBY_PAY"."COMPLETED_PMTS_GROUP_ID"=TO_NUMBER(:B2) AND
"IBY_PAY"."ORG_ID"=TO_NUMBER(:B1) AND "IBY_PAY"."ORG_TYPE"='OPERATING_UNIT')
9 - access("IBY_PAY"."PAYMENT_ID"="IBY_DOCS"."PAYMENT_ID")
10 - filter("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1"=TO_CHAR("SI"."CHECKRUN_ID") AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR("SI"."PAYMENT_NUM"))
11 - access("SI"."CHECKRUN_NAME"=:B3 AND "IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"=TO_CHAR("INVOICE_
ID"))
12 - access("INV"."INVOICE_ID"="SI"."INVOICE_ID")
filter("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"=TO_CHAR("INVOICE_ID"))
13 - filter("INV"."INVOICE_TYPE_LOOKUP_CODE"<>'INTEREST')
Posts
No comments:
Post a Comment