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);
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;