Insert Into Rel_sub_processes_t Not Using Binds - Driving Increase In Shared Pool
(Doc ID 1434693.1)
Last updated on SEPTEMBER 20, 2023
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.
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |