Order Organizer OEXOEORD Performance Issue
(Doc ID 1611768.1)
Last updated on FEBRUARY 24, 2022
Applies to:
Oracle Order Management - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
On : 12.1.3 version, Transaction Entry
ACTUAL BEHAVIOR
---------------
OEXOEORD performance is poor
EXPECTED BEHAVIOR
-----------------------
OEXOEORD performance can be improved
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Order Management Responsibility
2. Order Organizer
3. Query sales orders
TKprof trace file showed the following SQL cost the most time:
SELECT ORDER_NUMBER,Quote_Date,Quote_Number,User_Status_Code,
sold_to_site_use_id,sales_document_name,SOLD_TO,ORDER_TYPE,CUSTOMER_NUMBER,
BLANKET_NUMBER,SOLD_TO_CONTACT,CUST_PO_NUMBER,VERSION_NUMBER,ORDERED_DATE,
EXPIRATION_DATE,REQUEST_DATE,PRICE_LIST,PRICING_DATE,ACCOUNTING_RULE,
ACCOUNTING_RULE_DURATION,INVOICING_RULE,TERMS,SHIP_TO,SHIP_TO_LOCATION,
SHIP_TO_ADDRESS1,SHIP_TO_ADDRESS2,SHIP_TO_ADDRESS3,SHIP_TO_ADDRESS4,
SHIP_TO_ADDRESS5,SHIP_TO_CONTACT,INVOICE_TO,INVOICE_TO_LOCATION,
INVOICE_TO_ADDRESS1,INVOICE_TO_ADDRESS2,INVOICE_TO_ADDRESS3,
INVOICE_TO_ADDRESS4,INVOICE_TO_ADDRESS5,INVOICE_TO_CONTACT,
SHIPPING_INSTRUCTIONS,PACKING_INSTRUCTIONS,SHIP_TOLERANCE_ABOVE,
SHIP_TOLERANCE_BELOW,TRANSACTIONAL_CURR_CODE,CURRENCY_PRECISION,
CONVERSION_RATE_DATE,TAX_EXEMPT_NUMBER,SHIP_FROM,SHIP_FROM_LOCATION,
SHIP_FROM_ADDRESS1,SHIP_FROM_ADDRESS2,SHIP_FROM_ADDRESS3,SHIP_FROM_ADDRESS4,
payment_amount,check_number,credit_card_holder_name,
credit_card_expiration_date,first_ack_code,last_ack_code,FIRST_ACK_DATE,
LAST_ACK_DATE,END_CUSTOMER_SITE_USE_ID,END_CUSTOMER_CONTACT_ID,
END_CUSTOMER_ID,IB_OWNER,IB_INSTALLED_AT_LOCATION,IB_CURRENT_LOCATION,
ROW_ID,HEADER_ID,credit_card_number,CONVERSION_RATE,
credit_card_approval_code,ORG_ID,ORDER_TYPE_ID,ORDER_SOURCE_ID,
ORDER_DATE_TYPE_CODE,EARLIEST_SCHEDULE_LIMIT,LATEST_SCHEDULE_LIMIT,
SOURCE_DOCUMENT_TYPE_ID,SOURCE_DOCUMENT_ID,SHIPMENT_PRIORITY_CODE,
PRICE_LIST_ID,TAX_EXEMPT_FLAG,TAX_EXEMPT_REASON_CODE,ORDER_CATEGORY_CODE,
CONVERSION_TYPE_CODE,DEMAND_CLASS_CODE,TRANSACTION_PHASE_CODE,AGREEMENT_ID,
TAX_POINT_CODE,INVOICING_RULE_ID,ACCOUNTING_RULE_ID,PAYMENT_TERM_ID,
SHIPPING_METHOD_CODE,FOB_POINT_CODE,FREIGHT_TERMS_CODE,FREIGHT_CARRIER_CODE,
SALESREP_ID,RETURN_REASON_CODE,sales_channel_code,payment_type_code,
CREDIT_CARD_CODE,FLOW_STATUS_CODE,SOLD_TO_ORG_ID,SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,INVOICE_TO_ORG_ID,DELIVER_TO_ORG_ID,SOLD_TO_CONTACT_ID,
SHIP_TO_CONTACT_ID,INVOICE_TO_CONTACT_ID,DELIVER_TO_CONTACT_ID,
CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,CONTEXT,
TP_CONTEXT,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,
ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,
ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,
ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,TP_ATTRIBUTE1,TP_ATTRIBUTE2,
TP_ATTRIBUTE3,TP_ATTRIBUTE4,TP_ATTRIBUTE5,TP_ATTRIBUTE6,TP_ATTRIBUTE7,
TP_ATTRIBUTE8,TP_ATTRIBUTE9,TP_ATTRIBUTE10,TP_ATTRIBUTE11,TP_ATTRIBUTE12,
TP_ATTRIBUTE13,TP_ATTRIBUTE14,TP_ATTRIBUTE15,GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,GLOBAL_ATTRIBUTE2,GLOBAL_ATTRIBUTE3,GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,GLOBAL_ATTRIBUTE6,GLOBAL_ATTRIBUTE7,GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,GLOBAL_ATTRIBUTE10,GLOBAL_ATTRIBUTE11,GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,GLOBAL_ATTRIBUTE14,GLOBAL_ATTRIBUTE15,GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,GLOBAL_ATTRIBUTE18,GLOBAL_ATTRIBUTE19,GLOBAL_ATTRIBUTE20,
BOOKED_FLAG,BOOKED_DATE,CANCELLED_FLAG,OPEN_FLAG,ORIG_SYS_DOCUMENT_REF
FROM
OE_ORDER_HEADERS_V WHERE header_id in (select /*+ unnest */ header_id from
oe_orderorg_GTT ) order by org_id desc,ordered_date desc,order_number desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 344.72 740.42 2298160 372516 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 344.72 740.43 2298160 372516 0 1
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |