Insert Into Rel_sub_processes_t Not Using Binds - Driving Increase In Shared Pool (Doc ID 1434693.1)

Last updated on JULY 19, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.4.0.0.0 to 7.4.0.0.0 [Release 7.4.0]
Information in this document applies to any platform.

Symptoms

On Oracle Communications Billing and Revenue Management(BRM), 7.4.0.0.0 version, it is observed that while INSERT INTO rel_sub_processes_t not using binds - driving increase in shared pool.

Due to not using binds suffered an out of memory fault on the database. Here the DBAs have identified a number of queries putting pressure on the shared pool cache.

The below one has been identified as having > 8000 instances in the shared pool. 
 
SQL_TEXT 
---------------
INSERT INTO rel_sub_processes_t(obj_id0,rec_id,start_poid_id0,end_poid_id0,num_processed,num_sub_bal_processed,status,creation_process,tables,name,batch_size,pa 
rameters)VALUES(151152845887,15,1423243186664273894,1423243186664274026,0,0,105, 'RATING_PIPELINE','event_t;event_bal_impacts_t;event_essentials_t;event_delayed_ 
session_mms_t;event_vf_detail_session_info','pin_rel.pin_rel_updater_sp',100,'21') 
 
Reproduction steps:
---------------------
 1. Stop the batch controller 
 2. Generate multiple (say 20) edr files in ifw/data/in/ and corresponding out files will be generated in ifw/data/out/gsm/telephony 
 3. Check the insert query count in shared-pool with select count(*) from v$sql where sql_text like '%rel_sub_processes_t%'; 
 4. Start batch controller.
 5. Immediately check with the insert query count in shared-pool with select count(*) from v$sql where sql_text like '%rel_sub_processes_t%'; 
 6. Observe the insert query count.

Cause

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