Highly DB-Intensive Query During Cancel Deal (Doc ID 978512.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.0.0.0 to 7.5.0.0.0 [Release 7.3.0 to 7.5.0]
Information in this document applies to any platform.
Checked for relevance on 17-Aug-2011


Goal

During a cancel_deal operation, it is sometimes observed that the query below is spending a long time returning data, though it appears to be optimized (standing to DB trace logs).

The query is found while the CM pstack shows the process hanging within a call to "fm_act_handle_system_events".

We need to understand what the query is called for and if any optimization is possible.

select distinct event_t.poid_DB, event_t.poid_ID0, event_t.poid_TYPE, event_t.poid_REV, event_t.created_t, event_t.mod_t, event_t.read_access, event_t.write_access, event_t.account_obj_DB, event_t.account_obj_ID0, event_t.account_obj_TYPE, event_t.account_obj_REV, event_t.archive_status, event_t.batch_id, event_t.currency, event_t.descr, event_t.earned_end_t, event_t.earned_start_t, event_t.earned_type, event_t.effective_t, event_t.end_t, event_t.event_no, event_t.flags, event_t.group_obj_DB, event_t.group_obj_ID0, event_t.group_obj_TYPE, event_t.group_obj_REV, event_t.incr_quantity, event_t.incr_unit, event_t.invoice_data, event_t.item_obj_DB, event_t.item_obj_ID0, event_t.item_obj_TYPE, event_t.item_obj_REV, event_t.loader_batch_obj_DB, event_t.loader_batch_obj_ID0, event_t.loader_batch_obj_TYPE, event_t.loader_batch_obj_REV, event_t.min_quantity, event_t.min_unit, event_t.name, event_t.net_quantity, event_t.original_batch_id, event_t.profile_label_list, event_t.program_name, event_t.provider_descr, event_t.provider_id_DB, event_t.provider_id_ID0, event_t.provider_id_TYPE, event_t.provider_id_REV, event_t.provider_ipaddr, event_t.rated_timezone_id, event_t.rerate_obj_DB, event_t.rerate_obj_ID0, event_t.rerate_obj_TYPE, event_t.rerate_obj_REV, event_t.rounding_mode, event_t.rum_name, event_t.service_obj_DB, event_t.service_obj_ID0, event_t.service_obj_TYPE, event_t.service_obj_REV, event_t.session_obj_DB, event_t.session_obj_ID0, event_t.session_obj_TYPE, event_t.session_obj_REV, event_t.start_t, event_t.sys_descr, event_t.tax_locales, event_t.tax_supplier, event_t.timezone_adj_end_t, event_t.timezone_adj_start_t, event_t.timezone_id, event_t.timezone_mode, event_t.tod_mode, event_t.unit, event_t.unrated_quantity, event_t.usage_type, event_t.userid_DB, event_t.userid_ID0, event_t.userid_TYPE, event_t.userid_REV
from event_t ,event_billing_product_t ,event_product_fee_cycle_t
where event_t.account_obj_ID0 = :1
and event_t.poid_TYPE = :2
and event_t.end_t <= :3
and event_t.end_t >= :4
and ( event_billing_product_t.offering_obj_ID0 = :5 )
and event_product_fee_cycle_t.flags in (256, 2056, 1032, 4104, 65792, 262400, 327936, 524544, 525568, 8388636, 8389640)
and event_t.poid_id0 = event_billing_product_t.obj_id0
and event_t.poid_id0 = event_product_fee_cycle_t.obj_id0
order by event_t.end_t desc, event_t.created_t desc;

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