Recently on the project, customers asked me to help see a view with slow queries, provided that they had optimized it internally before giving it to me. When I saw their optimization, they simply used hint statements, and it didn't work.
First of all, we will describe the situation of this view. It consists of three SQL unions, totaling about 220 + rows. Some field values are obtained through DBLINK. Details are as follows:

CREATE OR REPLACE VIEW CUX_DWMS_GROUP_LOT_ITA_V AS
select t.batch_no,
       t.delivery_detail_id,
       t.order_type,
       t.virtual_ship_flag,
       t.ship_number,
       t.source_header_number,
       t.source_line_number,
       t.reference_number,
       t.party_name,
       t.ship_add,
       t.item_number,
       t.item_name,
       t.requested_quantity,
       t.picked_qty,
       t.requested_quantity_uom,
       t.pick_person,
       t.schedule_ship_date,
       t.ordered_date,
       t.organization_id,
       t.organization_code,
       t.organization_name,
       t.subinventory,
       t.ship_method_code,
       t.ship_method,
       t.released_status,
       t.line_status,
       t.remark,
       t.creation_date,
       t.printed_flag,
       t.box_count,
       t.pack_method,
       t.ship_desctiption,
       t.ddt_type
  from (SELECT /*+ leading (ooh) index(ooh ONT.OE_ORDER_HEADERS_N11) index (csi CUX.CUX_SHIP_INPUT_INFO_ITA_N01)*/
         row_number() over(partition by trh.request_number, wdd.source_header_number, wdd.source_line_number order by wdd.delivery_detail_id desc) rn,
         trh.request_number batch_no,
         wdd.delivery_detail_id,
         ott.attribute2 order_type,
         ooh.attribute9 virtual_ship_flag,
         (select csi.print_num
            from cux_ship_input_info_ita csi
           where csi.batch_num = trh.request_number
             and csi.source_header_number = wdd.source_header_number) ship_number,
         wdd.source_header_number,
         wdd.source_line_number,
         wdd.reference_number,
         ooh.attribute5 party_name,
         ool.attribute1 || '.' || ool.attribute4 ship_add,
         msib.segment1 item_number,
         msib.description item_name,
         wdd.requested_quantity,
         nvl(trl.quantity_delivered, 0) picked_qty,
         wdd.requested_quantity_uom,
         ooh.attribute6 pick_person,
         ool.schedule_ship_date,
         ooh.ordered_date,
         ood.organization_id,
         ood.organization_code,
         ood.organization_name,
         wdd.ORIGINAL_SUBINVENTORY subinventory,
         ool.attribute2 ship_method_code,
         ool.attribute3 ship_method,
         wdd.released_status,
         flv.meaning line_status,
         ooh.attribute2 remark,
         ooh.creation_date,
         decode(csi.print_flag, NULL, 'N', 'Y') printed_flag,
         csi.box_count, --Number
         csi.pack_method, --Loading method
         csi.ship_desctiption, --describe
         (select flv.lookup_code
            from fnd_lookup_values_vl                                   flv,
                 apps.cux_mid_order_header_temp_t@db_iwms.dahuatech.com cmo,
                 --apps.oe_order_headers_all@db_iwms.dahuatech.com        ooh1,
                 cux_wms_so_sync_all cws
           where cmo.freasonsforshipment = flv.MEANING
             and flv.lookup_type = 'CUX_PRINT_TEMPLATE_MAPPING'
             and cmo.fodernumber = cws.order_number
             and cws.mo_number = ooh.order_number
             and rownum<2
             /*group by flv.lookup_code*/) ddt_type
          FROM wsh_delivery_details         wdd,
               wsh_delivery_assignments     wda,
               oe_order_headers_all         ooh,
               mtl_txn_request_lines        trl,
               mtl_txn_request_headers      trh,
               ont.oe_transaction_types_all ott,
               oe_order_lines_all           ool,
               mtl_system_items_b           msib,
               org_organization_definitions ood,
               fnd_lookup_values            flv,
               cux_ship_input_info_ita      csi
         WHERE 1 = 1
           AND trh.header_id = trl.header_id
           AND trl.line_id = wdd.move_order_line_id
           AND wda.delivery_detail_id = wdd.delivery_detail_id
           AND ool.header_id = ooh.header_id
           AND ooh.order_type_id = ott.transaction_type_id
           AND wdd.source_line_id = ool.line_id
           AND wdd.source_code = 'OE'
           AND wdd.inventory_item_id = msib.inventory_item_id
           AND wdd.organization_id = msib.organization_id
           AND wdd.organization_id = ood.organization_id
           AND flv.lookup_type = 'PICK_STATUS'
           AND flv.lookup_code = wdd.released_status
           AND flv.enabled_flag = 'Y'
           AND (SYSDATE BETWEEN flv.start_date_active AND
               nvl(flv.end_date_active, SYSDATE + 2))
           AND flv.language = userenv('LANG')
           AND ooh.flow_status_code in ('BOOKED', 'CLOSED')
           AND ood.ORGANIZATION_CODE = '941'
           AND wdd.released_status in ('Y', 'C')
           AND csi.batch_num = trh.request_number
           AND csi.source_header_number = wdd.source_header_number
           ) t
 WHERE t.rn = 1
 UNION
 --Sample collar
 select /*+index (csi CUX.CUX_SHIP_INPUT_INFO_ITA_N01)*/mtrh1.request_number batch_no,
        0 delivery_detail_id,
        'Move Order' order_type,
        '' virtual_ship_flag,
        '' ship_number,
        '' source_header_number,
        '' source_line_number,
        '' reference_number,
        decode(cic.Accnt_name,
               'Overseas internal testing',
               cic.bus_owner,
               cic.accnt_name) party_name,
        cic.addr ship_add,
        cic.part_num item_number,
        msi.description item_name,
        mtrl.quantity requested_quantity,
        mmt.transaction_quantity picked_qty,
        msi.primary_uom_code requested_quantity_uom,
        (select to_char(user_name)
           from fnd_user
          where user_id = mmt.created_by) pick_person,
        mtrh.creation_date schedule_ship_date,
        mtrh.creation_date ordered_date,
        mmt.organization_id organization_id,
        cic.out_inv_org organization_code,
        (select organization_name
           from org_organization_definitions
          where organization_code = cic.out_inv_org) organization_name,
        mmt.subinventory_code subinventory,
        '' ship_method_code,
        '' ship_method,
        '' released_status,
        '' line_status,
        '' remark,
        sysdate creation_date,
        csi.print_flag printed_flag,
        csi.box_count box_count,
        csi.pack_method pack_method,
        csi.ship_desctiption ship_desctiption,
        decode(cic.accnt_name, 'Overseas internal testing', 'B9', 'B4') DDT_TYPE
   from apps.cux_inv_crm_move_orders@db_iwms.dahuatech.com cic,
        apps.mtl_txn_request_headers@db_iwms.dahuatech.com mtrh,
        apps.mtl_txn_request_lines@db_iwms.dahuatech.com   mtrl,
        mtl_material_transactions                          mmt,
        mtl_txn_request_headers                            mtrh1,
        mtl_txn_request_lines                              mtrl1,
        mtl_system_items_b                                 msi,
        cux_ship_input_info_ita      csi
  where cic.out_inv_org = '941'
    and cic.process_status = 'S'
    and cic.move_order_header_id = mtrh.header_id
    and cic.move_order_line_id = mtrl.line_id
    and msi.inventory_item_id = mmt.inventory_item_id
    and msi.organization_id = mmt.organization_id
    and mtrh1.header_id = mmt.transaction_source_id
    and mtrl1.header_id = mtrl1.header_id
    and mtrl1.line_id = mmt.trx_source_line_id
    and mmt.transaction_type_id = 160
    and mmt.organization_id = 270
    and mmt.transaction_quantity > 0
    and mtrl1.line_number = mtrl.line_number
    and mtrh1.request_number = 'W' || mtrh.request_number
    and mtrh1.request_number=csi.batch_num(+)
