提高快照性能

Applies to:

Oracle Advanced Supply Chain Planning - Version: 11.5.10 to 12.0.4

Information in this document applies to any platform.

EXECUTABLE:MSRFWOR - Refresh Collection Snapshots

适用版本：11.5.10to12.0.4

Goal

Introduction

When the Planning Data Collection is run, a number of concurrent programs are launched to collect data from the source (ERP) instance to the planning (destination) instance.  One of these programs is the Refresh Collection Snapshots program.  This program is run on the source instance and is responsible for taking 'snapshots' of the current data in the ERP system.  Many times it is this program that causes the biggest performance impact for the entire Planning Data Collection process.  The intention of this note is to help identify when this process is performing poorly and the steps that can be performed to help improve the performance going forward.

Solution

Terminology

Snapshots - Snapshots are copies of remote data based on queries.  From an Advanced Supply Chain Planning perspective, the snapshots are queries based on the entities used during the planning process.  For example, sales order information is stored in OE_ORDER_LINES_ALL and the corresponding snapshot is OE_ODR_LINES_SN. 

Planning Data Collection - Planning Data Collection is the process by which data retrieved from ERP tables and stored in ASCP tables for the purpose of planning.  This can be done between 2 distinct databases across database links (decentralized) or within the same database (centralized)

Complete Refresh (Data Collection) - The Complete Refresh method clears all transaction data for all business entities from the planning server (for the source instance being collected), then copies over information about the user-selected entities. This method can be time consuming.

Complete Refresh (Snapshots) - Complete refreshes completely recreate the snapshot.

Fast Refresh (Snapshots) - Fast refreshes are only available for simple snapshots (all planning entity snapshots in our case).  They use tables called snapshot logs to send specific rows from the master table to the snapshot.

Collection Process

The Planning Data Collection process uses database snapshots to manage the data on the ERP instance.  The Planning Data Pull program, has a list of parameters (entities) that correspond to one or more of these snapshots.   When data is inserted, updated, or deleted in the master table, these changes are inserted into the snapshot log tables (MLOG$_ tables).  When the Planning Data Pull program is run, it spawns the Refresh Collection Snapshots (RCS) program.  Based on the parameters passed to the Planning Data Pull program, the Refresh Collection Snapshots program begins refreshing the snapshots on the source instance.  In most cases a Fast Refresh is done on the snapshot.  Once the RCS program is complete, control is returned to the Planning Data Pull program to continue with the data collection. 

A Fast Refresh takes the changes in the MLOG$_ tables and updates the snapshots.  It is these snapshots that are used as a basis for the views used to pull the data into the staging tables on the planning instance.  If a Complete Refresh is performed, the snapshot is updated directly from the master table.

For example, the Items parameter corresponds with the snapshot MTL_SYS_ITEMS_SN.  If a change is made to an item (MTL_SYSTEM_ITEMS_B table) or a new item is created, data is inserted into MLOG$_MTL_SYSTEM_ITEMS_B.  When the Refresh Collection Snapshot runs, the data in MLOG$_MTL_SYSTEM_ITEMS_B is used to update MTL_SYS_ITEMS_SN and then the data in MLOG$_MTL_SYSTEM_ITEMS_B is deleted.

Performance Issues

There are certain entities that change frequently throughout the day.  Many customers perform a Complete Refresh Data Collection on a nightly basis.  When this is done, a Fast Refresh of the snapshots is performed by the Refresh Collection Snapshots program for all entities except MTL_MATERIAL_TRANSACTIONS_TEMP.  For this table/snapshot, a Complete Refresh of the snapshot is performed.  If the volume of the data in the MLOG$_ table is too large, a Fast Refresh is not as effective as a Complete Refresh of the snapshots. 

Note: Never perform a Complete Refresh of any of the planning snapshots unless advised by support. 

To improve the performance of the Complete Refresh Data Collection it is advisable to schedule the Refresh Collection Snapshots program to run periodically throughout the day to keep the volume of data in the MLOG$_ tables from growing too large during the day.  This does not need to be done for all snapshots, just those that have a high volume of inserts, updates, and deletes throughout the day.  When running the Refresh Collection Snapshots program it is advised that you use the following parameters:

·         Refresh Mode = Fast

·         Snapshot Name =

·         Threshold for Truncating Logs = NULL

·         Degree = 0

To determine which entities within the system have large growth in the MLOG$_ tables, the following script can be used to monitor the changes in the snapshot logs.

1.  Download the file create_table_rcs_analysis.sql.  This file creates the temporary tables used for this process.

