INVDVDSD Item Supply Demand Form Performance/Error: APP-INV-05647: The Remote Call Failed No Manager Was Available And/Or APP-INV-05649 The Remote Call failed due to a time out After All Rollup Patches Applied
(Doc ID 1624856.1)
Last updated on FEBRUARY 03, 2019
Oracle Materials Requirement Planning - Version 18.104.22.168 and later Information in this document applies to any platform.
On : 22.214.171.124 version, Item Supply Demand
Users used to not be on any INV_SD rollup patches. They applied INV_SD Rollup #7 Patch 7350872 on 12-OCT-2013. In Test and PROD they're running into severe performance problems where many times, but not all, the item supply demand form will query for 5 minutes (ie 300 seconds as per profile INV: RPC Timeout). It should be noted that sometimes the error does occur after 8-10 seconds. See errors below and related times for each. This can happen for any item in any organization so it's not isolated. We abandoned Test as it appeared to us that they had severe I/O problem and went back to working in PROD
Please note RDBMS is 126.96.36.199.0 and O/S is IBM AIX
inldsd.ppc 115.58.115100.19 - latest is .21 but for different issues - confirms they're on INVSD RUP#7 though inudsd.ppc 115.38.115100.8 - latest is .11 - same as above
There seems to be no problem for the most part in the morning using the form. Problem seems to happen more often than not in the afternoon
Here are the two errors they run into:
"APP-INV-05647: The Remote Call failed because no manager was available." This occurs after 5 minutes and is the error they see most of the time or APP-INV-05649 The Remote Call failed due to a time out - This doesn't occur as much and was more related to Test than PROD
Things we tried:
1. Increasing INV Remote Procedure Manager processes from 3 to 5 to 10 to 15 to 20 and bouncing they still run into the problem more often than not. They say that sometimes the form works in the morning but is then a problem for the rest of the afternoon
2. We found relinking was done on INCTM but we did it again and confirmed the key file versions but that didn't help
3. We found 6379665 records with line_status = 5 (closed) in table mtl_txn_request_lines. We got that down to under 100K and retested but that didn't help. We also confirmed they have all the indexes on this same table (U1, U2, N1 thru N10). We also rebuilt the indexes and gathered table statistics at 30% but it did not help
4. We found no useful information - in fact very little was written in INV debug log.
5. Running the diagnostic from Note 404459.1 gets hung and will not complete nor provide output as far as the output. We did run the diagnostic with performance = Yes and found it hanging on this SQL
SQL ID: 5q6gb1byk8m9f Plan Hash: 4048796650
SELECT D.RESERVATION_TYPE, DECODE(D.DEMAND_SOURCE_TYPE,2, DECODE(D.RESERVATION_TYPE,1,2,3,23,9),8,DECODE( D.RESERVATION_TYPE,1,21,22), D.DEMAND_SOURCE_TYPE) , DECODE(D.DEMAND_SOURCE_TYPE,8,2, D.DEMAND_SOURCE_TYPE) , D.DEMAND_SOURCE_HEADER_ID , 1 , -1*(D.PRIMARY_UOM_QUANTITY-D.TOTAL_RESERVATION_QUANTITY-D.COMPLETED_QUANTIT Y) , TO_NUMBER(TO_CHAR( DECODE(NVL(D.MFG_LEAD_TIME,0), 0, C.PRIOR_DATE, DECODE( I.BOM_ITEM_TYPE, 1, C.PRIOR_DATE, MRP_CALENDAR.DATE_OFFSET ( D.ORGANIZATION_ID, 1, C.PRIOR_DATE, -1*(D.MFG_LEAD_TIME)))) , 'J')) , CHARTOROWID('0') , V.INVENTORY_ITEM_ID , V.ORGANIZATION_ID , DECODE(D.RESERVATION_TYPE, 2, TO_NUMBER(:B1 ), C.PRIOR_SEQ_NUM) , D.DEMAND_SOURCE_NAME FROM MTL_GROUP_ITEM_ATPS_VIEW V, MTL_PARAMETERS P , MTL_SYSTEM_ITEMS I , MTL_ATP_RULES R , BOM_CALENDAR_DATES C , MRP_DEMAND_OM_RESERVATIONS_V D WHERE D.OPEN_FLAG = 'Y' AND D.RESERVATION_TYPE != 2 AND R.PAST_DUE_DEMAND_CUTOFF_FENCE IS NULL AND D.ORGANIZATION_ID = V.ORGANIZATION_ID AND D.PRIMARY_UOM_QUANTITY > (D.TOTAL_RESERVATION_QUANTITY+ D.COMPLETED_QUANTITY) AND D.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID AND ( D.VISIBLE_DEMAND_FLAG = 'Y' OR ( NVL(D.VISIBLE_DEMAND_FLAG,'N')='N' AND D.ATO_LINE_ID IS NOT NULL AND NOT EXISTS ( SELECT NULL FROM OE_ORDER_LINES_ALL OOL, MTL_DEMAND MD WHERE TO_CHAR(OOL.LINE_ID) =MD.DEMAND_SOURCE_LINE AND OOL.ATO_LINE_ID = D.ATO_LINE_ID AND OOL.ITEM_TYPE_CODE = 'CONFIG' AND MD.RESERVATION_TYPE IN (2,3) ) AND NOT EXISTS ( SELECT 1 FROM WIP_FLOW_SCHEDULES, OE_ORDER_LINES_ALL OOL WHERE DEMAND_SOURCE_LINE = TO_CHAR(OOL.LINE_ID) AND SCHEDULED_FLAG = 1 AND ( STATUS = 1 OR ( STATUS = 2 AND QUANTITY_COMPLETED > 0 ) ) AND ROWNUM = 1 ) ) ) AND D.RESERVATION_TYPE != DECODE(NVL(V.N_COLUMN1,R.INCLUDE_ONHAND_AVAILABLE), 2 , 2, -1) AND D.RESERVATION_TYPE != DECODE(R.DEMAND_CLASS_ATP_FLAG, 1, 2, -1) AND D.DEMAND_SOURCE_TYPE != DECODE(R.INCLUDE_SALES_ORDERS, 2, 2, -1) AND D.DEMAND_SOURCE_TYPE != DECODE(R.INCLUDE_INTERNAL_ORDERS, 2, 8, -1) AND EXISTS ( SELECT NULL FROM MTL_SECONDARY_INVENTORIES S WHERE S.ORGANIZATION_ID =D.ORGANIZATION_ID AND S.INVENTORY_ATP_CODE = DECODE(R.DEFAULT_ATP_SOURCES,1, 1,NULL, 1, S.INVENTORY_ATP_CODE) AND S.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES, 2,1, S.AVAILABILITY_TYPE) AND S.SECONDARY_INVENTORY_NAME = D.SUBINVENTORY UNION ALL SELECT NULL FROM DUAL WHERE D.SUBINVENTORY IS NULL ) AND V.AVAILABLE_TO_ATP = 1 AND V.ATP_RULE_ID = R.RULE_ID AND V.ATP_GROUP_ID = :B2 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID AND NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE( R.DEMAND_CLASS_ATP_FLAG, 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS, '@@@')),NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))) AND P.ORGANIZATION_ID = NVL(V.ATP_CALENDAR_ORGANIZATION_ID,V.ORGANIZATION_ID) AND C.PRIOR_SEQ_NUM >= DECODE(D.RESERVATION_TYPE,2,C.PRIOR_SEQ_NUM,DECODE( R.PAST_DUE_DEMAND_CUTOFF_FENCE, NULL, C.PRIOR_SEQ_NUM, :B1 - R.PAST_DUE_DEMAND_CUTOFF_FENCE)) AND C.PRIOR_SEQ_NUM < DECODE(D.RESERVATION_TYPE,2,C.PRIOR_SEQ_NUM+1,NVL( :B1 + (DECODE( R.INFINITE_SUPPLY_FENCE_CODE,1, I.CUMULATIVE_TOTAL_LEAD_TIME,2, I.CUM_MANUFACTURING_LEAD_TIME,3, I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+ I.POSTPROCESSING_LEAD_TIME,4, R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM +1)) AND P.CALENDAR_CODE = C.CALENDAR_CODE AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE) AND V.INVENTORY_ITEM_ID = DECODE(D.RESERVATION_TYPE,1,DECODE( D.PARENT_DEMAND_ID, NULL, V.INVENTORY_ITEM_ID,-1),2,V.INVENTORY_ITEM_ID,3, DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1,V.INVENTORY_ITEM_ID,DECODE( R.INCLUDE_NONSTD_WIP_RECEIPTS, 1,V.INVENTORY_ITEM_ID, -1)),-1) AND V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_SALES_ORDERS, 2,DECODE( R.INCLUDE_INTERNAL_ORDERS, 2,DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2,DECODE( R.INCLUDE_NONSTD_WIP_RECEIPTS, 2,DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 2, - 1,V.INVENTORY_ITEM_ID),V.INVENTORY_ITEM_ID),V.INVENTORY_ITEM_ID), V.INVENTORY_ITEM_ID),V.INVENTORY_ITEM_ID)
9.2 Complete listing of RDBMS settings from v$parameter: aq_tm_processes = 1 (maybe this should be 2 ???) job_queue_processes = 12 (Should be higher perhaps ???)
B. From AWR report - appears to be from before the trace was done above (up to 18:00:00 on 03-DEC but traces run around 18:43:00 so need another run for this time period
STEPS ----------------------- The issue can be reproduced at will with the following steps: 1. Inventory: On-Hand Availability > 2. Item Supply/Demand - some times during the day it works fine but suddenly that error message appears: "RFC failure because no managers available" and nobody can inquiry supply/demand information
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!