UNION
--Transfer warehouse, for central warehouse and Milan warehouse
select /*+index (csi CUX.CUX_SHIP_INPUT_INFO_ITA_N01)*/mtrh.request_number batch_no,
       0 delivery_detail_id,
       'Move Order' order_type,
       '' virtual_ship_flag,
       '' ship_number,
       '' source_header_number,
       '' source_line_number,
       '' reference_number,
       'Dahua Technology S.r.l.' party_name,
       'Via Brughetti, 9/h - 20813 Bovisio Masciago (MB)' ship_add,
       msi.segment1 item_number,
       msi.description item_name,
       mtrl.quantity requested_quantity,
       sum(mmt.transaction_quantity) picked_qty,
       msi.primary_uom_code requested_quantity_uom,
       (select to_char(user_name)
          from fnd_user
         where user_id = mmt.created_by) pick_person,
       mtrh.creation_date schedule_ship_date,
       mtrh.creation_date ordered_date,
       mmt.organization_id organization_id,
       ood.organization_code organization_code,
       ood.organization_name organization_name,
       mmt.subinventory_code subinventory,
       '' ship_method_code,
       '' ship_method,
       '' released_status,
       '' line_status,
       '' remark,
       sysdate creation_date,
       csi.print_flag printed_flag,
       csi.box_count box_count,
       csi.pack_method pack_method,
       csi.ship_desctiption ship_desctiption,
       'S1' DDT_TYPE
  from mtl_material_transactions    mmt,
       mtl_txn_request_headers      mtrh,
       mtl_txn_request_lines        mtrl,
       mtl_system_items_b           msi,
       org_organization_definitions ood,
       cux_ship_input_info_ita      csi
 where mtrh.header_id = mmt.transaction_source_id
   and mtrl.line_id = mmt.trx_source_line_id
   and mtrh.header_id = mtrh.header_id
   and mmt.organization_id = mtrh.organization_id
   and msi.inventory_item_id = mtrl.inventory_item_id
   and msi.organization_id = mmt.organization_id
   and ood.ORGANIZATION_ID = mmt.ORGANIZATION_ID
   and mmt.transaction_type_id = 115 --Subinventory transfer
   and mmt.organization_id = 270
   and mmt.transaction_quantity > 0
   and ((mtrl.to_subinventory_code = '91.02.01' and
       mtrl.from_subinventory_code = '91.02.02') or
       (mtrl.to_subinventory_code = '91.02.02' and
       mtrl.from_subinventory_code = '91.02.01'))
   and mtrh.request_number = csi.batch_num
   and not exists (select 1
          from mtl_txn_request_lines mtrl1
         where mtrl1.line_id = mtrl.line_id
           and mtrl1.line_status = ('7')) --Ensure that the single line of handling is not open
