1 Reply Latest reply: Nov 18, 2013 9:44 AM by Neeraj_Shrivastava RSS

    Query

    G.L.S.Kiranmayi

      The below query is running for a very long time post upgrade of database from 10g to 11g.

       

      SELECT default_supplier

        FROM (SELECT pv.vendor_name || '-'

                     || pvsa.vendor_site_code default_supplier

                FROM mrp_item_sourcing_levels_v mislv,

                     mfg_lookups ml1,

                     mfg_lookups ml2,

                     mfg_lookups ml3,

                     mtl_system_items_b msib,

                     po_vendors pv,

                     po_vendor_sites_all pvsa

               WHERE msib.segment1 = :b1

                 AND msib.organization_id = :b2

                 AND mislv.inventory_item_id = msib.inventory_item_id

                 AND mislv.organization_id = msib.organization_id

                 AND ml1.lookup_type = 'MRP_SOURCING_RULE_TYPE'

                 AND ml1.lookup_code = mislv.sourcing_rule_type

                 AND ml2.lookup_type = 'MRP_ASSIGNMENT_TYPE'

                 AND ml2.lookup_code = mislv.assignment_type

                 AND pv.vendor_id = mislv.vendor_id

                 AND pvsa.vendor_site_id = mislv.vendor_site_id

                 AND pvsa.vendor_id = mislv.vendor_id

                 AND ml3.lookup_type = 'MRP_SOURCE_TYPE'

                 AND ml3.lookup_code = mislv.sourcing_level

                 AND mislv.sourcing_level =

                        (SELECT MIN (sourcing_level)

                           FROM mrp_item_sourcing_levels_v

                          WHERE inventory_item_id = msib.inventory_item_id

                            AND organization_id = msib.organization_id)

              UNION

              SELECT ood.organization_code default_supplier

                FROM mrp_item_sourcing_levels_v mislv,

                     mfg_lookups ml1,

                     mfg_lookups ml2,

                     mfg_lookups ml3,

                     mtl_system_items_b msib,

                     org_organization_definitions ood

               WHERE msib.segment1 = :b1

                 AND msib.organization_id = :b2

                 AND mislv.inventory_item_id = msib.inventory_item_id

                 AND mislv.organization_id = msib.organization_id

                 AND ml1.lookup_type = 'MRP_SOURCING_RULE_TYPE'

                 AND ml1.lookup_code = mislv.sourcing_rule_type

                 AND ml2.lookup_type = 'MRP_ASSIGNMENT_TYPE'

                 AND ml2.lookup_code = mislv.assignment_type

                 AND ml3.lookup_type = 'MRP_SOURCE_TYPE'

                 AND ml3.lookup_code = mislv.sourcing_level

                 AND ood.organization_id = mislv.source_organization_id

                 AND mislv.sourcing_level =

                        (SELECT MIN (sourcing_level)

                           FROM mrp_item_sourcing_levels_v

                          WHERE inventory_item_id = msib.inventory_item_id

                            AND organization_id = msib.organization_id))

       

      From the trace files, we see that the issue is with the seeded view, mrp_item_sourcing_levels_v.

       

      Need pointers for fixing the performance.    

        • 1. Re: Query
          Neeraj_Shrivastava

          If you use the standard view mrp_item_sourcing_levels_v in the query then it will definitely run slow.

          You also have UNION which will further bring down the performance as it removes duplicates after the query has fetched all the records.

           

          1. Request the DBA to run Gather Schema Statistics for PO and INV.

          This will not cause any harm and may significantly improve the performance, if the Gather Schema Statistics  has not been run for a long time.

           

          2. Remove the view mrp_item_sourcing_levels_v from your query, see the possibility to directly use the underlying base tables for fetching the sourcing information.

           

          If that is not possible. Create a custom database function which will be fetch you MIN (sourcing_level) from mrp_item_sourcing_levels_v if you pass msib.inventory_item_id and

          msib.organization_id. This way you can avoid the join of this view with the tables in your query.


          3. Replace UNION with UNION ALL.


          4. You can also explore to have a Materialized view for mrp_item_sourcing_levels_v.


          5. If possible break this query into multiple small cursors using a PL\SQL procedure CURSOR and push the data to a custom table or global temp table.

          Later use that custom table data for your report.

           

          Regards,

          Neeraj.