Home » RDBMS Server » Performance Tuning » Sql having performance issue (19c)
Sql having performance issue [message #686704] Sun, 27 November 2022 02:53 Go to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
One of the sql having performance issue and the issue is Min(from_date) and max(from_date).After commenting out data comes very quickly as it is in with select clause.I am attaching sql text.how to handle Min(from_date) and Max(from_date)


WITH
    EXCEPTIONS
    AS
        (SELECT e.organization_code
                    ORG,
                e.exception_type,
                e.order_type_id,
                e.item_segments
                    ITEM,
                si.sr_inventory_item_id,
                e.inventory_item_id,
                e.item_description
                    ITEM_DESCRIPTION,
                e.supplier_name
                    SUPPLIER_NAME,
                e.supplier_site
                    SUPPLIER_SITE,
                e.order_type
                    ORDER_TYPE,
                e.quantity
                    QUANTITY,
                e.old_due_date
                    CURRENTLY_SCHED_DUE_DATE,
                e.new_dock_date
                    SUGGESTED_DOCK_DATE,
                e.planner_code
                    PLANNER_CODE,
                e.excess_duration
                    max_excess_duration,
                (SELECT COUNT (*) - 1
                   FROM apps.bom_calendar_dates
                  WHERE     calendar_code =
                            (SELECT calendar_code
                               FROM apps.mtl_parameters
                              WHERE organization_id = e.organization_id)
                        AND calendar_date >=
                            (SELECT MIN (from_date)
                               FROM [email]apps.msc_exception_details_v@xxbetsytobilly.emrsn.com[/email]
                              WHERE     plan_id = e.plan_id
                                    AND exception_type = e.exception_type
                                    AND sr_instance_id = 81
                                    AND organization_id = e.organization_id
                                    AND inventory_item_id =
                                        e.inventory_item_id
                                    AND category_set_id = 24)
                        AND calendar_date <=
                            (SELECT MAX (TO_DATE)
                               FROM [email]apps.msc_exception_details_v@xxbetsytobilly.emrsn.com[/email]
                              WHERE     plan_id = e.plan_id
                                    AND exception_type = e.exception_type
                                    AND sr_instance_id = 81
                                    AND organization_id = e.organization_id
                                    AND inventory_item_id =
                                        e.inventory_item_id
                                    AND category_set_id = 24)
                        AND seq_num IS NOT NULL)
                    shortage_duration2,
                e.shortage_duration
                    shortage_duration,
                ROUND (e.below_ss_qty_pct / 100, 3)
                    MAX_PERCENT_BELOW_SS,
                e.from_date
                    EARLIEST_FROM_DATE,
                e.TO_DATE
                    LATEST_TO_DATE,
                ROUND ((e.quantity / e.below_ss_qty_pct) * 100, 0)
                    SAFETY_STOCK_QUANTITY,
                e.source_organization_code,
                e.category_name
                    COMMODITY_CODE,
                e.order_date,
                e.transaction_id,
                e.organization_id,
                s.order_number,
                s.po_line_id,
                (SELECT MIN (msr.sourcing_rule_name)
                   FROM [email]apps.msc_sr_assignments@xxbetsytobilly.emrsn.com[/email]  msa,
                        [email]apps.msc_sourcing_rules@xxbetsytobilly.emrsn.com[/email]  msr
                  WHERE     msa.inventory_item_id = e.inventory_item_id
                        AND msa.organization_id = e.organization_id
                        AND msa.sr_instance_id = 81
                        AND msr.sr_instance_id = 81
                        AND msa.sourcing_rule_id = msr.sourcing_rule_id)
                    sourcing_rule,
                si.replenish_to_order_flag,
                si.planning_make_buy_code,
                e.buyer_name,
                e.exception_type_text
           FROM [email]apps.msc_exception_details_v@xxbetsytobilly.emrsn.com[/email]  e,
                [email]apps.msc_system_items@xxbetsytobilly.emrsn.com[/email]         si,
                [email]apps.msc_supplies@xxbetsytobilly.emrsn.com[/email]             s
          WHERE     e.plan_id = 1108
                AND e.exception_type IN (2,
                                         6,
                                         7,
                                         8,
                                         10,
                                         20,
                                         15,
                                         16)
                AND e.sr_instance_id = 81
                AND e.organization_id IN (16597,5000,10505,4154)
                AND (   'ALL' IN ('ALL')
                     OR e.planner_code IN ('ALL'))
                AND e.category_set_id = 24           -- Inventory category set
                AND e.inventory_item_id = si.inventory_item_id
                AND e.organization_id = si.organization_id
                AND e.plan_id = si.plan_id
                AND e.sr_instance_id = si.sr_instance_id
                AND e.plan_id = s.plan_id(+)
                AND e.sr_instance_id = s.sr_instance_id(+)
                AND e.transaction_id = s.transaction_id(+)),
    PLANNEDORDERS
    AS
        (SELECT 'Recommendation',
                mo.organization_code,
                mo.item_segments,
                mo.description,
                mo.order_number,
                mo.order_type_text,
                mo.quantity_rate,
                mo.new_dock_date,
                mo.new_due_date,
                mo.abc_class,
                mo.planner_code,
                si.full_lead_time
                    processing_lead_time,
                mo.source_organization_code,
                mo.new_order_date,
                mo.inventory_item_id,
                mo.organization_id,
                si.planning_make_buy_code,
                mo.buyer_name,
                si.sr_inventory_item_id,
                si.replenish_to_order_flag,
                si.full_lead_time,
                (SELECT MIN (msr.sourcing_rule_name)
                   FROM [email]apps.msc_sr_assignments@xxbetsytobilly.emrsn.com[/email]  msa,
                        [email]apps.msc_sourcing_rules@xxbetsytobilly.emrsn.com[/email]  msr
                  WHERE     msa.inventory_item_id = mo.inventory_item_id
                        AND msa.organization_id = mo.organization_id
                        AND msa.sr_instance_id = 81
                        AND msr.sr_instance_id = 81
                        AND msa.sourcing_rule_id = msr.sourcing_rule_id)
                    sourcing_rule
           FROM [email]apps.msc_orders_v@xxbetsytobilly.emrsn.com[/email]      mo,
                [email]apps.msc_system_items@xxbetsytobilly.emrsn.com[/email]  si
          WHERE     mo.source_table = 'MSC_SUPPLIES'
                AND mo.organization_id IN (16597,5000,10505,4154)
                AND mo.plan_id = 1108
                AND mo.order_type = 5                         --Planned Orders
                AND mo.days_from_today < 2
                AND mo.category_set_id = 24
                AND (   'ALL' IN ('ALL')
                     OR mo.planner_code IN ('ALL'))
                AND mo.inventory_item_id = si.inventory_item_id
                AND mo.organization_id = si.organization_id
                AND mo.plan_id = si.plan_id
                AND mo.sr_instance_id = si.sr_instance_id),
    X
    AS
        (  /***** PO in receiving' and Purchase orders  *****/
           SELECT 1
                      AS querypart,
                  --regexp_replace(listagg(e.exception_type_text,',') WITHIN GROUP (Order by e.exception_type),'([^,]+)(,\1)+', '\1') EXCEPTION_TYPE
                  TO_CHAR (
                      REGEXP_REPLACE (
                          RTRIM (
                              XMLAGG (XMLELEMENT (e,
                                                  e.exception_type_text,
                                                  ',').EXTRACT ('//text()') ORDER BY
                                                                                e.exception_type).getclobval (),
                              ', '),
                          '([^,]+)(,\1)+',
                          '\1'))
                      EXCEPTION_TYPE,
                  e.ORG,
                  e.item
                      ITEM,
                  e.item_description
                      ITEM_DESCRIPTION,
                  e.supplier_name
                      SUPPLIER_NAME,
                  e.supplier_site
                      SUPPLIER_SITE,
                  REGEXP_REPLACE (
                      (SELECT LISTAGG (
                                  aslinner.primary_vendor_item, ', ')
                                  WITHIN GROUP (ORDER BY
                                                    aslinner.primary_vendor_item)
                         FROM apps.po_approved_supplier_list aslinner
                        WHERE     1 = 1
                              AND aslinner.item_id = msib.inventory_item_id
                              AND aslinner.owning_organization_id =
                                  msib.organization_id),
                      '([^,]+)(,\1)*(,|$)')
                      supplier_item                   /* comma list required*/
                                   ,
                  pha.segment1
                      order_number,
                  pla.line_num,
                  e.order_type
                      ORDER_TYPE,
                  e.quantity
                      QUANTITY,
                  e.CURRENTLY_SCHED_DUE_DATE,
                  e.SUGGESTED_DOCK_DATE,
                  a.abc_class_name
                      ABC_CLASSIFICATION,
                  e.planner_code
                      PLANNER_CODE,
                  NVL (e.buyer_name, ppf.full_name)
                      BUYER_CODE,
                  msib.full_lead_time
                      processing_lead_time,
                  MAX (e.max_excess_duration)
                      max_excess_duration,
                  MAX (e.shortage_duration2)
                      shortage_duration2,
                  pla.quantity * unit_price
                      amt,
                  pha.currency_code,
                  MAX (e.shortage_duration)
                      shortage_duration,
                  e.MAX_PERCENT_BELOW_SS,
                  mp.description
                      plnr_description,
                  MIN (e.EARLIEST_FROM_DATE)
                      EARLIEST_FROM_DATE,
                  MAX (e.LATEST_TO_DATE)
                      LATEST_TO_DATE,
                  e.SAFETY_STOCK_QUANTITY,
                  e.source_organization_code,
                  pla.creation_date
                      po_line_creation_date,
                  e.COMMODITY_CODE,
                  msib.attribute5
                      awu,
                  msib.attribute6
                      fwu,
                  e.order_date,
                  fcl.meaning
                      item_type,
                  msib.inventory_item_id,
                  msib.organization_id,
                  NVL (mc.segment1, e.replenish_to_order_flag)
                      assemble_to_order_flag,
                  q.comment1
                      comments,
                  e.sourcing_rule,
                  e.inventory_item_id
                      ascp_item_id,
                  DECODE (msib.release_time_fence_code,
                          1, 'Cumulative total lead time',
                          2, 'Cumulative manufacturing lead time',
                          3, 'Item total lead time',
                          4, 'User-defined time fence',
                          5, 'Do not autorelease',
                          6, 'Kanban Item (Do Not Release)')
                      release_time_fence_code,
                  msib.release_time_fence_days
             FROM EXCEPTIONS                e,
                  apps.mtl_abc_assignments_v a,
                  apps.MTL_SYSTEM_ITEMS_B   msib,
                  apps.mtl_planners         mp,
                  apps.po_lines_all         pla,
                  apps.po_headers_all       pha,
                  apps.fnd_common_lookups   fcl,
                  apps.per_all_people_f     ppf,
                  apps.mtl_item_categories  mic,
                  apps.mtl_categories_b     mc,
                  (SELECT ROWNUM,
                          qpcv.character6 item,
                          mp.organization_id,
                          qpcv.comment1,
                          qpcv.character2
                     FROM apps.qa_plans      qp,
                          apps.mtl_parameters mp,
                          apps.qa_results    qpcv
                    WHERE     SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
                          AND mp.organization_id IN (16597,5000,10505,4154)
                          AND mp.organization_code = SUBSTR (qp.NAME, -3)
                          AND qp.plan_id = qpcv.plan_id
                          AND (   'ALL' IN ('SHAIKH, IMRAN')
                               OR qpcv.character14 IN ('SHAIKH, IMRAN'))
                          AND (   'ALL' IN ('ALL')
                               OR qpcv.character15 IN ('ALL'))) q
            WHERE     (   'ALL' IN ('SHAIKH, IMRAN')
                       OR NVL (ppf.full_name, 'NONE') IN ('SHAIKH, IMRAN'))
                  AND (   e.planning_make_buy_code = 2
                       OR msib.item_type IN ('OP', 'OA'))
                  AND e.sr_inventory_item_id = a.inventory_item_id(+)
                  AND e.organization_id = a.organization_id(+)
                  AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
                  AND e.organization_id = msib.organization_id
                  AND e.sr_inventory_item_id = msib.inventory_item_id
                  AND e.planner_code = mp.planner_code(+)
                  AND e.organization_id = mp.organization_id(+)
                  AND e.po_line_id = pla.po_line_id
                  AND pla.po_header_id = pha.po_header_id
                  AND e.order_type IN ('PO in receiving', 'Purchase order')
                  AND msib.item_type = fcl.lookup_code
                  AND fcl.lookup_type = 'ITEM_TYPE'
                  AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
                  AND msib.buyer_id = ppf.person_id(+)
                  ---and nvl(effective_end_date,sysdate+1) > sysdate  --Commented by SLK on 31-Jan-2018
                  AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
                  AND msib.organization_id = mic.organization_id(+)
                  AND msib.inventory_item_id = mic.inventory_item_id(+)
                  AND mic.category_set_id(+) = 1100001406
                  AND mic.category_id = mc.category_id(+)
                  AND msib.segment1 = q.item(+)
                  AND msib.organization_id = q.organization_id(+)
                  AND q.character2(+) = 'Open'
         GROUP BY e.org,
                  e.item,
                  e.item_description,
                  e.supplier_name,
                  e.supplier_site,
                  pha.segment1,
                  pla.line_num,
                  e.order_type,
                  e.quantity,
                  e.CURRENTLY_SCHED_DUE_DATE,
                  e.SUGGESTED_DOCK_DATE,
                  a.abc_class_name,
                  e.planner_code,
                  msib.full_lead_time,
                  pla.quantity * unit_price,
                  pha.currency_code,
                  e.SAFETY_STOCK_QUANTITY,
                  mp.description,
                  e.MAX_PERCENT_BELOW_SS,
                  e.source_organization_code,
                  pla.creation_date,
                  e.COMMODITY_CODE,
                  msib.attribute5,
                  msib.attribute6,
                  e.order_date,
                  fcl.meaning,
                  msib.inventory_item_id,
                  msib.organization_id,
                  NVL (e.buyer_name, ppf.full_name),
                  NVL (mc.segment1, e.replenish_to_order_flag),
                  q.comment1,
                  e.inventory_item_id,
                  e.organization_id,
                  e.sourcing_rule,
                  e.inventory_item_id,
                  msib.release_time_fence_code,
                  msib.release_time_fence_days
         UNION                           /***** Purchase Requisitions *******/
         SELECT 2
                    AS querypart,
                e.exception_type_text
                    EXCEPTION_TYPE,
                e.ORG,
                e.item,
                e.item_description
                    ITEM_DESCRIPTION,
                e.supplier_name
                    SUPPLIER_NAME,
                e.supplier_site
                    SUPPLIER_SITE,
                REGEXP_REPLACE (
                    (SELECT LISTAGG (
                                aslinner.primary_vendor_item, ', ')
                                WITHIN GROUP (ORDER BY
                                                  aslinner.primary_vendor_item)
                       FROM apps.po_approved_supplier_list aslinner
                      WHERE     1 = 1
                            AND aslinner.item_id = msib.inventory_item_id
                            AND aslinner.owning_organization_id =
                                msib.organization_id),
                    '([^,]+)(,\1)*(,|$)')
                    supplier_item                     /* comma list required*/
                                 ,
                rha.segment1
                    order_num,
                rla.line_num,
                e.order_type
                    ORDER_TYPE,
                e.quantity
                    QUANTITY,
                e.CURRENTLY_SCHED_DUE_DATE,
                e.SUGGESTED_DOCK_DATE,
                a.abc_class_name
                    ABC_CLASSIFICATION,
                e.planner_code
                    PLANNER_CODE,
                NVL (e.buyer_name, ppf.full_name)
                    BUYER_CODE,
                msib.full_lead_time
                    processing_lead_time,
                e.max_excess_duration,
                e.shortage_duration2,
                rla.quantity * unit_price
                    amt,
                rla.currency_code,
                e.shortage_duration,
                e.MAX_PERCENT_BELOW_SS,
                mp.description
                    plnr_description,
                e.EARLIEST_FROM_DATE,
                e.LATEST_TO_DATE,
                e.SAFETY_STOCK_QUANTITY,
                e.source_organization_code,
                NULL
                    po_line_creation_date,
                e.COMMODITY_CODE,
                msib.attribute5
                    awu,
                msib.attribute6
                    fwu,
                e.order_date,
                fcl.meaning,
                msib.inventory_item_id,
                msib.organization_id,
                NVL (mc.segment1, e.replenish_to_order_flag)
                    assemble_to_order_flag,
                q.comment1
                    comments,
                e.sourcing_rule,
                e.inventory_item_id
                    ascp_item_id,
                DECODE (msib.release_time_fence_code,
                        1, 'Cumulative total lead time',
                        2, 'Cumulative manufacturing lead time',
                        3, 'Item total lead time',
                        4, 'User-defined time fence',
                        5, 'Do not autorelease',
                        6, 'Kanban Item (Do Not Release)')
                    release_time_fence_code,
                msib.release_time_fence_days
           FROM EXCEPTIONS                       e,
                apps.mtl_abc_assignments_v       a,
                apps.MTL_SYSTEM_ITEMS_B          msib,
                apps.mtl_planners                mp,
                apps.po_requisition_lines_all    rla,
                apps.po_requisition_headers_all  rha,
                apps.fnd_common_lookups          fcl,
                apps.per_all_people_f            ppf,
                apps.mtl_item_categories         mic,
                apps.mtl_categories_b            mc,
                (SELECT ROWNUM,
                        qpcv.character6 item,
                        mp.organization_id,
                        qpcv.comment1,
                        qpcv.character2
                   FROM apps.qa_plans        qp,
                        apps.mtl_parameters  mp,
                        apps.qa_results      qpcv
                  WHERE     SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
                        AND mp.organization_id IN (16597,5000,10505,4154)
                        AND mp.organization_code = SUBSTR (qp.NAME, -3)
                        AND qp.plan_id = qpcv.plan_id
                        AND (   'ALL' IN ('SHAIKH, IMRAN')
                             OR qpcv.character14 IN ('SHAIKH, IMRAN'))
                        AND (   'ALL' IN ('ALL')
                             OR qpcv.character15 IN ('ALL'))) q
          WHERE     (   'ALL' IN ('SHAIKH, IMRAN')
                     OR NVL (NVL (e.buyer_name, ppf.full_name), 'NONE') IN
                            ('SHAIKH, IMRAN'))
                AND (   e.planning_make_buy_code = 2
                     OR msib.item_type IN ('OP', 'OA'))
                AND e.sr_inventory_item_id = a.inventory_item_id(+)
                AND e.organization_id = a.organization_id(+)
                AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
                AND e.organization_id = msib.organization_id
                AND e.sr_inventory_item_id = msib.inventory_item_id
                AND e.planner_code = mp.planner_code(+)
                AND e.organization_id = mp.organization_id(+)
                AND e.po_line_id = rla.requisition_line_id
                AND rla.requisition_header_id = rha.requisition_header_id
                AND e.order_type = 'Purchase requisition'
                AND msib.item_type = fcl.lookup_code
                AND fcl.lookup_type = 'ITEM_TYPE'
                AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
                AND msib.buyer_id = ppf.person_id(+)
                ---and nvl(effective_end_date,sysdate+1) > sysdate  --Commented by SLK on 31-Jan-2018
                AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
                AND msib.organization_id = mic.organization_id(+)
                AND msib.inventory_item_id = mic.inventory_item_id(+)
                AND mic.category_set_id(+) = 1100001406
                AND mic.category_id = mc.category_id(+)
                AND msib.segment1 = q.item(+)
                AND msib.organization_id = q.organization_id(+)
                AND q.character2(+) = 'Open'
         UNION /***  not in Purchase order, Purchase requisition, Work order, non-standard job, PO in receiving   ***/
         SELECT 3
                    AS querypart,
                e.exception_type_text
                    EXCEPTION_TYPE,
                e.ORG,
                e.ITEM,
                e.item_description
                    ITEM_DESCRIPTION,
                e.supplier_name
                    SUPPLIER_NAME,
                e.supplier_site
                    SUPPLIER_SITE,
                REGEXP_REPLACE (
                    (SELECT LISTAGG (
                                aslinner.primary_vendor_item, ', ')
                                WITHIN GROUP (ORDER BY
                                                  aslinner.primary_vendor_item)
                       FROM apps.po_approved_supplier_list aslinner
                      WHERE     1 = 1
                            AND aslinner.item_id = msib.inventory_item_id
                            AND aslinner.owning_organization_id =
                                msib.organization_id),
                    '([^,]+)(,\1)*(,|$)')
                    supplier_item                     /* comma list required*/
                                 ,
                CASE
                    WHEN e.exception_type = 10
                    THEN
                        (SELECT MIN (rha.segment1)
                           FROM [email]apps.msc_supplies@xxbetsytobilly.emrsn.com[/email] ms,
                                apps.rcv_shipment_lines          rsl,
                                apps.po_requisition_lines_all    rla,
                                apps.po_requisition_headers_all  rha
                          WHERE     ms.transaction_id = e.transaction_id
                                AND ms.po_line_id = rsl.shipment_line_id
                                AND rsl.requisition_line_id =
                                    rla.requisition_line_id
                                AND rla.requisition_header_id =
                                    rha.requisition_header_id)
                    ELSE
                        e.order_number
                END
                    ORDER_NUMBER,
                CASE
                    WHEN e.exception_type = 10
                    THEN
                        (SELECT MIN ((rla.line_num))
                           FROM [email]apps.msc_supplies@xxbetsytobilly.emrsn.com[/email] ms,
                                apps.rcv_shipment_lines          rsl,
                                apps.po_requisition_lines_all    rla,
                                apps.po_requisition_headers_all  rha
                          WHERE     ms.transaction_id = e.transaction_id
                                AND ms.po_line_id = rsl.shipment_line_id
                                AND rsl.requisition_line_id =
                                    rla.requisition_line_id
                                AND rla.requisition_header_id =
                                    rha.requisition_header_id
                                AND rha.segment1 =
                                    (SELECT MIN (rha1.segment1)
                                       FROM [email]apps.msc_supplies@xxbetsytobilly.emrsn.com[/email]
                                            ms1,
                                            apps.rcv_shipment_lines  rsl1,
                                            apps.po_requisition_lines_all
                                            rla1,
                                            apps.po_requisition_headers_all
                                            rha1
                                      WHERE     ms1.transaction_id =
                                                e.transaction_id
                                            AND ms1.po_line_id =
                                                rsl1.shipment_line_id
                                            AND rsl1.requisition_line_id =
                                                rla1.requisition_line_id
                                            AND rla1.requisition_header_id =
                                                rha1.requisition_header_id))
                    ELSE
                        NULL
                END
                    line_num,
                e.order_type
                    ORDER_TYPE,
                e.quantity
                    QUANTITY,
                e.CURRENTLY_SCHED_DUE_DATE,
                e.SUGGESTED_DOCK_DATE,
                a.abc_class_name
                    ABC_CLASSIFICATION,
                e.planner_code
                    PLANNER_CODE,
                NVL (e.buyer_name, ppf.full_name)
                    BUYER_CODE,
                msib.full_lead_time
                    processing_lead_time,
                e.max_excess_duration,
                e.shortage_duration2,
                NULL
                    amt,
                NULL
                    currency_code,
                e.shortage_duration,
                e.MAX_PERCENT_BELOW_SS,
                mp.description
                    plnr_description,
                e.EARLIEST_FROM_DATE,
                e.LATEST_TO_DATE,
                e.SAFETY_STOCK_QUANTITY,
                e.source_organization_code,
                NULL
                    po_line_creation_date,
                e.COMMODITY_CODE,
                msib.attribute5
                    awu,
                msib.attribute6
                    fwu,
                e.order_date,
                fcl.meaning,
                msib.inventory_item_id,
                msib.organization_id,
                NVL (mc.segment1, e.replenish_to_order_flag)
                    assemble_to_order_flag,
                q.comment1
                    comments,
                e.sourcing_rule,
                e.inventory_item_id
                    ascp_item_id,
                DECODE (msib.release_time_fence_code,
                        1, 'Cumulative total lead time',
                        2, 'Cumulative manufacturing lead time',
                        3, 'Item total lead time',
                        4, 'User-defined time fence',
                        5, 'Do not autorelease',
                        6, 'Kanban Item (Do Not Release)')
                    release_time_fence_code,
                msib.release_time_fence_days
           FROM EXCEPTIONS                  e,
                apps.mtl_abc_assignments_v  a,
                apps.MTL_SYSTEM_ITEMS_B     msib,
                apps.mtl_planners           mp,
                apps.fnd_common_lookups     fcl,
                apps.per_all_people_f       ppf,
                apps.mtl_item_categories    mic,
                apps.mtl_categories_b       mc,
                (SELECT ROWNUM,
                        qpcv.character6 item,
                        mp.organization_id,
                        qpcv.comment1,
                        qpcv.character2
                   FROM apps.qa_plans        qp,
                        apps.mtl_parameters  mp,
                        apps.qa_results      qpcv
                  WHERE     SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
                        AND mp.organization_id IN (16597,5000,10505,4154)
                        AND mp.organization_code = SUBSTR (qp.NAME, -3)
                        AND qp.plan_id = qpcv.plan_id
                        AND (   'ALL' IN ('SHAIKH, IMRAN')
                             OR qpcv.character14 IN ('SHAIKH, IMRAN'))
                        AND (   'ALL' IN ('ALL')
                             OR qpcv.character15 IN ('ALL'))) q
          WHERE     (   'ALL' IN ('SHAIKH, IMRAN')
                     OR NVL (NVL (e.buyer_name, ppf.full_name), 'NONE') IN
                            ('SHAIKH, IMRAN'))
                AND (   e.planning_make_buy_code = 2
                     OR msib.item_type IN ('OP', 'OA'))
                AND e.sr_inventory_item_id = a.inventory_item_id(+)
                AND e.organization_id = a.organization_id(+)
                AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
                AND e.organization_id = msib.organization_id
                AND e.sr_inventory_item_id = msib.inventory_item_id
                AND e.planner_code = mp.planner_code(+)
                AND e.organization_id = mp.organization_id(+)
                AND e.order_type_id NOT IN (1,
                                            2,
                                            3,
                                            7,
                                            8) -- not in Purchase order, Purchase requisition, Work order, non-standard job, PO in receiving
                AND msib.item_type = fcl.lookup_code
                AND fcl.lookup_type = 'ITEM_TYPE'
                AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
                AND msib.buyer_id = ppf.person_id(+)
                ---and nvl(effective_end_date,sysdate+1) > sysdate  --Commented by SLK on 31-Jan-2018
                AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
                AND msib.organization_id = mic.organization_id(+)
                AND msib.inventory_item_id = mic.inventory_item_id(+)
                AND mic.category_set_id(+) = 1100001406
                AND mic.category_id = mc.category_id(+)
                AND msib.segment1 = q.item(+)
                AND msib.organization_id = q.organization_id(+)
                AND q.character2(+) = 'Open'
         UNION
           SELECT 4
                      AS querypart,
                  e.exception_type_text,
                  e.ORG,
                  e.ITEM,
                  e.item_description
                      ITEM_DESCRIPTION,
                  e.supplier_name
                      SUPPLIER_NAME,
                  e.supplier_site
                      SUPPLIER_SITE,
                  REGEXP_REPLACE (
                      (SELECT LISTAGG (
                                  aslinner.primary_vendor_item, ', ')
                                  WITHIN GROUP (ORDER BY
                                                    aslinner.primary_vendor_item)
                         FROM apps.po_approved_supplier_list aslinner
                        WHERE     1 = 1
                              AND aslinner.item_id = msib.inventory_item_id
                              AND aslinner.owning_organization_id =
                                  msib.organization_id),
                      '([^,]+)(,\1)*(,|$)')
                      supplier_item                   /* comma list required*/
                                   ,
                  e.order_number
                      ORDER_NUMBER,
                  NULL
                      line_num,
                  e.order_type
                      ORDER_TYPE,
                  MIN (e.quantity)
                      QUANTITY,
                  e.CURRENTLY_SCHED_DUE_DATE,
                  e.SUGGESTED_DOCK_DATE,
                  a.abc_class_name
                      ABC_CLASSIFICATION,
                  e.planner_code
                      PLANNER_CODE,
                  NVL (e.buyer_name, ppf.full_name)
                      BUYER_CODE,
                  msib.full_lead_time
                      processing_lead_time,
                  MAX (e.max_excess_duration)
                      max_excess_duration,
                  MAX (e.shortage_duration2),
                  NULL
                      amt,
                  NULL
                      currency_code,
                  MAX (e.shortage_duration),
                  e.MAX_PERCENT_BELOW_SS,
                  mp.description
                      plnr_description,
                  MIN (e.EARLIEST_FROM_DATE),
                  MAX (e.LATEST_TO_DATE),
                  e.SAFETY_STOCK_QUANTITY,
                  e.source_organization_code,
                  NULL
                      po_line_creation_date,
                  e.COMMODITY_CODE,
                  msib.attribute5
                      awu,
                  msib.attribute6
                      fwu,
                  e.order_date,
                  fcl.meaning,
                  msib.inventory_item_id,
                  msib.organization_id,
                  NVL (mc.segment1, e.replenish_to_order_flag)
                      assemble_to_order_flag,
                  q.comment1
                      comments,
                  e.sourcing_rule,
                  e.inventory_item_id
                      ascp_item_id,
                  DECODE (msib.release_time_fence_code,
                          1, 'Cumulative total lead time',
                          2, 'Cumulative manufacturing lead time',
                          3, 'Item total lead time',
                          4, 'User-defined time fence',
                          5, 'Do not autorelease',
                          6, 'Kanban Item (Do Not Release)')
                      release_time_fence_code,
                  msib.release_time_fence_days
             FROM EXCEPTIONS                e,
                  apps.mtl_abc_assignments_v a,
                  apps.MTL_SYSTEM_ITEMS_B   msib,
                  apps.mtl_planners         mp,
                  apps.fnd_common_lookups   fcl,
                  apps.per_all_people_f     ppf,
                  apps.mtl_item_categories  mic,
                  apps.mtl_categories_b     mc,
                  (SELECT ROWNUM,
                          qpcv.character6 item,
                          mp.organization_id,
                          qpcv.comment1,
                          qpcv.character2
                     FROM apps.qa_plans      qp,
                          apps.mtl_parameters mp,
                          apps.qa_results    qpcv
                    WHERE     SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
                          AND mp.organization_id IN (16597,5000,10505,4154)
                          AND mp.organization_code = SUBSTR (qp.NAME, -3)
                          AND qp.plan_id = qpcv.plan_id
                          AND (   'ALL' IN ('SHAIKH, IMRAN')
                               OR qpcv.character14 IN ('SHAIKH, IMRAN'))
                          AND (   'ALL' IN ('ALL')
                               OR qpcv.character15 IN ('ALL'))) q
            WHERE     e.exception_type = 2
                  AND (   'ALL' IN ('SHAIKH, IMRAN')
                       OR NVL (NVL (e.buyer_name, ppf.full_name), 'NONE') IN
                              ('SHAIKH, IMRAN'))
                  AND (   e.planning_make_buy_code = 2
                       OR msib.item_type IN ('OP', 'OA'))
                  AND e.sr_inventory_item_id = a.inventory_item_id(+)
                  AND e.organization_id = a.organization_id(+)
                  AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
                  AND e.organization_id = msib.organization_id
                  AND e.sr_inventory_item_id = msib.inventory_item_id
                  AND e.planner_code = mp.planner_code(+)
                  AND e.organization_id = mp.organization_id(+)
                  AND msib.item_type = fcl.lookup_code
                  AND fcl.lookup_type = 'ITEM_TYPE'
                  AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
                  AND msib.buyer_id = ppf.person_id(+)
                  ---and nvl(effective_end_date,sysdate+1) > sysdate  --Commented by SLK on 31-Jan-2018
                  AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
                  AND msib.organization_id = mic.organization_id(+)
                  AND msib.inventory_item_id = mic.inventory_item_id(+)
                  AND mic.category_set_id(+) = 1100001406
                  AND mic.category_id = mc.category_id(+)
                  AND msib.segment1 = q.item(+)
                  AND msib.organization_id = q.organization_id(+)
                  AND q.character2(+) = 'Open'
         GROUP BY e.org,
                  e.exception_type_text,
                  e.item,
                  e.item_description,
                  e.supplier_name,
                  e.supplier_site,
                  e.order_number,
                  e.order_type,
                  e.CURRENTLY_SCHED_DUE_DATE,
                  e.SUGGESTED_DOCK_DATE,
                  a.abc_class_name,
                  e.planner_code,
                  msib.full_lead_time,
                  mp.description,
                  e.source_organization_code,
                  e.COMMODITY_CODE,
                  msib.attribute5,
                  msib.attribute6,
                  MAX_PERCENT_BELOW_SS,
                  e.order_date,
                  fcl.meaning,
                  msib.inventory_item_id,
                  msib.organization_id,
                  NVL (e.buyer_name, ppf.full_name),
                  NVL (mc.segment1, e.replenish_to_order_flag),
                  q.comment1,
                  e.inventory_item_id,
                  e.organization_id--    , e.max_excess_duration
                                   ,
                  e.SAFETY_STOCK_QUANTITY,
                  e.sourcing_rule,
                  e.inventory_item_id,
                  msib.release_time_fence_code,
                  msib.release_time_fence_days
         UNION
           SELECT 5
                      AS querypart,
                  e.exception_type_text,
                  e.ORG,
                  e.ITEM,
                  e.item_description
                      ITEM_DESCRIPTION,
                  e.supplier_name
                      SUPPLIER_NAME,
                  e.supplier_site
                      SUPPLIER_SITE,
                  REGEXP_REPLACE (
                      (SELECT LISTAGG (
                                  aslinner.primary_vendor_item, ', ')
                                  WITHIN GROUP (ORDER BY
                                                    aslinner.primary_vendor_item)
                         FROM apps.po_approved_supplier_list aslinner
                        WHERE     1 = 1
                              AND aslinner.item_id = msib.inventory_item_id
                              AND aslinner.owning_organization_id =
                                  msib.organization_id),
                      '([^,]+)(,\1)*(,|$)')
                      supplier_item                   /* comma list required*/
                                   ,
                  e.order_number
                      ORDER_NUMBER,
                  NULL
                      line_num,
                  e.order_type
                      ORDER_TYPE,
                  MIN (e.quantity)
                      QUANTITY,
                  e.CURRENTLY_SCHED_DUE_DATE,
                  e.SUGGESTED_DOCK_DATE,
                  a.abc_class_name
                      ABC_CLASSIFICATION,
                  e.planner_code
                      PLANNER_CODE,
                  NVL (e.buyer_name, ppf.full_name)
                      BUYER_CODE,
                  msib.full_lead_time
                      processing_lead_time,
                  MAX (e.max_excess_duration),
                  MAX (e.shortage_duration2),
                  NULL
                      amt,
                  NULL
                      currency_code,
                  MAX (e.shortage_duration),
                  MIN (e.MAX_PERCENT_BELOW_SS)
                      MAX_PERCENT_BELOW_SS,
                  mp.description
                      plnr_description,
                  MIN (e.EARLIEST_FROM_DATE),
                  MAX (e.LATEST_TO_DATE),
                  MAX (e.SAFETY_STOCK_QUANTITY)
                      SAFETY_STOCK_QUANTITY,
                  e.source_organization_code,
                  NULL
                      po_line_creation_date,
                  e.COMMODITY_CODE,
                  msib.attribute5
                      awu,
                  msib.attribute6
                      fwu,
                  e.order_date,
                  fcl.meaning,
                  msib.inventory_item_id,
                  msib.organization_id,
                  NVL (mc.segment1, e.replenish_to_order_flag)
                      assemble_to_order_flag,
                  q.comment1
                      comments,
                  e.sourcing_rule,
                  e.inventory_item_id
                      ascp_item_id,
                  DECODE (msib.release_time_fence_code,
                          1, 'Cumulative total lead time',
                          2, 'Cumulative manufacturing lead time',
                          3, 'Item total lead time',
                          4, 'User-defined time fence',
                          5, 'Do not autorelease',
                          6, 'Kanban Item (Do Not Release)')
                      release_time_fence_code,
                  msib.release_time_fence_days
             FROM EXCEPTIONS                e,
                  apps.mtl_abc_assignments_v a,
                  apps.MTL_SYSTEM_ITEMS_B   msib,
                  apps.mtl_planners         mp,
                  apps.fnd_common_lookups   fcl,
                  apps.per_all_people_f     ppf,
                  apps.mtl_item_categories  mic,
                  apps.mtl_categories_b     mc,
                  (SELECT ROWNUM,
                          qpcv.character6 item,
                          mp.organization_id,
                          qpcv.comment1,
                          qpcv.character2
                     FROM apps.qa_plans      qp,
                          apps.mtl_parameters mp,
                          apps.qa_results    qpcv
                    WHERE     SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
                          AND mp.organization_id IN (16597,5000,10505,4154)
                          AND mp.organization_code = SUBSTR (qp.NAME, -3)
                          AND qp.plan_id = qpcv.plan_id
                          AND (   'ALL' IN ('SHAIKH, IMRAN')
                               OR qpcv.character14 IN ('SHAIKH, IMRAN'))
                          AND (   'ALL' IN ('ALL')
                               OR qpcv.character15 IN ('ALL'))) q
            WHERE     e.exception_type = 20
                  AND (   'ALL' IN ('SHAIKH, IMRAN')
                       OR NVL (NVL (e.buyer_name, ppf.full_name), 'NONE') IN
                              ('SHAIKH, IMRAN'))
                  AND (   e.planning_make_buy_code = 2
                       OR msib.item_type IN ('OP', 'OA'))
                  AND e.sr_inventory_item_id = a.inventory_item_id(+)
                  AND e.organization_id = a.organization_id(+)
                  AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
                  AND e.organization_id = msib.organization_id
                  AND e.sr_inventory_item_id = msib.inventory_item_id
                  AND e.planner_code = mp.planner_code(+)
                  AND e.organization_id = mp.organization_id(+)
                  AND msib.item_type = fcl.lookup_code
                  AND fcl.lookup_type = 'ITEM_TYPE'
                  AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
                  AND msib.buyer_id = ppf.person_id(+)
                  ---and nvl(effective_end_date,sysdate+1) > sysdate  --Commented by SLK on 31-Jan-2018
                  AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
                  AND msib.organization_id = mic.organization_id(+)
                  AND msib.inventory_item_id = mic.inventory_item_id(+)
                  AND mic.category_set_id(+) = 1100001406
                  AND mic.category_id = mc.category_id(+)
                  AND msib.segment1 = q.item(+)
                  AND msib.organization_id = q.organization_id(+)
                  AND q.character2(+) = 'Open'
         GROUP BY e.org,
                  e.exception_type_text,
                  e.item,
                  e.item_description,
                  e.supplier_name,
                  e.supplier_site,
                  e.order_number,
                  e.order_type,
                  e.CURRENTLY_SCHED_DUE_DATE,
                  e.SUGGESTED_DOCK_DATE,
                  a.abc_class_name,
                  e.planner_code,
                  e.buyer_name,
                  msib.full_lead_time,
                  mp.description,
                  e.source_organization_code,
                  e.COMMODITY_CODE,
                  msib.attribute5,
                  msib.attribute6,
                  e.order_date,
                  fcl.meaning,
                  msib.inventory_item_id,
                  msib.organization_id,
                  NVL (e.buyer_name, ppf.full_name),
                  NVL (mc.segment1, e.replenish_to_order_flag),
                  q.comment1,
                  e.inventory_item_id,
                  e.organization_id--   , e.MAX_PERCENT_BELOW_SS
                                   --    , e.SAFETY_STOCK_QUANTITY
                                   ,
                  e.sourcing_rule,
                  e.inventory_item_id,
                  msib.release_time_fence_code,
                  msib.release_time_fence_days
         UNION                                     /*** Planned Orders  *****/
         SELECT 6
                    AS querypart,
                'Recommendation',
                mo.organization_code,
                mo.item_segments,
                mo.description,
                NULL
                    supplier,
                NULL
                    supplier_site,
                REGEXP_REPLACE (
                    (SELECT LISTAGG (
                                aslinner.primary_vendor_item, ', ')
                                WITHIN GROUP (ORDER BY
                                                  aslinner.primary_vendor_item)
                       FROM apps.po_approved_supplier_list aslinner
                      WHERE     1 = 1
                            AND aslinner.item_id = msib.inventory_item_id
                            AND aslinner.owning_organization_id =
                                msib.organization_id),
                    '([^,]+)(,\1)*(,|$)')
                    supplier_item                     /* comma list required*/
                                 ,
                order_number,
                NULL
                    line,
                mo.order_type_text,
                mo.quantity_rate,
                new_dock_date,
                mo.new_due_date,
                mo.abc_class,
                mo.planner_code,
                NVL (mo.buyer_name, ppf.full_name),
                mo.full_lead_time
                    processing_lead_time,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                mp.description,
                NULL,
                NULL,
                NULL,
                mo.source_organization_code,
                NULL,
                NULL,
                NULL,
                NULL,
                mo.new_order_date,
                fcl.meaning,
                msib.inventory_item_id,
                msib.organization_id,
                NVL (mc.segment1, mo.replenish_to_order_flag)
                    assemble_to_order_flag,
                q.comment1
                    comments,
                mo.sourcing_rule,
                mo.inventory_item_id
                    ascp_item_id,
                DECODE (msib.release_time_fence_code,
                        1, 'Cumulative total lead time',
                        2, 'Cumulative manufacturing lead time',
                        3, 'Item total lead time',
                        4, 'User-defined time fence',
                        5, 'Do not autorelease',
                        6, 'Kanban Item (Do Not Release)')
                    release_time_fence_code,
                msib.release_time_fence_days
           FROM PLANNEDORDERS             mo,
                apps.mtl_system_items_b   msib,
                apps.fnd_common_lookups   fcl,
                apps.per_all_people_f     ppf,
                apps.mtl_planners         mp,
                apps.mtl_item_categories  mic,
                apps.mtl_categories_b     mc,
                (SELECT ROWNUM,
                        qpcv.character6 item,
                        mp.organization_id,
                        qpcv.comment1,
                        qpcv.character2
                   FROM apps.qa_plans        qp,
                        apps.mtl_parameters  mp,
                        apps.qa_results      qpcv
                  WHERE     SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
                        AND mp.organization_id IN (16597,5000,10505,4154)
                        AND mp.organization_code = SUBSTR (qp.NAME, -3)
                        AND qp.plan_id = qpcv.plan_id
                        AND (   'ALL' IN ('SHAIKH, IMRAN')
                             OR qpcv.character14 IN ('SHAIKH, IMRAN'))
                        AND (   'ALL' IN ('ALL')
                             OR qpcv.character15 IN ('ALL'))) q
          WHERE     mo.sr_inventory_item_id = msib.inventory_item_id
                AND mo.organization_id = msib.organization_id
                AND (   mo.planning_make_buy_code = 2
                     OR msib.item_type IN ('OP', 'OA'))
                AND (   'ALL' IN ('SHAIKH, IMRAN')
                     OR NVL (NVL (mo.buyer_name, ppf.full_name), 'NONE') IN
                            ('SHAIKH, IMRAN'))
                AND msib.item_type = fcl.lookup_code
                AND fcl.lookup_type = 'ITEM_TYPE'
                AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
                AND msib.buyer_id = ppf.person_id(+)
                AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE
                AND mo.planner_code = mp.planner_code(+)
                AND mo.organization_id = mp.organization_id(+)
                AND msib.organization_id = mic.organization_id(+)
                AND msib.inventory_item_id = mic.inventory_item_id(+)
                AND mic.category_set_id(+) = 1100001406
                AND mic.category_id = mc.category_id(+)
                AND msib.segment1 = q.item(+)
                AND msib.organization_id = q.organization_id(+)
                AND q.character2(+) = 'Open'
         UNION
           SELECT 7
                      AS querypart,
                  REGEXP_REPLACE (
                      LISTAGG (e.exception_type_text, ',')
                          WITHIN GROUP (ORDER BY e.exception_type),
                      '([^,]+)(,\1)+',
                      '\1')
                      EXCEPTION_TYPE,
                  e.ORG,
                  e.ITEM,
                  e.item_description
                      ITEM_DESCRIPTION,
                  pv.vendor_name
                      SUPPLIER_NAME,
                  pvs.vendor_site_code
                      SUPPLIER_SITE,
                  REGEXP_REPLACE (
                      (SELECT LISTAGG (
                                  aslinner.primary_vendor_item, ', ')
                                  WITHIN GROUP (ORDER BY
                                                    aslinner.primary_vendor_item)
                         FROM apps.po_approved_supplier_list aslinner
                        WHERE     1 = 1
                              AND aslinner.item_id = msib.inventory_item_id
                              AND aslinner.owning_organization_id =
                                  msib.organization_id),
                      '([^,]+)(,\1)*(,|$)')
                      supplier_item                   /* comma list required*/
                                   ,
                  'WO' || we.wip_entity_name || ', PO' || pha.segment1
                      order_number,
                  pla.line_num
                      line_num,
                  'OSP-' || e.order_type
                      ORDER_TYPE,
                  e.quantity
                      QUANTITY,
                  e.CURRENTLY_SCHED_DUE_DATE,
                  e.SUGGESTED_DOCK_DATE,
                  a.abc_class_name
                      ABC_CLASSIFICATION,
                  e.planner_code
                      PLANNER_CODE,
                  NVL (e.buyer_name, ppf.full_name)
                      BUYER_CODE,
                  msib.full_lead_time
                      processing_lead_time,
                  MAX (e.max_excess_duration)
                      max_excess_duration,
                  MAX (e.shortage_duration2),
                  pla.quantity * unit_price
                      amt,
                  pha.currency_code
                      currency_code,
                  MAX (e.shortage_duration)
                      shortage_duration,
                  MIN (e.MAX_PERCENT_BELOW_SS)
                      MAX_PERCENT_BELO

[Updated on: Sun, 27 November 2022 02:57]

Report message to a moderator

Re: Sql having performance issue [message #686705 is a reply to message #686704] Sun, 27 November 2022 02:58 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
As sql is big,so cannot get all the sql in this.so i am attaching sql text and trace
Re: Sql having performance issue [message #686706 is a reply to message #686705] Sun, 27 November 2022 03:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Nishant, that code is impossible for an outsider to debug. For example:

No comments. How can anyone assist without embedded descriptions of the row sources and what you are trying to do with them?
Those table aliases: e, a, and so on. You may be abe to see "a.whatever" and know to what row source it refers, no-one else does. You need to use meaningful aliases.
You aren't using ansi join syntax. Your WHERE clause is an incomprehensible jumble of join predicates and filter predicates. You could at least describe the table relationships.

If you re-write it to comprehensible form, perhaps people will have a better chance of assisting. Be sure to provide the exec plan, too.
Re: Sql having performance issue [message #686707 is a reply to message #686706] Sun, 27 November 2022 03:56 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
i am attaching tkprof
Re: Sql having performance issue [message #686708 is a reply to message #686707] Sun, 27 November 2022 03:57 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
if i have to tune this part only how to do


WITH
    EXCEPTIONS
    AS
        (SELECT e.organization_code
                    ORG,
                e.exception_type,
                e.order_type_id,
                e.item_segments
                    ITEM,
                si.sr_inventory_item_id,
                e.inventory_item_id,
                e.item_description
                    ITEM_DESCRIPTION,
                e.supplier_name
                    SUPPLIER_NAME,
                e.supplier_site
                    SUPPLIER_SITE,
                e.order_type
                    ORDER_TYPE,
                e.quantity
                    QUANTITY,
                e.old_due_date
                    CURRENTLY_SCHED_DUE_DATE,
                e.new_dock_date
                    SUGGESTED_DOCK_DATE,
                e.planner_code
                    PLANNER_CODE,
                e.excess_duration
                    max_excess_duration,
                (SELECT COUNT (*) - 1
                   FROM apps.bom_calendar_dates
                  WHERE     calendar_code =
                            (SELECT calendar_code
                               FROM apps.mtl_parameters
                              WHERE organization_id = e.organization_id)
                        AND calendar_date >=
                            (SELECT MIN (from_date)
                               FROM apps.msc_exception_details_v@xxbetsytobilly.emrsn.com
                              WHERE     plan_id = e.plan_id
                                    AND exception_type = e.exception_type
                                    AND sr_instance_id = 81
                                    AND organization_id = e.organization_id
                                    AND inventory_item_id =
                                        e.inventory_item_id
                                    AND category_set_id = 24)
                        AND calendar_date <=
                            (SELECT MAX (TO_DATE)
                               FROM apps.msc_exception_details_v@xxbetsytobilly.emrsn.com
                              WHERE     plan_id = e.plan_id
                                    AND exception_type = e.exception_type
                                    AND sr_instance_id = 81
                                    AND organization_id = e.organization_id
                                    AND inventory_item_id =
                                        e.inventory_item_id
                                    AND category_set_id = 24)
                        AND seq_num IS NOT NULL)
                    shortage_duration2,
                e.shortage_duration
                    shortage_duration,
                ROUND (e.below_ss_qty_pct / 100, 3)
                    MAX_PERCENT_BELOW_SS,
                e.from_date
                    EARLIEST_FROM_DATE,
                e.TO_DATE
                    LATEST_TO_DATE,
                ROUND ((e.quantity / e.below_ss_qty_pct) * 100, 0)
                    SAFETY_STOCK_QUANTITY,
                e.source_organization_code,
                e.category_name
                    COMMODITY_CODE,
                e.order_date,
                e.transaction_id,
                e.organization_id,
                s.order_number,
                s.po_line_id,
                (SELECT MIN (msr.sourcing_rule_name)
                   FROM apps.msc_sr_assignments@xxbetsytobilly.emrsn.com  msa,
                        apps.msc_sourcing_rules@xxbetsytobilly.emrsn.com  msr
                  WHERE     msa.inventory_item_id = e.inventory_item_id
                        AND msa.organization_id = e.organization_id
                        AND msa.sr_instance_id = 81
                        AND msr.sr_instance_id = 81
                        AND msa.sourcing_rule_id = msr.sourcing_rule_id)
                    sourcing_rule,
                si.replenish_to_order_flag,
                si.planning_make_buy_code,
                e.buyer_name,
                e.exception_type_text
           FROM apps.msc_exception_details_v@xxbetsytobilly.emrsn.com  e,
                apps.msc_system_items@xxbetsytobilly.emrsn.com         si,
                apps.msc_supplies@xxbetsytobilly.emrsn.com             s
          WHERE     e.plan_id = 1108
                AND e.exception_type IN (2,
                                         6,
                                         7,
                                         8,
                                         10,
                                         20,
                                         15,
                                         16)
                AND e.sr_instance_id = 81
                AND e.organization_id IN (16597,5000,10505,4154)
                AND (   'ALL' IN ('ALL')
                     OR e.planner_code IN ('ALL'))
                AND e.category_set_id = 24           -- Inventory category set
                AND e.inventory_item_id = si.inventory_item_id
                AND e.organization_id = si.organization_id
                AND e.plan_id = si.plan_id
                AND e.sr_instance_id = si.sr_instance_id
                AND e.plan_id = s.plan_id(+)
                AND e.sr_instance_id = s.sr_instance_id(+)
                AND e.transaction_id = s.transaction_id(+)
Re: Sql having performance issue [message #686709 is a reply to message #686708] Sun, 27 November 2022 03:58 Go to previous messageGo to next message
nishant87
Messages: 53
Registered: September 2013
Location: india
Member
THe ISSUE is with MIN (from_date) and MAX (TO_DATE)
Re: Sql having performance issue [message #686712 is a reply to message #686709] Mon, 28 November 2022 12:28 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
If you re-write it to comprehensible form, perhaps people will have a better chance of assisting. Be sure to provide the exec plan, too.
All I can say at the moment is that you are projecting scalar (well, I hope scalar) subqueries which is usually a Bad Thing.
Previous Topic: Why *is* my index used?
Next Topic: rdbms 12.2C performance issues sometimes
Goto Forum:
  


Current Time: Thu Mar 28 05:29:41 CDT 2024