--and mtrh.request_number = 'W10194646'
 group by mtrh.request_number,
          msi.segment1,
          msi.description,
          mtrl.quantity,
          msi.primary_uom_code,
          mmt.created_by,
          mtrh.creation_date,
          mtrh.creation_date,
          mmt.organization_id,
          ood.organization_code,
          ood.organization_name,
          mmt.subinventory_code ,
          csi.print_flag,
          csi.box_count,
          csi.pack_method,
          csi.ship_desctiption;

Secondly, the view was queried in plsql. After 200 + minutes, the result was not executed. Finally, the trace log file was typed out after running. The result of this SQL is only 6 pieces of data. Why does it take so long?


Finally, let me talk about my sql optimization idea.

• Generally, Sql optimization has to start from the root and adjust the SQL. Most technical or consultants or customers only pay attention to the execution results while writing the SQL, regardless of their performance in time/space, so most SQL can not withstand deliberation, so the premise of adjusting the SQL is to ensure that the results are correct. The purpose of SQL adjustment is to make use of the data set driver in Oracle to make the result set with small amount of data predominate and associate other tables or objects. 2. Let where clause condition index and try to index the result set with small amount. The adjusted view is as follows:

CREATE OR REPLACE VIEW CUX_DWMS_GROUP_LOT_ITA_V AS
select t.batch_no,
       t.delivery_detail_id,
       t.order_type,
       t.virtual_ship_flag,
       t.ship_number,
       t.source_header_number,
       t.source_line_number,
       t.reference_number,
       t.party_name,
       t.ship_add,
       t.item_number,
       t.item_name,
       t.requested_quantity,
       t.picked_qty,
       t.requested_quantity_uom,
       t.pick_person,
       t.schedule_ship_date,
       t.ordered_date,
       t.organization_id,
       t.organization_code,
       t.organization_name,
       t.subinventory,
       t.ship_method_code,
       t.ship_method,
       t.released_status,
       t.line_status,
       t.remark,
       t.creation_date,
       t.printed_flag,
       t.box_count,
       t.pack_method,
       t.ship_desctiption,
       t.ddt_type
  from (SELECT row_number() over(partition by trh.request_number, wdd.source_header_number, wdd.source_line_number order by wdd.delivery_detail_id desc) rn,
               trh.request_number batch_no,
               wdd.delivery_detail_id,
               (select ott.attribute2
                  from ont.oe_transaction_types_all ott
                 where ooh.order_type_id = ott.transaction_type_id) order_type,
               ooh.attribute9 virtual_ship_flag,
               (select csi.print_num
                  from cux_ship_input_info_ita csi
                 where csi.batch_num = trh.request_number
                   and csi.source_header_number = wdd.source_header_number) ship_number,
               wdd.source_header_number,
               wdd.source_line_number,
               wdd.reference_number,
               ooh.attribute5 party_name,
               ool.attribute1 || '.' || ool.attribute4 ship_add,
               msib.segment1 item_number,
               msib.description item_name,
               wdd.requested_quantity,
               nvl(trl.quantity_delivered, 0) picked_qty,
               wdd.requested_quantity_uom,
               ooh.attribute6 pick_person,
               ool.schedule_ship_date,
               ooh.ordered_date,
               ood.organization_id,
               ood.organization_code,
               ood.organization_name,
               wdd.original_subinventory subinventory,
               ool.attribute2 ship_method_code,
               ool.attribute3 ship_method,
               wdd.released_status,
               (select flv.meaning
                  from fnd_lookup_values_vl flv
                 where 1 = 1
                   AND flv.lookup_type = 'PICK_STATUS'
                   AND flv.lookup_code = wdd.released_status
                   AND flv.enabled_flag = 'Y'
                   AND (SYSDATE BETWEEN flv.start_date_active AND
                       nvl(flv.end_date_active, SYSDATE + 2))) line_status,
               ooh.attribute2 remark,
               ooh.creation_date,
               decode(csi.print_flag, NULL, 'N', 'Y') printed_flag,
               csi.box_count, --Number
               csi.pack_method, --Loading method
               csi.ship_desctiption, --describe
               (select flv.lookup_code
                  from fnd_lookup_values_vl                                   flv,
                       apps.cux_mid_order_header_temp_t@db_iwms.dahuatech.com cmo,
                       cux_wms_so_sync_all                                    cws -- Not go T2 Indexes
                 where cmo.freasonsforshipment = flv.MEANING
                   and flv.lookup_type = 'CUX_PRINT_TEMPLATE_MAPPING'
                   and cmo.fodernumber = cws.order_number
                   and cws.mo_number = ooh.order_number
                   and rownum = 1) ddt_type
          FROM wsh_delivery_details         wdd,
               oe_order_headers_all         ooh,
               oe_order_lines_all           ool,
               mtl_txn_request_lines        trl,
               mtl_txn_request_headers      trh,
               mtl_system_items_b           msib,
               org_organization_definitions ood,
               cux_ship_input_info_ita      csi
         WHERE 1 = 1
           and wdd.source_header_id = ooh.header_id
           AND wdd.source_line_id = ool.line_id
           AND wdd.source_code = 'OE'
           AND wdd.released_status in ('Y', 'C')
           and wdd.inventory_item_id = ool.inventory_item_id
           AND wdd.move_order_line_id = trl.line_id
           and wdd.inventory_item_id = trl.inventory_item_id
           and wdd.organization_id = trl.organization_id
           AND wdd.inventory_item_id = msib.inventory_item_id
           AND wdd.organization_id = msib.organization_id
           AND wdd.organization_id = ood.organization_id
           AND trh.header_id = trl.header_id
           AND ooh.header_id = ool.header_id
           AND ooh.flow_status_code in ('BOOKED', 'CLOSED')
           AND ood.organization_id = 270
           AND csi.batch_num = trh.request_number
           AND csi.source_header_number = wdd.source_header_number) t
 WHERE t.rn = 1

