Home » SQL & PL/SQL » SQL & PL/SQL » Sql is causing performance issue
| Sql is causing performance issue [message #686496] |
Tue, 27 September 2022 23:52  |
 |
nishant1987
Messages: 8
Registered: September 2022
|
Junior Member |
|
|
SELECT
DISTINCT
pll.po_line_id,
ploc.line_location_id,
(SELECT ptl.line_type
FROM apps.po_line_types_tl ptl
WHERE ptl.line_type_id = pll.line_type_id AND ptl.LANGUAGE = 'US')
"Line_Type",
ploc.quantity_accepted,
NULL
release_approved_date,
NULL
release_date,
NULL
release_hold_flag,
NULL
release_type,
DECODE (ploc.po_release_id, NULL, NULL, ploc.quantity)
released_quantity,
(SELECT /*+ use_nl (msib ploc) */ items.preprocessing_lead_time
FROM apps.mtl_system_items_b items
WHERE items.inventory_item_id = pll.item_id
AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)
"PreProcessing_LT",
(SELECT /*+ use_nl (msib ploc) */items.full_lead_time
FROM apps.mtl_system_items_b items
WHERE items.inventory_item_id = pll.item_id
AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)
"Processing_LT",
(SELECT /*+ use_nl (msib ploc) */ items.postprocessing_lead_time
FROM apps.mtl_system_items_b items
WHERE items.inventory_item_id = pll.item_id
AND items.organization_id = ploc.SHIP_TO_ORGANIZATION_ID)
"PostProcessing_LT",
ploc.firm_status_lookup_code,
NVL (
(SELECT pla.promised_date
FROM apps.po_line_locations_archive_all pla
WHERE pla.po_header_id = pha.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pla.line_location_id = ploc.line_location_id
AND pla.revision_num =
(SELECT MIN (revision_num)
FROM apps.po_line_locations_archive_all plla2
WHERE plla2.promised_date IS NOT NULL
AND plla2.line_location_id =
ploc.line_location_id)),
ploc.promised_date)
"Original_Promise_Date",
(SELECT items.long_description
FROM apps.mtl_system_items_tl items
WHERE items.inventory_item_id = pll.item_id
AND items.organization_id IN
(SELECT fin.inventory_organization_id
FROM apps.financials_system_params_all fin
WHERE fin.org_id = pha.org_id)
AND items.LANGUAGE = 'US')
"Item_Long_Description",
NVL (ploc.approved_flag, 'N')
approved_code,
pvs.country
"Supplier_Site_Country",
pll.note_to_vendor,
NVL (ploc.quantity, 0)
- NVL (ploc.quantity_cancelled, 0)
- NVL (ploc.quantity_received, 0) * ploc.price_override
"Shipment_Amount",
ploc.attribute4
"PO_Ship_Date",
(SELECT meaning
FROM apps.fnd_lookup_values
WHERE lookup_type = 'SHIP_METHOD'
AND lookup_code = ploc.attribute9
AND language = 'US')
"Ship_Method",
(SELECT prla.note_to_receiver
FROM apps.po_req_distributions_all prda
INNER JOIN apps.po_requisition_lines_all prla
ON prda.requisition_line_id = prla.requisition_line_id
WHERE prda.distribution_id = pdi.req_distribution_id)
"Note_To_Receiver",
DECODE (pha.USER_HOLD_FLAG, 'Y', 'Y', pll.USER_HOLD_FLAG)
"Hold_Flag",
(SELECT ABC_CLASS_NAME
FROM APPS.MTL_ABC_ASSIGNMENT_GROUPS ASG
INNER JOIN APPS.MTL_ABC_ASSIGNMENTS ASSI
ON ASG.ASSIGNMENT_GROUP_ID = ASSI.ASSIGNMENT_GROUP_ID
INNER JOIN APPS.MTL_ABC_CLASSES classes
ON ASSI.ABC_CLASS_ID = classes.ABC_CLASS_ID
WHERE ASG.organization_id = ploc.SHIP_TO_ORGANIZATION_ID
AND ASG.ASSIGNMENT_GROUP_NAME = 'MIN ABC Assignment'
AND ASSI.inventory_item_id = pll.item_id)
ABCClass,
(SELECT CONCATENATED_SEGMENTS AS charge_accountsfrom
FROM apps.gl_code_combinations_kfv gcc
WHERE gcc.code_combination_id = pdi.code_combination_id)
AS charge_accounts
FROM apps.po_headers_all pha,
apps.po_lines_all pll,
apps.po_line_locations_all ploc,
apps.po_distributions_all pdi,
-- apps.per_all_people_f papf,
-- apps.AP_SUPPLIERS pv,
apps.AP_SUPPLIER_SITES_ALL pvs,
-- apps.AP_SUPPLIER_CONTACTS pvc,
-- apps.ap_terms apt,
-- apps.po_lookup_codes plc1,
-- apps.po_lookup_codes plc2,
-- apps.hr_locations hlv_line_ship_to,
-- apps.hr_locations hlv_ship_to,
-- apps.hr_locations hlv_bill_to,
apps.hr_organization_units hou
-- ,
-- apps.hr_locations_no_join loc,
-- apps.hr_locations_all_tl hrl1,
-- apps.hr_locations_all_tl hrl2
WHERE 1 = 1
AND pll.po_header_id(+) = pha.po_header_id
AND ploc.po_line_id(+) = pll.po_line_id
AND pdi.line_location_id(+) = ploc.line_location_id
AND ploc.shipment_type IN ('STANDARD', 'PLANNED')
/* AND papf.person_id(+) = pha.agent_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.employee_number IS NOT NULL
AND pv.vendor_id(+) = pha.vendor_id */
AND pvs.vendor_site_id(+) = pha.vendor_site_id
AND pvs.ORG_ID = pha.org_id
-- AND pvc.vendor_contact_id(+) = pha.vendor_contact_id
-- AND apt.term_id(+) = pha.terms_id
/* AND plc1.lookup_code(+) = pha.fob_lookup_code
AND plc1.lookup_type(+) = 'FOB'
AND plc2.lookup_code(+) = pha.freight_terms_lookup_code
AND plc2.lookup_type(+) = 'FREIGHT TERMS'
AND hlv_line_ship_to.location_id(+) = ploc.ship_to_location_id
AND hlv_ship_to.location_id(+) = pha.ship_to_location_id
AND hlv_bill_to.location_id(+) = pha.bill_to_location_id */
AND hou.organization_id = pha.org_id
-- AND hou.location_id = loc.location_id(+)
/* AND hrl1.location_id(+) = pha.ship_to_location_id
AND hrl1.LANGUAGE(+) = 'US'
AND hrl2.location_id(+) = pha.bill_to_location_id
AND hrl2.LANGUAGE(+) = 'US' */
AND hou.organization_id IN (2763)
AND NVL (pha.closed_code, 'OPEN') IN ('OPEN', 'CLOSED')
AND NVL (pll.closed_code, 'OPEN') IN ('OPEN', 'CLOSED')
AND NVL (ploc.cancel_flag, 'N') = 'N'
AND pha.authorization_status IN
('APPROVED', 'REQUIRES REAPPROVAL', 'IN PROCESS')
|
|
|
|
| Re: Sql is causing performance issue [message #686498 is a reply to message #686496] |
Wed, 28 September 2022 01:15  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You are projecting ten (I think) scalar subqueries. Projecting a subquery is perhaps the most horrible form of correlation there is. Each of those queries will be run repeatedly, the worst case being once for every row returned by the outer query. If you rewrite the query to use outer joins instead, you may find a big improvement.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 22:26:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
