Record Print Status Too Slow, Sql#5

Poor Performing Sql#5

(This query is formatted for readability)

UPDATE AP_SELECTED_INVOICES_ALL ASI
SET ASI.INVOICE_PAYMENT_ID                               = AP_INVOICE_PAYMENTS_S.NEXTVAL
WHERE ASI.CHECKRUN_ID                                    = :B1
AND (TO_CHAR(ASI.INVOICE_ID), TO_CHAR(ASI.PAYMENT_NUM)) IN
  (SELECT CALLING_APP_DOC_UNIQUE_REF2
  , CALLING_APP_DOC_UNIQUE_REF3
  FROM IBY_FD_DOCS_PAYABLE_V IBYDOCS
  , IBY_FD_PAYMENTS_V IBYPMTS
  WHERE IBYDOCS.PAYMENT_ID            = IBYPMTS.PAYMENT_ID
  AND IBYPMTS.ORG_TYPE                = 'OPERATING_UNIT'
  AND IBYPMTS.COMPLETED_PMTS_GROUP_ID = :B3
  AND IBYPMTS.ORG_ID                  = :B2
  )
;

This query has following plan

Plan hash value: 3252324178
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                 |                          |       |       |   277 (100)|          |
|   1 |  UPDATE                          | AP_SELECTED_INVOICES_ALL |       |       |            |          |
|   2 |   SEQUENCE                       | AP_INVOICE_PAYMENTS_S    |       |       |            |          |
|*  3 |    FILTER                        |                          |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID  | AP_SELECTED_INVOICES_ALL |     1 |    36 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN            | AP_SELECTED_INVOICES_N7  |     1 |       |     3   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                 |                          |       |       |            |          |
|   7 |      NESTED LOOPS                |                          |     2 |    84 |   273   (0)| 00:00:04 |
|   8 |       TABLE ACCESS BY INDEX ROWID| IBY_DOCS_PAYABLE_ALL     |     2 |    32 |   269   (0)| 00:00:04 |
|*  9 |        INDEX SKIP SCAN           | IBY_DOCS_PAYABLE_ALL_N13 |     2 |       |   268   (0)| 00:00:04 |
|* 10 |       INDEX UNIQUE SCAN          | IBY_PAYMENTS_ALL_U1      |     1 |       |     1   (0)| 00:00:01 |
|* 11 |      TABLE ACCESS BY INDEX ROWID | IBY_PAYMENTS_ALL         |     1 |    26 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter( IS NOT NULL)
   5 - access("SYS_ALIAS_2"."CHECKRUN_ID"=:B1)
   9 - access("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"=:B1 AND 
              "IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR(:B2))
       filter(("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"=:B1 AND 
              "IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR(:B2)))
  10 - access("IBY_DOCS"."PAYMENT_ID"="IBY_PAY"."PAYMENT_ID")
  11 - filter(("IBY_PAY"."COMPLETED_PMTS_GROUP_ID"=:B3 AND "IBY_PAY"."ORG_TYPE"='OPERATING_UNIT' AND 
              "IBY_PAY"."ORG_ID"=:B2))
 

Q: Why this query is bad:

A:

1. "IN" clause is not right for this query. "NOT EXISTS" should have been used in this query
2. This query should use CALLING_APP_ID and CALLING_APP_DOC_UNIQUE_REF1 in where clasue for iby_docs_payable_all so that index iby_docs_payable_n13 can be used and this index returns only one record for corresponding record in ap_selected_invoices_all and Oracle optimizer does not has to use SKIP SCAN index on IBY_DOCS_PAYABLE_ALL_N13. i am sure that skip scan index on IBY_DOCS_PAYABLE_ALL_N13 will not work in production in longer term because we create lot of payment batches and "CALLING_APP_DOC_UNIQUE_REF1 is checkrun_id. Everyday we create four payment batches. in year there will be 800 of them. So skip index scan can not perform. I am not sure what will be plan at that time. i guess at that time, accessing iby_docs_payable_all on completed_pmts_group_id will be better.

Solution
At present i can not create baseline sql plan for this query. i am begging Oracle Development to look into this query and do the right thing.

I added following hint to eh sub query

/*+ leading (ibypmts) index(ibypmts.iby_pay iby_payments_all_n10) use_nl(ibypmts.iby_pay ibydocs.iby_docs) index(ibydocs.iby_docs iby_docs_payable_all_n6) */

the modified query looks like

UPDATE AP_SELECTED_INVOICES_ALL ASI
SET ASI.INVOICE_PAYMENT_ID                               = AP_INVOICE_PAYMENTS_S.NEXTVAL
WHERE ASI.CHECKRUN_ID                                    = &B1
AND (TO_CHAR(ASI.INVOICE_ID), TO_CHAR(ASI.PAYMENT_NUM)) IN
  (SELECT /*+  leading (ibypmts) index(ibypmts.iby_pay iby_payments_all_n10) use_nl(ibypmts.iby_pay ibydocs.iby_docs) index(ibydocs.iby_docs iby_docs_payable_all_n6) */CALLING_APP_DOC_UNIQUE_REF2
  , CALLING_APP_DOC_UNIQUE_REF3
  FROM IBY_FD_DOCS_PAYABLE_V IBYDOCS
  , IBY_FD_PAYMENTS_V IBYPMTS
  WHERE IBYDOCS.PAYMENT_ID            = IBYPMTS.PAYMENT_ID
  AND IBYPMTS.ORG_TYPE                = 'OPERATING_UNIT'
  AND IBYPMTS.COMPLETED_PMTS_GROUP_ID = &B3
  AND IBYPMTS.ORG_ID                  = &B2
  )
;

Note: I have replace :B(n) with &B(n) so that i can run this query from sql developer./

Now do the following

1. Execute query with hint so that a better plan is available to create it as baseline plan
2. Load bad plan the sql
3. Disable bad plan for sql
4. 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: 3856305428
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                  |                          | 58310 |  7972K|       | 10552   (1)| 00:02:07 |
|   1 |  UPDATE                           | AP_SELECTED_INVOICES_ALL |       |       |       |            |          |
|   2 |   SEQUENCE                        | AP_INVOICE_PAYMENTS_S    |       |       |       |            |          |
|*  3 |    HASH JOIN SEMI                 |                          | 58310 |  7972K|  2736K| 10552   (1)| 00:02:07 |
|   4 |     TABLE ACCESS BY INDEX ROWID   | AP_SELECTED_INVOICES_ALL | 58310 |  2049K|       |  3237   (1)| 00:00:39 |
|*  5 |      INDEX RANGE SCAN             | AP_SELECTED_INVOICES_N7  | 58605 |       |       |   211   (1)| 00:00:03 |
|   6 |     VIEW                          | VW_NSO_1                 | 73175 |  7431K|       |  6699   (1)| 00:01:21 |
|   7 |      NESTED LOOPS                 |                          |       |       |       |            |          |
|   8 |       NESTED LOOPS                |                          | 73175 |  3001K|       |  6699   (1)| 00:01:21 |
|   9 |        TABLE ACCESS BY INDEX ROWID| IBY_PAYMENTS_ALL         |  1488 | 38688 |       |   239   (0)| 00:00:03 |
|* 10 |         INDEX RANGE SCAN          | IBY_PAYMENTS_ALL_N10     |  1488 |       |       |    15   (0)| 00:00:01 |
|* 11 |        INDEX RANGE SCAN           | IBY_DOCS_PAYABLE_ALL_N6  |    50 |       |       |     2   (0)| 00:00:01 |
|  12 |       TABLE ACCESS BY INDEX ROWID | IBY_DOCS_PAYABLE_ALL     |    49 |   784 |       |    21   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access(TO_CHAR("INVOICE_ID")="CALLING_APP_DOC_UNIQUE_REF2" AND 
              "CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR("ASI"."PAYMENT_NUM"))
   5 - access("ASI"."CHECKRUN_ID"=TO_NUMBER(:B1))
  10 - access("IBY_PAY"."COMPLETED_PMTS_GROUP_ID"=TO_NUMBER(:B3) AND "IBY_PAY"."ORG_ID"=TO_NUMBER(:B2) AND 
              "IBY_PAY"."ORG_TYPE"='OPERATING_UNIT')