Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Performance issue with one of the sql

Nishant RanjanJun 15 2023

We have one sql that is running long .sql in select clause is causing issue.i collect gather plan stats and in that also i foundbelow sql is taking time

SELECT DISTINCT FIRST_VALUE(MODEL_STRING) OVER (
PARTITION BY OL.INVENTORY_ITEM_ID ORDER BY OL.CREATION_DATE
)
FROM XXOM.XXOM_3LP_SYM_ORA_ORDER_LINES ML
,APPS.OE_ORDER_LINES_ALL OL

SELECT
TRUNC(PH.CREATION_DATE) ORDERED_DATE
,PH.TYPE_LOOKUP_CODE PO_TYPE
,PH.AUTHORIZATION_STATUS PO_STATUS
,PLL.CLOSED_CODE LINE_STATUS
,PH.SEGMENT1 PO_NUMBER
,PL.LINE_NUM PO_LINE_NUM
,(
SELECT MAX(RELEASE_NUM)
FROM APPS.PO_RELEASES_ALL
WHERE PO_HEADER_ID = PH.PO_HEADER_ID

   ) PO\_RELEASE\_NUM  

,(
SELECT MSI.SEGMENT1
FROM APPS.MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
AND MSI.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
) ITEM
,COALESCE(PL.ITEM_DESCRIPTION, (
SELECT MSI.DESCRIPTION
FROM APPS.MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
AND MSI.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
)) ITEM_DESCRIPTION
,PLL.QUANTITY
,PLL.QUANTITY_RECEIVED
,PV.VENDOR_NAME SUPPLIER_NAME
,PVS.VENDOR_SITE_CODE SUPPLIER_SITE
,(
SELECT PEO.FULL_NAME
FROM APPS.PER_ALL_PEOPLE_F PEO
WHERE PH.AGENT_ID = PEO.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
) BUYER
,PH.REVISION_NUM PO_REV
,PH.COMMENTS PO_DESCRIPTION
,PH.CURRENCY_CODE CURRENCY
,PL.UNIT_PRICE LINE_PRICE_IN_FOREIGN_CURRENCY
,PL.UNIT_PRICE * PLL.QUANTITY EXTENDED_LINE_VALUE_IN_F_C
,(
SELECT SUM(PL1.QUANTITY * PL1.UNIT_PRICE)
FROM APPS.PO_LINES_ALL PL1
WHERE PL1.PO_HEADER_ID = PL.PO_HEADER_ID
) TOTAL_AMOUNT
,ROUND(NVL(ph.rate, 1) * PL.UNIT_PRICE, 5) LINE_PRICE_IN_USD
,(
SELECT LOCATION_CODE
FROM APPS.HR_LOCATIONS
WHERE LOCATION_ID = PH.SHIP_TO_LOCATION_ID
) SHIP_TO
,(
SELECT LOCATION_CODE
FROM APPS.HR_LOCATIONS
WHERE LOCATION_ID = PH.BILL_TO_LOCATION_ID
) BILL_TO
,POA.ACTION
,POA.ACCEPTED_FLAG ACCEPTED
,(
SELECT POC.DISPLAYED_FIELD ACCEPTANCE_TYPE
FROM APPS.PO_LOOKUP_CODES POC
WHERE POC.LOOKUP_TYPE(+) = 'ACCEPTANCE TYPE'
AND POC.LOOKUP_CODE(+) = POA.ACCEPTANCE_LOOKUP_CODE
) ACCEPTANCE_TYPE
,PL.ITEM_REVISION ITEM_REV
,PL.NOTE_TO_VENDOR NOTE_TO_SUPPLIER
,PLL.NOTE_TO_RECEIVER
,LINE_TYPE
,PL.UNIT_MEAS_LOOKUP_CODE UOM
,ROUND(NVL(ph.rate, 1) * PL.UNIT_PRICE, 5) * PLL.QUANTITY EXTENDED_LINE_VALUE_IN_USD

