MRCNSP - Memory Based Snapshot Performance Severely Degraded After 11.1.0.7 RDBMS Upgrade (Doc ID 1354578.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Materials Requirement Planning - Version 11.5.10.2 and later
Information in this document applies to any platform.
Executable:MRCNSP - Memory-based Snapshot
ConcurrentProgram:MRCNSW - Memory-based Snapshot Worker
***Checked for relevance on 22-Aug-2013***

Symptoms

On : 11.5.10.2 version, Master Production Schedule

Find the Memory Based Snapshot performance has degraded on the following statement after upgrade from 9.2.0.8 to 11.1.0.7 database. The file mrlsii.ppc (version 115.23) with the problem code statement has not changed recently. The snapshot (MRCNSP program) is using "ORDERED" hint which is the costly query.

The request log file shows "Selecting BOM" as the poorly performing statement

MRCNSP module: Memory-based Snapshot
+---------------------------------------------------------------------------+

Current system time is 18-NOV-2009 01:31:23
Selecting Items 97,485 3:41
Selecting BOM 209,059 99:36

The trace file reports this sql as the worst performance:

select /*+ ORDERED parallel(msi) parallel(bbm) +*/ bic.component_item_id ,
bbm.organization_id ,bbm.assembly_item_id ,bbm.organization_id ,
msi_comp.mrp_planning_code ,msi.mrp_planning_code ,bic.operation_seq_num ,
TO_CHAR(bic.effectivity_date,'J') ,NVL(TO_CHAR(bic.disable_date,'J'),
(-23453)) ,DECODE(NVL(eri.status_type,6),6,(-23453),NVL(eri.use_up,2)) ,
(((bic.component_quantity/bic.component_yield_factor)* bic.planning_factor)
/100) ,DECODE(NVL(eri.status_type,6),6,(-23453),NVL(eri.use_up_item_id,
(-23453))) ,nvl(bbm.alternate_bom_designator,'-23453') ,
nvl(msi.effectivity_control,1)
from
mrp_plan_organizations_v mpo ,mtl_system_items msi ,bom_bill_of_materials
bbm ,bom_inventory_components bic ,eng_revised_items eri ,mtl_system_items
msi_comp where (((((((((((((((((((((((((((DECODE(:b0,1,NVL(eri.mrp_active,1)
,DECODE(NVL(eri.status_type,6),6,1,NVL(eri.mrp_active,1)))=1 and
eri.revised_item_sequence_id(+)=bic.revised_item_sequence_id) and
DECODE(bic.old_component_sequence_id,null ,NVL(bic.disable_date,
...
...
mpo.planned_organization) and mpo.organization_id=:b4) and
mpo.compile_designator=:b5) order by bbm.assembly_item_id,
bic.component_item_id,bic.operation_seq_num,bic.effectivity_date desc ,
bbm.alternate_bom_designator


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.02 0.03 1 11 0 0
Fetch 2091 4947.02 5834.51 71530983 755199866 0 209059
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2093 4947.05 5834.55 71530984 755199877 0 209059

...
...

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 22335502 7.55 789.04
PX Deq Credit: send blkd 31 0.00 0.00
PX Deq: Join ACK 6 0.00 0.00
PX Deq Credit: need buffer 16 0.00 0.00
PX Deq: Parse Reply 7 0.00 0.00
SQL*Net message to client 2092 0.00 0.00
SQL*Net message from client 2092 0.02 0.87
PX Deq: Execute Reply 200 2.00 155.38
db file parallel read 6 0.50 0.74
direct path read 6812127 3.72 1390.83

EXPECTED BEHAVIOR
-----------------------
Expect ... that the Memory Based Snapshot will perform same or better in the newly upgraded database to 11.1.0.7

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. have an 11.5.10.2 apps environment with 9.2.0.8 database
2. upgrade the database to 11.1.0.7
3. run the MPS and see this statement takes more time.

Changes

Upgrade RDBMS from 9.2.0.8 to 11.1.0.7

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