7 Replies Latest reply: Sep 20, 2011 11:47 AM by Sandeep Gandhi, Consultant RSS

    How to avoid full table scan of BOM_SMALL_EXPL_TEMP ?

    875878
      Hi,
      We are creating this custom report which has to explode the boms for all the work orders for the day and get the first level phantom items. Issue is that, the query is causing a full-table-scan on the BOM_SMALL_EXPL_TEMP and this is causing performance issues some times. Is there any way to avoid this?

      Below given is the query and execution plan:

      Query:-
      SELECT item, description, total_qty,
      NVL ((SELECT SUM (transaction_quantity)
      FROM mtl_onhand_quantities mhq
      WHERE inventory_item_id = x.inventory_item_id
      AND organization_id = x.organization_id),
      0
      ) onhand_qty
      FROM (SELECT si.segment1 item, si.description, si.inventory_item_id,
      si.organization_id,
      SUM (wor.required_quantity - wor.quantity_issued)
      total_qty
      FROM wip_entities we,
      wip_discrete_jobs dj,
      bom_departments bd,
      wip_operations wo,
      wip_requirement_operations wor,
      mtl_system_items_b si,
      wip_reservations_v wrv
      WHERE 1 = 1
      AND we.organization_id = :p_org_id
      AND dj.organization_id = we.organization_id
      AND we.wip_entity_id = dj.wip_entity_id
      AND wo.organization_id = we.organization_id
      AND bd.organization_id = wo.organization_id
      AND si.organization_id = wor.organization_id
      AND bd.department_id = wo.department_id
      AND wo.wip_entity_id = dj.wip_entity_id
      AND wor.inventory_item_id = si.inventory_item_id
      AND si.item_type = 'AP'
      AND we.entity_type = '1'
      AND wor.wip_entity_id(+) = we.wip_entity_id
      AND wrv.wip_entity_id(+) = we.wip_entity_id
      AND TRUNC (dj.scheduled_completion_date) BETWEEN (:p_date_from
      )
      AND (:p_date_to)
      AND bd.department_code BETWEEN :p_dept_from AND :p_dept_to
      AND we.wip_entity_name = NVL (:p_wo_num, we.wip_entity_name)
      AND wrv.segment1 = NVL (:p_so_num, wrv.segment1)
      AND :p_rollup = 'Rollup by Item'
      AND si.inventory_item_id IN (
      SELECT msi1.inventory_item_id
      FROM bom_small_expl_temp bomt,
      mtl_system_items_b msi1
      WHERE bomt.component_item_id = msi1.inventory_item_id
      AND bomt.organization_id = msi1.organization_id
      AND bomt.GROUP_ID = :p_group_id
      AND bomt.top_item_id = dj.primary_item_id
      AND bomt.organization_id = dj.organization_id
      AND msi1.item_type = 'AP'
      AND plan_level =
      (SELECT MIN (plan_level)
      FROM bom_small_expl_temp bomt1,
      mtl_system_items_b msi2
      WHERE bomt1.component_item_id =
      msi2.inventory_item_id
      AND bomt1.organization_id =
      msi2.organization_id
      AND bomt1.GROUP_ID = :p_group_id
      AND bomt1.top_item_id = bomt.top_item_id
      AND msi2.organization_id =
      msi1.organization_id
      AND msi2.item_type = 'AP'
      AND SUBSTR (bomt1.sort_order, 8, 7) =
      SUBSTR (bomt.sort_order, 8, 7)))
      GROUP BY si.segment1,
      si.description,
      si.inventory_item_id,
      si.organization_id) x
      ORDER BY item

      Execution Plan:-

      SELECT STATEMENT ALL_ROWS Cost: 1,209
           3 SORT AGGREGATE Bytes: 17 Cardinality: 1
                2 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_ONHAND_QUANTITIES_DETAIL Cost: 4 Bytes: 17 Cardinality: 1
                     1 INDEX RANGE SCAN INDEX INV.MTL_ONHAND_QUANTITIES_N6 Cost: 3 Cardinality: 1
           77 SORT GROUP BY Bytes: 247 Cardinality: 1
                76 CONCATENATION
                     41 FILTER
                          40 HASH JOIN SEMI Cost: 1,177 Bytes: 247 Cardinality: 1
                               28 NESTED LOOPS Cost: 1,168 Bytes: 215 Cardinality: 1
                                    25 NESTED LOOPS Cost: 1,166 Bytes: 143 Cardinality: 1
                                         22 NESTED LOOPS Cost: 1,163 Bytes: 121 Cardinality: 1
                                              19 NESTED LOOPS Cost: 1,161 Bytes: 107 Cardinality: 1
                                                   17 NESTED LOOPS Cost: 1,160 Bytes: 101 Cardinality: 1
                                                        14 NESTED LOOPS Cost: 1,157 Bytes: 79 Cardinality: 1
                                                             11 NESTED LOOPS Cost: 1,155 Bytes: 58 Cardinality: 1
                                                                  8 NESTED LOOPS Cost: 1,149 Bytes: 102 Cardinality: 3
                                                                       5 TABLE ACCESS BY INDEX ROWID TABLE BOM.BOM_DEPARTMENTS Cost: 2 Bytes: 19 Cardinality: 1
                                                                            4 INDEX RANGE SCAN INDEX (UNIQUE) BOM.BOM_DEPARTMENTS_U2 Cost: 1 Cardinality: 1
                                                                       7 TABLE ACCESS BY INDEX ROWID TABLE WIP.WIP_OPERATIONS Cost: 1,147 Bytes: 2,085 Cardinality: 139
                                                                            6 INDEX RANGE SCAN INDEX WIP.WIP_OPERATIONS_N2 Cost: 15 Cardinality: 4,858
                                                                  10 TABLE ACCESS BY INDEX ROWID TABLE WIP.WIP_DISCRETE_JOBS Cost: 2 Bytes: 24 Cardinality: 1
                                                                       9 INDEX UNIQUE SCAN INDEX (UNIQUE) WIP.WIP_DISCRETE_JOBS_U1 Cost: 1 Cardinality: 1
                                                             13 TABLE ACCESS BY INDEX ROWID TABLE WIP.WIP_ENTITIES Cost: 2 Bytes: 21 Cardinality: 1
                                                                  12 INDEX UNIQUE SCAN INDEX (UNIQUE) WIP.WIP_ENTITIES_U1 Cost: 1 Cardinality: 1
                                                        16 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_RESERVATIONS Cost: 3 Bytes: 22 Cardinality: 1
                                                             15 INDEX RANGE SCAN INDEX INV.MTL_RESERVATIONS_N9 Cost: 2 Cardinality: 1
                                                   18 INDEX UNIQUE SCAN INDEX (UNIQUE) ONT.OE_ORDER_LINES_U1 Cost: 1 Bytes: 6 Cardinality: 1
                                              21 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SALES_ORDERS Cost: 2 Bytes: 14 Cardinality: 1
                                                   20 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SALES_ORDERS_U1 Cost: 1 Cardinality: 1
                                         24 TABLE ACCESS BY INDEX ROWID TABLE WIP.WIP_REQUIREMENT_OPERATIONS Cost: 3 Bytes: 264 Cardinality: 12
                                              23 INDEX RANGE SCAN INDEX (UNIQUE) WIP.WIP_REQUIREMENT_OPERATIONS_U1 Cost: 2 Cardinality: 12
                                    27 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 72 Cardinality: 1
                                         26 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1 Cardinality: 1
                               39 VIEW VIEW VW_SQ_1 Cost: 8 Bytes: 32 Cardinality: 1
                                    38 FILTER
                                         37 NESTED LOOPS Cost: 4 Bytes: 1,087 Cardinality: 1
                                         *     29 TABLE ACCESS FULL TABLE (TEMP) BOM.BOM_SMALL_EXPL_TEMP Cost: 2 Bytes: 1,067 Cardinality: 1*
                                              36 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 20 Cardinality: 1
                                                   35 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1 Cardinality: 1
                                                        34 SORT AGGREGATE Bytes: 1,087 Cardinality: 1
                                                             33 NESTED LOOPS Cost: 4 Bytes: 1,087 Cardinality: 1
                                                                  30 TABLE ACCESS FULL TABLE (TEMP) BOM.BOM_SMALL_EXPL_TEMP Cost: 2 Bytes: 1,067 Cardinality: 1
                                                                  32 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 20 Cardinality: 1
                                                                       31 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1 Cardinality: 1
                     75 FILTER
                          74 HASH JOIN SEMI Cost: 31 Bytes: 247 Cardinality: 1
                               66 NESTED LOOPS Cost: 22 Bytes: 215 Cardinality: 1
                                    63 NESTED LOOPS Cost: 20 Bytes: 143 Cardinality: 1
                                         60 NESTED LOOPS Cost: 17 Bytes: 121 Cardinality: 1
                                              58 NESTED LOOPS Cost: 16 Bytes: 115 Cardinality: 1
                                                   55 NESTED LOOPS Cost: 15 Bytes: 96 Cardinality: 1
                                                        52 NESTED LOOPS Cost: 12 Bytes: 81 Cardinality: 1
                                                             49 NESTED LOOPS Cost: 10 Bytes: 57 Cardinality: 1
                                                                  46 NESTED LOOPS Cost: 8 Bytes: 36 Cardinality: 1
                                                                       43 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SALES_ORDERS Cost: 4 Bytes: 14 Cardinality: 1
                                                                            42 INDEX RANGE SCAN INDEX INV.MTL_SALES_ORDERS_N1 Cost: 3 Cardinality: 1
                                                                       45 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_RESERVATIONS Cost: 4 Bytes: 22 Cardinality: 1
                                                                            44 INDEX RANGE SCAN INDEX INV.MTL_RESERVATIONS_N8 Cost: 2 Cardinality: 4
                                                                  48 TABLE ACCESS BY INDEX ROWID TABLE WIP.WIP_ENTITIES Cost: 2 Bytes: 21 Cardinality: 1
                                                                       47 INDEX UNIQUE SCAN INDEX (UNIQUE) WIP.WIP_ENTITIES_U1 Cost: 1 Cardinality: 1
                                                             51 TABLE ACCESS BY INDEX ROWID TABLE WIP.WIP_DISCRETE_JOBS Cost: 2 Bytes: 24 Cardinality: 1
                                                                  50 INDEX UNIQUE SCAN INDEX (UNIQUE) WIP.WIP_DISCRETE_JOBS_U1 Cost: 1 Cardinality: 1
                                                        54 TABLE ACCESS BY INDEX ROWID TABLE WIP.WIP_OPERATIONS Cost: 3 Bytes: 15 Cardinality: 1
                                                             53 INDEX RANGE SCAN INDEX (UNIQUE) WIP.WIP_OPERATIONS_U1 Cost: 2 Cardinality: 1
                                                   57 TABLE ACCESS BY INDEX ROWID TABLE BOM.BOM_DEPARTMENTS Cost: 1 Bytes: 19 Cardinality: 1
                                                        56 INDEX UNIQUE SCAN INDEX (UNIQUE) BOM.BOM_DEPARTMENTS_U1 Cost: 0 Cardinality: 1
                                              59 INDEX UNIQUE SCAN INDEX (UNIQUE) ONT.OE_ORDER_LINES_U1 Cost: 1 Bytes: 6 Cardinality: 1
                                         62 TABLE ACCESS BY INDEX ROWID TABLE WIP.WIP_REQUIREMENT_OPERATIONS Cost: 3 Bytes: 264 Cardinality: 12
                                              61 INDEX RANGE SCAN INDEX (UNIQUE) WIP.WIP_REQUIREMENT_OPERATIONS_U1 Cost: 2 Cardinality: 12
                                    65 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 72 Cardinality: 1
                                         64 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1 Cardinality: 1
                               73 VIEW VIEW VW_SQ_1 Cost: 8 Bytes: 32 Cardinality: 1
                                    72 FILTER
                                         71 NESTED LOOPS Cost: 4 Bytes: 1,087 Cardinality: 1
                                              67 TABLE ACCESS FULL TABLE (TEMP) BOM.BOM_SMALL_EXPL_TEMP Cost: 2 Bytes: 1,067 Cardinality: 1
                                              70 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 20 Cardinality: 1
                                                   69 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1 Cardinality: 1
                                                        68 SORT AGGREGATE Bytes: 1,087 Cardinality: 1