2.  For 11i, download the file rcs_analysis_11i.sql.  For R12, download the file rcs_analysis_r12.sql.  These files check the various MLOG$_ tables to get row counts for the respective applications release.

3.  Schedule 'rcs_analysis_xxx.sql' in a cron job to run every 30-60 minutes for a week to monitor the row changes in the MLOG$_ tables.

4.  Analyze the data in the temp tables that were created in Step 1 by exporting the output of the following queries into an excel spreadsheet.

o    For 11i, run this SQL:  select * from rcs_analysis_11i_temp;

o    For R12, run this SQL:  select * from rcs_analysis_r12_temp;

When you analyze the output of each run, you should see the record count of the MLOG$_ tables increasing throughout the day up to the point where the Complete Refresh Data Collection is run.  At that time, the record count should return to 0 and they will start to grow again.  It's the MLOG$_ tables with the largest amount of growth during the day that should be analyzed.  Here is an example of the MLOG$_OE_ORDER_LINES_ALL table.

Between 21:00 and 23:30, the volume of data is a 0.  Then the data grows to between 2.7 and 3.5 million rows each day.  If the Refresh Collection Snapshots program is scheduled to run every 2 hours starting at 06:30, the amount of data that needs to be processed during the Complete Refresh Data Collection is reduced to about 300,000.  This can cause dramatic improvements to the overall Data Collection process.

In 11i, the performance impact is more drastic than R12 since the Refresh Collection Snapshots program run sequentially in 11i, but in parallel in R12.  We observed a 50-60% reduction in run times of the RCS program for the OE_ODR_LINES_SN snapshot after scheduling the program to run every 2 hours between runs of the Data Collection process.  This type of increased performance can have even greater impact in 11i if multiple snapshots are improved.

Note:  It is important that the Refresh Collection Snapshots program not be scheduled to run during the Data Collection process.  Be sure to allow enough time for the last scheduled RCS program to complete prior to the scheduled data collection process.

 

drop table rcs_analysis_11i_temp;

 

drop table rcs_analysis_r12_temp;

 

create table rcs_analysis_11i_temp

(script_date            DATE,

 bom_structures_b       NUMBER,

 bom_cto_order_demand   NUMBER,

 bom_components_b       NUMBER,

 bom_operational_rout   NUMBER,

 bom_operation_networ   NUMBER,

 bom_operation_resour   NUMBER,

 bom_operation_sequen   NUMBER,

 bom_resource_changes   NUMBER,

 bom_substitute_compo   NUMBER,

 bom_sub_operation_re   NUMBER,

 mtl_demand             NUMBER,

 mtl_material_transac   NUMBER,

 mtl_onhand_quantitie   NUMBER,

 mtl_reservations       NUMBER,

 mtl_supply             NUMBER,

 mtl_system_items_b     NUMBER,

 mtl_txn_request_line   NUMBER,

 mtl_user_demand        NUMBER,

 mtl_user_supply        NUMBER,

 mrp_forecast_dates     NUMBER,

 mrp_forecast_designa   NUMBER,

 mrp_forecast_items     NUMBER,

 mrp_schedule_dates     NUMBER,

 oe_order_lines_all     NUMBER,

 po_acceptances         NUMBER,

 po_change_requests     NUMBER,

 po_supplier_item_cap   NUMBER,

 wip_discrete_jobs      NUMBER,

 wip_flow_schedules     NUMBER,

 wip_lines              NUMBER,

 wip_operations         NUMBER,

 wip_operation_resour   NUMBER,

 wip_repetitive_items   NUMBER,

 wip_repetitive_sched   NUMBER,

 wip_requirement_oper   NUMBER);

   

create table rcs_analysis_r12_temp

