Very Poor Performance When Querying Order Organizer

Last updated on JANUARY 09, 2018

Applies to:

Symptoms

ACTUAL BEHAVIOR
When attempting to search a order number in the Sales order form, the form is hanging and not returning sales order.
The poor performance is coming from code in the following statement:

SQL ID: a1ffzkwmwwyw7 Plan Hash: 971569441

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 0 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 649.14 2474.15 2534376 18541363 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 649.14 2474.15 2534376 18541363 0 0

EXPECTED BEHAVIOR
Expect the search engine to return data in seconds.

STEPS TO REPRODUCE
1. Order Management Responsibility
2. Order Organizer
3. Query a sales order

Cause