,(
SELECT DISTINCT FIRST_VALUE(MODEL_STRING) OVER (
PARTITION BY OL.INVENTORY_ITEM_ID ORDER BY OL.CREATION_DATE
)
FROM XXOM.XXOM_3LP_SYM_ORA_ORDER_LINES ML
,APPS.OE_ORDER_LINES_ALL OL
WHERE ML.LINE_ID = OL.LINE_ID
AND ML.ORG_ID = OL.ORG_ID
AND PL.ITEM_ID = OL.INVENTORY_ITEM_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
AND MODEL_STRING IS NOT NULL
) MODEL_STRING
,PLL.QUANTITY_CANCELLED
,CAST(to_clob(CASE
WHEN :IncludeAttachementData = 'No'
THEN NULL
ELSE (
SELECT /*listagg(fdct.user_name, ', ') within
GROUP (
ORDER BY fdct.user_name
,dst.short_text
) user_name */
RTRIM(XMLAGG(XMLELEMENT("user", regexp_replace(fdct.user_name, '[[:cntrl:]]', ''), ', ').EXTRACT('//text()') ORDER BY regexp_replace(fdct.user_name, '[[:cntrl:]]', '')).GetClobVal(), ', ') AS user_name
FROM apps.fnd_attached_documents b
,apps.fnd_document_categories_tl fdct
,apps.fnd_documents c
,apps.fnd_documents_short_text dst
WHERE c.document_id = b.document_id
AND C.category_id = fdct.category_id
AND dst.media_id = c.media_id
AND c.datatype_id = 1
AND fdct.LANGUAGE = 'US'
AND b.pk1_value = TO_CHAR(pl.po_line_id)
AND :IncludeAttachementData = 'Yes'
)
END) AS VARCHAR2(4000)) ATTACHMENT_TYPE
,CAST(to_clob(CASE
WHEN :IncludeAttachementData = 'No'
THEN NULL
ELSE replace(replace((
SELECT /*listagg(dst.short_text, ', ') within
GROUP (
ORDER BY fdct.user_name
,dst.short_text
) short_text*/
RTRIM(XMLAGG(XMLELEMENT("text", REGEXP_REPLACE(regexp_replace(dst.short_text, '[[:cntrl:]]', ''), '[^' || CHR(32) || '-' || CHR(127) || ']', ' '), ', ').EXTRACT('//text()') ORDER BY regexp_replace(dst.short_text, '[[:cntrl:]]', '')).GetClobVal(), ', ') AS short_text
FROM apps.fnd_attached_documents b
,apps.fnd_document_categories_tl fdct
,apps.fnd_documents c
,apps.fnd_documents_short_text dst
WHERE c.document_id = b.document_id
AND C.category_id = fdct.category_id
AND dst.media_id = c.media_id
AND c.datatype_id = 1
AND fdct.LANGUAGE = 'US'
AND b.pk1_value = TO_CHAR(pl.po_line_id)
AND :IncludeAttachementData = 'Yes'
), chr(10), '; '), chr(13), '; ')
END) AS VARCHAR2(4000)) ATTACHMENT_DETAILS
,TRUNC(PLL.PROMISED_DATE) PROMISED_DATE
,TRUNC(PLL.NEED_BY_DATE) NEED_BY_DATE
,TRUNC((
SELECT DISTINCT first_value(plla.promised_date) OVER (
ORDER BY revision_num ASC
) AS original_promise_date
FROM apps.po_line_locations_archive_all plla
WHERE plla.line_location_id = pll.line_location_id
AND plla.promised_date IS NOT NULL
)) ORIGINAL_PROMISED_DATE
,CASE
WHEN (
PLL.INSPECTION_REQUIRED_FLAG = 'N'
AND PLL.RECEIPT_REQUIRED_FLAG = 'N'
)
THEN '2WAY'
WHEN (
PLL.INSPECTION_REQUIRED_FLAG = 'N'
AND PLL.RECEIPT_REQUIRED_FLAG = 'Y'
)
THEN '3WAY'
WHEN (
PLL.INSPECTION_REQUIRED_FLAG IS NULL
OR PLL.RECEIPT_REQUIRED_FLAG IS NULL
)
THEN '3WAY'
ELSE '4WAY'
END MATCH_APPROVAL_LEVEL
,PLL.ATTRIBUTE2 RESCHEDULE_RESP
,PLL.ATTRIBUTE3 BUYER_NOTES
,PLL.ATTRIBUTE1 EARLY_SHIPMENT
,(
SELECT RU.ROUTING_NAME
FROM APPS.RCV_ROUTING_HEADERS RU
WHERE RU.ROUTING_HEADER_ID = PLL.RECEIVING_ROUTING_ID
) RECEIPT_ROUTING
,(
SELECT PEO.FULL_NAME
FROM APPS.PER_ALL_PEOPLE_F PEO
WHERE PD.DELIVER_TO_PERSON_ID = PEO.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
) REQUESTOR
,COALESCE((
SELECT LOCATION_CODE
FROM APPS.HR_LOCATIONS
WHERE LOCATION_ID = PD.DELIVER_TO_LOCATION_ID
), (
SELECT ADDRESS1 || '-' || CITY
FROM Apps.Hz_Locations
WHERE LOCATION_ID = PD.DELIVER_TO_LOCATION_ID
)) DELIVER_TO
,PD.DESTINATION_SUBINVENTORY SUBINVENTORY
,PRHA.SEGMENT1 REQUISITION_NUMBER
,PRLA.LINE_NUM REQ_LINE_NUM
,OEH.ORDER_NUMBER RELATED_SO_NUMBER
,OEL.LINE_NUMBER RELATED_SO_LINE_NUMBER
,(
SELECT GCCK.CONCATENATED_SEGMENTS
FROM APPS.GL_CODE_COMBINATIONS_KFV GCCK
WHERE GCCK.CODE_COMBINATION_ID = PD.ACCRUAL_ACCOUNT_ID
) PO_ACCRUAL_ACCOUNT
,(
SELECT GCCK.CONCATENATED_SEGMENTS
FROM APPS.GL_CODE_COMBINATIONS_KFV GCCK
WHERE GCCK.CODE_COMBINATION_ID = PD.CODE_COMBINATION_ID
) DESTINATION_CHARGE_ACCOUNT
,PD.ATTRIBUTE15 AR_NUMBER
,PLL.SHIPMENT_NUM
,PL.ATTRIBUTE1 DRAWING_REV
,PL.ATTRIBUTE4 BOM_REV
,MP.ORGANIZATION_CODE
,COALESCE(PH.CLOSED_CODE, 'OPEN') CLOSED_CODE
,COALESCE(PL.CLOSED_CODE, 'OPEN') LINE_CLOSED_CODE
,PLL.QUANTITY - PLL.QUANTITY_RECEIVED OPEN_QTY
,(
SELECT MCB.SEGMENT2
FROM APPS.MTL_ITEM_CATEGORIES MIC
,APPS.MTL_CATEGORIES_KFV MCB
,APPS.MTL_CATEGORY_SETS_B MCS
WHERE MIC.CATEGORY_ID = MCB.CATEGORY_ID
AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
AND MIC.INVENTORY_ITEM_ID = PL.ITEM_ID
AND MIC.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
AND MCS.CATEGORY_SET_ID = 1100000041
AND ROWNUM < 2
) PRODUCT_BRANCH
,(
SELECT MCB.SEGMENT1 DIVISION_ID
FROM apps.MTL_ITEM_CATEGORIES MIC
,apps.MTL_CATEGORIES_B MCB
WHERE MIC.CATEGORY_ID = MCB.CATEGORY_ID
AND PL.ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = 85
AND MIC.CATEGORY_SET_ID = 1100000021
AND ROWNUM < 2
) DIVISION_ID
,(
SELECT meaning
FROM apps.fnd_lookup_values
WHERE lookup_type = 'INVOICE MATCH OPTION'
AND LANGUAGE = 'US'
AND lookup_code = pll.MATCH_OPTION
AND ROWNUM < 2
) INVOICE_MATCH_OPTION
,CASE
WHEN COALESCE(PLL.CLOSED_CODE, 'OPEN') <> 'OPEN'
THEN 'CLOSED'
WHEN TRUNC(PLL.PROMISED_DATE) < TRUNC(SYSDATE)
THEN 'OVERDUE PD'
WHEN (
(TRUNC(PLL.PROMISED_DATE) - TRUNC(SYSDATE)) - ((TRUNC(PLL.PROMISED_DATE, 'D') - TRUNC(SYSDATE, 'D')) / 7 * 2) - (
CASE
WHEN TO_CHAR(TRUNC(SYSDATE), 'DY', 'nls_date_language=english') = 'SUN'
AND TRUNC(PLL.PROMISED_DATE) <> TRUNC(SYSDATE)
THEN 1
ELSE 0
END
) - (
CASE
WHEN TO_CHAR(PLL.PROMISED_DATE, 'DY', 'nls_date_language=english') = 'SAT'
AND TRUNC(PLL.PROMISED_DATE) <> TRUNC(SYSDATE)
THEN 1
ELSE 0
END
) - (
SELECT COUNT(bce.exception_date) EXC
FROM apps.bom_calendar_exceptions bce
WHERE bce.calendar_code = MP.CALENDAR_CODE
AND TRUNC(bce.exception_date) BETWEEN TRUNC(SYSDATE)
AND TRUNC(PLL.PROMISED_DATE)
) + (
SELECT COUNT(bce.exception_date) EXC
FROM apps.bom_calendar_exceptions bce
WHERE bce.calendar_code = MP.CALENDAR_CODE
AND TRUNC(bce.exception_date) BETWEEN TRUNC(PLL.PROMISED_DATE)
AND TRUNC(SYSDATE)
)
) BETWEEN 0
AND 9
THEN 'IN-TRANSIT PO'
WHEN PLL.PROMISED_DATE IS NULL
AND (
(TRUNC(PLL.NEED_BY_DATE) - TRUNC(SYSDATE)) - ((TRUNC(PLL.NEED_BY_DATE, 'D') - TRUNC(SYSDATE, 'D')) / 7 * 2) - (
CASE
WHEN TO_CHAR(TRUNC(SYSDATE), 'DY', 'nls_date_language=english') = 'SUN'
AND TRUNC(PLL.NEED_BY_DATE) <> TRUNC(SYSDATE)
THEN 1
ELSE 0
END
) - (
CASE
WHEN TO_CHAR(PLL.NEED_BY_DATE, 'DY', 'nls_date_language=english') = 'SAT'
AND TRUNC(PLL.NEED_BY_DATE) <> TRUNC(SYSDATE)
THEN 1
ELSE 0
END
) - (
SELECT COUNT(bce.exception_date) EXC
FROM apps.bom_calendar_exceptions bce
WHERE bce.calendar_code = MP.CALENDAR_CODE
AND TRUNC(bce.exception_date) BETWEEN TRUNC(SYSDATE)
AND TRUNC(PLL.NEED_BY_DATE)
) + (
SELECT COUNT(bce.exception_date) EXC
FROM apps.bom_calendar_exceptions bce
WHERE bce.calendar_code = MP.CALENDAR_CODE
AND TRUNC(bce.exception_date) BETWEEN TRUNC(PLL.NEED_BY_DATE)
AND TRUNC(SYSDATE)
)
) <= 9
THEN 'PENDING OA'
END OVERDUE_PO_STATUS