(script_date            DATE,

 ahl_schedule_materia   NUMBER,

 bom_operation_sequen   NUMBER,

 wip_operation_resour   NUMBER,

 wsm_copy_op_networks   NUMBER,

 wsm_copy_op_resource   NUMBER,

 wsm_copy_op_resource1  NUMBER,

 po_change_requests     NUMBER,

 wip_operations         NUMBER,

 wip_op_resource_inst   NUMBER,

 wip_repetitive_sched   NUMBER,

 bom_structures_b       NUMBER,

 mtl_material_transac   NUMBER,

 mtl_system_items_b     NUMBER,

 mtl_user_demand        NUMBER,

 mtl_user_supply        NUMBER,

 mrp_forecast_items     NUMBER,

 po_acceptances         NUMBER,

 wip_requirement_oper   NUMBER,

 wip_sub_operation_re   NUMBER,

 bom_operation_resour   NUMBER,

 bom_resource_changes   NUMBER,

 bom_sub_operation_re   NUMBER,

 mtl_item_categories    NUMBER,

 mrp_forecast_designa   NUMBER,

 wip_flow_schedules     NUMBER,

 wip_repetitive_items   NUMBER,

 wsm_copy_operations    NUMBER,

 bom_components_b       NUMBER,

 mrp_forecast_dates     NUMBER,

 mrp_schedule_dates     NUMBER,

 oe_order_lines_all     NUMBER,

 wip_lines              NUMBER,

 bom_operation_networ   NUMBER,

 mtl_demand             NUMBER,

 mtl_onhand_quantitie   NUMBER,

 po_supplier_item_cap   NUMBER,

 wip_discrete_jobs      NUMBER,

 bom_operational_rout   NUMBER,

 bom_substitute_compo   NUMBER,

 eam_wo_relationships   NUMBER,

 mtl_reservations       NUMBER,

 mtl_supply             NUMBER,

 bom_res_instance_cha   NUMBER,

 mtl_txn_request_line   NUMBER,

 wip_operation_networ   NUMBER,

 wsh_trips              NUMBER,

 wsh_trip_stops         NUMBER,

 wsm_copy_requirement   NUMBER);

 

 

rcs_analysis_r12.sql

DECLARE

 

  -- R12 Count Variables

  v_r12_ahl_schedule_materia  NUMBER;

  v_r12_bom_operation_sequen  NUMBER;

  v_r12_wip_operation_resour  NUMBER;

  v_r12_wsm_copy_op_networks  NUMBER;

  v_r12_wsm_copy_op_resource  NUMBER;

  v_r12_wsm_copy_op_resource1 NUMBER;

  v_r12_po_change_requests    NUMBER;

  v_r12_wip_operations        NUMBER;

  v_r12_wip_op_resource_inst  NUMBER;

  v_r12_wip_repetitive_sched  NUMBER;

  v_r12_bom_structures_b      NUMBER;

  v_r12_mtl_material_transac  NUMBER;

  v_r12_mtl_system_items_b    NUMBER;

  v_r12_mtl_user_demand       NUMBER;

  v_r12_mtl_user_supply       NUMBER;

  v_r12_mrp_forecast_items    NUMBER;

  v_r12_po_acceptances        NUMBER;

  v_r12_wip_requirement_oper  NUMBER;

  v_r12_wip_sub_operation_re  NUMBER;

  v_r12_bom_operation_resour  NUMBER;

  v_r12_bom_resource_changes  NUMBER;

  v_r12_bom_sub_operation_re  NUMBER;

  v_r12_mtl_item_categories   NUMBER;

  v_r12_mrp_forecast_designa  NUMBER;

  v_r12_wip_flow_schedules    NUMBER;

  v_r12_wip_repetitive_items  NUMBER;

  v_r12_wsm_copy_operations   NUMBER;

  v_r12_bom_components_b      NUMBER;

  v_r12_mrp_forecast_dates    NUMBER;

  v_r12_mrp_schedule_dates    NUMBER;

  v_r12_oe_order_lines_all    NUMBER;

  v_r12_wip_lines             NUMBER;

  v_r12_bom_operation_networ  NUMBER;

  v_r12_mtl_demand            NUMBER;

  v_r12_mtl_onhand_quantitie  NUMBER;

  v_r12_po_supplier_item_cap  NUMBER;

  v_r12_wip_discrete_jobs     NUMBER;

  v_r12_bom_operational_rout  NUMBER;

  v_r12_bom_substitute_compo  NUMBER;

  v_r12_eam_wo_relationships  NUMBER;

  v_r12_mtl_reservations      NUMBER;

  v_r12_mtl_supply            NUMBER;

  v_r12_bom_res_instance_cha  NUMBER;

  v_r12_mtl_txn_request_line  NUMBER;

  v_r12_wip_operation_networ  NUMBER;

  v_r12_wsh_trips             NUMBER;

  v_r12_wsh_trip_stops        NUMBER;

  v_r12_wsm_copy_requirement  NUMBER;

 

  -- Other Variables  

  v_script_date DATE;

 

