Performance Issue With Memory Based Snapshot Worker 64 Bit Linux When Adding New Org To The Plan (Doc ID 2729147.1)

Last updated on SEPTEMBER 26, 2023

Applies to:

Symptoms

12.2.x

PROBLEM
-------------
MRP plan is getting stuck at Memory based snapshot phase and keeps on running for very very long.
We have observed that one of the 'Memory Based Snapshot Worker 64 bit Linux' gets stuck at a specific sql.

Since we have multiple regional plan to be run on Production instance on daily basis, we can not afford to keep this running and have to terminate the plan after its stuck for 100+mins at the memory Based Snapshot phase itself
When we remove newly added org - then snapshot completes in just a few minutes.


AWR shows SQL ID as 9vr40t24gnw3b
select mrr.transaction_id , mrr.department_id , mrr.resource_id , mrr.organization_id , ....

Log shows error in APP-FND-01564: ORACLE error 3114 in mscsrr_snp_res_reqs

mscsrr_snp_res_reqs is the C code to snapshot resource requirements from file mslsyi.ppc

They also report:
msc_resource_requirement table count for this plan_id :
without new org 3.4K
with new org 13.5Mn
-----------------
"select count(*), sr_instance_id, organization_id, plan_id from msc_resource_requirements
 where organization_id in (select organization_id from msc_plan_organizations where plan_id = &plan_id)
 and plan_id in (-1, &plan_id)
 group by sr_instance_id, organization_id, plan_id
 order by sr_instance_id, organization_id, plan_id"

COUNT(*) SR_INSTANCE_ID ORGANIZATION_ID PLAN_ID
57                      3023                     624                   -1
156                    3023                    296                5023
13,777,761         3023                    624                5023
3,689                 3023                    983                5023

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.