UNION
--Sample collar
select wms_temp.request_number batch_no,
       0 delivery_detail_id,
       'Move Order' order_type,
       '' virtual_ship_flag,
       '' ship_number,
       '' source_header_number,
       '' source_line_number,
       '' reference_number,
       decode(ebs_temp.accnt_name,
              'Overseas internal testing',
              ebs_temp.bus_owner,
              ebs_temp.accnt_name) party_name,
       ebs_temp.addr ship_add,
       ebs_temp.part_num item_number,
       wms_temp.description item_name,
       ebs_temp.quantity requested_quantity,
       wms_temp.transaction_quantity picked_qty,
       wms_temp.primary_uom_code requested_quantity_uom,
       (select to_char(user_name)
          from fnd_user
         where user_id = wms_temp.created_by) pick_person,
       ebs_temp.creation_date schedule_ship_date,
       ebs_temp.creation_date ordered_date,
       wms_temp.organization_id organization_id,
       ebs_temp.out_inv_org organization_code,
       (select organization_name
          from org_organization_definitions
         where organization_code = ebs_temp.out_inv_org) organization_name,
       wms_temp.subinventory_code subinventory,
       '' ship_method_code,
       '' ship_method,
       '' released_status,
       '' line_status,
       '' remark,
       sysdate creation_date,
       csi.print_flag printed_flag,
       csi.box_count box_count,
       csi.pack_method pack_method,
       csi.ship_desctiption ship_desctiption,
       decode(ebs_temp.accnt_name, 'Overseas internal testing', 'B9', 'B4') ddt_type
  from (select cic.addr,
               cic.part_num,
               mtrl.quantity,
               mtrh.creation_date,
               mtrh.request_number,
               mtrl.line_number,
               cic.out_inv_org,
               cic.bus_owner,
               cic.accnt_name
          from apps.cux_inv_crm_move_orders@db_iwms.dahuatech.com cic,
               apps.mtl_txn_request_headers@db_iwms.dahuatech.com mtrh,
               apps.mtl_txn_request_lines@db_iwms.dahuatech.com   mtrl
         where cic.out_inv_org = '941'
           and cic.process_status = 'S'
           and cic.move_order_header_id = mtrh.header_id
           and cic.move_order_line_id = mtrl.line_id
           and mtrh.header_id = mtrl.line_id) ebs_temp,
       (select mtrh1.request_number,
               mtrl1.line_number,
               msi.description,
               mmt.transaction_quantity,
               msi.primary_uom_code,
               mmt.created_by,
               mmt.organization_id,
               mmt.subinventory_code
          from mtl_material_transactions mmt,
               mtl_txn_request_headers   mtrh1,
               mtl_txn_request_lines     mtrl1,
               mtl_system_items_b        msi
         where 1 = 1
           and msi.inventory_item_id = mmt.inventory_item_id
           and msi.organization_id = mmt.organization_id
           and mtrh1.header_id = mmt.transaction_source_id
           and mtrl1.header_id = mtrl1.header_id
           and mtrl1.line_id = mmt.trx_source_line_id
           and mmt.transaction_type_id = 160
           and mmt.organization_id = 270
           and mmt.transaction_quantity > 0) wms_temp,
       cux_ship_input_info_ita csi
 where 1 = 1
   and wms_temp.line_number = ebs_temp.line_number
   and wms_temp.request_number = 'W' || ebs_temp.request_number
   and wms_temp.request_number = csi.batch_num(+)

