My Oracle Support Banner

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

Last updated on FEBRUARY 28, 2019

Applies to:

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


On RMB v2. against FW v4.

BILLING batch performance issue due to hibernate join-fetch SQLs

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.

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

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.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.