I have one sql that is running long around 120 min just to fetch 20k record and issue i think is with per_all_people_f which is outer join and po_line_locations_all which is not exist.i tried with clause also but it didn't work much.
SELECT haou.name
operating_unit,
pr_organization.organization_code
organization,
prh.segment1
requisition_number,
buyer.full_name
buyer,
preparer.full_name
preparer,
ood.organization_name
PR_ORGANIZATION,
CASE
WHEN oola.source_type_code = 'EXTERNAL'
THEN
hl.address1
|| ', '
|| hl.address2
|| ', '
|| hl.address2
|| ', '
|| hl.address3
|| ', '
|| hl.address4
|| ', '
|| hl.city
|| ', '
|| hl.postal_code
|| ', '
|| hl.country
ELSE
del_loc.description
END
PR_SHIP_TO_LOCATION,
pv.vendor_name
SUPPLIER,
pvs.vendor_site_code
SUPPLIER_SITE,
prl.line_num,
pltt.line_type
REQ_LINE_TYPE,
msib.segment1
item,
NVL (msib.description, prl.item_description)
description,
prl.quantity,
prl.need_by_date,
gcck_charge.concatenated_segments
charge_account,
gcck_charge.segment5
PG,
prda.attribute15
AR_NUMBER,
CASE
WHEN prh.type_lookup_code = 'INTERNAL'
AND prh.authorization_status <> 'APPROVED'
THEN
'Internal Req Not Approved'
WHEN prh.type_lookup_code = 'INTERNAL'
AND prh.authorization_status = 'APPROVED'
THEN
'No Internal SO Created'
WHEN prh.type_lookup_code = 'PURCHASE'
THEN
'No PO Created'
END
REQ_ISSUE,
prh.authorization_status
AUTHORIZATION_STATUS,
ooha.order_number
SO,
oola.line_number
SO_LINE,
oola.source_type_code
SO_SOURCE,
oos.name
SO_Type,
(SELECT MAX (comp.ordered_item)
FROM apps.oe_order_lines_all comp
WHERE comp.TOP_MODEL_LINE_ID = oola.top_model_line_id
AND comp.ordered_item IN ('BMMC-BUILD',
'BRMC-BUILD',
'DMMC-BUILD',
'DSI-BUILD',
'EBIC-BUILD',
'IMMC-BUILD',
'JMMC-BUILD',
'KRT-BUILD',
'MBR-BUILD',
'MEM-BUILD',
'MET-BUILD',
'PIC-BUILD',
'RNI-BUILD',
'RSK-BUILD',
'RTC-BUILD',
'RTR-BUILD',
'SMMC-BUILD',
'VENDOR-BUILD',
'WSS-BUILD',
'BKN-BUILD',
'EGF-BUILD',
'MRS-BUILD',
'PER-BUILD',
'PSD-BUILD'))
BUILD_LOCATION,
lpl.assemble_to
ASSEMBLE_TO,
CASE
WHEN ( lpl.model_string LIKE '%C1%'
OR lpl.model_string LIKE '%C2%'
OR lpl.model_string LIKE '%C9%'
OR SUBSTR (lpl.model_string, 3, 3) IN ('485', '585', '405')
OR SUBSTR (lpl.model_string, 3, 4) IN ('1495', '1595')
OR SUBSTR (lpl.model_string, 3, 5) IN ('3095F', '3095M')
OR SUBSTR (lpl.model_string, 3, 6) IN ('3051FA')
OR SUBSTR (lpl.model_string, 3, 7) IN ('3095MFA',
'3095MFP',
'3095MFC',
'3051SFP',
'3051SFC',
'3051CFA',
'3051CFP',
'3051CFA',
'2051CFA',
'2051CFP',
'2051CFC'))
THEN
'YES'
ELSE
'NO'
END
CDS_REQUIRED,
xss.SUBSCRIBER_NAME
ADMIN,
prl.creation_Date,
we.wip_entity_name
WORK_ORDER,
note_to_agent,
ppa.name
project,
ppa.segment1
project_number,
pt.task_name
task,
pt.task_number,
prda.expenditure_type,
O1.NAME
EXPENDITURE_ORGANIZATION_NAME,
prl.destination_type_code
DELIVERTO,
requestor.full_name
requestor,
mck.concatenated_segments
Category,
prl.unit_meas_lookup_code
UOM,
COALESCE (prl.currency_code, gsb.currency_code)
Currency,
ROUND (prl.UNIT_PRICE, 2)
UNIT_PRICE,
REPLACE (mcb.segment1 || '.' || mcb.segment2 || '.', '..', '')
Sourcing_Rule,
prl.line_location_id,
prl.Org_id,
prda.distribution_id
FROM apps.po_requisition_lines_all prl
JOIN apps.PO_REQ_DISTRIBUTIONS_ALL prda
ON prl.requisition_line_id = prda.requisition_line_id
AND prl.org_id = prda.org_id
LEFT JOIN APPS.gl_code_combinations_kfv gcck_charge
ON gcck_charge.code_combination_id = prda.code_combination_id
LEFT JOIN apps.mtl_system_items_b msib
ON msib.inventory_item_id = prl.item_id
AND msib.organization_id = 85
JOIN apps.PO_LINE_TYPES_tl pltt
ON pltt.line_type_id = prl.line_type_id
JOIN apps.po_requisition_headers_all prh
ON prh.org_id = prl.org_id
AND prh.requisition_header_id = prl.requisition_header_id
JOIN apps.hr_all_organization_units haou
ON haou.organization_id = prh.org_id
LEFT JOIN apps.per_all_people_f buyer
ON prl.suggested_buyer_id = buyer.person_id
LEFT JOIN apps.per_all_people_f preparer
ON prh.preparer_id = preparer.person_id
LEFT JOIN apps.per_all_people_f requestor
ON prl.to_person_id = requestor.person_id
LEFT JOIN apps.ap_suppliers pv ON pv.vendor_id = prl.vendor_id
LEFT JOIN apps.ap_supplier_sites_all pvs
ON pvs.vendor_site_id = prl.vendor_site_id
LEFT JOIN apps.mtl_parameters pr_organization
ON pr_organization.organization_id =
prl.destination_organization_id
LEFT JOIN apps.hr_locations del_loc
ON del_loc.location_id = prl.deliver_to_location_id
LEFT JOIN apps.oe_order_lines_all oola
ON TO_CHAR (oola.line_id) = prl.attribute1
LEFT JOIN apps.oe_order_headers_all ooha
ON oola.header_id = ooha.header_id
LEFT JOIN apps.oe_order_sources oos
ON oos.order_source_id = ooha.order_source_id
LEFT JOIN APPS.mtl_categories_kfv mck
ON mck.category_id = prl.category_id
JOIN apps.FINANCIALS_SYSTEM_PARAMS_ALL FSP ON prh.org_id = fsp.org_id
JOIN apps.gl_ledgers gsb ON FSP.SET_OF_BOOKS_ID = GSB.ledger_id
LEFT JOIN apps.org_organization_definitions ood
ON ood.organization_id = prl.destination_organization_id
LEFT JOIN apps.xxom_3lp_sym_ora_order_lines lpl
ON lpl.line_id = oola.line_id
AND lpl.org_id = oola.org_id
AND lpl.header_id = oola.header_id
LEFT JOIN apps.XXONT_SOM_SCHEDULER xss
ON TO_CHAR(xss.SUBSCRIBER_ID) = lpl.ORDER_ADMIN
LEFT JOIN apps.pa_projects_all ppa ON ppa.project_id = prda.project_id
LEFT JOIN wip.wip_entities we ON prl.wip_entity_id = we.wip_entity_id
LEFT JOIN apps.PA_TASKS pt ON prda.task_id = pt.task_id
LEFT JOIN apps.HR_ALL_ORGANIZATION_UNITS_TL O1
ON prda.expenditure_organization_id = O1.ORGANIZATION_ID
AND O1.language = 'US'
LEFT JOIN apps.hz_locations hl
ON hl.location_id = prl.deliver_to_location_id
LEFT JOIN apps.mtl_item_categories mic
ON mic.inventory_item_id = msib.inventory_item_id
AND 1100000906 = mic.category_set_id
AND mic.organization_id = oola.ship_from_org_id
LEFT JOIN apps.mtl_categories_b mcb
ON mcb.category_id = mic.category_id
WHERE TYPE = 'OU'
AND pltt.language = 'US'
AND mck.attribute_category = 'PO Item Category'
AND SYSDATE BETWEEN NVL (buyer.effective_start_date, SYSDATE)
AND NVL (buyer.effective_end_date, SYSDATE)
AND SYSDATE BETWEEN NVL (preparer.effective_start_date, SYSDATE)
AND NVL (preparer.effective_end_date, SYSDATE)
AND SYSDATE BETWEEN NVL (requestor.effective_start_date, SYSDATE)
AND NVL (requestor.effective_end_date, SYSDATE)
AND prh.authorization_status = 'APPROVED'
AND NVL (prh.closed_code, 'XX') <> 'FINALLY CLOSED'
AND COALESCE (prl.closed_code, 'XX') <> 'FINALLY CLOSED'
AND prh.type_lookup_code <> 'INTERNAL'
AND prl.cancel_flag = 'N'
AND prh.org_id IN (281,
2744,
2844,
3718,
3722,
4153,
4190,
4195,
4488,
4511,
4575,
4622,
4680,
5241,
5259,
6281,
6319,
6421,
6720,
6741)
AND NOT EXISTS
(SELECT /*+ no_unnest push_subq*/1
FROM apps.PO_LINE_LOCATIONS_ALL plla
WHERE prl.Line_Location_Id = plla.Line_Location_Id
AND prl.org_id = plla.Org_id
AND plla.org_id IN (281,
2744,
2844,
3718,
3722,
4153,
4190,
4195,
4488,
4511,
4575,
4622,
4680,
5241,
5259,
6281,
6319,
6421,
6720,
6741))
AND NOT EXISTS
(SELECT /*+ no_unnest push_subq*/1
FROM ont.oe_order_headers_all h,
apps.oe_order_lines_all l,
apps.org_organization_definitions oods
WHERE h.order_source_id = 10
AND h.header_id = l.header_id
AND h.orig_sys_document_ref = prh.segment1
AND h.source_document_id =
TO_NUMBER (prl.requisition_header_id)
AND l.orig_sys_document_ref = prh.segment1
AND l.orig_sys_line_ref = TO_CHAR (prl.line_num)
AND h.source_document_id = l.source_document_id
AND h.org_id = oods.operating_unit
AND prl.source_organization_id = oods.organization_id)