Latency on Order Creation (Doc ID 2662259.1)

Last updated on MARCH 31, 2023

Applies to:

Symptoms

Customer is experience high long creation times one system in particular. On further analysis  one specific DB query in taking longer time.
--
SELECT * FROM (SELECT * FROM (SELECT HEADER.ORDER_SEQ_ID C1, FLOW.HIST_SEQ_ID C2, HEADER.NUM_REMARKS C3, FLOW.STATE_ID C4, FLOW.USERNAME C5, FLOW.DATE_POS_STARTED C6, HEADER.ORDER_TYPE_ID C7, HEADER.ORDER_SOURCE_ID C8, HEADER.CARTRIDGE_ID C9, FLOW.EXECUTION_MODE C10, FLOW.COORD_SET_ID C11, FLOW.TASK_ID C12 , HEADER.ORD_STATE_ID C13, HEADER.ORD_TARGET_STATE_ID C14, HEADER.REFERENCE_NUMBER C15, HEADER.PRIORITY C16, HEADER.ORD_CREATION_DATE C17, HEADER.ORD_COMPL_DATE_EXPECTED C18, HEADER.ORD_COMPLETION_DATE C19, HEADER.EXPECTED_START_DATE C20, HEADER.REQUESTED_DELIVERY_DATE C21, HEADER.EXPECTED_DURATION C22, HEADER.PROCESS_STATUS_ID C23, ROW_NUMBER() OVER (PARTITION BY HEADER.ORDER_SEQ_ID ORDER BY FLOW.HIST_SEQ_ID DESC) RN, COUNT(*) OVER () TOTAL_ROWS FROM OM_ORDER_HEADER HEADER LEFT OUTER JOIN OM_ORDER_FLOW FLOW ON (HEADER.ORDER_SEQ_ID=FLOW.ORDER_SEQ_ID) WHERE HEADER.CARTRIDGE_ID MEMBER OF ? AND RTRIM(UPPER(REFERENCE_NUMBER)) LIKE RTRIM(?) AND HEADER.ORDER_TYPE_ID MEMBER OF ? AND HEADER.ORDER_SOURCE_ID MEMBER OF ? AND HEADER.DOMAIN_ID = ? ORDER BY C17 ASC) WHERE RN = ?) A WHERE ROWNUM <= ?
--

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.