Tuesday, April 10, 2012

Record Print Status Too Slow, Sql#2

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;
/


  1. 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
  2. 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.
  3. 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
there is no need to run step 4 because if we drop the bad plan, oracle will add it again for further review.

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.

No comments:

Post a Comment