Costly Query On AU_PROFILE_T Causing BRM To Stop Responding (Doc ID 1301619.1)

Last updated on FEBRUARY 22, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version: 7.3.1.0.0 and later   [Release: 7.3.1 and later ]
Information in this document applies to any platform.

Goal

In a customer setup with "radius --> cm --> dm_oracle --> database" chain, it was frequently observed that the system would hang and stop responding to the radius requests. When this happens the radius request queue would get filled to the maximum limit and no requests would get processed by BRM. After restarting the whole BRM system, the incoming requests would resume to get processed successfully.

After analyzing the DB AWR reports during the time of the issue, it was noticed that below query was causing a bottleneck. The below query is going through full table scan on au_profile_t and since this table has 12Million records in the customer setup, it is filling up the entire cache making other processes to hang/wait.

select name, poid_DB, poid_ID0, poid_TYPE, poid_REV from au_profile_t
where au_profile_t.au_parent_obj_ID0 = :1 and au_profile_t.effective_t = (
select max( au_profile_t.effective_t ) from au_profile_t where
au_profile_t.au_parent_obj_ID0 = :2 and au_profile_t.effective_t <= :3 )

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