Performance Issue with OE_ODR_LINES_SN

Perform these steps when other processes are not updating the table OE_ORDER_LINES_ALL:

1. Suspend workflow processes. Some customers have workflow processes that update order lines at a rate of several thousand lines per hour.
2. Get Count on MLOG$_OE_ORDER_LINES_ALL:
   

   select count(*) from MLOG$_OE_ORDER_LINES_ALL;

3. Truncate the MLOG$ table:
   

   truncate table ONT.MLOG$_OE_ORDER_LINES_ALL;

4. Confirm ZERO rows in the MLOG:
   

   select count(*) from MLOG$_OE_ORDER_LINES_ALL;

5. Gather table stats on the MLOG. From the System Administrator responsibility, run the single request Gather Table Statistics with the following parameters:
   
   • OWNER: ONT
   • TABLE: MLOG$_OE_ORDER_LINES_ALL
   Use the default for all the other parameters or in SQL*Plus, execute:
   

   begin FND_STATS.GATHER_TABLE_STATS ( 'ONT','MLOG$_OE_ORDER_LINES_ALL', 10 ); end;

6. Confirm that num_rows in dba_tables for the MLOG table = 0:
   

   select table_name, num_rows, last_analyzed from dba_tables
   where table_name in ('MLOG$_OE_ORDER_LINES_ALL');

7. Lock the Statistics using the following command:
   

   EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('ONT','MLOG$_OE_ORDER_LINES_ALL');

8. As APPS User in SQL*Plus, set the database parameter _mv_refresh_use_stats = FALSE:
   

   alter system set "_mv_refresh_use_stats"=FALSE;

   The DBA can set in the init.ora to be used at all times.
9. Run Refresh Collection Snapshots as a standalone request with the following parameters:
   
   • Refresh Mode: Complete
   • Snapshot Name: OE_ODR_LINES_SN
   • Other parameters: Use default
10. Gather table statistics on the snapshot. From the System Administrator responsibility, run the single request Gather Table Statistics with the following parameters:
    
    • Table Owner: ONT [or APPS in the latest code].
    • Table Name: OE_ODR_LINES_SN. You can check for the latest code using:
      

      Select * from dba_snapshots where name like '&snapshot_name';

    • All other parameters: Use the default.
    Alternatively, in SQL*Plus, execute the following:
    

    begin FND_STATS.GATHER_TABLE_STATS ( 'ONT [or APPS]','OE_ODR_LINES_SN', 10 ); end;

11. Run Data Collections with Targeted Refresh with only Planners = Yes. This is a setup step for certain code improvements that help with overall data collections performance. This step must be run for all the organizations being collected. Do not use an Collection Group for this run. To run in targeted mode, set the Planning Data Pull parameter Purge Previously Collected Data to 'No' and then choose the targeted collection mode.
12. Run Data Collections with either a complete or targeted refresh with only the Sales Orders parameter set to 'Yes'.
    
    Note: The Sales Order entity is unique. When complete refresh is specified for Data Collections, we default the Sales Order parameter to 'No' and run the net change refresh of sales orders automatically for performance reasons.
    
13. Observe that during the run of Data Collections, the performance is improved.