Краткая предыстория этого запроса: в корпоративной информационной системе (Oracle OeBS, Oracle Transportation Management) реализована интеграция с транспортными компаниями. Интеграция обменивается данными через интерфейсы API для отправки заявок на перевозку и последующим сбором данных.
Страховая стоимость груза - обязательный параметр, который требуется при оформлении заявки на перевозку. Межскладские перемещения формируются в OeBS, затем импортируются в OTM, где работает отдел логистики.
Собственно, смысл запроса, по номеру релиза (накладной) получить из OeBS стоимость позиций: сначала определяются склады этого релиза
Модули Oracle E-Business Suite в запросе
WSH (Warehouse Shipping)
Модуль в Oracle E-Business Suite (EBS), отвечающий за управление логистикой и доставками. Он включает:
- WSH_DELIVERY_DETAILS - детали доставки (товары, количества, адреса)
- WSH_DELIVERY_ASSIGNMENTS - связи между доставками и деталями
- WSH_NEW_DELIVERIES - заголовки доставок (номера, даты, статусы)
ONT (Order Management) — Управление заказами
Модуль для обработки заказов клиентов (sales orders). Интегрируется с другими модулями (например, INV для инвентаризации, WSH для доставок) и управляет жизненным циклом заказа от создания до отгрузки.
Ключевые таблицы:
- ont.oe_order_headers_all: Заголовки заказов (номера заказов, даты, статусы, типы заказов). Содержит общую информацию о заказе.
- ont.oe_order_lines_all: Строки заказов (товары, количества, цены, ссылки на источники). Каждая строка — это позиция в заказе.
- ont.oe_transaction_types_all: Типы транзакций заказов (например, 'Y' для определённых типов, как в фильтре ot.attribute2 = 'Y').
Эти таблицы используются для связи заказов с доставками (через ol.line_id = wdd.source_line_id) и получения данных о товарах/количествах.
PO (Purchasing) — Закупки
Модуль для управления закупками (requisitions, purchase orders). Обрабатывает запросы на закупку, заказы поставщикам и интеграцию с заказами клиентов.
Ключевые таблицы:
- po.po_requisition_headers_all: Заголовки запросов на закупку (номера, даты, статусы).
- po.po_requisition_lines_all: Строки запросов на закупку (товары, количества, склады назначения).
QP (Advanced Pricing)
Модуль в Oracle E-Business Suite (EBS), отвечающий за ценообразование и скидки. Позволяет создавать сложные прайс-листы, правила скидок и модификаторов цен на основе атрибутов товаров, клиентов и условий. Интегрируется с модулями OM (Order Management), PO (Purchasing) и INV (Inventory) для расчёта цен в заказах, котировках и закупках.
Идея запроса
Получить три прайс-лист цены по номенклатуре из накладной, чтобы определить суммарную страховую стоимость. Несмотря на наличие относительно большого количества соединений разных таблиц, типичный запрос возвращает незначительное количество записей (в основном до 100), поэтому ожидаемое время работы в пределах 1 секунды (для моих условий).
SQL исходного запроса (время выполнения 5-7 секунд)
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;
Проблема в том, что время выполнения запроса составляло 5-7 секунд. Так как интеграция работает из OTМ, то доступ к таблицам OeBS был по ссылкам (dblink).
Для начала было принято решение инкапсулировать запрос в функцию непосредственно в БД OeBS, чтобы возвращалась только конечная стоимость всех позиций типом NUMBER.
Это не принесло результата.
Далее было несколько попыток перекомпоновать запрос, но время выполнения менялось только незначительно, кардинальных изменений производительности не было.
Решил посмотреть EXPLAIN PLAN. На первый взгляд индексы присутствуют.
В принципе там сразу видно, что начиная с 48-й позиции TABLE ACCESS BY INDEX ROWID имеется значительный рост стоимости (Cost (%CPU) = 1412) а ниже на 49-й позиции INDEX RANGE SCAN QP_PRICING_ATTRIBUTES_N6 задействовано 25081 строк (а у меня в накладной всего-то три позиции!).
В информации по предикатам также была подсказка:
48 - filter (TO_NUMBER("QPA"."PRODUCT_ATTR_VALUE")="I"."INVENTORY_ITEM_ID"
Как выяснилось, PRODUCT_ATTR_VALUE в qp.qp_pricing_attributes имеет VARCHAR2, а INVENTORY_ITEM_ID в модуле WSH это NUMBER. Из-за этого возникает две проблемы:
Во-первых Oracle пытается сравнить строку с числом: PRODUCT_ATTR_VALUE = INVENTORY_ITEM_ID.
Поскольку типы не совпадают, Oracle автоматически выполняет неявное преобразование: TO_NUMBER(PRODUCT_ATTR_VALUE) = INVENTORY_ITEM_ID.
Это преобразование применяется к столбцу таблицы во время выполнения, для каждой строки, что дорого и Oracle не может использовать индекс напрямую.
Во-вторых происходит неверное применение индекса: вместо быстрого INDEX RANGE SCAN происходит сканирование 25тыс. строк, как видно в плане.
EXPLAIN PLAN (выполнено явное преобразование проблемного JOIN)
После изменения единственной строки (делаю явное преобразование в CTE TO_CHAR(wdd.INVENTORY_ITEM_ID) as INVENTORY_ITEM_ID), план выполнения теперь показывает использование INDEX RANGE SCAN который возвращает только 49 rows
И отсутствие неявного преобразования в предикатах:
Время выполнения запроса становится значительно быстрее (в пределах 0.1-0.4 сек)
