To Improve BRM Performance During Billing And Database Search (Doc ID 1223764.1)

Last updated on SEPTEMBER 26, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.1.0.0 to 7.5.0.0.0 [Release 7.3.1 to 7.5.0]
Information in this document applies to any platform.
Checked for relevance on 19-Nov-2013.
***Checked for relevance on 4-May-2015***

Goal

The purpose of this article is to address one aspect of improving the BRM database performance during billing activities and other searches.

Consider below incident :

During billing, the performance started to degrade all of a sudden, while the performance was normal in previous billing cycle. As per investigation of the AWR reports gathered during the bad performance period, the below taxation query was causing the bottleneck :

SELECT TAX_CODE , SUM (AMOUNT_DEFERRED) "AMOUNT" FROM (SELECT /*+ INDEX(eb
I_EVENT_BI_ITEM_OBJ__ID) ORDERED */ EB.RESOURCE_ID , EB.TAX_CODE ,
EB.AMOUNT_DEFERRED FROM ITEM_T I , EVENT_BAL_IMPACTS_T EB WHERE
EB.AMOUNT_DEFERRED != 0 AND EB.RESOURCE_ID = :b1 AND I.POID_DB IS NOT NULL
AND I.POID_ID0 = EB.ITEM_OBJ_ID0 AND I.BILLINFO_OBJ_ID0 = :b2 AND ( (
I.EFFECTIVE_T = 0 AND I.BILL_OBJ_ID0 = :b3 ) OR ( I.EFFECTIVE_T > :b4 AND
I.EFFECTIVE_T <= :b5 AND I.ARCHIVE_STATUS = 0 AND I.BILL_OBJ_ID0 = 0 ) ) )
GROUP BY TAX_CODE

The above query was taking 4-6 seconds to return.

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