BILLING Batch Performance Issue Due To Hibernate Join-fetch SQLs (Doc ID 2296432.1)

Last updated on AUGUST 18, 2017

Applies to:

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

Symptoms

On RMB v2.4.0.1.0 against FW v4.2.0.3.0:

BILLING batch performance issue due to hibernate join-fetch SQLs

ACTUAL BEHAVIOR
--------------------------------
The BILLING job has taken almost 1hr to process 9011 daily bills and 6555 monthly bills for 12591 single outlet merchants (persons) on a month end date.

There are 5 SQLs which require attention:
- Select query from CI_BSEG_CALC_LN left outer join CI_BSEG _CL_CHAR
- Select query from CI_BSEG_CALC left outer join CI_BSEG_CALC_LN
- Select query from CI_BSEG left outer join CI_BSEG_CALC
- Select query from CI_BSEG_CL_CHAR
- Select query from CI_BILL_CYC_SCH for all schedules of a bill cycle

Individually, these SQLs generated by framework are running in less than 10 ms each execution.
However, the problem is they were executed for more than 6 million times and some even got executed for more than 51 million times.
This is causing high CPU utilization on both database and RMB nodes.

EXPECTED BEHAVIOR
--------------------------------------
The Billing job should be completed within expected time range with reasonable CPU utilization.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
This is not going to meet the performance target for Migration (M1) as the total combined execution time taken by all group3 jobs (which includes BILLING) should not exceed 2hrs for 40k merchants on a month end date. The job is causing big delay in generation of payment requests so we might miss the daily cutoff time set by the banking channels.

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