CE Bank statement import and reconciliation takes a long time to complete.

Last updated on JANUARY 16, 2018

Applies to:

Symptoms

For an issue we had in CE, we applied some patch and mainly 13697994.
Since then, when we import a bank statement, the system is slow

SQL ID: 3tmdfspg18k1v
Plan Hash: 1385477331
INSERT INTO CE_AVAILABLE_TRANSACTIONS_TMP (SEQ_ID, ROW_ID, MULTI_SELECT,
 BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, BANK_NAME,
 BANK_BRANCH_NAME, TRX_ID, TRX_TYPE, TYPE_MEANING, TRX_NUMBER, CHECK_NUMBER,
 CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, GL_DATE,
 STATUS_DSP, STATUS, DESCRIPTION, TRX_DATE, CLEARED_DATE, MATURITY_DATE,
 EXCHANGE_RATE_DATE, EXCHANGE_RATE_TYPE, USER_EXCHANGE_RATE_TYPE,
 EXCHANGE_RATE, BANK_CHARGES, BANK_ERRORS, BATCH_NAME, BATCH_ID, AGENT_NAME,
 CUSTOMER_NAME, PAYMENT_METHOD, VENDOR_NAME, CUSTOMER_ID, SUPPLIER_ID,
 REFERENCE_TYPE_DSP, REFERENCE_TYPE, REFERENCE_ID, ACTUAL_AMOUNT_CLEARED,
 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
 REMITTANCE_NUMBER, CASH_RECEIPT_ID, APPLICATION_ID, COUNT_CLEARED,
 BANK_CURRENCY_CODE, TRX_CURRENCY_TYPE, CODE_COMBINATION_ID, PERIOD_NAME,
 JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER, JOURNAL_ENTRY_LINE_NUMBER,
 CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT, RECEIPT_CLASS_ID,
 RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE, VALUE_DATE,
 REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID, CE_BANK_ACCT_USE_ID,
 RECONCILED_STATUS_FLAG) SELECT CE_AVAILABLE_TRANSACTIONS_S.NEXTVAL, ROW_ID,
 MULTI_SELECT, BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM,
 BANK_NAME, BANK_BRANCH_NAME, TRX_ID, TRX_TYPE, TYPE_MEANING, TRX_NUMBER,
 CHECK_NUMBER, CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED,
 GL_DATE, STATUS_DSP, STATUS, DESCRIPTION, TRX_DATE, CLEARED_DATE,
 MATURITY_DATE, EXCHANGE_RATE_DATE, EXCHANGE_RATE_TYPE,
 USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE, BANK_CHARGES, BANK_ERRORS,
 BATCH_NAME, BATCH_ID, AGENT_NAME, CUSTOMER_NAME, PAYMENT_METHOD,
 VENDOR_NAME, CUSTOMER_ID, SUPPLIER_ID, REFERENCE_TYPE_DSP, REFERENCE_TYPE,
 REFERENCE_ID, ACTUAL_AMOUNT_CLEARED, CREATION_DATE, CREATED_BY,
 LAST_UPDATE_DATE, LAST_UPDATED_BY, REMITTANCE_NUMBER, CASH_RECEIPT_ID,
 APPLICATION_ID, COUNT_CLEARED, BANK_CURRENCY_CODE, TRX_CURRENCY_TYPE,
 CODE_COMBINATION_ID, PERIOD_NAME, JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER,
 JOURNAL_ENTRY_LINE_NUMBER, CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT,
  RECEIPT_CLASS_ID, RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE,
 VALUE_DATE, REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID,
 CE_BANK_ACCT_USE_ID, 'N' FROM CE_101_TRANSACTIONS_V WHERE BANK_ACCOUNT_ID =
 :B1



call     count       cpu    elapsed       disk      query    current   rows
------- ------  -------- ---------- ---------- ---------- --------------------
Parse        1      0.00       0.00          0          0          0 0
Execute      1     32.26   46063.90     232842    9990192          0 0
Fetch        0      0.00       0.00          0          0          0 0
------- ------  -------- ---------- ---------- ---------- --------------------
total        2     32.26   46063.90     232842    9990192          0 0

Changes

 Patch 13697994 is applied

Cause