BEGIN

 

  SELECT sysdate INTO v_script_date from dual;

  SELECT count(*)

    INTO v_r12_ahl_schedule_materia

    FROM ahl.mlog$_ahl_schedule_materia;

  SELECT count(*)

    INTO v_r12_bom_operation_sequen

    FROM bom.mlog$_bom_operation_sequen;

  SELECT count(*)

    INTO v_r12_wip_operation_resour

    FROM wip.mlog$_wip_operation_resour;

  SELECT count(*)

    INTO v_r12_wsm_copy_op_networks

    FROM wsm.mlog$_wsm_copy_op_networks;

  SELECT count(*)

    INTO v_r12_wsm_copy_op_resource

    FROM wsm.mlog$_wsm_copy_op_resource;

  SELECT count(*)

    INTO v_r12_wsm_copy_op_resource1

    FROM wsm.mlog$_wsm_copy_op_resource1;

  SELECT count(*)

    INTO v_r12_po_change_requests

    FROM po.mlog$_po_change_requests;

  SELECT count(*) INTO v_r12_wip_operations FROM wip.mlog$_wip_operations;

  SELECT count(*)

    INTO v_r12_wip_op_resource_inst

    FROM wip.mlog$_wip_op_resource_inst;

  SELECT count(*)

    INTO v_r12_wip_repetitive_sched

    FROM wip.mlog$_wip_repetitive_sched;

  SELECT count(*)

    INTO v_r12_bom_structures_b

    FROM bom.mlog$_bom_structures_b;

  SELECT count(*)

    INTO v_r12_mtl_material_transac

    FROM inv.mlog$_mtl_material_transac;

  SELECT count(*)

    INTO v_r12_mtl_system_items_b

    FROM inv.mlog$_mtl_system_items_b;

  SELECT count(*)

    INTO v_r12_mtl_user_demand

    FROM inv.mlog$_mtl_user_demand;

  SELECT count(*)

    INTO v_r12_mtl_user_supply

    FROM inv.mlog$_mtl_user_supply;

  SELECT count(*)

    INTO v_r12_mrp_forecast_items

    FROM mrp.mlog$_mrp_forecast_items;

  SELECT count(*) INTO v_r12_po_acceptances FROM po.mlog$_po_acceptances;

  SELECT count(*)

    INTO v_r12_wip_requirement_oper

    FROM wip.mlog$_wip_requirement_oper;

  SELECT count(*)

    INTO v_r12_wip_sub_operation_re

    FROM wip.mlog$_wip_sub_operation_re;

  SELECT count(*)

    INTO v_r12_bom_operation_resour

    FROM bom.mlog$_bom_operation_resour;

  SELECT count(*)

    INTO v_r12_bom_resource_changes

    FROM bom.mlog$_bom_resource_changes;

  SELECT count(*)

    INTO v_r12_bom_sub_operation_re

    FROM bom.mlog$_bom_sub_operation_re;

  SELECT count(*)

    INTO v_r12_mtl_item_categories

    FROM inv.mlog$_mtl_item_categories;

  SELECT count(*)

    INTO v_r12_mrp_forecast_designa

    FROM mrp.mlog$_mrp_forecast_designa;

  SELECT count(*)

    INTO v_r12_wip_flow_schedules

    FROM wip.mlog$_wip_flow_schedules;

  SELECT count(*)

    INTO v_r12_wip_repetitive_items

    FROM wip.mlog$_wip_repetitive_items;

  SELECT count(*)

    INTO v_r12_wsm_copy_operations

    FROM wsm.mlog$_wsm_copy_operations;

  SELECT count(*)

    INTO v_r12_bom_components_b

    FROM bom.mlog$_bom_components_b;

  SELECT count(*)

    INTO v_r12_mrp_forecast_dates

    FROM mrp.mlog$_mrp_forecast_dates;

  SELECT count(*)

    INTO v_r12_mrp_schedule_dates

    FROM mrp.mlog$_mrp_schedule_dates;

  SELECT count(*)

    INTO v_r12_oe_order_lines_all

    FROM ont.mlog$_oe_order_lines_all;

  SELECT count(*) INTO v_r12_wip_lines FROM wip.mlog$_wip_lines;

  SELECT count(*)

    INTO v_r12_bom_operation_networ

    FROM bom.mlog$_bom_operation_networ;

  SELECT count(*) INTO v_r12_mtl_demand FROM inv.mlog$_mtl_demand;

  SELECT count(*)

    INTO v_r12_mtl_onhand_quantitie

    FROM inv.mlog$_mtl_onhand_quantitie;

  SELECT count(*)

    INTO v_r12_po_supplier_item_cap

    FROM po.mlog$_po_supplier_item_cap;

  SELECT count(*)

    INTO v_r12_wip_discrete_jobs

    FROM wip.mlog$_wip_discrete_jobs;

  SELECT count(*)

    INTO v_r12_bom_operational_rout

    FROM bom.mlog$_bom_operational_rout;

  SELECT count(*)

    INTO v_r12_bom_substitute_compo

    FROM bom.mlog$_bom_substitute_compo;

  SELECT count(*)

    INTO v_r12_eam_wo_relationships

    FROM eam.mlog$_eam_wo_relationships;

  SELECT count(*)

    INTO v_r12_mtl_reservations

    FROM inv.mlog$_mtl_reservations;

  SELECT count(*) INTO v_r12_mtl_supply FROM inv.mlog$_mtl_supply;

  SELECT count(*)

    INTO v_r12_bom_res_instance_cha

    FROM bom.mlog$_bom_res_instance_cha;

  SELECT count(*)

    INTO v_r12_mtl_txn_request_line

    FROM inv.mlog$_mtl_txn_request_line;

  SELECT count(*)

    INTO v_r12_wip_operation_networ

    FROM wip.mlog$_wip_operation_networ;

  SELECT count(*) INTO v_r12_wsh_trips FROM wsh.mlog$_wsh_trips;

  SELECT count(*) INTO v_r12_wsh_trip_stops FROM wsh.mlog$_wsh_trip_stops;

  SELECT count(*)

    INTO v_r12_wsm_copy_requirement

    FROM wsm.mlog$_wsm_copy_requirement;

 

  INSERT INTO rcs_analysis_r12_temp

    (script_date,

     ahl_schedule_materia,

     bom_operation_sequen,

     wip_operation_resour,

     wsm_copy_op_networks,

     wsm_copy_op_resource,

     wsm_copy_op_resource1,

     po_change_requests,

     wip_operations,

     wip_op_resource_inst,

     wip_repetitive_sched,

     bom_structures_b,

     mtl_material_transac,

     mtl_system_items_b,

     mtl_user_demand,

     mtl_user_supply,

     mrp_forecast_items,

     po_acceptances,

     wip_requirement_oper,

     wip_sub_operation_re,

     bom_operation_resour,

     bom_resource_changes,

     bom_sub_operation_re,

     mtl_item_categories,

     mrp_forecast_designa,

     wip_flow_schedules,

     wip_repetitive_items,

     wsm_copy_operations,

     bom_components_b,

     mrp_forecast_dates,

     mrp_schedule_dates,

     oe_order_lines_all,

     wip_lines,

     bom_operation_networ,

     mtl_demand,

     mtl_onhand_quantitie,

     po_supplier_item_cap,

     wip_discrete_jobs,

     bom_operational_rout,

     bom_substitute_compo,

     eam_wo_relationships,

     mtl_reservations,

     mtl_supply,

     bom_res_instance_cha,

     mtl_txn_request_line,

     wip_operation_networ,

     wsh_trips,

     wsh_trip_stops,

     wsm_copy_requirement)

  VALUES

    (v_script_date,

     v_r12_ahl_schedule_materia,

     v_r12_bom_operation_sequen,

     v_r12_wip_operation_resour,

     v_r12_wsm_copy_op_networks,

     v_r12_wsm_copy_op_resource,

     v_r12_wsm_copy_op_resource1,

     v_r12_po_change_requests,

     v_r12_wip_operations,

     v_r12_wip_op_resource_inst,

     v_r12_wip_repetitive_sched,

     v_r12_bom_structures_b,

     v_r12_mtl_material_transac,

     v_r12_mtl_system_items_b,

     v_r12_mtl_user_demand,

     v_r12_mtl_user_supply,

     v_r12_mrp_forecast_items,

     v_r12_po_acceptances,

     v_r12_wip_requirement_oper,

     v_r12_wip_sub_operation_re,

     v_r12_bom_operation_resour,

     v_r12_bom_resource_changes,

     v_r12_bom_sub_operation_re,

     v_r12_mtl_item_categories,

     v_r12_mrp_forecast_designa,

     v_r12_wip_flow_schedules,

     v_r12_wip_repetitive_items,

     v_r12_wsm_copy_operations,

     v_r12_bom_components_b,

     v_r12_mrp_forecast_dates,

     v_r12_mrp_schedule_dates,

     v_r12_oe_order_lines_all,

     v_r12_wip_lines,

     v_r12_bom_operation_networ,

     v_r12_mtl_demand,

     v_r12_mtl_onhand_quantitie,

     v_r12_po_supplier_item_cap,

     v_r12_wip_discrete_jobs,

     v_r12_bom_operational_rout,

     v_r12_bom_substitute_compo,

     v_r12_eam_wo_relationships,

     v_r12_mtl_reservations,

     v_r12_mtl_supply,

     v_r12_bom_res_instance_cha,

     v_r12_mtl_txn_request_line,

     v_r12_wip_operation_networ,

     v_r12_wsh_trips,

     v_r12_wsh_trip_stops,

     v_r12_wsm_copy_requirement);

 

END;

/

 

commit;