As query.getStateAndStatus_MAIN* is used to know the state and state of BP across different workflows which is common use case for many clients across the industry. On a high volume application environment this query is taking more than 10+ mins and causing database CPU increase and impacting overall processing time.
Oracle vendor recommended to optimize this query.
Similar to above, below are set of queries which are captured in AWR report each time during application slowness.
SELECT MSG.MESSAGE_NAME AS MESSAGENAME, MSG.MESSAGE_ID, CREATED_DATETIME, MESSAGE_SIZE, MSG.CONTENT_TYPE , EXTRACTABLE_UNTIL, 0 AS EXTRACTABLE_COUNT, 0 AS EXTRACTABLE, 2 AS EXTRACT_TYPE FROM MBX_MESSAGE MSG, MBX_MAILBOX MBX, DOCUMENT, MBX_EXTRACT_TIL TIL WHERE MSG.MAILBOX_ID = MBX.MAILBOX_ID AND MSG.DOC_ID = DOCUMENT.DOC_ID AND (MBX.PATH = :1 )AND MSG.MESSAGE_NAME_UP LIKE :2 AND MSG.MESSAGE_ID = TIL.MESSAGE_ID UNION ALL SELECT MSG.MESSAGE_NAME AS MESSAGENAME, MSG.MESSAGE_ID, CREATED_DATETIME, MESSAGE_SIZE, MSG.CONTENT_TYPE , TO_DATE ('1900-01-01', 'YYYY-MM-DD') AS EXTRACTABLE_UNTIL, EXTRACTABLE_COUNT, 0 AS EXTRACTABLE, 0 AS EXTRACT_TYPE FROM MBX_MESSAGE MSG, MBX_MAILBOX MBX, DOCUMENT, MBX_EXTRACT_COUNT CNT WHERE MSG.MAILBOX_ID = MBX.MAILBOX_ID AND MSG.DOC_ID = DOCUMENT.DOC_ID AND (MBX.PATH = :3 )AND MSG.MESSAGE_NAME_UP LIKE :4 AND MSG.MESSAGE_ID = CNT.MESSAGE_ID UNION ALL SELECT MSG.MESSAGE_NAME AS MESSAGENAME, MSG.MESSAGE_ID, CREATED_DATETIME, MESSAGE_SIZE, MSG.CONTENT_TYPE , TO_DATE ('1900-01-01', 'YYYY-MM-DD') AS EXTRACTABLE_UNTIL, 0 AS EXTRACTABLE_COUNT, EXTRACTABLE, 1 AS EXTRACT_TYPE FROM MBX_MESSAGE MSG, MBX_MAILBOX MBX, DOCUMENT, MBX_EXTRACTABLE EXT WHERE MSG.MAILBOX_ID = MBX.MAILBOX_ID AND MSG.DOC_ID = DOCUMENT.DOC_ID AND (MBX.PATH = :5 )AND MSG.MESSAGE_NAME_UP LIKE :6 AND MSG.MESSAGE_ID = EXT.MESSAGE_ID
Below query used by Recovery.bpml which is taking couple of hours when WORKFLOW_CONTEXT table has millions of rows.
SELECT WORKFLOW_ID, WFD_ID, START_TIME, ( SELECT MIN( CASE WHEN BASIC_STATUS = 13 THEN -1 WHEN BASIC_STATUS = 11 THEN 0 WHEN BASIC_STATUS = 3 THEN 3 WHEN BASIC_STATUS = 10 THEN 4 WHEN BASIC_STATUS IN ( 400, 10400 ) AND NEXT_AI_ID = -1 THEN 9 WHEN BASIC_STATUS = 400 THEN 7 WHEN BASIC_STATUS IN ( 450, 10450 ) AND NEXT_AI_ID = -1 THEN 9 WHEN BASIC_STATUS = 450 THEN 6 WHEN BASIC_STATUS IN ( 100, 10100, 200, 10200, 300, 10300, 2, 10002, 1, 10001 ) AND NEXT_AI_ID = -1 AND WFE_STATUS = -3 THEN 8 WHEN BASIC_STATUS IN ( 100, 10100, 200, 10200, 300, 10300, 2, 10002, 1, 10001 ) AND NEXT_AI_ID = -1 THEN 9 WHEN BASIC_STATUS IN ( 100, 200, 300, 2, 1 ) AND ( NEXT_AI_ID = ACTIVITYINFO_ID OR WFE_STATUS IN ( 102, 103, 105, 203, 202, 205, 201, 206, 208 ) ) THEN 8 WHEN BASIC_STATUS IN ( 100, 200, 300, 2, 1 ) THEN 1 WHEN BASIC_STATUS = 900 THEN 5 WHEN BASIC_STATUS IN( 0, -1 ) AND NEXT_AI_ID = -2 THEN 10 WHEN BASIC_STATUS IN (0,12) AND NEXT_AI_ID = -1 THEN 9 WHEN NEXT_AI_ID != -2 AND NEXT_AI_ID != -1 THEN (SELECT CASE WHEN COUNT(*) > 0 THEN 2 ELSE 1 END FROM WF_INACTIVE WFI WHERE WFI.WF_ID = WFC_MAIN_1.WORKFLOW_ID) ELSE -1 END ) FROM WORKFLOW_CONTEXT WFC_STATE_1 WHERE (STEP_ID, BRANCH_ID, WORKFLOW_ID) IN ( SELECT /*+ FIRST_ROWS_1 */ * from ( select step_id, branch_id, workflow_id from workflow_context order by 1 desc) wfc_state_2 where rownum = 1 and WFC_MAIN_1.WORKFLOW_ID = WFC_STATE_2.WORKFLOW_ID AND WFC_STATE_1.BRANCH_ID = WFC_STATE_2.BRANCH_ID ) AND WFC_STATE_1.WORKFLOW_ID = WFC_MAIN_1.WORKFLOW_ID ) AS STATE, ( SELECT MAX( CASE WHEN BASIC_STATUS = 1 THEN 1 WHEN BASIC_STATUS = 10001 THEN 1 WHEN BASIC_STATUS = 200 THEN 1 WHEN BASIC_STATUS = 10200 THEN 1 WHEN BASIC_STATUS = 100 THEN 1 WHEN BASIC_STATUS = 10100 THEN 1 WHEN BASIC_STATUS = 300 THEN 1 WHEN BASIC_STATUS = 10300 THEN 1 WHEN BASIC_STATUS = 450 THEN 1 WHEN BASIC_STATUS = 10450 THEN 1 WHEN BASIC_STATUS = 400 THEN 1 WHEN BASIC_STATUS = 10400 THEN 1 WHEN BASIC_STATUS = 2 THEN 1 WHEN BASIC_STATUS = 10002 THEN 1 ELSE 0 END ) FROM WORKFLOW_CONTEXT WFC_STATUS_1 WHERE WFC_STATUS_1.WORKFLOW_ID = WFC_MAIN_1.WORKFLOW_ID) AS STATUS FROM WORKFLOW_CONTEXT WFC_MAIN_1 WHERE ACTIVITYINFO_ID = 0 AND STEP_ID = 0 AND WORKFLOW_ID IN (SELECT WF_ID FROM ARCHIVE_INFO WHERE ARCHIVE_FLAG=-1 )
Please help to optimize these queries which will in turn help lot of Sterling B2B users and improve application stability.