,zxd.input_tax_classification_code TAX_CODE
,(
SELECT MAC.ABC_CLASS_NAME
FROM apps.mtl_abc_classes mac
,apps.mtl_abc_assignments maa
,apps.mtl_abc_assignment_groups aag
WHERE mac.abc_class_id = maa.abc_class_id
AND maa.inventory_item_id = PL.ITEM_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = mac.organization_id
AND maa.assignment_group_id = aag.assignment_group_id
AND aag.assignment_group_name IN (
'SG4 PLANNING ABC'
)
AND aag.organization_id = PLL.SHIP_TO_ORGANIZATION_ID
AND ROWNUM < 2
) ABC_CLASSIFICATION
,(
SELECT MSI.PLANNER_CODE
FROM APPS.MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
AND MSI.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
) PLANNER_CODE
--,PLL.attribute3 BUYER_NOTES
,PLL.attribute7 PDSL_RDSL_COMMENT
,POA.ACTION_DATE
,pll.attribute5 pdsl_cause_coding
,pll.attribute6 rdsl_cause_coding
,NVL((
SELECT max(trunc(TRANSACTION_DATE))
FROM apps.RCV_TRANSACTIONS rt
WHERE PLL.PO_HEADER_ID = rt.PO_HEADER_ID
AND PLL.PO_LINE_ID = rt.PO_LINE_ID
AND PLL.LINE_LOCATION_ID = rt.PO_LINE_LOCATION_ID
AND NVL(PD.PO_DISTRIBUTION_ID, - 1) = NVL(rt.PO_DISTRIBUTION_ID, - 1)
AND rt.TRANSACTION_TYPE = 'RECEIVE'
), (
SELECT max(trunc(TRANSACTION_DATE))
FROM apps.RCV_TRANSACTIONS rt
WHERE PLL.PO_HEADER_ID = rt.PO_HEADER_ID
AND PLL.PO_LINE_ID = rt.PO_LINE_ID
AND PLL.LINE_LOCATION_ID = rt.PO_LINE_LOCATION_ID
-- AND NVL(PD.PO_DISTRIBUTION_ID, - 1) = NVL(rt.PO_DISTRIBUTION_ID, - 1)
AND rt.TRANSACTION_TYPE = 'RECEIVE'
)) receipt_date
,PH.COMMENTS
FROM APPS.PO_LINES_ALL PL
,APPS.PO_HEADERS_ALL PH
,APPS.PO_LINE_LOCATIONS_ALL PLL
,APPS.PO_DISTRIBUTIONS_ALL PD
,APPS.ap_suppliers PV
,apps.ap_supplier_sites_all PVS
,apps.mtl_parameters MP
,APPS.PO_LINE_TYPES_TL PLTT
,APPS.PO_ACCEPTANCES POA
,APPS.OE_DROP_SHIP_SOURCES ODS
,APPS.OE_ORDER_LINES_ALL OEL
,APPS.OE_ORDER_HEADERS_ALL OEH
,apps.PO_REQ_DISTRIBUTIONS_ALL PRDA
,apps.PO_REQUISITION_LINES_ALL PRLA
,apps.PO_REQUISITION_HEADERS_ALL PRHA
,apps.zx_lines zxl
,apps.zx_lines_det_factors zxd
WHERE PH.ORG_ID = 9082
AND PH.ORG_ID = PL.ORG_ID
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND PL.PO_HEADER_ID = PLL.PO_HEADER_ID
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PL.ORG_ID = PLL.ORG_ID
AND PLL.PO_HEADER_ID = PD.PO_HEADER_ID(+)
AND PLL.PO_LINE_ID = PD.PO_LINE_ID(+)
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID(+)
AND PH.VENDOR_ID = PV.VENDOR_ID(+)
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND MP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
AND PLTT.LINE_TYPE_ID = PL.LINE_TYPE_ID
AND PLTT.LANGUAGE = 'US'
AND POA.PO_HEADER_ID(+) = PH.PO_HEADER_ID
AND PLL.PO_HEADER_ID = ODS.PO_HEADER_ID(+)
AND PLL.PO_LINE_ID = ODS.PO_LINE_ID(+)
AND PLL.LINE_LOCATION_ID = ODS.LINE_LOCATION_ID(+)
AND OEL.LINE_ID(+) = ODS.LINE_ID
AND OEL.CANCELLED_FLAG(+) <> 'Y'
AND OEH.HEADER_ID(+) = OEL.HEADER_ID
AND PD.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID(+)
AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID(+)
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID(+)
AND zxl.trx_id(+) = pll.po_header_id
AND zxl.trx_line_id(+) = pll.line_location_id
AND zxd.trx_id(+) = zxl.trx_id
AND zxd.trx_line_id(+) = zxl.trx_line_id
AND zxd.entity_code(+) = 'PURCHASE_ORDER'
AND TRUNC(ph.creation_date) BETWEEN TO_DATE('10/01/2022 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND TO_DATE('06/15/2023 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND COALESCE(ph.closed_code, 'OPEN') IN (N'OPEN',N'CLOSED')
AND PLL.SHIP_TO_ORGANIZATION_ID IN (N'12389',N'12390',N'12469',N'9083',N'12549')
AND NVL(PD.DISTRIBUTION_NUM, 1) = NVL((
SELECT MAX(PD2.DISTRIBUTION_NUM)
FROM APPS.PO_DISTRIBUTIONS_ALL PD2
WHERE PD.PO_HEADER_ID = PD2.PO_HEADER_ID
AND PD.PO_LINE_ID = PD2.PO_LINE_ID
AND PD.LINE_LOCATION_ID = PD2.LINE_LOCATION_ID
), 1)
AND (
PH.AGENT_ID IN (:Buyer)
OR - 1 IN (:Buyer)
)
ORDER BY PO_NUMBER
,PO_LINE_NUM
,SHIPMENT_NUM

gather plan stats.txt

Comments
Post Details
Added on Jun 15 2023
7 comments
531 views