UNION
--Transfer warehouse, for central warehouse and Milan warehouse
select mmt_temp.batch_no,
       0 delivery_detail_id,
       'Move Order' order_type,
       '' virtual_ship_flag,
       '' ship_number,
       '' source_header_number,
       '' source_line_number,
       '' reference_number,
       'Dahua Technology S.r.l.' party_name,
       'Via Brughetti, 9/h - 20813 Bovisio Masciago (MB)' ship_add,
       mmt_temp.item_number,
       mmt_temp.item_name,
       mmt_temp.requested_quantity,
       mmt_temp.picked_qty,
       mmt_temp.requested_quantity_uom,
       (select to_char(user_name)
          from fnd_user
         where user_id = mmt_temp.created_by) pick_person,
       mmt_temp.creation_date schedule_ship_date,
       mmt_temp.creation_date ordered_date,
       mmt_temp.organization_id,
       ood.organization_code organization_code,
       ood.organization_name organization_name,
       mmt_temp.subinventory,
       '' ship_method_code,
       '' ship_method,
       '' released_status,
       '' line_status,
       '' remark,
       sysdate creation_date,
       csi.print_flag printed_flag,
       csi.box_count box_count,
       csi.pack_method pack_method,
       csi.ship_desctiption ship_desctiption,
       'S1' ddt_type
  from (select mtrh.request_number batch_no,
               msi.segment1 item_number,
               msi.description item_name,
               mtrl.quantity requested_quantity,
               sum(mmt.transaction_quantity) picked_qty,
               msi.primary_uom_code requested_quantity_uom,
               mmt.created_by,
               mtrh.creation_date,
               mmt.organization_id,
               mmt.subinventory_code subinventory
          from mtl_material_transactions mmt,
               mtl_txn_request_headers   mtrh,
               inv.mtl_txn_request_lines mtrl,
               mtl_system_items_b        msi
         where 1 = 1
           and mmt.transaction_source_id = mtrh.header_id
           and mmt.trx_source_line_id = mtrl.line_id
           and mmt.organization_id = mtrl.organization_id
           and mmt.inventory_item_id = msi.inventory_item_id
           and mmt.organization_id = msi.organization_id
           and mtrh.header_id = mtrl.header_id
           and mmt.transaction_type_id = 115 --Subinventory transfer
           and mmt.transaction_quantity > 0
           and mmt.organization_id = 270
           and ((mtrl.to_subinventory_code = '91.02.01' and
               mtrl.from_subinventory_code = '91.02.02') or
               (mtrl.to_subinventory_code = '91.02.02' and
               mtrl.from_subinventory_code = '91.02.01'))
           and mtrl.line_status <> '7' --Ensure that the single line of handling is not open
         group by mtrh.request_number,
                  msi.segment1,
                  msi.description,
                  mtrl.quantity,
                  msi.primary_uom_code,
                  mmt.created_by,
                  mtrh.creation_date,
                  mmt.organization_id,
                  mmt.subinventory_code) mmt_temp,
       org_organization_definitions ood,
       cux_ship_input_info_ita csi
 where 1 = 1
   and mmt_temp.batch_no = csi.batch_num
   and mmt_temp.organization_id = ood.organization_id;

After adjustment, cost decreased from 383.4w to 1.8w.

• Query the view again after sql adjustment, its execution time is not ideal, so it has to be analyzed through trace file.
  
  Here is the sub-query on the select result. The T2 index of the table is built on the order_number field, and there is also dblink. First, it is strongly recommended that customers adjust the value sql here. The worst way is to specify the index with hint.
  
  This also needs to be adjusted, but look at its sql and index field on the table [move_order_line_id], it is not inappropriate, if you must adjust, only adjust the index, build on move_order_line_id and release_status to reduce the result set, but the individual is not very recommend, reason for follow-up.

If adjusted, the execution time is controlled within 20-50 seconds.