A query that is part of the fcm_calcs_load function was choosing a bad plan (or plans). The specific query was sql ID 5nbc9s91ak20y and the full text of the query is included below. Jithu identified that there were over 180 plans for this query. He locked in a good plan and loading speeds quickly returned to normal.

As a result of the delay 259 businesses took longer than 2 hours to load, the longest load time was 2.5 hours

Slack Thread: https://averobuzz.slack.com/archives/C40RKCMAT/p1545221213102000

Query:

INSERT INTO CALC_LOG (KITCHEN_NUM, CALC_NUM, PRODUCT_NUM, DATE_CALCED, CALC_AMT, CALC_UOFM) WITH KITCH AS ( SELECT KITCHEN_NUM, :B3 C_CALC_NUM, START_PERIOD_DATE, TO_NUMBER(TO_CHAR(START_PERIOD_DATE,'yyyymmdd')) START_PERIOD , END_PERIOD_DATE , TO_NUMBER(TO_CHAR(END_PERIOD_DATE,'yyyymmdd')) END_PERIOD , TRUNC(:B2 ) - 1 YESTERDAY_DATE FROM ( SELECT KITCHEN_NUM, TRUNC(:B2 ) - 90 START_PERIOD_DATE, TRUNC(:B2 ) END_PERIOD_DATE FROM KITCHEN_MASTER WHERE KITCHEN_NUM = :B1 )) , BIZ AS (SELECT BUSINESS_NUM, BKM.KITCHEN_NUM FROM BUSINESS_KITCHEN_MAP BKM , KITCH K WHERE K.KITCHEN_NUM = BKM.KITCHEN_NUM), INV_RES AS ( SELECT DISTINCT R.KITCHEN_NUM, NVL(P.MERGED_PRODUCT_NUM,R.PRODUCT_NUM) MATCH_PRODUCT_NUM, MAX(UPDATE_DATE) OVER (PARTITION BY R.KITCHEN_NUM, NVL(P.MERGED_PRODUCT_NUM,R.PRODUCT_NUM)) LATEST_INVENTORY_DATETIME, MAX(TRUNC(UPDATE_DATE)) OVER (PARTITION BY R.KITCHEN_NUM, NVL(P.MERGED_PRODUCT_NUM,R.PRODUCT_NUM)) LATEST_INVENTORY_DATE, COUNT() OVER (PARTITION BY R.KITCHEN_NUM, NVL(P.MERGED_PRODUCT_NUM,R.PRODUCT_NUM)) COUNT_INVENTORIES, PM.INVENTORY_UOFM , TRUNC(SYSDATE) - MAX(TRUNC(UPDATE_DATE)) OVER (PARTITION BY R.KITCHEN_NUM, NVL(P.MERGED_PRODUCT_NUM,R.PRODUCT_NUM)) DAYS_SINCE_LAST_INVENTORY FROM INVENTORY_RESULTS R , KITCH K, PRODUCTS P, PRODUCTS PM WHERE TRUNC(UPDATE_DATE) BETWEEN TO_DATE(START_PERIOD,'yyyymmdd') AND TO_DATE (END_PERIOD,'yyyymmdd') AND R.KITCHEN_NUM = K.KITCHEN_NUM AND NVL(WORKSHEET_NUM,0) <> 0 AND R.PRODUCT_NUM = P.PRODUCT_NUM AND P.MATCH_PRODUCT_NUM = PM.PRODUCT_NUM ) , PO_RES AS ( SELECT DISTINCT R.KITCHEN_NUM, P.MERGED_PRODUCT_NUM, NVL(P.MERGED_PRODUCT_NUM,R.PRODUCT_NUM) MATCH_PRODUCT_NUM, MAX(TRUNC(UPDATE_DATE)) OVER (PARTITION BY R.KITCHEN_NUM, NVL(P.MERGED_PRODUCT_NUM,R.PRODUCT_NUM)) LATEST_RECEIVED_DATE, COUNT() OVER (PARTITION BY R.KITCHEN_NUM, NVL(P.MERGED_PRODUCT_NUM,R.PRODUCT_NUM)) COUNT_RECEIVED, PM.INVENTORY_UOFM, END_PERIOD_DATE, YESTERDAY_DATE FROM INVENTORY_RESULTS R , KITCH K , PRODUCTS P, PRODUCTS PM WHERE TRUNC(UPDATE_DATE) BETWEEN TO_DATE(START_PERIOD,'yyyymmdd') AND TO_DATE (END_PERIOD,'yyyymmdd') AND R.KITCHEN_NUM = K.KITCHEN_NUM AND PO_NUM IS NOT NULL AND QTY > 0 AND R.PRODUCT_NUM = P.PRODUCT_NUM AND P.MATCH_PRODUCT_NUM = PM.PRODUCT_NUM) , WHICH_FORMULA AS (SELECT DISTINCT P.KITCHEN_NUM, P.MATCH_PRODUCT_NUM, CASE WHEN NVL(COUNT_INVENTORIES,0) > 0 AND COUNT_RECEIVED > 0 THEN ( CASE WHEN DAYS_SINCE_LAST_INVENTORY < :B5 THEN 'I' ELSE CASE WHEN (SYSDATE-LATEST_RECEIVED_DATE) < :B4 OR LATEST_RECEIVED_DATE > LATEST_INVENTORY_DATE THEN 'P' ELSE 'I' END END ) ELSE CASE WHEN NVL(COUNT_INVENTORIES,0) = 0 AND COUNT_RECEIVED >= 1 THEN 'P' ELSE 'N' END END CALC_TYPE, CASE WHEN NVL(COUNT_INVENTORIES,0) > 0 AND COUNT_RECEIVED > 0 THEN I.LATEST_INVENTORY_DATE ELSE LATEST_RECEIVED_DATE END LATEST_DATE, P.LATEST_RECEIVED_DATE, COUNT_RECEIVED , LATEST_INVENTORY_DATETIME, LATEST_INVENTORY_DATE, NVL(COUNT_INVENTORIES,0) COUNT_INVENTORIES , P.INVENTORY_UOFM , END_PERIOD_DATE, YESTERDAY_DATE , DAYS_SINCE_LAST_INVENTORY, (CASE WHEN SYSDATE - LATEST_RECEIVED_DATE < :B4 THEN 1 ELSE 0 END) RECENT_RECD FROM PO_RES P, INV_RES I WHERE I.MATCH_PRODUCT_NUM (+) = P.MATCH_PRODUCT_NUM UNION ALL SELECT DISTINCT I.KITCHEN_NUM, I.MATCH_PRODUCT_NUM, CASE WHEN NVL(COUNT_INVENTORIES,0) > 1 AND NVL(COUNT_RECEIVED,0) = 0 THEN 'I' ELSE 'N' END CALC_TYPE, CASE WHEN NVL(COUNT_INVENTORIES,0) > 0 THEN I.LATEST_INVENTORY_DATE ELSE LATEST_RECEIVED_DATE END LATEST_DATE, P.LATEST_RECEIVED_DATE, COUNT_RECEIVED , LATEST_INVENTORY_DATETIME, LATEST_INVENTORY_DATE, NVL(COUNT_INVENTORIES,0) COUNT_INVENTORIES , I.INVENTORY_UOFM , END_PERIOD_DATE, YESTERDAY_DATE , DAYS_SINCE_LAST_INVENTORY, (CASE WHEN SYSDATE - LATEST_RECEIVED_DATE < :B4 THEN 1 ELSE 0 END) RECENT_RECD FROM PO_RES P, INV_RES I WHERE I.MATCH_PRODUCT_NUM = P.MATCH_PRODUCT_NUM (+) AND P.MATCH_PRODUCT_NUM IS NULL ) , INVS AS (SELECT DISTINCT KITCHEN_NUM, MATCH_PRODUCT_NUM, (CASE WHEN DAYS_SINCE_LAST_INVENTORY < :B5 THEN SUM(CONV_QTY) OVER (PARTITION BY KITCHEN_NUM, MATCH_PRODUCT_NUM) ELSE 0 END) INV_QTY , LATEST_INVENTORY_DATETIME FROM( SELECT WF.KITCHEN_NUM, WF.MATCH_PRODUCT_NUM, R.PRODUCT_NUM, R.QTY, R.UOFM , DECODE(UOFM,P.INVENTORY_UOFM,QTY, QTYUOFM_CONV(R.PRODUCT_NUM, UOFM, P.INVENTORY_UOFM)) CONV_QTY, LATEST_INVENTORY_DATETIME , MIN (DAYS_SINCE_LAST_INVENTORY) OVER (PARTITION BY WF.KITCHEN_NUM, WF.MATCH_PRODUCT_NUM) DAYS_SINCE_LAST_INVENTORY FROM INVENTORY_RESULTS R, WHICH_FORMULA WF, PRODUCTS P WHERE WF.CALC_TYPE='I' AND LATEST_INVENTORY_DATETIME = UPDATE_DATE AND WF.MATCH_PRODUCT_NUM = P.MATCH_PRODUCT_NUM AND P.PRODUCT_NUM = R.PRODUCT_NUM AND WORKSHEET_NUM IS NOT NULL )) , WASTE_RES AS (SELECT W.KITCHEN_NUM, W.PRODUCT_NUM, SUM(W.WASTE_QTYUOFM_CONV(W.PRODUCT_NUM, WASTE_UOM, INVENTORY_UOFM)) WASTE_QTY FROM WASTE_LOG W, KITCH, WHICH_FORMULA PD WHERE PD.MATCH_PRODUCT_NUM = W.PRODUCT_NUM AND TRUNC(DATE_ADDED) BETWEEN LATEST_DATE AND KITCH.YESTERDAY_DATE AND KITCH.KITCHEN_NUM = W.KITCHEN_NUM GROUP BY W.KITCHEN_NUM, W.PRODUCT_NUM ) , LC AS (SELECT BIZ.KITCHEN_NUM, PD.MATCH_PRODUCT_NUM, SUM(DAY_GROSS_SALES) GROSS_SALES FROM LOAD_CALENDAR LC , BIZ , KITCH, WHICH_FORMULA PD WHERE BIZ.BUSINESS_NUM = LC.BUSINESS_NUM AND BUSINESS_DAY BETWEEN TO_NUMBER(TO_CHAR(LATEST_DATE,'YYYYMMDD')) AND END_PERIOD GROUP BY BIZ.KITCHEN_NUM , PD.MATCH_PRODUCT_NUM ) , UR AS (SELECT DISTINCT CL.KITCHEN_NUM, CL.PRODUCT_NUM, SUM(CASE WHEN CALC_AMT < 0 THEN 0 ELSE DECODE(CALC_UOFM,WF.INVENTORY_UOFM,CALC_AMT, CALC_AMTUOFM_CONV(CL.PRODUCT_NUM, CALC_UOFM, WF.INVENTORY_UOFM)) END ) OVER (PARTITION BY CL.PRODUCT_NUM) CONV_AMT, MAX(CALC_UOFM) OVER (PARTITION BY CL.PRODUCT_NUM) CALC_UOFM FROM CALC_LOG CL, WHICH_FORMULA WF WHERE CALC_NUM = 10 AND WF.MATCH_PRODUCT_NUM = CL.PRODUCT_NUM ), POS_RES AS (SELECT DISTINCT KITCHEN_NUM, MATCH_PRODUCT_NUM, INVENTORY_UOFM, CALC_TYPE, SUM(CONV_QTY) OVER (PARTITION BY KITCHEN_NUM, MATCH_PRODUCT_NUM) QTY FROM ( SELECT PD.KITCHEN_NUM, R.PO_NUM, R.PRODUCT_NUM, P.PRODUCT_NUM, P.MATCH_PRODUCT_NUM, QTY, UOFM, TRUNC(UPDATE_DATE) RECEIVED_DATE, PD.LATEST_DATE, CALC_TYPE, PD.LATEST_RECEIVED_DATE, LATEST_INVENTORY_DATE, PD.INVENTORY_UOFM , DECODE(UOFM,PD.INVENTORY_UOFM,QTY, QTYUOFM_CONV(R.PRODUCT_NUM, UOFM, PD.INVENTORY_UOFM)) CONV_QTY, PD.YESTERDAY_DATE, PD.END_PERIOD_DATE , RECENT_RECD FROM INVENTORY_RESULTS R, WHICH_FORMULA PD, PRODUCTS P WHERE PD.MATCH_PRODUCT_NUM = P.MATCH_PRODUCT_NUM AND (P.MATCH_PRODUCT_NUM = R.PRODUCT_NUM OR R.PRODUCT_NUM = P.PRODUCT_NUM) AND PD.KITCHEN_NUM = R.KITCHEN_NUM AND PO_NUM IS NOT NULL AND QTY > 0 AND CALC_TYPE IN ('P','I') AND TRUNC(UPDATE_DATE) >= LATEST_DATE ) WHERE RECEIVED_DATE BETWEEN LATEST_DATE AND END_PERIOD_DATE) , OUTSTAND_ORD AS ( SELECT MATCH_PRODUCT_NUM, SUM (CONV_QTY1) QTY, MIN (CONV_UOFM) UOFM FROM (SELECT MATCH_PRODUCT_NUM, OPS.PRODUCT_NUM, QTY, UOFM, CNTPROD, CNTUOFM, DECODE (CNTPROD,1, QTY, DECODE (CNTUOFM,1, QTY, DECODE (OPS.UOFM,'CS', QTY, QTY * UOFM_CONV (OPS.PRODUCT_NUM, OPS.UOFM, 'CS')))) CONV_QTY1, DECODE (CNTPROD, 1, OPS.UOFM, DECODE (CNTUOFM, 1, UOFM, 'CS')) CONV_UOFM FROM (SELECT PRODUCT_NUM, QTY, UOFM, COUNT () OVER (PARTITION BY PRODUCT_NUM) CNTPROD, COUNT (DISTINCT UOFM) OVER (PARTITION BY PRODUCT_NUM) CNTUOFM FROM KITCH K, ORDER_MASTER OM, ORDER_PRODUCT_DETAIL OPD, VENDOR_NEXT_DELIVERIES VND WHERE K.KITCHEN_NUM = OM.KITCHEN_NUM AND OM.PO_NUM = OPD.PO_NUM AND OM.VENDOR_NUM = VND.VENDOR_NUM AND OM.STATUS_NUM BETWEEN 10 AND 24 AND NVL (DELIVERY_DATE, NEXT_DELIV) > TRUNC (SYSDATE)) OPS, PRODUCTS P WHERE P.PRODUCT_NUM = OPS.PRODUCT_NUM) PPS GROUP BY PPS.MATCH_PRODUCT_NUM) , IDR_RECIPE AS (SELECT KITCH.KITCHEN_NUM, IDR.ITEM_NUM, RPI.RECIPE_NUM, BUSINESS_DAY, SUM(ITEM_COUNT) ITEM_COUNT FROM KITCH, BIZ, RECIPES_POS_ITEMS RPI, ITEM_DAY_ROLLUP IDR WHERE IDR.BUSINESS_NUM = BIZ.BUSINESS_NUM AND RPI.BUSINESS_NUM = BIZ.BUSINESS_NUM AND IDR.BUSINESS_NUM = RPI.BUSINESS_NUM AND IDR.ITEM_NUM = RPI.ITEM_NUM AND IDR.BUSINESS_DAY >= TO_NUMBER(TO_CHAR(SYSDATE - 90,'yyyymmdd')) AND IDR.BUSINESS_DAY BETWEEN START_PERIOD AND END_PERIOD GROUP BY KITCH.KITCHEN_NUM, IDR.ITEM_NUM, RPI.RECIPE_NUM, BUSINESS_DAY ) , RECIPE_QTY AS ( SELECT KITCHEN_NUM, MATCH_PRODUCT_NUM, INVENTORY_UOFM, SUM(ITEM_COUNTTHEOR_QTY) PROD_QTY FROM ( SELECT /*+ ORDERED INDEX (rpd) INDEX (p) */ RPD.KITCHEN_NUM, IDR.ITEM_NUM, PD.MATCH_PRODUCT_NUM, PD.INVENTORY_UOFM, SUM(ITEM_COUNT) ITEM_COUNT , AVG ( DECODE( RPD.PRICE,0,0,( (RPD.COST_AMT / RPD.PRICE ) * NVL (FCM.UOFM_CONV (RPD.PRODUCT_NUM, RPD.PRICE_UOFM, P.INVENTORY_UOFM), 1))) ) THEOR_QTY FROM WHICH_FORMULA PD , PRODUCTS P, IDR_RECIPE IDR, RECIPES2PRODUCTS RPD WHERE RPD.RECIPE_NUM = IDR.RECIPE_NUM AND RPD.KITCHEN_NUM = PD.KITCHEN_NUM AND COST_AMT > 0 AND IDR.BUSINESS_DAY >= TO_NUMBER(TO_CHAR(LATEST_DATE,'YYYYMMDD')) AND (RPD.PRODUCT_NUM = P.PRODUCT_NUM) AND PD.KITCHEN_NUM = P.KITCHEN_NUM AND PD.MATCH_PRODUCT_NUM = P.MATCH_PRODUCT_NUM AND IDR.BUSINESS_DAY BETWEEN RPD.EFFECTIVE_START_DAY_NUM AND NVL(RPD.EFFECTIVE_END_DAY_NUM,IDR.BUSINESS_DAY) GROUP BY RPD.KITCHEN_NUM, IDR.ITEM_NUM, PD.MATCH_PRODUCT_NUM, PD.INVENTORY_UOFM ) GROUP BY KITCHEN_NUM, MATCH_PRODUCT_NUM, INVENTORY_UOFM ) SELECT KITCHEN_NUM, CALC_NUM, PRODUCT_NUM, DATE_CALCED, CASE WHEN CALC_AMT_FRONT - NVL(CALC_AMT_END,0) < 0 THEN 0 ELSE CALC_AMT_FRONT - NVL(CALC_AMT_END,0) END AS CALC_AMT, CALC_UOFM FROM ( SELECT WF.KITCHEN_NUM, C_CALC_NUM CALC_NUM, WF.MATCH_PRODUCT_NUM PRODUCT_NUM, :B2 DATE_CALCED, CASE WHEN TRUNC(I.LATEST_INVENTORY_DATETIME) = TRUNC(SYSDATE) THEN NVL(I.INV_QTY,0) ELSE ((CASE WHEN WF.CALC_TYPE='I' THEN 1 ELSE 0 END)*NVL(I.INV_QTY,0))+ NVL(P.QTY,0) - NVL(WASTE_QTY,0) END CALC_AMT_FRONT, CASE WHEN RQ.PROD_QTY > ((NVL(GROSS_SALES,0)/1000)*NVL(CONV_AMT,0)) THEN RQ.PROD_QTY ELSE ((NVL(GROSS_SALES,0)/1000)*NVL(CONV_AMT,0)) END CALC_AMT_END, WF.INVENTORY_UOFM CALC_UOFM, RQ.PROD_QTY FROM POS_RES P, INVS I, WHICH_FORMULA WF, WASTE_RES WS, LC, UR, KITCH K, RECIPE_QTY RQ WHERE WF.MATCH_PRODUCT_NUM = I.MATCH_PRODUCT_NUM (+) AND WF.MATCH_PRODUCT_NUM = P.MATCH_PRODUCT_NUM (+) AND WS.PRODUCT_NUM (+) = WF.MATCH_PRODUCT_NUM AND LC.MATCH_PRODUCT_NUM (+) = WF.MATCH_PRODUCT_NUM AND UR.PRODUCT_NUM (+) = WF.MATCH_PRODUCT_NUM AND WF.MATCH_PRODUCT_NUM= RQ.MATCH_PRODUCT_NUM (+) )