C1-BLPPR Poor Performance In ORMB 2.5.0.3 (Doc ID 2279828.1)

Last updated on JUNE 26, 2017

Applies to:

Oracle Financial Services Revenue Management and Billing - Version 2.5.0.3.0 to 2.5.0.3.0 [Release 2]
Information in this document applies to any platform.

Goal

Performance testing environment is composed of 2 application server cluster, 9 batch server cluster and 2 database server cluster. Each batch server has 4 thread pool workers with 8 threads each. We have executed the base billing jobs in this environment to generate trial bills where the billing mode is set as 'Trial'. About 1,881,949 trial bills were successfully generated by C1-PNDBL and then 3,727,857 trial bill segments were successfully generated by C1-BLGEN. Then we submitted C1-BLPPR in trial mode also. It processed 1,886,191 records but it took 4 hours and 30 minutes to complete using 280 threads. After extracting the AWR Report, the following SQL was seen as the poorest in performance with the largest elapsed time: UPDATE CI_TRL_FT SET BILL_ID=:1 WHERE SA_ID IN (SELECT SA.SA_ID FROM CI_SA SA WHERE SA.ACCT_ID = :2 ) AND SUBSTR(BILL_ID , 1 , 12) = :3 AND FT_TYPE_FLG IN ('AD' , 'AX' , 'BS' , 'BX') AND SHOW_ON_BILL_SW = 'Y'. Upon checking this table, there is no index that supports the combination of the WHERE clause columns of this UPDATE statement, particularly the SUBSTR(BILL_ID, 1, 12). In the past, this batch job also performed poorly even if the billing mode is 'Actual'. Please provide solutions to both 'Trial' and 'Actual' billing modes even though this issue was found in 'Trial' mode of C1-BLPPR.
 

Solution

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