Payment Process Request Program is SLOW, Sql#1

Poor performing Sql #1

UPDATE AP_SELECTED_INVOICES_ALL ASI
SET EXCLUSIVE_PAYMENT_FLAG                               = 'N'
WHERE ASI.CHECKRUN_ID                                    = :B1
AND AP_PAYMENT_UTIL_PKG.IS_FEDERAL_INSTALLED(ASI.ORG_ID) = 'N'
AND EXISTS
  (SELECT NULL
  FROM AP_SELECTED_INVOICES_ALL ASI2
  WHERE ASI2.ORIGINAL_INVOICE_ID IS NOT NULL
  AND ASI2.ORIGINAL_INVOICE_ID    = ASI.INVOICE_ID
  AND ASI2.OK_TO_PAY_FLAG         = 'Y'
  AND ASI2.CHECKRUN_ID            = :B1
  ) ; 

This sql was using following plan

Plan hash value: 3052128332
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                          |       |       |     7 (100)|          |
|   1 |  UPDATE                       | AP_SELECTED_INVOICES_ALL |       |       |            |          |
|   2 |   NESTED LOOPS SEMI           |                          |     1 |    32 |     7   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL |     1 |    16 |     4   (0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN          | AP_SELECTED_INVOICES_N7  |     1 |       |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL |     1 |    16 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | AP_SELECTED_INVOICES_N7  |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("AP_PAYMENT_UTIL_PKG"."IS_FEDERAL_INSTALLED"("ASI"."ORG_ID")='N')
   4 - access("ASI"."CHECKRUN_ID"=TO_NUMBER(:B1))
   5 - filter(("ASI2"."ORIGINAL_INVOICE_ID" IS NOT NULL AND 
              "ASI"."INVOICE_ID"=TO_NUMBER("ASI2"."ORIGINAL_INVOICE_ID")))
   6 - access("ASI2"."CHECKRUN_ID"=TO_NUMBER(:B1) AND "ASI2"."OK_TO_PAY_FLAG"='Y')

Q: why is this sql bad?
A: in the sub query, it is using index on checkrun_id column of ap_selected_invoices_all and then applying filters. in our cases, each checkrun_id has thousands of invoices. because of nested loop, it is like Cartesian join. i think in this case HASH SEMI JOIN would be better. But it would have been better if Oracle development has realized that ORIGINAL_INVOICE_ID column is VARCHAR2 column, so instead of using ASI2.ORIGINAL_INVOICE_ID = ASI.INVOICE_ID, used ASI2.ORIGINAL_INVOICE_ID = TO_CHAR(ASI.INVOICE_ID), so that index AP_SELECTED_INVOICES_N4 could be used and i think that would have been best. The modified query would look like

in other test environment, i found this plan for the query and it worked well.

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                |                          |       |       |     5 (100)|          |
|   1 |  UPDATE                         | AP_SELECTED_INVOICES_ALL |       |       |            |          |
|   2 |   NESTED LOOPS                  |                          |       |       |            |          |
|   3 |    NESTED LOOPS                 |                          |     1 |    40 |     5  (20)| 00:00:01 |
|   4 |     SORT UNIQUE                 |                          |     1 |    24 |     1   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL |     1 |    24 |     1   (0)| 00:00:01 |

|*  6 |       INDEX FULL SCAN           | AP_SELECTED_INVOICES_N4  |     1 |       |     1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN            | AP_SELECTED_INVOICES_N1  |     1 |       |     2   (0)| 00:00:01 |
|*  8 |    TABLE ACCESS BY INDEX ROWID  | AP_SELECTED_INVOICES_ALL |     1 |    16 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter(("ASI2"."CHECKRUN_ID"=1 AND "ASI2"."OK_TO_PAY_FLAG"='Y'))
   6 - filter("ASI2"."ORIGINAL_INVOICE_ID" IS NOT NULL)
   7 - access("ASI"."INVOICE_ID"=TO_NUMBER("ASI2"."ORIGINAL_INVOICE_ID"))
   8 - filter(("ASI"."CHECKRUN_ID"=1 AND "AP_PAYMENT_UTIL_PKG"."IS_FEDERAL_INSTALLED"("ASI"."ORG_ID"
              )='N'))