Changing Oracle Application Users Password

At my client site, we needed to change password of all testers, functional and technical folks. Although we used Oracle application System Administrator responsibility and changed each users password, i think this script could have worked well

--set serveroutput on size 100000 DECLARE i number; TYPE t_tbl is TABLE of varchar2(80) ; l_users t_tbl := t_tbl('GX3ABC', 'X0ABCD'); BEGIN FOR i in l_users.FIRST..l_users.LAST LOOP dbms_output.put_line('changing Password for user: ' || l_users(i)); fnd_user_pkg.updateuser ( x_user_name => l_users(i) , x_owner => 'USER-ABC' -- This should be user-id of person who is running the script , x_unencrypted_password => 'Welocme2LaLaLand', , x_password_date => SYSDATE - 1 ) ; END LOOP; commit; END;

Date Format RRRR-MM-DD HH24:MI:SS

One of my colleague used following condition in a query

AND trunc(trx.printing_original_date) between trunc(to_date('25-APR-2012', 'RRRR-MM-DD HH24:MI:SS')) and trunc(to_date('09-MAY-2012', 'RRRR-MM-DD HH24:MI:SS'))

The query did not show any result. i looked into the query and thought date format is completely incorrect. then i thought why no error? so i ran this query and got the answer.

select to_date('30-APR-2012', 'RRRR-MM-DD HH24:MI:SS') from dual

The above query gave 20th Apr 2030

DBA Tips for Developers

1. Sql Plam Management
   
   
   1. Baseline Plan of a SQL Query
   
   
   2. SQL Plan Baseline Example
   
   
   3. SQL Plan Baselines: Migration from one Instance to Another
   
   
2. Log Messages Missing from Concurrent Program Log File


3. Sql statements executed by a Concurrent Request


4. Create Database Link


5. Runaway Sql Sessions


6. Query Being executed by Concurrent Program


7. ORA-01031: insufficient privileges


8. Composite Index with NULL Columns


9. Session(s) locking a pl/sql package


10. Seesion Information of a Concurrent Request


11. Trace File Size


12. Metalink Notes/White Papers and other links
    
    
    1. SQL Plan Management in Oracle Database 11g
    
    
    2. Sql Plan Management - By Arun Nanda
    
    
    3. ORA-01555: Snapshot too old - Error in Oracle. This link offers a good explanation.
    
    
    4. Semi-Join and Anti-Joins : This is a good article on the subject
    
    
    5. Oracle Database 10g Release 1 (10.1) New and Updated Features for Performance Tuning
    
    

Oracle SQL Function LNNVL

This is a very good function. Here is the oracle documentation for this function. This function can be used for these purposes too

1. You want to confuse others
2. You want others not to touch your code

Elaborating the example given in the above documentation, the query

SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);

is equivalent to

SELECT COUNT(*) FROM employees WHERE commission_pct < .2 or commission_pct is NULL;

I think this function can be used wherever we need to do (:bind_var is NULL or :bind_val <condition> <value>).

if we replace the above condition with LNNVL function then we have to make sure that condition in LNNVL is exact opposite.

Some example of this function are:(to be added later)

Condition WITHOUT LNNVL	Condition WITH LNNVL

SQL Plan Baselines: Migration from one Instance to Another.

1. Create staging table. it needs to be created only once. Script to create staging table is
   
   DECLARE i number; BEGIN DBMS_SPM.create_stgtab_baseline ( table_name => 'XXFND_SQL_PLAN_BASELINES' , table_owner => 'XXFND' ) ; END;
   
   
2. Transfer baselines to staging table. Script to transfer sql plan baselines to staging table is
   
   DECLARE l_ret_value NMBER; BEGIN l_ret_value := DBMS_SPM.pack_stgtab_baseline ( table_name => 'XXFND_SQL_PLAN_BASELINES' , table_owner => 'XXFND' , enabled => 'YES' , accepted => 'YES' ) ; END;
   
   
3. Export staging table


4. Copy file to desired instance and then import it


5. Load sql plan baselines from staging table. Script to load sql plan baselines is
   
   declare i number ; begin i := dbms_spm.unpack_stgtab_baseline ( table_name => 'xxfnd_sql_plan_baselines' , table_owner => 'XXFND' ) ; dbms_output.put_line('Plans Loaded : ' || i); end;
   
   


6. Record from Staging table can deleted so that only new sql plan baselines can be migrated or you may unpack only selected sql plan baselines using function dbms_spm.unpack_stgtab_baseline.

Accounts Payable : Table of Content

1. Accounts Payable queries
   
   
   1. Invoices Eligible for Payments
   2. Supplier Bank Account Details
   3. Supplier's Payment Methods
   4. Payment Batch Status
   5. Payment Batch Details
      


2. Performance Issue with Accounts Payable
   
   
   1. Payment Process Request Program is SLOW
      


3. Example Scripts
   
   
   1. Cancel Accounts Payable Invoice Example
   2. Release Hold on Accounts Payable Invoices


4. FND_FLEX_EXT.get_ccid Example


5. Creating Banks in Accounts Payables


6. Creating Bank Branch in Accounts Payables


7. Accounts Payable Lookup Codes


8. Account Payable Metallink Notes/White Papers
   
   
   1. R12 Payment Process Request - Functional and Technical Information [ID 821133.1]
   
   

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'))
 

Payment Process Request Program is SLOW

At my client site, this is another program that found to be slow. List of sql causing performance bottleneck are listed below.

1. Bad Sql#1

How to Debug : Table of Content

1. Explain Plan Using DBMS_XPLAN


2. Usage of Autonomous Transactions


3. Examine Oracle Forms Block.Column Values


4. Common Tracing Techniques within the Oracle Applications 11i/Rel 12


5. Trace File Location For a Concurrent Request


6. Trace File Location for pl/sql Session


7. Trace Options


8. How to Debug Oracle Order Management (OM)


9. FND Logging

Oracle Payments : Table of Content

