2 Replies Latest reply on Apr 17, 2015 9:58 PM by 379746

    Need help in tuning a query

    379746

      Hi,

      I have the following query/report that takes hours to complete. Application version is 11.5.9 and database is on 9.2.0.6

      {code}

      SELECT   c.inventory_location_id,

                  c.segment1

               || '.'

               || c.segment2

               || '.'

               || c.segment3

               || '.'

               || c.segment4 loc,

               b.lot_number lot, -1 * b.primary_quantity lot_qty

          FROM mtl_material_transactions a,

               mtl_transaction_lot_numbers b,

               mtl_item_locations c

         WHERE a.transaction_id = b.transaction_id

           AND a.locator_id = c.inventory_location_id

           AND a.trx_source_line_id = :b1

      ORDER BY 1

      {code}

      Explain Plan shows the following

      {code}

       

      SELECT STATEMENT  CHOOSECost: 694                    

          9 SORT ORDER BY  Cost: 694  Bytes: 96  Cardinality: 2                

              8 TABLE ACCESS BY INDEX ROWID INV.MTL_TRANSACTION_LOT_NUMBERS Cost: 4  Bytes: 17  Cardinality: 1            

                  7 NESTED LOOPS  Cost: 693  Bytes: 96  Cardinality: 2        

                      5 NESTED LOOPS  Cost: 687  Bytes: 62  Cardinality: 2    

                          2 TABLE ACCESS BY INDEX ROWID INV.MTL_MATERIAL_TRANSACTIONS Cost: 683  Bytes: 26  Cardinality: 2

                              1 INDEX FULL SCAN INV.NFPC_LOCATOR_ID_N1 Cost: 27  Cardinality: 2,579,440

                          4 TABLE ACCESS BY INDEX ROWID INV.MTL_ITEM_LOCATIONS Cost: 3  Bytes: 18  Cardinality: 1

                              3 INDEX RANGE SCAN INV.MTL_ITEM_LOCATIONS_U1 Cost: 2  Cardinality: 1

                      6 INDEX RANGE SCAN INV.MTL_TRANSACTION_LOT_NUMBERS_N1 Cost: 3  Cardinality: 1    

       

      {code}

      Please let me know what other information I need to upload for any help.

      Thanks

      AJ