Billing Performance Issue Due To Bottleneck In OOB SQL Query On Event Tables (Doc ID 1671566.1)

Last updated on JUNE 14, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.5.0.0.0 to 7.5.0.0.0 [Release 7.5.0]
Information in this document applies to any platform.

Goal

On : 7.5.0.0.0 version, Core Billing

Issue :

It is observed that billing is slow in Production.
*  It takes approximately 40 minutes to 60 minutes to bill 8,000 accounts.
*  DBA identified that an OOB query is the bottleneck
*  Below is the query and it is looking for last 90 days data

Sql text:

Global Information
Status : DONE (ALL ROWS)
Instance ID : 1
Session : PIN (1586:30805)
SQL ID : 1p1har2h5fjv7
SQL Execution ID : 17713168
Execution Started : 03/24/2014 07:27:27
First Refresh Time : 03/24/2014 07:2

Note :

*  Event tables are monthly partitioned and event_t has 1,049,278,491 records and EVENT_BAL_IMPACTS_T has 2,357,983,897 records.
*  Recent partition in event_t has almost 130 million records.


Question :

Why it is looking for last 90 days data and what are some suggestions in this scenario to improve billing performance ?
 

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