Poor Performing Sql #2
(This sql was formatted for readability)
SELECT SYSDATE , :B4 , (SI.AMOUNT_REMAINING - IBYDOCS.PAYMENT_AMOUNT - NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN,0)) , 0 , DECODE(SI.AMOUNT_REMAINING - IBYDOCS.PAYMENT_AMOUNT - NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN,0), 0, 'Y', 'P') , SI.WITHHOLDING_AMOUNT , NULL , PS.INVOICE_ID , PS.PAYMENT_NUM FROM AP_PAYMENT_SCHEDULES_ALL PS , AP_INVOICES_ALL INV , AP_SELECTED_INVOICES_ALL SI , IBY_FD_DOCS_PAYABLE_V IBYDOCS WHERE SI.CHECKRUN_NAME = :B3 AND SI.PAYMENT_NUM = PS.PAYMENT_NUM AND SI.INVOICE_ID = PS.INVOICE_ID AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 = TO_CHAR(SI.CHECKRUN_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 = TO_CHAR(SI.INVOICE_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 = TO_CHAR(SI.PAYMENT_NUM) AND IBYDOCS.COMPLETED_PMTS_GROUP_ID = :B2 AND IBYDOCS.ORG_ID = :B1 AND IBYDOCS.ORG_TYPE = 'OPERATING_UNIT' AND INV.INVOICE_ID = SI.INVOICE_ID AND INV.INVOICE_ID = PS.INVOICE_ID AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'INTEREST'
This sql had following plan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5tru8vxmktswq, child number 0
-------------------------------------
SELECT SYSDATE, :B4 , (SI.AMOUNT_REMAINING - IBYDOCS.PAYMENT_AMOUNT -
NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN,0)), 0,
DECODE(SI.AMOUNT_REMAINING - IBYDOCS.PAYMENT_AMOUNT -
NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN,0), 0, 'Y', 'P'),
SI.WITHHOLDING_AMOUNT, NULL, PS.INVOICE_ID, PS.PAYMENT_NUM FROM
AP_PAYMENT_SCHEDULES_ALL PS, AP_INVOICES_ALL INV,
AP_SELECTED_INVOICES_ALL SI, IBY_FD_DOCS_PAYABLE_V IBYDOCS WHERE
SI.CHECKRUN_NAME = :B3 AND SI.PAYMENT_NUM = PS.PAYMENT_NUM AND
SI.INVOICE_ID = PS.INVOICE_ID AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 =
TO_CHAR(SI.CHECKRUN_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 =
TO_CHAR(SI.INVOICE_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 =
TO_CHAR(SI.PAYMENT_NUM) AND IBYDOCS.COMPLETED_PMTS_GROUP_ID = :B2 AND
IBYDOCS.ORG_ID = :B1 AND IBYDOCS.ORG_TYPE = 'OPERATING_UNIT' AND
INV.INVOICE_ID = SI.INVOICE_ID AND INV.INVOICE_ID = PS.INVOICE_ID AND
INV.INVOICE_TYPE_LOOKUP_CODE <> 'INTEREST'
Plan hash value: 1689057269
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 406 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 131 | 406 (1)| 00:00:05 |
| 3 | NESTED LOOPS | | 1 | 85 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 70 | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL | 1 | 61 | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | AP_SELECTED_INVOICES_N5 | 1 | | 4 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | AP_PAYMENT_SCHEDULES_U1 | 1 | 9 | 1 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | AP_INVOICES_ALL | 1 | 15 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | AP_INVOICES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IBY_DOCS_PAYABLE_ALL_N16 | 6852 | | 15 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | IBY_DOCS_PAYABLE_ALL | 1 | 46 | 398 (1)| 00:00:05 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("SI"."CHECKRUN_NAME"=:B3)
7 - access("SI"."INVOICE_ID"="PS"."INVOICE_ID" AND "SI"."PAYMENT_NUM"="PS"."PAYMENT_NUM")
8 - filter("INV"."INVOICE_TYPE_LOOKUP_CODE"<>'INTEREST')
9 - access("INV"."INVOICE_ID"="PS"."INVOICE_ID")
filter("INV"."INVOICE_ID"="SI"."INVOICE_ID")
10 - access("IBY_DOCS"."COMPLETED_PMTS_GROUP_ID"=:B2)
11 - filter(("IBY_DOCS"."ORG_ID"=:B1 AND "IBY_DOCS"."ORG_TYPE"='OPERATING_UNIT' AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1"=TO_CHAR("SI"."CHECKRUN_ID") AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"="SI"."SYS_NC00115$" AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR("SI"."PAYMENT_NUM")))
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.
now do the following
Add hint to the poor performing sql and execute from sqlplus
SELECT /*+ leading (si) use_nl (si inv ps ibydocs) index_ss(ibydocs iby_docs_payable_all_n13) */ SYSDATE , :B4 , (SI.AMOUNT_REMAINING - IBYDOCS.PAYMENT_AMOUNT - NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN,0)) , 0 , DECODE(SI.AMOUNT_REMAINING - IBYDOCS.PAYMENT_AMOUNT - NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN,0), 0, 'Y', 'P') , SI.WITHHOLDING_AMOUNT , NULL , PS.INVOICE_ID , PS.PAYMENT_NUM FROM AP_PAYMENT_SCHEDULES_ALL PS , AP_INVOICES_ALL INV , AP_SELECTED_INVOICES_ALL SI , IBY_FD_DOCS_PAYABLE_V IBYDOCS WHERE SI.CHECKRUN_NAME = :B3 AND SI.PAYMENT_NUM = PS.PAYMENT_NUM AND SI.INVOICE_ID = PS.INVOICE_ID AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 = TO_CHAR(SI.CHECKRUN_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 = TO_CHAR(SI.INVOICE_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 = TO_CHAR(SI.PAYMENT_NUM) AND IBYDOCS.COMPLETED_PMTS_GROUP_ID = :B2 AND IBYDOCS.ORG_ID = :B1 AND IBYDOCS.ORG_TYPE = 'OPERATING_UNIT' AND INV.INVOICE_ID = SI.INVOICE_ID AND INV.INVOICE_ID = PS.INVOICE_ID AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'INTEREST'
i created this scrip to save bad plan, disable bad plan and then save better plan for original query.
declare
l_ret_value NUMBER;
l_clob CLOB;
l_step NUMBER;
l_sql_id VARCHAR2(80);
l_plan_hash_value VARCHAR2(80);
l_sql_handle VARCHAR2(80);
l_plan_name VARCHAR2(80);
begin
l_step := &1 ;
l_sql_id := '&2';
l_plan_hash_value := '&3';
l_sql_handle := '&4';
l_plan_name := '&5';
IF l_step = 1
THEN
l_ret_value := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
( sql_id => l_sql_id
, plan_hash_value => l_plan_hash_value
) ;
ELSIF l_step = 2
THEN
l_ret_value := DBMS_SPM.ALTER_SQL_PLAN_BASELINE
( SQL_HANDLE => l_sql_handle
, PLAN_NAME => l_plan_name
, ATTRIBUTE_NAME => 'enabled'
, ATTRIBUTE_VALUE => 'NO'
) ;
ELSIF l_step = 3
THEN
l_ret_value := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
( sql_id => l_sql_id
, plan_hash_value => null --l_plan_hash_value
, sql_handle => l_sql_handle
) ;
ELSIF l_step = 4
THEN
l_ret_value := DBMS_SPM.DROP_SQL_PLAN_BASELINE
( SQL_HANDLE => 'SQL_1be05243ae2286bc'
, PLAN_NAME => 'SQL_PLAN_1rs2k8fr251pwc6fe2586'
);
ELSE
fnd_file.put_line(fnd_file.log, 'Invalid value for parameter 1 = ' || l_step);
END IF;
fnd_file.put_line(fnd_file.log,'Return Value :' || l_ret_value);
end;
/
- Get sql_id and plan_hash_value for poor performing sql from gv$sql where sql_text like the poor performing sql and call the script with parameters 1,sql_id value and plan_hash_value
- Get sql_handle, plan_name for poor performing sql from dba_sql_plan_baselines and call the script with parameters 2, null, null, sql_handle, plan_name. The query select sql_handle, plan_name, sql_text, created, a.* from dba_sql_plan_baselines a order by a.created desc; could be helpful to get sql handle, plan name.
- Now get the sql_id, plan_hash_value for poor performing sql with hints. Call the script with parameters 3, sql_id, plan_hash_value and sql_handle. Note:Sql_Handle is same for sql with and without hints
Now we have better plan for poor performing sql. Run the "Record Print Status" and verify if better plan is used or not.
the modified sql has following plan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bj3xhtzt05h32, child number 0
-------------------------------------
SELECT /*+ leading (si) use_nl (si inv ps ibydocs) index_ss(ibydocs
iby_docs_payable_all_n13) */SYSDATE, :B4 , (SI.AMOUNT_REMAINING -
IBYDOCS.PAYMENT_AMOUNT - NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN,0)),
0, DECODE(SI.AMOUNT_REMAINING - IBYDOCS.PAYMENT_AMOUNT -
NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN,0), 0, 'Y', 'P'),
SI.WITHHOLDING_AMOUNT, NULL, PS.INVOICE_ID, PS.PAYMENT_NUM FROM
AP_PAYMENT_SCHEDULES_ALL PS, AP_INVOICES_ALL INV,
AP_SELECTED_INVOICES_ALL SI, IBY_FD_DOCS_PAYABLE_V IBYDOCS WHERE
SI.CHECKRUN_NAME = :B3 AND SI.PAYMENT_NUM = PS.PAYMENT_NUM AND
SI.INVOICE_ID = PS.INVOICE_ID AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 =
TO_CHAR(SI.CHECKRUN_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 =
TO_CHAR(SI.INVOICE_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 =
TO_CHAR(SI.PAYMENT_NUM) AND IBYDOCS.COMPLETED_PMTS_GROUP_ID = :B2 AND
IBYDOCS.ORG_ID = :B1 AND IBYDOCS.ORG_TYPE = 'OPERATING_UNIT' AND
INV.INVOICE_ID = SI.INVOICE_ID AND INV.INVOICE_ID = PS.INVOICE_ID AND
INV.INVOICE_TYPE_LOOKUP_CODE <
Plan hash value: 2411719764
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 231K(100)| |
| 1 | NESTED LOOPS | | 1 | 131 | 231K (1)| 00:46:15 |
| 2 | NESTED LOOPS | | 1 | 85 | 231K (1)| 00:46:15 |
| 3 | NESTED LOOPS | | 74325 | 5516K| 156K (1)| 00:31:23 |
| 4 | TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL | 74325 | 4427K| 8112 (1)| 00:01:38 |
|* 5 | INDEX RANGE SCAN | AP_SELECTED_INVOICES_N5 | 74325 | | 659 (1)| 00:00:08 |
|* 6 | TABLE ACCESS BY INDEX ROWID| AP_INVOICES_ALL | 1 | 15 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | AP_INVOICES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | AP_PAYMENT_SCHEDULES_U1 | 1 | 9 | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | IBY_DOCS_PAYABLE_ALL | 1 | 46 | 4 (0)| 00:00:01 |
|* 10 | INDEX SKIP SCAN | IBY_DOCS_PAYABLE_ALL_N13 | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SI"."CHECKRUN_NAME"=:B3)
6 - filter("INV"."INVOICE_TYPE_LOOKUP_CODE"<>'INTEREST')
7 - access("INV"."INVOICE_ID"="SI"."INVOICE_ID")
8 - access("INV"."INVOICE_ID"="PS"."INVOICE_ID" AND "SI"."PAYMENT_NUM"="PS"."PAYMENT_NUM")
filter("SI"."INVOICE_ID"="PS"."INVOICE_ID")
9 - filter(("IBY_DOCS"."COMPLETED_PMTS_GROUP_ID"=TO_NUMBER(:B2) AND
"IBY_DOCS"."ORG_ID"=TO_NUMBER(:B1) AND "IBY_DOCS"."ORG_TYPE"='OPERATING_UNIT'))
10 - access("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1"=TO_CHAR("SI"."CHECKRUN_ID") AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"="SI"."SYS_NC00115$" AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR("SI"."PAYMENT_NUM"))
filter(("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1"=TO_CHAR("SI"."CHECKRUN_ID") AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"="SI"."SYS_NC00115$" AND
"IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR("SI"."PAYMENT_NUM")))
54 rows selected.
Posts
No comments:
Post a Comment