C1-TXNIP Batch is Taking Longer Time to Execute Than Expected for 500k+ Records (Doc ID 2217659.1)

Last updated on JANUARY 05, 2017

Applies to:

Oracle Financial Services Revenue Management and Billing - Version 2.3 and later
Information in this document applies to any platform.

Symptoms

On ORMB 2.3.0.2.0 version, BT - Batch

Performance issue with C1-TXNIP batch. It is taking longer time to execute than expected. For 500k+ records.

ACTUAL BEHAVIOR
----------------------
As per AWR report, below delete SQL query from C1-TXNIP is taking longer time. CI_TXN_DETAIL_STG table doesn’t have any INDEX and so it is doing full table scan.

DELETE FROM CI_TXN_DETAIL_STG WHERE TRUNC(TXN_DTTM) = :1 AND TXN_DETAIL_ID = :2 AND DISAGG_SW = :3 AND BO_STATUS_CD= :4

EXPECTED BEHAVIOR
-----------------------
A normal index on CI_TXN_DETAIL_STG. TXN_DETAIL_ID should exist as it would give better performance.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. C1-TXNIP Batch Run

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Performance issue.

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms