Taxation Related SQL Causing Billing Slowdown (Doc ID 1598189.1)

Last updated on AUGUST 07, 2024

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.