1. Oracle Payments Queries
   
   
   1. Payment Document and Payment Profile Link
   
   
   
2. Example Scripts
   
   
   1. Payment Batch Stuck at Formatting Stage
   2. Terminate Payment Instruction
   
   
   
3. Performance Issue with Oracle Payments
   
   
   1. Record Print Status Too Slow
   
   
4. IBY Tables Notes
   
   
   1. IBY_PAY_SERVICE_REQUESTS
   2. IBY_DOCS_PAYABLE_ALL
   3. IBY_PAYMENTS_ALL
   4. IBY_ACCOUNT_OWNERS
   5. Customer's Credit Card (IBY_CREDITCARD)


5. Java.lang.OutOfMemoryError: Java heap space error in Oracle Payments


6. Bank and Bank Branches in Rel12


7. Oracle Payments Lookup Codes


8. Payment Instructions Page: Void All Payments Icon

Payment Instructions Page: Void All Payments Icon

Payment instruction Page displays "Void all Payments" icon only if following function is added to user's responsibility


Function Name : Void All Payments in Payment Instruction Page
Function Code : IBY_FD_PAYMENT_VOID_ALL


This icon is very helpful in development environment. Using this icon, users can void whole payment batch and rerun payments for desired invoices to verify certain customizations.

MOAC for Custom Code

Want to know about Multi Org Access Control? read Metallink note: 420787.1

Record Print Status Too Slow

At my client site, this program was not performing well. We did a trace and found several sql statements were written poorly as if Oracle development did not understand the data and just joined the table without any regards to how query will perform. At my client site, client pays tens of thousands of invoices everyday and volume is higher at the end of every week, every bi-week and every month end. We knew we can not pay invoices in timely manner if we did not do anything. so first thing we did was to log a service request with Oracle and Oracle response was not satisfactory. I thought about it and decided to use sql plan management for these problem sqls. The details of bad sql and desired plan is as given below

1. Bad Sql#1
2. Bad Sql#2
3. Bad Sql#3
4. Bad Sql#4
5. Bad Sql#5

Please leave some comments if you are experiencing slow performance of Record Print Status (IBY_FD_RECORD_PRINT_STATUS) program

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')

Record Print Status Too slow, Sql#4

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 site

PLAN_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.

1. 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.
2. 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

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.

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')
 

Record Print Status Too Slow, Sql#3

Poor Performing Sql#3

(This sql was formatted for readability)

SELECT NEW.INVOICE_ID P_INT_INVOICE_ID
, NEW.DUE_DATE P_ACCOUNTING_DATE
, PV.VENDOR_ID P_VENDOR_ID
, ORIG.INVOICE_NUM P_OLD_INVOICE_NUM
, NEW.INVOICE_NUM P_INT_INVOICE_NUM
, NEW.PAYMENT_AMOUNT P_INTEREST_AMOUNT
, DECODE(ORIG.INVOICE_CURRENCY_CODE, :B3 , NULL, DECODE(BASE.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND(NEW.PAYMENT_AMOUNT / ORIG.PAYMENT_CROSS_RATE * NVL(NEW.PAYMENT_EXCHANGE_RATE,1), BASE.PRECISION), ROUND( ((NEW.PAYMENT_AMOUNT / ORIG.PAYMENT_CROSS_RATE * NVL(NEW.PAYMENT_EXCHANGE_RATE,1)) / BASE.MINIMUM_ACCOUNTABLE_UNIT) * BASE.MINIMUM_ACCOUNTABLE_UNIT ) ) ) P_INTEREST_BASE_AMOUNT
, ORIG.SET_OF_BOOKS_ID P_SET_OF_BOOKS_ID
, ORIG.PAYMENT_CROSS_RATE P_PAYMENT_CROSS_RATE
, NEW.PAYMENT_EXCHANGE_RATE P_EXCHANGE_RATE
, NEW.PAYMENT_EXCHANGE_RATE_TYPE P_EXCHANGE_RATE_TYPE
, IBYDOCS.PAYMENT_DATE P_EXCHANGE_DATE
, ORIG.INVOICE_ID P_INVOICE_ID
, ORIG.INVOICE_CURRENCY_CODE P_INVOICE_CURRENCY_CODE
, ORIG.ORG_ID P_ORG_ID
FROM PO_VENDORS PV
, AP_INVOICES_ALL ORIG
, AP_SELECTED_INVOICES_ALL NEW
, FND_CURRENCIES BASE
, FND_CURRENCIES FCINV
, IBY_FD_DOCS_PAYABLE_V IBYDOCS
WHERE NEW.ORIGINAL_INVOICE_ID           = ORIG.INVOICE_ID
AND NEW.VENDOR_ID                       = PV.VENDOR_ID
AND NEW.CHECKRUN_NAME                   = :B5
AND NEW.CHECKRUN_ID                     = :B4
AND BASE.CURRENCY_CODE                  = :B3
AND FCINV.CURRENCY_CODE                 = ORIG.INVOICE_CURRENCY_CODE
AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 = NEW.CHECKRUN_ID
AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 = NEW.INVOICE_ID
AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 = NEW.PAYMENT_NUM
AND IBYDOCS.COMPLETED_PMTS_GROUP_ID     = :B2
AND IBYDOCS.ORG_ID                      = :B1
AND NEW.ORG_ID                          = :B1

