NPI Create Similar Product Runs For A Long Time (Doc ID 1418269.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Demantra Demand Management - Version 7.3.0.1 and later
Information in this document applies to any platform.

Symptoms


Release 7.3.0.1

NPI Create Similar Product runs for 10 minutes per combination.

Step to duplicate
-----------------------
1. Run the method Create Similar Product on Product Id [lowest item level]
2. Method runs for more than 10 minutes for just one item_id and location_id
3. Merge statement runs on the background for a very long time

Research
--------------
AWR shows the following slow running sql

MERGE INTO SALES_DATA sales USING ( SELECT mdp_matrix.item_id item_id, mdp_matrix.location_id location_id, inputs.datet sales_date FROM inputs , mdp_matrix WHERE inputs.datet >= :maxSalesDate AND inputs.datet <= :leadEndDate AND mdp_matrix.item_id in (select unique item_id from mdp_matrix where t_ep_item_ep_id=:m_memberId) ) eligible_dates ON ( sales.item_id = eligible_dates.item_id AND sales.location_id = eligible_dates.location_id AND sales.sales_date = eligible_dates.sales_date ) WHEN NOT MATCHED THEN INSERT ( sales.item_id, sales.location_id, sales.sales_date ) VALUES ( eligible_dates.item_id, eligible_dates.location_id, eligible_dates.sales_date )

Elapsed Time (s) 2,373.34
Executions 2
Elapsed Time per Exec (s) 1,186.67
%Total 62.58
%CPU 54.07
%IO 49.72
SQL Id g8gd7sv60yacm
SQL Module JDBC Thin Client
SQL Text MERGE INTO SALES_DATA sales US...

This is similar to bug 12625055 but in this case, the customer is already on 7301 so the fixes from patch 9219488 should already be included.
Customer cannot reduce the 'lead' param [from init_params_0 table] to a lesser value because GMCR currently runs it at 82 weeks and is planning to change this to 104 Weeks.
Usually the first recommendation would be to run rebuild_schema but the customer's procedure does not finish (SR 3-2735336561)
Requested to manually run exec rebuild_schema('1');  but this too ran for 30hrs without completing. When the customer looked into the sql sessions this is the statement that was running for a long time

ALTER TABLE SALES_DATA_ORIGINAL MOVE TABLESPACE TS_SALES_DATA NOLOGGING

So customer wrote its own procedure to gather schema stats.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms