Billing Performance Bottleneck from OOB Query on Event Tables
(Doc ID 1671566.1)
Last updated on FEBRUARY 09, 2024
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.
Purpose
Observation:
* Billing was slow in Production.
* It took approximately 40 minutes to 60 minutes to bill 8,000 accounts.
* DBA identified that an Out of Box (OOB) query is the bottleneck.
* 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.
This is the query which is looking for last 90 days data:
Global Information
Status : DONE (ALL ROWS)
Instance ID : 1
Session : PIN
SQL ID : 1p1har2h5fjv7
SQL Execution ID : 17713168
Execution Started : 03/24/2014 07:27:27
First Refresh Time : 03/24/2014 07:2
Why it is looking for last 90 days data and what are some suggestions in this scenario to improve billing performance?
Questions and Answers
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
| Purpose |
| Questions and Answers |
| Why is it looking for last 90 days data? |
| Any suggestion to improve the billing performance issue? |
Should one create index on EVENT_BAL_IMPACTS_T (item_obj_id0, obj_id0,tax_code, resource_id, amount_deferred,TAX_LOCALES)? |
Does one need to drop this index I_EVENT_BI_ITEM_OBJ__ID which is on EVENT_BAL_IMPACTS_T(ITEM_OBJ_ID0) now? |
Can one create compressed index for both of these? |
If yes, what should be the compressed columns? |
| References |