MSRFWOR - Refresh Snapshots Performance Is Poor When Querying MLOG$ Tables
(Doc ID 376471.1)
Last updated on MARCH 31, 2023
Applies to:
Oracle Advanced Supply Chain Planning - Version 11.5.9 and laterInformation in this document applies to any platform.
EXECUTABLE:MSRFWOR - Refresh Collection Snapshots
Symptoms
When running ASCP Planning Data Collections.
A large portion of the overall collection elapsed time (75%) is within the Refresh Collection Snapshots program
STEPS
1. Run ASCP Planning Data Collections
2. Compare times for programs that are run by checking log files and/OR using Script in <Note 280295.1>
3. Review TKPROF of the trace files for the longer running processes... in this case MSRFWOR - Refresh Collection Snapshots
Verified the issue in the TKPROF of the trace file as noted below:
Note: This could happen with different MLOG$ tables that are accessed during the process.
delete from "INV"."MLOG$_MTL_DEMAND"
where
snaptime$$ <= :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 384.32 678.69 1988205 1988595 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 384.32 678.69 1988205 1988595 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE MLOG$_MTL_DEMAND (cr=1988595 pr=1988205 pw=0 time=678692317 us)
0 TABLE ACCESS FULL MLOG$_MTL_DEMAND (cr=1988595 pr=1988205 pw=0 time=678692295 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 250916 0.51 476.18
db file sequential read 4 0.00 0.00
latch free 7 0.01 0.02
********************************************************************************
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 |
Cause |
Solution |
References |