Drop/Recreate Index Procedure For Duplicate Check Impacts Performance
Last updated on JANUARY 05, 2017
Applies to:Oracle Communications Billing and Revenue Management - Version 220.127.116.11.0 to 18.104.22.168.0 [Release 7.5.0]
Information in this document applies to any platform.
***Checked for relevance on 23-Mar-2016***
On BRM 7.5 PS4, Pipeline Performance
Dropping and recreating Index procedures for duplicate check is impacting performance.
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.
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