Drop/Recreate Index Procedure For Duplicate Check Impacts Performance (Doc ID 1924902.1)

Last updated on JANUARY 05, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.5.0.0.0 to 7.5.0.0.0 [Release 7.5.0]
Information in this document applies to any platform.
***Checked for relevance on 23-Mar-2016***

Symptoms

On BRM 7.5 PS4, Pipeline Performance

Issue:

Dropping and recreating Index procedures for duplicate check is impacting performance.

Description:

It is an issue because right now in IFW_DUPLICATECHECK% tables, some of them have more than 1000 millions (1 billion or 1 milliard) records. Therefore dropping and recreating the indexes takes about 1 hour and 10-15 minutes.

Every day during index creation these are the issues faced by customer:

- Huge archive log activity (about 1 switch every 3 seconds).
- Data Guard (standby database) is way out of sync for a few hours until it applies all archive logs.
- Some AWR snapshots are not created because DB is working so heavily that will not have time to create them (AWR frequency is 15 minutes for BRM Production DB).

Need to have the possibility to avoid drop/recreate index. For e.g. it may be one parameter inside FCT_DuplicateCheck module that would allow customers to decide which approach they choose. Or some other improvement that will reduce impact of flushing old data into DB when StoreLimit/BufferLimit are changed.

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