My Oracle Support Banner

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 later
Information 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

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