I am in need of inputs to tune the below query, please provide some inputs to rewrite it.
SELECT ooh.order_number,
ooh.org_id,
ooh.header_id,
ool.line_number,
ool.line_id,
hca.account_number,
decode(substr(hca.account_number, 1, 2), 'US',
substr(hca.account_number, 3), hca.account_number) cust_number,
ooh.flow_status_code status,
(SELECT organization_code
FROM apps.org_organization_definitions ood
WHERE ood.organization_id = ool.ship_from_org_id) warehouse,
ool.ordered_item item_number,
ool.ordered_quantity,
(SELECT SUM(primary_reservation_quantity)
FROM apps.mtl_reservations
WHERE demand_source_line_id = ool.line_id) allocated_qty,
(nvl(ool.ordered_quantity, 0) -
(SELECT SUM(primary_reservation_quantity)
FROM apps.mtl_reservations
WHERE demand_source_line_id = ool.line_id) -
nvl(ool.shipped_quantity, 0)) backorder_qty
FROM apps.oe_order_headers_all ooh,
apps.oe_order_lines_all ool,
apps.oe_transaction_types_tl ott,
apps.hz_cust_accounts hca
WHERE ooh.header_id = ool.header_id
AND ooh.order_type_id = ott.transaction_type_id
AND ott.language = userenv('LANG')
AND ott.name IN (SELECT meaning
FROM apps.fnd_lookup_values flv
WHERE flv.lookup_type = :b9
AND flv.description = :b6
AND flv.tag = :b8
AND nvl(flv.enabled_flag, 'Y') = :b7
AND trunc(SYSDATE) BETWEEN
nvl(trunc(flv.start_date_active), trunc(SYSDATE)) AND
nvl(trunc(flv.end_date_active), trunc(SYSDATE))
AND flv.language = userenv('LANG'))
AND ooh.order_category_code = :b6
AND ooh.org_id IN (:b5, :b4)
AND ooh.sold_to_org_id = hca.cust_account_id
AND hca.customer_class_code = 'DL'
AND (((ooh.last_update_date) >= :b3 AND (ooh.last_update_date) <= :b2) OR
((ool.last_update_date) >= :b3 AND (ool.last_update_date) <= :b2) OR
(EXISTS (SELECT 1
FROM apps.ra_customer_trx_lines_all
WHERE interface_line_context = 'ORDER ENTRY'
AND interface_line_attribute1 = ooh.order_number
AND org_id = ooh.org_id
AND interface_line_attribute6 = to_char(ool.line_id)
AND creation_date >= :b3)) OR
(ooh.order_number = nvl(:b1, ooh.order_number)));
Plan Hash Value :
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 253 | 28396 | 00:00:02 |
| 1 | NESTED LOOPS | | 1 | 92 | 7 | 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 92 | 7 | 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 63 | 6 | 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 55 | 5 | 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 30 | 3 | 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 23 | 2 | 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | MTL_PARAMETERS | 1 | 7 | 1 | 00:00:01 |
| * 8 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | | 1 | 00:00:01 |
| * 9 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 16 | 1 | 00:00:01 |
| * 10 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 7 | 1 | 00:00:01 |
| * 11 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 1 | 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED | HR_ORGANIZATION_INFORMATION | 1 | 25 | 2 | 00:00:01 |
| * 13 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | | 1 | 00:00:01 |
| * 14 | TABLE ACCESS BY INDEX ROWID | GL_LEDGERS | 1 | 8 | 1 | 00:00:01 |
| * 15 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | | 1 | 00:00:01 |
| * 16 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | | 1 | 00:00:01 |
| * 17 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 29 | 2 | 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 10 | | |
| 19 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_RESERVATIONS | 1 | 10 | 3 | 00:00:01 |
| * 20 | INDEX RANGE SCAN | MTL_RESERVATIONS_N2 | 1 | | 2 | 00:00:01 |
| * 21 | FILTER | | | | | |
| 22 | NESTED LOOPS OUTER | | 1 | 253 | 28385 | 00:00:02 |
| 23 | NESTED LOOPS | | 1 | 238 | 28382 | 00:00:02 |
| 24 | NESTED LOOPS | | 1 | 194 | 28377 | 00:00:02 |
| * 25 | HASH JOIN | | 1 | 159 | 28376 | 00:00:02 |
| 26 | NESTED LOOPS | | 1 | 112 | 7 | 00:00:01 |
| 27 | NESTED LOOPS | | 1 | 112 | 7 | 00:00:01 |
| 28 | SORT UNIQUE | | 1 | 81 | 5 | 00:00:01 |
| * 29 | TABLE ACCESS BY INDEX ROWID BATCHED | FND_LOOKUP_VALUES | 1 | 81 | 5 | 00:00:01 |
| * 30 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 27 | | 2 | 00:00:01 |
| * 31 | INDEX RANGE SCAN | OE_TRANSACTION_TYPES_TL_U2 | 1 | | 1 | 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | OE_TRANSACTION_TYPES_TL | 1 | 31 | 1 | 00:00:01 |
| * 33 | TABLE ACCESS FULL | OE_ORDER_HEADERS_ALL | 477719 | 22452793 | 28364 | 00:00:02 |
| * 34 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 1 | 35 | 1 | 00:00:01 |
| * 35 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 1 | | 1 | 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID BATCHED | OE_ORDER_LINES_ALL | 16 | 704 | 5 | 00:00:01 |
| * 37 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 16 | | 2 | 00:00:01 |
| 38 | VIEW PUSHED PREDICATE | VW_SSQ_1 | 1 | 15 | 3 | 00:00:01 |
| 39 | SORT GROUP BY | | 1 | 10 | 3 | 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_RESERVATIONS | 1 | 10 | 3 | 00:00:01 |
| * 41 | INDEX RANGE SCAN | MTL_RESERVATIONS_N2 | 1 | | 2 | 00:00:01 |
| * 42 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 1 | 24 | 3 | 00:00:01 |
| * 43 | INDEX RANGE SCAN | XXRA_CUSTOMER_TRX_LINES_N15 | 1 | | 2 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 8 - access("MP"."ORGANIZATION_ID"=:B1)
* 9 - access("ORGANIZATION_ID"=:B1 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='ORA$BASE')
* 9 - filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGANIZATION_UNITS',"ORGANIZATION_ID"))='TRUE')
* 10 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_BUSINESS_GROUP_ID"()))
* 11 - access("HAO"."ORGANIZATION_ID"=:B1)
* 13 - access("HOI2"."ORGANIZATION_ID"=:B1)
* 13 - filter("HOI2"."ORG_INFORMATION_CONTEXT"||''='Accounting Information')
* 14 - filter("LGR"."OBJECT_TYPE_CODE"='L' AND NVL("LGR"."COMPLETE_FLAG",'Y')='Y')
* 15 - access("LGR"."LEDGER_ID"=TO_NUMBER(DECODE(RTRIM(TRANSLATE("HOI2"."ORG_INFORMATION1",'0123456789',' ')),NULL,"HOI2"."ORG_INFORMATION1",'-99999')))
* 16 - access("HOI1"."ORGANIZATION_ID"=:B1)
* 16 - filter("HOI1"."ORG_INFORMATION_CONTEXT"||''='CLASS')
* 17 - filter("HOI1"."ORG_INFORMATION1"='INV' AND "HOI1"."ORG_INFORMATION2"='Y')
* 20 - access("DEMAND_SOURCE_LINE_ID"=:B1)
* 21 - filter("OOH"."LAST_UPDATE_DATE">=:B3 AND "OOH"."LAST_UPDATE_DATE"<=:B2 OR "OOL"."LAST_UPDATE_DATE">=:B3 AND "OOL"."LAST_UPDATE_DATE"<=:B2 OR EXISTS (SELECT 0 FROM
"APPS"."RA_CUSTOMER_TRX_LINES_ALL" "RA_CUSTOMER_TRX_LINES_ALL" WHERE "INTERFACE_LINE_ATTRIBUTE6"=TO_CHAR(:B1) AND "INTERFACE_LINE_CONTEXT"='ORDER ENTRY' AND "ORG_ID"=:B2 AND "CREATION_DATE">=:B3
AND TO_NUMBER("INTERFACE_LINE_ATTRIBUTE1")=:B3) OR "OOH"."ORDER_NUMBER"=NVL(:B1,"OOH"."ORDER_NUMBER"))
* 25 - access("OOH"."ORDER_TYPE_ID"="OTT"."TRANSACTION_TYPE_ID")
* 29 - filter("DESCRIPTION"=:B6 AND "TAG"=:B8 AND "ENABLED_FLAG"=:B7 AND NVL(TRUNC(INTERNAL_FUNCTION("START_DATE_ACTIVE")),TRUNC(SYSDATE@!))<=TRUNC(SYSDATE@!) AND
NVL(TRUNC(INTERNAL_FUNCTION("END_DATE_ACTIVE")),TRUNC(SYSDATE@!))>=TRUNC(SYSDATE@!))
* 30 - access("LOOKUP_TYPE"=:B9 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20160910_0212')
* 30 - filter("LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20160910_0212')
* 31 - access("OTT"."NAME"="MEANING" AND "OTT"."LANGUAGE"=USERENV('LANG'))
* 33 - filter("OOH"."ORDER_CATEGORY_CODE"=:B6 AND ("OOH"."ORG_ID"=TO_NUMBER(:B5) OR "OOH"."ORG_ID"=TO_NUMBER(:B4)))
* 34 - filter("HCA"."CUSTOMER_CLASS_CODE"='DL')
* 35 - access("OOH"."SOLD_TO_ORG_ID"="HCA"."CUST_ACCOUNT_ID")
* 37 - access("OOH"."HEADER_ID"="OOL"."HEADER_ID")
* 41 - access("DEMAND_SOURCE_LINE_ID"="OOL"."LINE_ID")
* 42 - filter("INTERFACE_LINE_CONTEXT"='ORDER ENTRY' AND "ORG_ID"=:B1 AND "CREATION_DATE">=:B3)
* 43 - access("INTERFACE_LINE_ATTRIBUTE6"=TO_CHAR(:B1))
* 43 - filter(TO_NUMBER("INTERFACE_LINE_ATTRIBUTE1")=:B1)