I have below query taking 5+ minutes even though no data.
Not understanding which one is causing issue.
PLAN_TABLE_OUTPUT
SQL_ID b8wkjgj9p96p0, child number 0
-------------------------------------
SELECT DISTINCT -- analog lines JP2. LO1, NY1
jrs.NAME "Salesperson", hp.party_name "Customer Name",
DECODE (ool.ship_from_org_id,
3, 'SG1',
321, 'AM1',
381, 'LO1',
382, 'NY1',
744, 'JP1',
764, 'JP2',
804, 'CON',
ool.ship_from_org_id
) inv_org,
'CustomHdGd' "Anlg/Dgtl", ooh.order_number "Order Nbr",
ool.line_number "Line Nbr", ooh.ordered_date "Order Date",
ool.last_update_date "Line Date",
ool.flow_status_code "Line Status",
DECODE (wdd.released_status,
'B', 'Backordered',
'C', 'Shipped',
'D', 'Cancelled',
'R', 'Ready_to_Release',
'Y', 'Staged',
wdd.released_status
) "Pick Status",
ool.ordered_item "SKU", ooh.transactional_curr_code "CURR",
ool.ordered_quantity * ool.unit_selling_price "Price",
ooh.org_id "Opg Unit", ooh.header_id "Header ID",
ool.line_id "Line ID",
ooh.cust_po_number "Customer PO Number",
hp.country "Sales Order Country"
FROM apps.oe_order_headers_all ooh,
apps.oe_order_lines_all ool,
wsh.wsh_delivery_details wdd,
ar.hz_cust_accounts hca,
ar.hz_parties hp,
jtf.jtf_rs_salesreps jrs
WHERE ooh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND jrs.salesrep_id = ooh.salesrep_id
AND jrs.org_id = ooh.org_id
AND ool.header_id = ooh.header_id
AND ool.line_id = wdd.source_line_id(+)
AND ( ool.attribute4 IN ('Dupe', 'Print')
OR ool.ordered_item IN
('SRVBURN', 'SRVHGFTGINT', 'SRVDIGFTGINT')
)
AND ool.flow_status_code IN
('ENTERED', 'AWAITING_SHIPPING', 'BOOKED', 'PICKED',
'FULFILLED')
AND ool.ship_from_org_id IN (746, 381, 382)
AND ool.subinventory = 'FGI'
AND ooh.ordered_date > SYSDATE - 92
AND ooh.ordered_date < SYSDATE - 1
AND ooh.order_category_code = 'ORDER'
AND ooh.order_type_id <> 1132
AND ool.ordered_quantity * ool.unit_selling_price > 0
AND ooh.ordered_date > SYSDATE - 92
AND ooh.ordered_date < SYSDATE - 1
AND wdd.released_status (+)= 'B'
Plan hash value: 1064083130
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:05:54.73 | 6533K| 200K| | | |
| 1 | HASH UNIQUE | | 1 | 226 | 0 |00:05:54.73 | 6533K| 200K| 746K| 746K| |
|* 2 | FILTER | | 1 | | 0 |00:05:54.73 | 6533K| 200K| | | |
| 3 | NESTED LOOPS | | 1 | | 0 |00:05:54.73 | 6533K| 200K| | | |
| 4 | NESTED LOOPS | | 1 | 226 | 0 |00:05:54.73 | 6533K| 200K| | | |
| 5 | NESTED LOOPS | | 1 | 226 | 0 |00:05:54.73 | 6533K| 200K| | | |
| 6 | NESTED LOOPS OUTER | | 1 | 226 | 0 |00:05:54.73 | 6533K| 200K| | | |
|* 7 | HASH JOIN | | 1 | 226 | 0 |00:05:54.73 | 6533K| 200K| 784K| 784K| 192K (0)|
| 8 | NESTED LOOPS | | 1 | | 0 |00:05:54.73 | 6533K| 200K| | | |
| 9 | NESTED LOOPS | | 1 | 226 | 3876K|00:00:48.08 | 1819K| 30531 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| OE_ORDER_HEADERS_ALL | 1 | 80990 | 852K|00:00:18.82 | 505K| 23885 | | | |
|* 11 | INDEX RANGE SCAN | OE_ORDER_HEADERS_CN1 | 1 | 163K| 861K|00:00:03.61 | 8090 | 2365 | | | |
|* 12 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 852K| 10 | 3876K|00:00:20.92 | 1314K| 6646 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL | 3876K| 1 | 0 |00:04:56.21 | 4713K| 169K| | | |
| 14 | TABLE ACCESS FULL | JTF_RS_SALESREPS | 0 | 11189 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID BATCHED | WSH_DELIVERY_DETAILS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 16 | INDEX RANGE SCAN | WSH_DELIVERY_DETAILS_N3 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 17 | INDEX RANGE SCAN | GETTY_HZ_CUST_ACCOUNTS_U4 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 18 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYSDATE@!-1>SYSDATE@!-92)
7 - access("JRS"."SALESREP_ID"="OOH"."SALESREP_ID" AND "JRS"."ORG_ID"="OOH"."ORG_ID")
10 - filter(("OOH"."ORDER_CATEGORY_CODE"='ORDER' AND "OOH"."ORDER_TYPE_ID"<>1132))
11 - access("OOH"."ORDERED_DATE">SYSDATE@!-92 AND "OOH"."ORDERED_DATE"<SYSDATE@!-1)
12 - access("OOL"."HEADER_ID"="OOH"."HEADER_ID")
13 - filter(("OOL"."SUBINVENTORY"='FGI' AND "OOL"."ORDERED_QUANTITY"*"OOL"."UNIT_SELLING_PRICE">0 AND INTERNAL_FUNCTION("OOL"."SHIP_FROM_ORG_ID") AND
(INTERNAL_FUNCTION("OOL"."ATTRIBUTE4") OR INTERNAL_FUNCTION("OOL"."ORDERED_ITEM")) AND INTERNAL_FUNCTION("OOL"."FLOW_STATUS_CODE")))
15 - filter("WDD"."RELEASED_STATUS"<>'B')
16 - access("OOL"."LINE_ID"="WDD"."SOURCE_LINE_ID")
17 - access("OOH"."SOLD_TO_ORG_ID"="HCA"."CUST_ACCOUNT_ID")
18 - access("HCA"."PARTY_ID"="HP"."PARTY_ID")
Note
-----
- this is an adaptive plan