This sql had following plan at our site

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3suxb7k1zzxmr, child number 0
-------------------------------------
SELECT NEW.INVOICE_ID P_INT_INVOICE_ID, NEW.DUE_DATE P_ACCOUNTING_DATE,
PV.VENDOR_ID P_VENDOR_ID, ORIG.INVOICE_NUM P_OLD_INVOICE_NUM,
NEW.INVOICE_NUM P_INT_INVOICE_NUM, NEW.PAYMENT_AMOUNT
P_INTEREST_AMOUNT, DECODE(ORIG.INVOICE_CURRENCY_CODE, :B3 , NULL,
DECODE(BASE.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND(NEW.PAYMENT_AMOUNT /
ORIG.PAYMENT_CROSS_RATE * NVL(NEW.PAYMENT_EXCHANGE_RATE,1),
BASE.PRECISION), ROUND( ((NEW.PAYMENT_AMOUNT / ORIG.PAYMENT_CROSS_RATE
* NVL(NEW.PAYMENT_EXCHANGE_RATE,1)) / BASE.MINIMUM_ACCOUNTABLE_UNIT) *
BASE.MINIMUM_ACCOUNTABLE_UNIT ) ) ) P_INTEREST_BASE_AMOUNT,
ORIG.SET_OF_BOOKS_ID P_SET_OF_BOOKS_ID, ORIG.PAYMENT_CROSS_RATE
P_PAYMENT_CROSS_RATE, NEW.PAYMENT_EXCHANGE_RATE P_EXCHANGE_RATE,
NEW.PAYMENT_EXCHANGE_RATE_TYPE P_EXCHANGE_RATE_TYPE,
IBYDOCS.PAYMENT_DATE P_EXCHANGE_DATE, ORIG.INVOICE_ID P_INVOICE_ID,
ORIG.INVOICE_CURRENCY_CODE P_INVOICE_CURRENCY_CODE, ORIG.ORG_ID
P_ORG_ID FROM PO_VENDORS PV, AP_INVOICES_ALL ORIG,
AP_SELECTED_INVOICES_ALL NEW, FND_CURRENCIES BAS

