Taxation Related SQL Causing Billing Slowdown (Doc ID 1598189.1)

Last updated on MARCH 01, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.4.0.0.0 to 7.5.0.0.0 [Release 7.4.0 to 7.5.0]
Information in this document applies to any platform.
Checked for relevance on 09-Sep-2015.
Checked for relevance on 01-Mar-2017.

Goal

During pin_bill_accts, the following SQL was taking long time and caused the delay in billing.

SELECT TAX_CODE, SUM(AMOUNT_DEFERRED) "AMOUNT", TAX_LOCALES FROM ( SELECT /*+ USE_CONCAT INDEX(eb I_EVENT_BI_ITEM_OBJ__ID) ORDERED */ EB.RESOURCE_ID, EB.TAX_CODE, EB.AMOUNT_DEFERRED, E.TAX_LOCALES, E.TAX_SUPPLIER FROM ITEM_T I, EVENT_BAL_IMPACTS_T EB, EVENT_T E WHERE ((EB.OBJ_ID0 > :B11 AND EB.OBJ_ID0 :B9 AND EB.OBJ_ID0 0 AND EB.RESOURCE_ID = :B6 AND I.POID_DB IS NOT NULL AND I.POID_ID0 = EB.ITEM_OBJ_ID0 AND E.POID_ID0 = EB.OBJ_ID0 AND I.BILLINFO_OBJ_ID0 = :B5 AND E.TAX_SUPPLIER = :B4 AND EB.OBJ_ID0 = (CASE WHEN BITAND(EB.OBJ_ID0, 1152921504606846976) <> 0 THEN (CASE WHEN NOT EXISTS (SELECT START_POID_ID0 FROM TMP_UNPROCESSED_EVENTS_T WHERE EB.OBJ_ID0 BETWEEN START_POID_ID0 AND END_POID_ID0) THEN EB.OBJ_ID0 ELSE 0 END) ELSE EB.OBJ_ID0 END) AND ( ( ((I.EFFECTIVE_T = 0) OR (I.EFFECTIVE_T = :B2 AND I.EFFECTIVE_T < :B1 AND I.ARCHIVE_STATUS = 0 AND I.BILL_OBJ_ID0 = 0 ) ) ) GROUP BY TAX_CODE, TAX_LOCALES

DBAs suggested to remove the hint /*+ USE_CONCAT INDEX(eb I_EVENT_BI_ITEM_OBJ__ID) ORDERED */, but it is OOB SQL.
 
The solution provided in the KM article - Poor Performance During Deferred Taxation (<Note 1541723.1>) did not help. The issue here was not TMP_UNPROCESSED_EVENTS, it was with the plan it was picking, and there were many bad plans being picked.

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