I have below query taking 5 minutes to complete. Is there any better way to write this?
Actually no rows returns from the query.
PLAN_TABLE_OUTPUT
SQL_ID ddcdwg832p8ka, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */
DISTINCT 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,
'Hardgood' "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.ship_from_org_id IN (3, 321, 744, 804)
AND ool.flow_status_code IN
('ENTERED', 'AWAITING_SHIPPING', 'BOOKED', 'PICKED',
'FULFILLED')
AND ooh.order_category_code = 'ORDER'
AND ooh.order_type_id <> 1132
-- do not select internal requisition sales orders
AND ooh.ordered_date > SYSDATE - 92
--to_date('&Beg_Date' ,'DD-MON-YYYY')
AND ooh.ordered_date < SYSDATE - 1
--to_date('&End_Date' ,'DD-MON-YYYY')
AND ool.ordered_quantity* ool.unit_selling_price > 0
AND ool.inventory_item_id IN -- Select only CD's and Catalogs
(
SELECT msi.inventory_item_id
FROM inv.mtl_system_items_b msi,
inv.mtl_categories_b mcb,
inv.mtl_category_set_valid_cats mcsvc,
inv.mtl_item_categories mic,
apps.mtl_category_sets mcs
WHERE mcb.category_id = mcsvc.category_id
AND mcsvc.category_set_id = mic.category_set_id
AND mcsvc.category_id = mic.category_id
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND msi.organization_id=ool.ship_from_org_id
and msi.inventory_item_id=ool.inventory_item_id
AND mcsvc.category_set_id = mcs.category_set_id
AND msi.organization_id IN (3, 321, 744, 804)
AND mcs.category_set_name = 'Accounting Item Category'
AND mcb.segment2 IN
('CATALOG', 'CD', 'FONTCD', 'MOTIONCD')
-- media type
)
AND wdd.released_status != 'B';
Plan hash value: 2837187812
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:06:09.84 | 651K| 336K| | | |
| 1 | HASH UNIQUE | | 1 | 3 | 0 |00:06:09.84 | 651K| 336K| 727K| 727K| |
|* 2 | FILTER | | 1 | | 0 |00:06:09.84 | 651K| 336K| | | |
| 3 | NESTED LOOPS | | 1 | | 0 |00:06:09.84 | 651K| 336K| | | |
| 4 | NESTED LOOPS | | 1 | 3 | 0 |00:06:09.84 | 651K| 336K| | | |
| 5 | NESTED LOOPS | | 1 | 3 | 0 |00:06:09.84 | 651K| 336K| | | |
| 6 | NESTED LOOPS | | 1 | 3 | 0 |00:06:09.84 | 651K| 336K| | | |
| 7 | NESTED LOOPS | | 1 | 3 | 0 |00:06:09.84 | 651K| 336K| | | |
| 8 | NESTED LOOPS | | 1 | 24 | 14 |00:06:09.96 | 651K| 336K| | | |
| 9 | NESTED LOOPS | | 1 | 462 | 53 |00:00:05.41 | 651K| 336K| | | |
| 10 | NESTED LOOPS | | 1 | 2789 | 23988 |00:00:36.35 | 386K| 332K| | | |
|* 11 | HASH JOIN | | 1 | 7670 | 24036 |00:00:04.22 | 329K| 329K| 1753K| 1753K| 1255K (0)|
|* 12 | HASH JOIN RIGHT SEMI | | 1 | 5 | 199 |00:00:00.02 | 108 | 106 | 2293K| 2293K| 1574K (0)|
|* 13 | TABLE ACCESS FULL | MTL_CATEGORIES_B | 1 | 101 | 209 |00:00:00.01 | 91 | 89 | | | |
| 14 | NESTED LOOPS | | 1 | 252 | 2525 |00:00:00.01 | 17 | 17 | | | |
| 15 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 | 5 | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID BATCHED| MTL_CATEGORY_SETS_TL | 1 | 1 | 1 |00:00:00.01 | 4 | 4 | | | |
|* 17 | INDEX SKIP SCAN | MTL_CATEGORY_SETS_TL_U1 | 1 | 20 | 20 |00:00:00.01 | 1 | 1 | | | |
|* 18 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_B_U1 | 1 | 1 | 1 |00:00:00.01 | 1 | 1 | | | |
|* 19 | INDEX RANGE SCAN | MTL_CATEGORY_SET_VALID_CATS_N1 | 1 | 252 | 2525 |00:00:00.01 | 12 | 12 | | | |
| 20 | INLIST ITERATOR | | 1 | | 71M|00:08:59.70 | 329K| 329K| | | |
|* 21 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_U1 | 4 | 1526 | 71M|00:07:06.69 | 329K| 329K| | | |
|* 22 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 24036 | 1 | 23988 |00:00:05.57 | 57175 | 2547 | | | |
|* 23 | TABLE ACCESS BY INDEX ROWID BATCHED | OE_ORDER_LINES_ALL | 23988 | 1 | 53 |00:00:03.62 | 264K| 4451 | | | |
|* 24 | INDEX RANGE SCAN | OE_ORDER_LINES_N3 | 23988 | 8 | 120K|00:00:00.29 | 26742 | 18 | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID BATCHED | WSH_DELIVERY_DETAILS | 53 | 1 | 14 |00:00:00.04 | 77 | 39 | | | |
|* 26 | INDEX RANGE SCAN | WSH_DELIVERY_DETAILS_N3 | 53 | 1 | 22 |00:00:00.04 | 58 | 31 | | | |
|* 27 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HEADERS_ALL | 14 | 1 | 0 |00:00:00.02 | 41 | 17 | | | |
|* 28 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_U1 | 14 | 1 | 14 |00:00:00.01 | 30 | 10 | | | |
| 29 | TABLE ACCESS BY INDEX ROWID | JTF_RS_SALESREPS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 30 | INDEX UNIQUE SCAN | JTF_RS_SALESREPS_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 31 | INDEX RANGE SCAN | GETTY_HZ_CUST_ACCOUNTS_U4 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 32 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 33 | 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)
11 - access("MCSVC"."CATEGORY_SET_ID"="MIC"."CATEGORY_SET_ID" AND "MCSVC"."CATEGORY_ID"="MIC"."CATEGORY_ID")
12 - access("MCB"."CATEGORY_ID"="MCSVC"."CATEGORY_ID")
13 - filter(("MCB"."SEGMENT2"='CATALOG' OR "MCB"."SEGMENT2"='CD' OR "MCB"."SEGMENT2"='FONTCD' OR "MCB"."SEGMENT2"='MOTIONCD'))
16 - filter("CATEGORY_SET_NAME"='Accounting Item Category')
17 - access("LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='ORA$BASE')
filter(("LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='ORA$BASE'))
18 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "ZD_EDITION_NAME"='ORA$BASE')
19 - access("MCSVC"."CATEGORY_SET_ID"="CATEGORY_SET_ID")
21 - access(("MIC"."ORGANIZATION_ID"=3 OR "MIC"."ORGANIZATION_ID"=321 OR "MIC"."ORGANIZATION_ID"=744 OR "MIC"."ORGANIZATION_ID"=804))
22 - access("MIC"."INVENTORY_ITEM_ID"="MSI"."INVENTORY_ITEM_ID" AND "MIC"."ORGANIZATION_ID"="MSI"."ORGANIZATION_ID")
filter(("MSI"."ORGANIZATION_ID"=3 OR "MSI"."ORGANIZATION_ID"=321 OR "MSI"."ORGANIZATION_ID"=744 OR "MSI"."ORGANIZATION_ID"=804))
23 - filter(("MSI"."ORGANIZATION_ID"="OOL"."SHIP_FROM_ORG_ID" AND "OOL"."ORDERED_QUANTITY"*"OOL"."UNIT_SELLING_PRICE">0 AND INTERNAL_FUNCTION("OOL"."SHIP_FROM_ORG_ID")
AND INTERNAL_FUNCTION("OOL"."FLOW_STATUS_CODE")))
24 - access("OOL"."INVENTORY_ITEM_ID"="MSI"."INVENTORY_ITEM_ID")
25 - filter("WDD"."RELEASED_STATUS"<>'B')
26 - access("OOL"."LINE_ID"="WDD"."SOURCE_LINE_ID")
27 - filter(("OOH"."ORDER_CATEGORY_CODE"='ORDER' AND "OOH"."ORDERED_DATE">SYSDATE@!-92 AND "OOH"."ORDER_TYPE_ID"<>1132 AND "OOH"."ORDERED_DATE"<SYSDATE@!-1))
28 - access("OOL"."HEADER_ID"="OOH"."HEADER_ID")
30 - access("JRS"."SALESREP_ID"="OOH"."SALESREP_ID" AND "JRS"."ORG_ID"="OOH"."ORG_ID")
31 - access("OOH"."SOLD_TO_ORG_ID"="HCA"."CUST_ACCOUNT_ID")
32 - access("HCA"."PARTY_ID"="HP"."PARTY_ID")
Note
-----
- this is
an adaptive pl
an