Plan hash value: 902921929

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |       |       |   403 (100)|          |
|   1 |  NESTED LOOPS                      |                          |       |       |            |          |
|   2 |   NESTED LOOPS                     |                          |     1 |   207 |   403   (1)| 00:00:05 |
|   3 |    NESTED LOOPS                    |                          |     1 |   176 |     9   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                   |                          |     1 |   171 |     9   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                  |                          |     1 |   134 |     7   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                          |     1 |   129 |     6   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                          |     1 |   119 |     4   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| FND_CURRENCIES           |     1 |    10 |     1   (0)| 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN         | FND_CURRENCIES_U1        |     1 |       |     0   (0)|          |
|* 10 |         TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL |     1 |   109 |     3   (0)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | AP_SELECTED_INVOICES_N7  |     1 |       |     2   (0)| 00:00:01 |
|  12 |        TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS             |     1 |    10 |     2   (0)| 00:00:01 |
|* 13 |         INDEX UNIQUE SCAN          | AP_SUPPLIERS_U1          |     1 |       |     1   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN            | HZ_PARTIES_U1            |     1 |     5 |     1   (0)| 00:00:01 |
|  15 |      TABLE ACCESS BY INDEX ROWID   | AP_INVOICES_ALL          |     1 |    37 |     2   (0)| 00:00:01 |
|* 16 |       INDEX UNIQUE SCAN            | AP_INVOICES_U1           |     1 |       |     1   (0)| 00:00:01 |
|* 17 |     INDEX UNIQUE SCAN              | FND_CURRENCIES_U1        |     1 |     5 |     0   (0)|          |
|* 18 |    INDEX RANGE SCAN                | IBY_DOCS_PAYABLE_ALL_N16 |  6787 |       |    15   (0)| 00:00:01 |
|* 19 |   TABLE ACCESS BY INDEX ROWID      | IBY_DOCS_PAYABLE_ALL     |     1 |    31 |   394   (1)| 00:00:05 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("BASE"."CURRENCY_CODE"=:B3)
  10 - filter(("NEW"."CHECKRUN_NAME"=:B5 AND "NEW"."ORG_ID"=:B1))
  11 - access("NEW"."CHECKRUN_ID"=:B4)
  13 - access("NEW"."VENDOR_ID"="PAV"."VENDOR_ID")
  14 - access("PAV"."PARTY_ID"="HP"."PARTY_ID")
  16 - access("ORIG"."INVOICE_ID"=TO_NUMBER("NEW"."ORIGINAL_INVOICE_ID"))
  17 - access("FCINV"."CURRENCY_CODE"="ORIG"."INVOICE_CURRENCY_CODE")
  18 - access("IBY_DOCS"."COMPLETED_PMTS_GROUP_ID"=:B2)
  19 - filter((TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1")=:B4 AND "IBY_DOCS"."ORG_ID"=:B1 AND
              "NEW"."CHECKRUN_ID"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1") AND
              "NEW"."INVOICE_ID"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2") AND
              "NEW"."PAYMENT_NUM"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3")))


62 rows selected.

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.


I added following hint the sql

/*+ leading (IBYDOCS) use_nl (IBYDOCS new pv orig ) */

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: 3515255252
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |     1 |   232 |   536   (1)| 00:00:07 |
|   1 |  NESTED LOOPS                     |                          |     1 |   232 |   536   (1)| 00:00:07 |
|   2 |   NESTED LOOPS                    |                          |     1 |   227 |   536   (1)| 00:00:07 |
|   3 |    NESTED LOOPS                   |                          |     1 |   190 |   534   (1)| 00:00:07 |
|   4 |     NESTED LOOPS                  |                          |     1 |   185 |   533   (1)| 00:00:07 |
|   5 |      NESTED LOOPS                 |                          |     1 |   175 |   531   (1)| 00:00:07 |
|   6 |       NESTED LOOPS                |                          |    25 |  1050 |   456   (1)| 00:00:06 |
|   7 |        TABLE ACCESS BY INDEX ROWID| FND_CURRENCIES           |     1 |    10 |     1   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN         | FND_CURRENCIES_U1        |     1 |       |     0   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS BY INDEX ROWID| IBY_DOCS_PAYABLE_ALL     |    25 |   800 |   455   (1)| 00:00:06 |
|* 10 |         INDEX RANGE SCAN          | IBY_DOCS_PAYABLE_ALL_N16 |  7867 |       |    19   (0)| 00:00:01 |
|* 11 |       TABLE ACCESS BY INDEX ROWID | AP_SELECTED_INVOICES_ALL |     1 |   133 |     3   (0)| 00:00:01 |
|* 12 |        INDEX RANGE SCAN           | AP_SELECTED_INVOICES_N1  |     1 |       |     2   (0)| 00:00:01 |
|  13 |      TABLE ACCESS BY INDEX ROWID  | AP_SUPPLIERS             |     1 |    10 |     2   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN           | AP_SUPPLIERS_U1          |     1 |       |     1   (0)| 00:00:01 |
|* 15 |     INDEX UNIQUE SCAN             | HZ_PARTIES_U1            |     1 |     5 |     1   (0)| 00:00:01 |
|  16 |    TABLE ACCESS BY INDEX ROWID    | AP_INVOICES_ALL          |     1 |    37 |     2   (0)| 00:00:01 |
|* 17 |     INDEX UNIQUE SCAN             | AP_INVOICES_U1           |     1 |       |     1   (0)| 00:00:01 |
|* 18 |   INDEX UNIQUE SCAN               | FND_CURRENCIES_U1        |     1 |     5 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - access("BASE"."CURRENCY_CODE"=:B3)
   9 - filter(TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1")=TO_NUMBER(:B4) AND 
              "IBY_DOCS"."ORG_ID"=TO_NUMBER(:B1))
  10 - access("IBY_DOCS"."COMPLETED_PMTS_GROUP_ID"=TO_NUMBER(:B2))
  11 - filter("NEW"."CHECKRUN_NAME"=:B5 AND "NEW"."CHECKRUN_ID"=TO_NUMBER(:B4) AND 
              "NEW"."ORG_ID"=TO_NUMBER(:B1) AND "NEW"."CHECKRUN_ID"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF
              1"))
  12 - access("NEW"."INVOICE_ID"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2") AND 
              "NEW"."PAYMENT_NUM"=TO_NUMBER("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"))
  14 - access("NEW"."VENDOR_ID"="PAV"."VENDOR_ID")
  15 - access("PAV"."PARTY_ID"="HP"."PARTY_ID")
  17 - access("ORIG"."INVOICE_ID"=TO_NUMBER("NEW"."ORIGINAL_INVOICE_ID"))
  18 - access("FCINV"."CURRENCY_CODE"="ORIG"."INVOICE_CURRENCY_CODE")

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.

Baseline Plan for a SQL Query

SELECT *
FROM TABLE( dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_6b9356eea6ab68ab', format=>'all'));

sql_handle comes from dba_sqpl_base_lines.sql_handle

parameter format can have following 3 value

1. basic: i do not know why somebody will use this value for format parameter. i can think of only one situation when you would use this value. Suppose you want to see plan for a query and your girl friend is shouting from the driveway and threatening you. At that time you may use this value for format parameter. See the plan and save in your brain and run. when you reach driveway, you beg your girl friend to drive your Shelby. while sitting in passenger seat, just do two things, say your are right to whatever your girl friend says and use remaining part of brain to analyze sql plan
2. typical: This value is good enough
3. all: Gives most information

Record Print Status Too Slow, Sql#1

Poor performing Sql #1

(This sql was formatted for readability)

INSERT
INTO AP_INVOICE_RELATIONSHIPS
  (
    ORIGINAL_INVOICE_ID
  , RELATED_INVOICE_ID
  , CREATED_BY
  , CREATION_DATE
  , ORIGINAL_PAYMENT_NUM
  , LAST_UPDATED_BY
  , LAST_UPDATE_DATE
  , CHECKRUN_NAME
  )
SELECT ORIG.INVOICE_ID
, NEW.INVOICE_ID
, :B4
, SYSDATE
, NEW.ORIGINAL_PAYMENT_NUM
, :B4
, SYSDATE
, :B1
FROM AP_INVOICES_ALL ORIG
, AP_SELECTED_INVOICES_ALL NEW
, IBY_FD_PAYMENTS_V IBYPMTS
, IBY_FD_DOCS_PAYABLE_V IBYDOCS
WHERE IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 = TO_CHAR(NEW.CHECKRUN_ID)
AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2   = TO_CHAR(NEW.INVOICE_ID)
AND IBYPMTS.ORG_TYPE                      = 'OPERATING_UNIT'
AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3   = TO_CHAR(NEW.PAYMENT_NUM)
AND IBYPMTS.PAYMENT_ID                    = IBYDOCS.PAYMENT_ID
AND IBYPMTS.COMPLETED_PMTS_GROUP_ID       = :B3
AND IBYPMTS.ORG_ID                        = :B2
AND NEW.ORIGINAL_INVOICE_ID               = ORIG.INVOICE_ID
AND NEW.CHECKRUN_NAME                     = :B1 ; 

This sql was using following plan

Plan hash value: 2909465723

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |                          |       |       |   934 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL         |                          |       |       |            |          |
|   2 |   NESTED LOOPS                   |                          |       |       |            |          |
|   3 |    NESTED LOOPS                  |                          |     1 |   103 |   934   (1)| 00:00:12 |
|   4 |     MERGE JOIN CARTESIAN         |                          |   168 | 13608 |   242   (0)| 00:00:03 |
|   5 |      NESTED LOOPS                |                          |     1 |    55 |     6   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL |     1 |    49 |     5   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | AP_SELECTED_INVOICES_N5  |     1 |       |     4   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN          | AP_INVOICES_U1           |     1 |     6 |     1   (0)| 00:00:01 |
|   9 |      BUFFER SORT                 |                          |  1563 | 40638 |   241   (0)| 00:00:03 |
|  10 |       TABLE ACCESS BY INDEX ROWID| IBY_PAYMENTS_ALL         |  1563 | 40638 |   236   (0)| 00:00:03 |
|* 11 |        INDEX RANGE SCAN          | IBY_PAYMENTS_ALL_N10     |  1563 |       |    14   (0)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN             | IBY_DOCS_PAYABLE_ALL_N6  |    45 |       |     2   (0)| 00:00:01 |
|* 13 |    TABLE ACCESS BY INDEX ROWID   | IBY_DOCS_PAYABLE_ALL     |     1 |    22 |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter(TO_NUMBER("ORIGINAL_INVOICE_ID") IS NOT NULL)
   7 - access("NEW"."CHECKRUN_NAME"=:B1)
   8 - access("ORIG"."INVOICE_ID"=TO_NUMBER("ORIGINAL_INVOICE_ID"))
  11 - access("IBY_PAY"."COMPLETED_PMTS_GROUP_ID"=:B3 AND "IBY_PAY"."ORG_ID"=:B2 AND
              "IBY_PAY"."ORG_TYPE"='OPERATING_UNIT')
  12 - access("IBY_PAY"."PAYMENT_ID"="IBY_DOCS"."PAYMENT_ID")
  13 - filter(("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1"=TO_CHAR("NEW"."CHECKRUN_ID") AND
              "IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"="NEW"."SYS_NC00115$" AND
              "IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR("NEW"."PAYMENT_NUM")))

Q: Why this plan is bad?

A: The merge Cartesian join is the culprit. In my case there were around 98,000 invoices. So accessing ap_selected_invoices_all on checkrun name will return 98,000 rows. since all of the invoices were paid without any error, so it had created 98,000 rows in iby_docs_payable_all table too. All these invoices had same completed_pmts_group_id value. So accessing iby_payments_all on completed_pmts_group_id and then iby_docs_payable_all on payment_id will return same 98,000 rows. This kind of join between ap_selected_invoices and iby table is not efficient. The better plan would be to access iby_docs_payable_all using following columns

1. CALLING_APP_ID
2. CALLING_APP_DOC_UNIQUE_REF1
3. CALLING_APP_DOC_UNIQUE_REF2
4. CALLING_APP_DOC_UNIQUE_REF3

And then access iby_payments_all table.

i feel there is no need to use view IBY_FD_PAYMENTS_V in this query. Completed_pmts_group_id and org_type columns are available in iby_docs_payable_all table itself. Did Oracle forget about normalization? same columns in both the table. what were they thinking?

I am not sure calling_appi_id = 200 is not added to where clause. This query is being called from AP side. if Oracle development had added this condition then Oracle optimizer might have found using index iby_docs_payable_n13 is better than doing merge join Cartesian.

So we got bad sql_text, sql_id, plan_hash_value from gv$sql. This is the sql statement without formatting

INSERT INTO AP_INVOICE_RELATIONSHIPS( ORIGINAL_INVOICE_ID, RELATED_INVOICE_ID, CREATED_BY, CREATION_DATE, ORIGINAL_PAYMENT_NUM, LAST_UPDATED_BY, LAST_UPDATE_DATE, CHECKRUN_NAME) SELECT ORIG.INVOICE_ID, NEW.INVOICE_ID, B4 , SYSDATE, NEW.ORIGINAL_PAYMENT_NUM, B4 , SYSDATE, B1 FROM AP_INVOICES_ALL ORIG, AP_SELECTED_INVOICES_ALL NEW, IBY_FD_PAYMENTS_V IBYPMTS, IBY_FD_DOCS_PAYABLE_V IBYDOCS WHERE IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 = TO_CHAR(NEW.CHECKRUN_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 = TO_CHAR(NEW.INVOICE_ID) AND IBYPMTS.ORG_TYPE = 'OPERATING_UNIT' AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 = TO_CHAR(NEW.PAYMENT_NUM) AND IBYPMTS.PAYMENT_ID = IBYDOCS.PAYMENT_ID AND IBYPMTS.COMPLETED_PMTS_GROUP_ID = B3 AND IBYPMTS.ORG_ID = B2 AND NEW.ORIGINAL_INVOICE_ID = ORIG.INVOICE_ID AND NEW.CHECKRUN_NAME = B1 ;


Using plan_hash_value and sql_id, I executed following script using a concurrent program to create sql plan. ( this script can be used from sqlplus too, I used concurrent program because I do not have permission to execute any package from sqlplus)

declare
 l_ret_value NUMBER;
 l_clob CLOB;
begin
 l_ret_value := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
                ( sql_id          =>'awf51xwyyanvn'
                , plan_hash_value => '2909465723'
 fnd_file.put_line(fnd_file.log,'Return Value :' || l_ret_value);                ) ;

end;
/

Check log file, if Return value is 1 then one plan is loaded otherwise there was some error while loading plan.


Run following sql to get sql_handle and plan_name. These will be needed to drop the bad plan.

select sql_handle, plan_name from dba_sql_plan_baselines a;


Then we added hint to sql statement so that execution plan is using nested loop between ap_selected_invoices_all, biy_docs_payable_all, iby_payments_all and ap_invoices_all. This was the small script to execute it from sqlplus

declare
  b1 varchar2(30) := 'SL EFT 06-APR-2012 1';
  b2 number := 81;
  b3 number := 745;
  b4 number := 1;
begin

INSERT INTO AP_INVOICE_RELATIONSHIPS( ORIGINAL_INVOICE_ID, RELATED_INVOICE_ID, CREATED_BY, CREATION_DATE, ORIGINAL_PAYMENT_NUM, LAST_UPDATED_BY, LAST_UPDATE_DATE, CHECKRUN_NAME) SELECT /*+ leading (new) use_nl (new ibydocs ibypmts orig) index_ss(ibydocs iby_docs_payable_all_n13) */ORIG.INVOICE_ID, NEW.INVOICE_ID, B4 , SYSDATE, NEW.ORIGINAL_PAYMENT_NUM, B4 , SYSDATE, B1 FROM AP_INVOICES_ALL ORIG, AP_SELECTED_INVOICES_ALL NEW, IBY_FD_PAYMENTS_V IBYPMTS, IBY_FD_DOCS_PAYABLE_V IBYDOCS WHERE IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 = TO_CHAR(NEW.CHECKRUN_ID) AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 = TO_CHAR(NEW.INVOICE_ID) AND IBYPMTS.ORG_TYPE = 'OPERATING_UNIT' AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 = TO_CHAR(NEW.PAYMENT_NUM) AND IBYPMTS.PAYMENT_ID = IBYDOCS.PAYMENT_ID AND IBYPMTS.COMPLETED_PMTS_GROUP_ID = B3 AND IBYPMTS.ORG_ID = B2 AND NEW.ORIGINAL_INVOICE_ID = ORIG.INVOICE_ID AND NEW.CHECKRUN_NAME = B1 ;

end;

Note: I had to make sure that sql statement remains same except hint part. Slight change in sql statement cause failure in DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

Now run “select module, sql_id, plan_hash_value, a.* from gv$sql a where sql_text like 'INSERT%INTO AP_INVOICE_RELATIONSHIPS%(%' order by last_active_time desc;” to get sql_id and plan_hash_value for sql with hint.

Use those sql_id, plan_hash_value for sql with proper hint and sql_handle for bad plan and run following script

declare
 l_ret_value NUMBER;
 l_clob CLOB;
begin
 l_ret_value := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
                ( sql_id          =>'gkhvwymvww8v6'
                , plan_hash_value => '1577246184'
                , sql_handle      => 'SQL_1be05243ae2286bc' --<< pertains to  bad sql plan
                ) ;


   fnd_file.put_line(fnd_file.log,'Return Value :' || l_ret_value);
end;
/

Now drop the BAD sql plan for the query

declare
 l_ret_value NUMBER;
 l_clob CLOB;
begin
) ;

   l_ret_value := DBMS_SPM.DROP_SQL_PLAN_BASELINE
                  ( SQL_HANDLE      => 'SQL_1be05243ae2286bc'           
                  , PLAN_NAME       => 'SQL_PLAN_1rs2k8fr251pwc6fe2586' -- < pertains to bad plan
                  );

   fnd_file.put_line(fnd_file.log,'Return Value :' || l_ret_value);
end;
/ 

This is the plan that works for us

Plan hash value: 1577246184
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |                          |     1 |   128 |   250K  (1)| 00:50:03 |
|   1 |  LOAD TABLE CONVENTIONAL         | AP_INVOICE_RELATIONSHIPS |       |       |            |          |
|   2 |   NESTED LOOPS                   |                          |       |       |            |          |
|   3 |    NESTED LOOPS                  |                          |     1 |   128 |   250K  (1)| 00:50:03 |
|   4 |     NESTED LOOPS                 |                          |     1 |   102 |   250K  (1)| 00:50:03 |
|   5 |      NESTED LOOPS                |                          |  9382 |   879K|   240K  (1)| 00:48:11 |
|   6 |       TABLE ACCESS BY INDEX ROWID| AP_SELECTED_INVOICES_ALL | 58310 |  4213K|  7461   (1)| 00:01:30 |
|*  7 |        INDEX RANGE SCAN          | AP_SELECTED_INVOICES_N5  | 58310 |       |   473   (1)| 00:00:06 |
|   8 |       TABLE ACCESS BY INDEX ROWID| IBY_DOCS_PAYABLE_ALL     |     1 |    22 |     4   (0)| 00:00:01 |
|*  9 |        INDEX SKIP SCAN           | IBY_DOCS_PAYABLE_ALL_N13 |     1 |       |     3   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN           | AP_INVOICES_U1           |     1 |     6 |     1   (0)| 00:00:01 |
|* 11 |     INDEX UNIQUE SCAN            | IBY_PAYMENTS_ALL_U1      |     1 |       |     1   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS BY INDEX ROWID   | IBY_PAYMENTS_ALL         |     1 |    26 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("NEW"."CHECKRUN_NAME"=:B1)
   9 - access("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1"=TO_CHAR("NEW"."CHECKRUN_ID") AND 
              "IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"=TO_CHAR("INVOICE_ID") AND 
              "IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR("NEW"."PAYMENT_NUM"))
       filter("IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF1"=TO_CHAR("NEW"."CHECKRUN_ID") AND 
              "IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF2"=TO_CHAR("INVOICE_ID") AND 
              "IBY_DOCS"."CALLING_APP_DOC_UNIQUE_REF3"=TO_CHAR("NEW"."PAYMENT_NUM"))
  10 - access("ORIG"."INVOICE_ID"=TO_NUMBER("NEW"."ORIGINAL_INVOICE_ID"))
  11 - access("IBY_PAY"."PAYMENT_ID"="IBY_DOCS"."PAYMENT_ID")
  12 - filter("IBY_PAY"."COMPLETED_PMTS_GROUP_ID"=TO_NUMBER(:B3) AND 
              "IBY_PAY"."ORG_TYPE"='OPERATING_UNIT' AND "IBY_PAY"."ORG_ID"=TO_NUMBER(:B2))

Now run "Record Print Status" to check if new plan is used or not. This query can give the sql plan used by the query.

select * from table( 
    dbms_xplan.display_sql_plan_baseline( 
        sql_handle=>'SQL_1be05243ae2286bc', 
        format=>'basic'));

Oracle Support

I hope Oracle Support guys read this Wall Street Journal Article
Oracle Customers Rankled by Product Roadmap

Invoices Eligible for Payments

This query can give count of invoices by batch, payment method and payment group that can be or can not be selected for payments. If an invoices is on hold or not validated or not due for payment, it will not be sele3cted by Payment Process Request.

SELECT pay_group_lookup_code , payment_method_code , batch_name , holds , wfapproval_status , validated , due_date , count(1) FROM (SELECT pay_group_lookup_code , aps.payment_method_code , wfapproval_status , NVL((select 'N' from ap_invoice_distributions_all aid where aid.invoice_id = aia.invoice_id and match_status_flag <> 'A' and rownum = 1 ), 'Y' ) validated , (select batch_name from ap_batches_all ab where ab.batch_id = aia.batch_id ) batch_name , NVL((select 'Y' from ap_holds_all where invoice_id = aia.invoice_id and release_lookup_code is null and rownum = 1 ), 'N' ) holds , trunc(aps.due_date) due_date FROM ap_invoices_all aia , ap_payment_schedules_all aps WHERE aia.payment_status_flag = 'N' AND aia.cancelled_date is NULL AND aia.invoice_id = aps.invoice_id ) a GROUP BY pay_group_lookup_code , payment_method_code , batch_name , holds , wfapproval_status , validated , due_date ORDER BY pay_group_lookup_code , payment_method_code , batch_name , holds , wfapproval_status , validated , due_date , 8 desc ;

Sql Plan management article

SQL Plan Management: SQL Plan Baseline Example

At my site, we are upgrading Oracle Application from 11.5.9 to Rel12.1.3 and many of Oracle's program are performing very poorly. We logged service requests with Oracle support but it was little time consuming to get the fix, so we decided to experiment with sql plan baselines for sql statements with bad plan.

This is what i did to see how sql plan baselines work.

Create table SKM_SQL_PLAN_MGMT_TEST as select * from dba_objects so that we have a table with good number of rows.

Execute “select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL';”. At this moment there is no index on SKM_SQL_PLAN_MGMT_TEST table, so execution plan will use full table scan on the table SKM_SQL_PLAN_MGMT_TEST. This is the plan used by sql

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  4v8quwv000yrr, child number 0
-------------------------------------
select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name =
'AP_INVOICES_ALL'

Plan hash value: 325872721

--------------------------------------------------------------------------------------------
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                        |       |       |  1677 (100)|          |
|*  1 |  TABLE ACCESS FULL| SKM_SQL_PLAN_MGMT_TEST |    68 | 14076 |  1677   (1)| 00:00:21 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='AP_INVOICES_ALL')

Note
-----
   - dynamic sampling used for this statement (level=2)


23 rows selected.

Run this query to get sql_id for the above sql statement

select sql_id, a.* from gv$sql a where sql_text = 'select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = ''AP_INVOICES_ALL'''; 

Use the sql_id returned by the above sql and run following script. (Sql_id in this case was 4v8quwv000yrr)

declare
   l_ret_value NUMBER;
begin
   l_ret_value :=DBMS_SPM.load_plans_from_cursor_cache(sql_id=> ‘4v8quwv000yrr’);
end;
/

It will create a base line plan that is enabled and accepted. Detail of this baseline can be seen using this query

SELECT * FROM dba_sql_plan_baselines;

Now create an index on object_name column of SKM_SQL_PLAN_MGMT_TEST

create index SKM_SQL_PLAN_MGMT_TEST_N1 on  db_gx3mn0.SKM_SQL_PLAN_MGMT_TEST (object_name)
/

Now run “select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL';”.

Now check the execution plan used by the above query
set long 20000
set pages 100
set lines 132
select * from table(dbms_xplan.display_cursor());

You will see following output :

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4v8quwv000yrr, child number 1
-------------------------------------
select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL'

Plan hash value: 325872721

--------------------------------------------------------------------------------------------
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                        |       |       |  1677 (100)|          |
|*  1 |  TABLE ACCESS FULL| SKM_SQL_PLAN_MGMT_TEST |  5017 |  1014K|  1677   (1)| 00:00:21 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='AP_INVOICES_ALL')

Note
-----

- SQL plan baseline SQL_PLAN_068dj65793y565b6edfd3 used for this statement


23 rows selected.


Although index access provides better execution plan but because of baseline defined for sql statement, query uses bad plan. This demonstrates that once you define baseline for sql statement, that baseline plan will get executed even if Oracle optimizer find a supposedly better plan.

Now if you run the query “select * from dba_sql_plan_baselines where to_char(sql_text) = 'select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = ''AP_INVOICES_ALL'''”, you will find that one more sql plan baseline is added and it has low cost but value of ACCEPTED column is “NO”. so plan is available for review and could marked as accept for future use. To make plan acceptable, do the following

Run sql “select sql_handle, plan_name, a.* from dba_sql_plan_baselines a where to_char(sql_text) = 'select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = ''AP_INVOICES_ALL''';”

Now drop the sql plan baseline that does full table scan using following script

declare
  l_ret_value NUMBER;
begin
  l_ret_value := DBMS_SPM.DROP_SQL_PLAN_BASELINE
                 ( SQL_HANDLE      => 'SQL_0321b1314e91f8a6'
                 , PLAN_NAME       => 'SQL_PLAN_068dj65793y565b6edfd3'
                 );
end;
/

Now make the better plan that was generated automatically “ACCEPTED” using following script

declare

  l_clob CLOB;
begin
  l_clob      := DBMS_SPM.evolve_sql_plan_baseline
                 ( sql_handle   => 'SQL_0321b1314e91f8a6'
                 , plan_name    => 'SQL_PLAN_068dj65793y56ae570ac6'
                 , time_limit   => 10
                 , verify       => 'YES'
                 , commit       => 'YES'
                 ) ;
end;
/

Now run the query “select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL';”

Now run “select * from table(dbms_xplan.display_cursor());”, you will see following output

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  4v8quwv000yrr, child number 0
-------------------------------------
select * from SKM_SQL_PLAN_MGMT_TEST where objecT_name = 'AP_INVOICES_ALL'

Plan hash value: 3071631708

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |       |       |     5 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| SKM_SQL_PLAN_MGMT_TEST    |     3 |   621 |     5   (0)| 00:00:01
|*  2 |   INDEX RANGE SCAN          | SKM_SQL_PLAN_MGMT_TEST_N1 |     3 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"='AP_INVOICES_ALL')

Note
-----
   - dynamic sampling used for this statement (level=2)

- SQL plan baseline SQL_PLAN_068dj65793y56ae570ac6 used for this statement


25 rows selected.

Please notice that new sql baseline plan is used for this execution.

Explain Plan Using DBMS_XPLAN

Usage of DBMS_XPLAN:

explain plan set statement_id = 'skm' for
SELECT acct.account_number
     , acct.status Account_Status
     , acct.account_established_date
     , acct.account_termination_date
     , acct.account_activation_date
     , asite.bill_to_flag
     , asite.ship_to_flag
     , asite.customer_category_code
     , psite.party_site_name
     , psite.status Party_Site_Status
     , psite.start_date_active
     , psite.end_date_active
     , loc.address1 || ' ' || loc.address2 address
     , loc.city
     , loc.state
     , loc.postal_code
     , siteu.site_use_code
     , siteu.primary_flag
     , siteu.status site_uses_status
     , banku.start_date
     , banku.end_date 
     , banku.primary_flag
     , bank.bank_account_num
     , bank.bank_account_name
     , bank.inactive_date
  FROM apps.hz_cust_accounts         acct
     , apps.hz_cust_acct_sites_all   asite
     , apps.hz_party_sites           psite
     , apps.hz_locations             loc
     , apps.hz_cust_site_uses_all    siteu
     , apps.ap_bank_account_uses_all banku
     , apps.ap_bank_accounts_all     bank
 WHERE acct.account_number        = :account_number -- in single quotes
   AND asite.cust_account_id      = acct.cust_account_id
   AND asite.party_site_id        = psite.party_site_id
   AND psite.location_id          = loc.location_id
   AND siteu.cust_acct_site_id    = asite.cust_acct_site_id
   AND banku.customer_site_use_id = siteu.site_use_id
   AND bank.bank_account_id       = banku.external_bank_account_id
 ORDER BY bank.bank_account_num
/

Now run following sql statement

set pages 1000
set lines 132
select * from table(dbms_xplan.display('plan_table','skm'));

And you will see something like

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3845713369

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |  3264 |   299K| 38586   (1)| 00:07:44 |
|*  1 |  FILTER                          |                            |       |       |            |          |
|   2 |   NESTED LOOPS                   |                            |       |       |            |          |
|   3 |    NESTED LOOPS                  |                            |  3264 |   299K| 38586   (1)| 00:07:44 |
|   4 |     NESTED LOOPS                 |                            |  3264 |   248K| 28788   (1)| 00:05:46 |
|   5 |      NESTED LOOPS                |                            |  3585 |   213K| 18026   (1)| 00:03:37 |
|*  6 |       TABLE ACCESS FULL          | AP_SUPPLIERS               |  2534 |   113K|  9067   (2)| 00:01:49 |
|*  7 |       TABLE ACCESS BY INDEX ROWID| AP_SUPPLIER_SITES_ALL      |     1 |    15 |     4   (0)| 0
|*  8 |        INDEX RANGE SCAN          | AP_SUPPLIER_SITES_U2       |     1 |       |     2   (0)| 00:00:01
|*  9 |      TABLE ACCESS BY INDEX ROWID | IBY_EXTERNAL_PAYEES_ALL    |     1 |    17 |     3   (0)| 0
|* 10 |       INDEX RANGE SCAN           | IBY_EXTERNAL_PAYEES_ALL_N7 |     1 |       |     2   (0)| 00:00:01 
|* 11 |     INDEX RANGE SCAN             | IBY_EXT_PARTY_PMT_MTHDS_N1 |     1 |       |     2   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS BY INDEX ROWID   | IBY_EXT_PARTY_PMT_MTHDS    |     1 |    16 |     3   (0)| 00
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYSDATE@!-300<=SYSDATE@!-290)
   6 - filter("SUP"."END_DATE_ACTIVE" IS NULL AND "SUP"."CREATION_DATE">=SYSDATE@!-300 AND
              "SUP"."CREATION_DATE"<=SYSDATE@!-290)
   7 - filter("SS"."INACTIVE_DATE" IS NULL)
   8 - access("SUP"."VENDOR_ID"="SS"."VENDOR_ID")
   9 - filter("EPA"."INACTIVE_DATE" IS NULL)
  10 - access("SS"."VENDOR_SITE_ID"="EPA"."SUPPLIER_SITE_ID")
       filter("EPA"."SUPPLIER_SITE_ID" IS NOT NULL)
  11 - access("EPA"."EXT_PAYEE_ID"="PPM"."EXT_PMT_PARTY_ID")
  12 - filter("PPM"."INACTIVE_DATE" IS NULL)

33 rows selected.

For Better looking output, you may run following sql statement

set pages 1000
set lines 132
spool plan.htm
select  dbms_xplan.display_plan( table_name    =>'plan_table'
                                      , statement_id  => 'skm'
                                      , format        => 'ALL'
                                      , filter_preds  => NULL
                                      , type          => 'HTML'
                                      )
 from dual
/
spool off

Now open plan.htm in a browser, you will a better layout of query plan.

FND_FLEX_EXT.get_ccid Example

DECLARE l_coa_id gl_sets_of_books.chart_of_accounts_id % TYPE; l_ccid gl_code_combinations.code_combination_id % TYPE; l_err varchar2(2000); BEGIN MO_GLOBAL.init('SQLAP'); MO_GLOBAL.set_policy_context('S','81'); FND_GLOBAL.apps_initialize ( user_id => 7043 , resp_id => 20639 , resp_appl_id => 200 ) ; SELECT bks.chart_of_accounts_id INTO l_coa_id FROM ap_system_parameters param , gl_sets_of_books bks WHERE param.set_of_books_id = bks.set_of_books_id ; l_ccid := FND_FLEX_EXT.get_ccid ('SQLGL' , 'GL#' , l_coa_id , TO_CHAR(SYSDATE,'DD-MON-YYYY') , '01' ||'.'|| '224020' || '.56617.0000.0000.0000' /* The concatenated segments will vary from client to client*/ ) ; DBMS_OUTPUT.put_line('l_ccid:= ' || l_ccid); l_err := fnd_message.get; DBMS_OUTPUT.put_line('Error Line#1 :' || substr(l_err,1,225)); DBMS_OUTPUT.put_line('Error Line#2 :' || substr(l_err,225,225)); END; /