Brief background of this query: In a corporate information system (Oracle EBS, Oracle Transportation Management), integration with transportation companies is implemented. The integration exchanges data through API interfaces for sending transportation requests and subsequent data collection.
Cargo insurance cost is a mandatory parameter required when submitting a transportation request. Inter-warehouse movements are formed in EBS, then imported into OTM, where the logistics department operates.
Essentially, the purpose of the query is to obtain the cost of positions from EBS by release number (invoice): first, the warehouses of this release are determined.
Oracle E-Business Suite Modules in the Query
WSH (Warehouse Shipping)
Module in Oracle E-Business Suite (EBS) responsible for logistics and delivery management. It includes:
- WSH_DELIVERY_DETAILS - delivery details (goods, quantities, addresses)
- WSH_DELIVERY_ASSIGNMENTS - links between deliveries and details
- WSH_NEW_DELIVERIES - delivery headers (numbers, dates, statuses)
ONT (Order Management) — Order Management
Module for processing customer orders (sales orders). It integrates with other modules (e.g., INV for inventory, WSH for deliveries) and manages the order lifecycle from creation to shipment.
Key tables:
- ont.oe_order_headers_all: Order headers (order numbers, dates, statuses, order types). Contains general order information.
- ont.oe_order_lines_all: Order lines (goods, quantities, prices, source references). Each line is a position in the order.
- ont.oe_transaction_types_all: Order transaction types (e.g., 'Y' for certain types, as in your filter ot.attribute2 = 'Y').
These tables are used to link orders with deliveries (via ol.line_id = wdd.source_line_id) and obtain data about goods/quantities.
PO (Purchasing) — Procurement
Module for procurement management (requisitions, purchase orders). It processes procurement requests, supplier orders, and integration with customer orders.
Key tables:
- po.po_requisition_headers_all: Procurement request headers (numbers, dates, statuses).
- po.po_requisition_lines_all: Procurement request lines (goods, quantities, destination warehouses).
QP (Advanced Pricing)
Module in Oracle E-Business Suite (EBS) responsible for pricing and discounts. It allows creating complex price lists, discount rules, and price modifiers based on product, customer, and condition attributes. QP integrates with OM (Order Management), PO (Purchasing), and INV (Inventory) modules for price calculation in orders, quotes, and procurements.
Query Idea
Obtain three price list prices by nomenclature from the invoice to determine the total insurance cost. Despite the presence of a relatively large number of joins of different tables, a typical query returns a small number of records (mainly up to 100), so the expected execution time is within 1 second (for my conditions).
Original SQL Query (execution time 5-7 seconds)
WITH items AS (
SELECT DISTINCT
wdd.INVENTORY_ITEM_ID,
wdd.ITEM_DESCRIPTION,
QUANTITY,
NVL(wdd.subinventory, ol.subinventory) AS src_wh,
prl.destination_subinventory AS dst_whs
FROM po.po_requisition_headers_all prh
INNER JOIN po.po_requisition_lines_all prl ON prh.requisition_header_id = prl.requisition_header_id
INNER JOIN ont.oe_order_lines_all ol ON ol.source_document_line_id = prl.requisition_line_id
INNER JOIN ont.oe_order_headers_all oh ON oh.header_id = ol.header_id
INNER JOIN ont.oe_transaction_types_all ot ON oh.order_type_id = ot.transaction_type_id AND ot.attribute2 = 'Y'
LEFT JOIN wsh.wsh_delivery_details wdd ON ol.line_id = wdd.source_line_id
LEFT JOIN wsh.wsh_delivery_assignments wda ON wdd.delivery_detail_id = wda.delivery_detail_id
LEFT JOIN wsh.wsh_new_deliveries wnd ON wda.delivery_id = wnd.delivery_id
WHERE wnd.delivery_id = :p_release
)
SELECT SUM(COALESCE(qp1.operand, qp2.operand) * QUANTITY) as COST
FROM items i
LEFT JOIN (
SELECT DISTINCT qh.NAME, qpa.PRODUCT_ATTR_VALUE, ql.operand
FROM qp_list_headers_vl qh
JOIN qp.qp_pricing_attributes qpa ON qh.LIST_HEADER_ID = qpa.LIST_HEADER_ID
JOIN qp.qp_list_lines ql ON ql.LIST_LINE_ID = qpa.LIST_LINE_ID
WHERE qpa.product_attribute_context = 'ITEM'
AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
) qp1 ON qp1.NAME = i.src_wh AND qp1.PRODUCT_ATTR_VALUE = i.INVENTORY_ITEM_ID
LEFT JOIN (
SELECT DISTINCT qh.NAME, qpa.PRODUCT_ATTR_VALUE, ql.operand
FROM qp_list_headers_vl qh
JOIN qp.qp_pricing_attributes qpa ON qh.LIST_HEADER_ID = qpa.LIST_HEADER_ID
JOIN qp.qp_list_lines ql ON ql.LIST_LINE_ID = qpa.LIST_LINE_ID
WHERE qpa.product_attribute_context = 'ITEM'
AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
) qp2 ON qp2.NAME = i.dst_whs AND qp2.PRODUCT_ATTR_VALUE = i.INVENTORY_ITEM_ID;
The problem is that the query execution time was 5-7 seconds. Since the integration works from OTM, access to EBS tables was via database links (dblink).
Initially, it was decided to encapsulate the query in a function directly in the EBS database to return only the final cost as a NUMBER type.
This did not bring results.
Then there were several attempts to restructure the query, but the execution time changed only slightly, there were no significant performance improvements.
I decided to look at EXPLAIN PLAN. At first glance, indexes are present.
Basically, it is immediately visible that starting from position 48 TABLE ACCESS BY INDEX ROWID has a significant cost increase (Cost (%CPU) = 1412) and below at position 49 INDEX RANGE SCAN QP_PRICING_ATTRIBUTES_N6 involves 25,081 rows (and I have only three positions in the invoice!).
In the predicate information, there was also a hint:
48 - filter (TO_NUMBER("QPA"."PRODUCT_ATTR_VALUE")="I"."INVENTORY_ITEM_ID"
As it turned out, PRODUCT_ATTR_VALUE in qp.qp_pricing_attributes has VARCHAR2, and INVENTORY_ITEM_ID in the WSH module is NUMBER. Due to this, two problems arise:
First, Oracle tries to compare a string with a number: PRODUCT_ATTR_VALUE = INVENTORY_ITEM_ID.
Since the types do not match, Oracle automatically performs implicit conversion: TO_NUMBER(PRODUCT_ATTR_VALUE) = INVENTORY_ITEM_ID.
This conversion is applied to the table column during execution, for each row, which is expensive and Oracle cannot use the index directly.
Second, incorrect index application occurs: instead of a fast INDEX RANGE SCAN, scanning of 25 thousand rows occurs, as seen in the plan.
EXPLAIN PLAN (explicit conversion of the problematic JOIN performed)
After changing a single line (making explicit conversion in CTE TO_CHAR(wdd.INVENTORY_ITEM_ID) as INVENTORY_ITEM_ID), the execution plan now shows the use of INDEX RANGE SCAN which returns only 49 rows
And the absence of implicit conversion in predicates:
The query execution time becomes significantly faster (within 0.1-0.4 seconds)
