Costly Query On AU_PROFILE_T Causing BRM To Stop Responding
Last updated on FEBRUARY 22, 2017
Applies to:Oracle Communications Billing and Revenue Management - Version: 18.104.22.168.0
Information in this document applies to any platform.
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 )
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms