Billing Performance Issue Due To Bottleneck In OOB SQL Query On Event Tables
Last updated on JUNE 14, 2017
Applies to:Oracle Communications Billing and Revenue Management - Version 184.108.40.206.0 to 220.127.116.11.0 [Release 7.5.0]
Information in this document applies to any platform.
On : 18.104.22.168.0 version, Core Billing
* 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
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
* 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.
Why it is looking for last 90 days data and what are some suggestions in this scenario to improve billing performance ?
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms