Pin_sub_balance_cleanup Performance Issues

(Doc ID 1561728.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version to [Release 7.4.0]
Linux x86-64
***Checked for Relevance on 17th August 2015***


When one have,  say a customer base of 10 million accounts in production, the pin_sub_balance_cleanup utility in production is taking more time to process

As per the understanding the utility selects all balance_group objects irrespective of input option
eg .,  following configuration in the system
  o Children : 5
  o Per batch : 500
  o Fetch : 5000

The flow of the batch is like below:
1. The batch selects 5000 (fetch_size) objects / balance_group by ordering by poid_id0
2. The batch distributes 500 (per_batch) objects / balance_group each of its 5 (children) threads
   a. Each thread is a lock on the 500 objects / balance_group for this SQL query "SELECT ... FOR UPDATE"
   b. Each thread deletes the table entries corresponding to bal_grp_sub_bals_t 500 objects / balance_group for validity prior to the current date minus 90 days
3. Processing loop until all objects / balance_group were treated

Here are some questions:

1.How to improve the performance of the batch?
2.Irrespective of the input options it is selecting all the balance_group objects why it so? This should delete only the expired sub balances based on the given input value(n= 90), is should deleted all sub balances which are expire 90 days before the current time.
3. Any how this batch is deleting all expired sub balances in this case why does it lock the bal_grp_t table with query
    select poid_id0 from bal_grp_t where poid_id0 between 1 and 42528 for update


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