Andy writes: “Operations reported that a query was taking a long time.  Even the 'developers' of this query didn't know why it was taking a long time.”

I tell ya, folks… some submissions, you just set down and back away slowly… then hunt up a magnifying glass and a bottle of aspirin.


SELECT /*+ optimizer_features_enable('9.2.0') */ N1.ORDER_ID AS ORDER_ID, N1.N1_STATUS_ID AS N1_STATUS_ID, N1.SKU_ID AS SKU_ID, O.SITE_ID AS SITE_ID, O.ORDER_DT AS ORDER_DT, O.D_TOTAL_PRICE AS PRICE, RS.LEGAL_NAME AS RESELLER_LEGAL_NAME, PRS.LEGAL_NAME AS PARENT_RESELLER_LEGAL_NAME, S.COMPANY_NAME AS SITE_COMPANY_NAME, O.ORDER_STATE_ID AS ORDER_STATE_ID, ITEM.COUNTRY AS ITEM_COUNTRY, E.COUNTRY AS E_COUNTRY, C.CUST_SERVICE_REGION AS REGION, RS.PARTNER_LEVEL, O.FOLLOWUP_DT, N1.APPROVER_CONFIRM_DT, 'N' AS REISSUE_IND, sign(nvl(sum(distinct(R.ORDER_ID * ( -(SIGN(NVL(R.OVERRIDE_PERSON_ID, 0)) -1)) )), 0)) AS RSRC_CTRL_VIOLATION, max(decode( p.email_address, :1, 'Y', 'N' )) AS MANUAL_APPROVAL_IND, max(nvl(R.RESOURCE_CTRL_TYPE_ID, 0)) AS RESOURCE_CTRL_TYPE_ID , nvl(CFA.POLL_STATUS, 'N/A') AS POLL_STATUS , CFA.APPROVE_METHOD FROM ORDERS_N1 N1, ORDERS O, ENTERPRISE E, ENTERPRISE RS, ENTERPRISE PRS, SITE S, ORDERS_RSRC_CTRL R, PERSON P, COUNTRY C, SITE_ITEM ITEM , C ERT_FILE_APPROVE_DV_T CFA WHERE N1.ORDER_ID = O.ORDER_ID AND N1.N1_STATUS_ID <> 3 AND N1.N1_STATUS_ID <> 10 AND O.CUST_ENTERPRISE_ID = E.ENTERPRISE_ID AND O.RESELLER_ENTERPRISE_ID = RS.ENTERPRISE_ID AND RS.RESELLER_REFERRED_BY_ENT_ID = PRS.ENTERPRISE_ID AND O.SITE_ID = S.SITE_ID AND O.ORDER_ID = R.ORDER_ID(+) AND O.ORDER_COMPLETE_IND = 'N' AND O.ORDER_ID = ITEM.ORDER_ID AND ITEM.ITEM_ID = CFA.ITEM_ID (+) AND (ITEM.COUNTRY IS NOT NULL AND C.COUNTRY_CD = ITEM.COUNTRY OR ITEM.COUNTRY IS NULL AND C.COUNTRY_CD = E.COUNTRY) AND O.ORDER_DT >= to_date(:2, 'mmddyyyyhh24miss') AND O.ORDER_DT <= to_date(:3, 'mmddyyyyhh24miss') AND BITAND(NVL(RS.ENTERPRISE_TYPE_BITMASK, 0), 32) = 0 AND C.CUST_SERVICE_REGION = :4 AND (P.PERSON_ID = ( select max(person_id) from contact_site cs where cs.site_id = o.site_id and cs.contact_type_id = decode( N1.SKU_ID , 5, 19 , 10, 20 , 11, 21 , 15, 25 , 36, 29 , 38, 33 , 40, 37 , 42, 41 , 44, 45 , 46, 65 , 140, 61 , 179, 69 , 185, 73 , 191, 77 , 310, 117 )) OR P.PERSON_ID = (SELECT PERSON_ID FROM ORDER_CONTACT_T WHERE ORDER_ID = o.ORDER_ID AND CONTACT_TYPE = 'COMPANY_APPROVER') OR (N1.SKU_ID = 191 AND P.PERSON_ID = (SELECT MAX(PERSON_ID) FROM PERSON)) ) AND O.D_PRIMARY_SKU_ID <> 220 GROUP BY N1.ORDER_ID, N1.N1_STATUS_ID, N1.SKU_ID, O.SITE_ID, O.ORDER_DT, O.D_TOTAL_PRICE, RS.LEGAL_NAME, PRS.LEGAL_NAME, S.COMPANY_NAME, O.ORDER_STATE_ID, ITEM.COUNTRY, E.COUNTRY, C.CUST_SERVICE_REGION, RS.PARTNER_LEVEL, O.FOLLOWUP_DT, N1.APPROVER_CONFIRM_DT, CFA.POLL_STATUS , CFA.APPROVE_METHOD UNION SELECT /*+ optimizer_features_enable('9.2.0') */ N1.ORDER_ID AS ORDER_ID, N1.N1_STATUS_ID AS N1_STATUS_ID, N1.SKU_ID AS SKU_ID, O.SITE_ID AS SITE_ID, O.ORDER_DT AS ORDER_DT, O.D_TOTAL_PRICE AS PRICE, RS.LEGAL_NAME AS RESELLER_LEGAL_NAME, PRS.LEGAL_NAME AS PARENT_RESELLER_LEGAL_NAME, S.COMPANY_NAME AS SITE_COMPANY_NAME, O.REISSUE_ORDER_STATE_ID AS ORDER_STATE_ID, ITEM.COUNTRY AS ITEM_COUNTRY, E.COUNTRY AS E_COUNTR Y, C.CUST_SERVICE_REGION AS REGION, RS.PARTNER_LEVEL, O.FOLLOWUP_DT, N1.APPROVER_CONFIRM_DT, 'Y' AS REISSUE_IND, sign(nvl(sum(distinct(R.ORDER_ID * ( -(SIGN(NVL(R.OVERRIDE_PERSON_ID, 0)) -1)) )), 0)) AS RSRC_CTRL_VIOLATION, max(decode( p.email_address, :5, 'Y', 'N' )) AS MANUAL_APPROVAL_IND, max(nvl(R.RESOURCE_CTRL_TYPE_ID, 0)) AS RESOURCE_CTRL_TYPE_ID , nvl(CFA.POLL_STATUS, 'N/A') AS POLL_STATUS , CFA.APPROVE_METHOD FROM ORDERS_N1 N1, ORDERS O, ENTERPRISE E, ENTERPRISE RS, ENTERPRISE PRS, SITE S, ORDERS_RSRC_CTRL R, PERSON P, COUNTRY C, SITE_ITEM ITEM , ITEM_FILE_APPROVE_DV_T CFA WHERE N1.ORDER_ID = O.ORDER_ID AND O.CUST_ENTERPRISE_ID = E.ENTERPRISE_ID AND O.RESELLER_ENTERPRISE_ID = RS.ENTERPRISE_ID AND RS.RESELLER_REFERRED_BY_ENT_ID = PRS.ENTERPRISE_ID AND O.SITE_ID = S.SITE_ID AND O.ORDER_ID = R.ORDER_ID(+) AND O.ORDER_ID = ITEM.ORDER_ID AND ITEM.ITEM_ID = CFA.ITEM_ID (+) AND ITEM.ITEM_STATUS_ID = 5 AND (ITEM.COUNTRY IS NOT NULL AND C.COUNTRY_CD = ITEM.COUNTRY OR C ERT.COUNTRY IS NULL AND C.COUNTRY_CD = E.COUNTRY) AND ITEM.START_DT >= to_date(:6, 'mmddyyyyhh24miss') AND ITEM.START_DT <= to_date(:7, 'mmddyyyyhh24miss') AND BITAND(NVL(RS.ENTERPRISE_TYPE_BITMASK, 0), 32) = 0 AND C.CUST_SERVICE_REGION = :8 AND (P.PERSON_ID = ( select max(person_id) from contact_site cs where cs.site_id = o.site_id and cs.contact_type_id = decode( N1.SKU_ID , 5, 19 , 10, 20 , 11, 21 , 15, 25 , 36, 29 , 38, 33 , 40, 37 , 42, 41 , 44, 45 , 46, 65 , 140, 61 , 179, 69 , 185, 73 , 191, 77 , 310, 117 )) OR P.PERSON_ID = (SELECT PERSON_ID FROM ORDER_CONTACT_T WHERE ORDER_ID = o.ORDER_ID AND CONTACT_TYPE = 'COMPANY_APPROVER') OR (N1.SKU_ID = 191 AND P.PERSON_ID = (SELECT MAX(PERSON_ID) FROM PERSON)) ) AND O.D_PRIMARY_SKU_ID <> 220 GROUP BY N1.ORDER_ID, N1.N1_STATUS_ID, N1.SKU_ID, O.SITE_ID, O.ORDER_DT, O.D_TOTAL_PRICE, RS.LEGAL_NAME, PRS.LEGAL_NAME, S.COMPANY_NAME, O.REISSUE_ORDER_STATE_ID, ITEM.COUNTRY, E.COUNTRY, C.CUST_SERVICE_REGI ON, RS.PARTNER_LEVEL, O.FOLLOWUP_DT, N1.APPROVER_CONFIRM_DT, CFA.POLL_STATUS , CFA.APPROVE_METHOD ORDER BY N1_STATUS_ID, ORDER_DT DESC
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!