6 Replies Latest reply: Apr 19, 2014 3:06 AM by BrendanP RSS

    Help needed in Query Tuning

    894333

      Hi All,


      As reported by the DBA the below query is taking a lot of time.

      Most of the time in completing this query has been spent on fetching (Index Blocks indicated by high number of the waits on DB File Sequential Read).

      If anyone can help me in tuning this query it would a great help.

       

       

      SELECT poh.po_header_id, msi.segment1 item, msi.inventory_item_id,
             msi.organization_id, pod.destination_subinventory LOCATION,
             NULL orders_lead_time, poh.segment1 po_mfg_number,
             pl.line_num line_for_po, v.vendor_name vendor,
             (SELECT TRUNC (MIN (action_date))
                FROM po_action_history_v
               WHERE object_id = poh.po_header_id
                 AND UPPER (action_code) = 'APPROVE') original_order_create_date,
             NVL (TRUNC (pll.need_by_date),
                  TRUNC (pll.promised_date)
                 ) expected_order_receive_date,
             (SELECT MAX (mmt.transaction_date)
                FROM mtl_material_transactions mmt,
                     rcv_transactions rt
               WHERE mmt.transaction_source_id = poh.po_header_id
                 AND mmt.rcv_transaction_id = rt.transaction_id
                 AND rt.po_line_id = pl.po_line_id
                 AND rt.vendor_id = v.vendor_id
                 AND rt.po_line_location_id = pll.line_location_id
                 AND mmt.transaction_type_id = 18) actual_order_receive_date,
             ABS (pll.quantity) original_request_quantity,
             ABS (pll.quantity_received) post_inspection_quantity,
             pl.unit_price unit_cost_on_order, pll.shipment_num splits,
             DECODE (pll.quantity, pll.quantity_received, '03', '00') status,
             NULL tracking_number, NULL reserved, NULL order_status,
             NULL supply_status, NULL received_at_dock, NULL invoice_number,
             NULL supply_reference_number, NULL component_item_no,
             'X' end_of_record
        FROM po_headers_all poh,
             po_lines_all pl,
             po_line_locations_all pll,
             mtl_system_items_b msi,
             mtl_parameters mp,
             po_distributions_all pod,
             mtl_secondary_inventories sub,
             po_vendors v
      WHERE TRUNC (poh.approved_date) BETWEEN :b2 AND :b1
         AND poh.approved_flag = 'Y'
         AND NVL (poh.cancel_flag, 'N') = 'N'
         AND poh.po_header_id = pl.po_header_id
         AND NVL (pl.cancel_flag, 'N') = 'N'
         AND NVL (pll.cancel_flag, 'N') = 'N'
         AND UPPER (poh.type_lookup_code) = 'STANDARD'
         AND UPPER (pod.destination_type_code) = 'INVENTORY'
         AND pll.po_line_id = pl.po_line_id
         AND msi.inventory_item_id = pl.item_id
         AND msi.end_date_active IS NULL
         AND UPPER (msi.inventory_item_status_code) IN ('ACTIVE', 'USE UP')
         AND msi.inventory_item_flag = 'Y'
         AND msi.mtl_transactions_enabled_flag = 'Y'
         AND msi.organization_id = pll.ship_to_organization_id
         AND (   (sub.attribute15 = 'GAINS' AND mp.attribute15 IS NULL)
              OR (sub.attribute15 IS NULL AND mp.attribute15 = 'GAINS')
             )
         AND mp.organization_id = msi.organization_id
         AND mp.organization_id = pll.ship_to_organization_id
         AND pod.po_header_id = poh.po_header_id
         AND pod.po_line_id = pl.po_line_id
         AND pod.line_location_id = pll.line_location_id
         AND v.vendor_id = poh.vendor_id
         AND pod.destination_organization_id = sub.organization_id
         AND EXISTS (
                SELECT 1
                  FROM mtl_material_transactions mmt, rcv_transactions rt
                 WHERE mmt.organization_id = msi.organization_id
                   AND mmt.inventory_item_id = msi.inventory_item_id
                   AND mmt.organization_id = mp.organization_id
                   AND mmt.subinventory_code = sub.secondary_inventory_name
                   AND mmt.transaction_source_id = poh.po_header_id
                   AND mmt.rcv_transaction_id = rt.transaction_id
                   AND rt.po_line_id = pl.po_line_id
                   AND rt.vendor_id = v.vendor_id
                   AND rt.po_line_location_id = pll.line_location_id
                   AND rt.po_header_id = poh.po_header_id
                   AND rt.po_distribution_id = pod.po_distribution_id
                   AND mmt.transaction_type_id = 18)

       

       

      CallCountcpuelapseddiskquerycurrentrows
      Parse10.000.000000
      Execute11.231.240000
      Fetch3691405.5718285.58718753571647346036834
      Total3711406.8018286.83718753571647346036834

       

       

       

      Elapsed times include waiting on following events:

       

      Header 1Header 2Header 3Header 4
      Event waited onTimes WaitedMax WaitTotal Waited
      db sequential read7186152.2316878.79

       

      Regards,

      Shruti

        • 1. Re: Help needed in Query Tuning
          Moazzam

          Please share query plan

          • 2. Re: Help needed in Query Tuning
            GregV

            Hi,

             

            One problem I see is many functions applied to columns, so this prevents "classic" indexes from potentially being used.

            For example, the first condition:

            TRUNC (poh.approved_date) BETWEEN :b2 AND :b1

             

            can probably be rewritten as:

            poh.approved_date >= trunc(:b2) AND poh.approved_date < trunc(:b1 + 1)  

             

            See also if you really need the UPPER functions on the following conditions:

             

            AND UPPER (poh.type_lookup_code) = 'STANDARD'

            AND UPPER (pod.destination_type_code) = 'INVENTORY'

            AND UPPER (msi.inventory_item_status_code) IN ('ACTIVE', 'USE UP')

             

            It's likely the literals are always in capitals.  

            • 3. Re: Help needed in Query Tuning
              894333

              Hi GregV,

               

              Thanks for your inputs.

              I have changed the query to remove the UPPER clause and also changed the date parameters as suggested.

              But still, the query is too slow.

               

              Can anyone help me as this is very critical and I have been on it since long.

               

              Regards,

              Shruti

              • 4. Re: Help needed in Query Tuning
                jihuyao

                The execution plan will help to see where the major cost comes from.  But it does not hurt to make blank assumptions,

                 

                1.  the exists in the where clause is nightmare particularly when both mtl_material_transactions mmt, rcv_transactions rt tables are huge.

                2.  the select from mtl_material_transactions mmt, rcv_transactions rt tables in the main select list also abuses index usage in nested loop join.

                 

                which can be easily confirmed by commenting out both parts of 1 and 2 in the original query.

                 

                The harder part is how to rewrite 1 and 2 which needs deeper understanding of the data model.  Neither propagated look-up nor pre-join of two huge tables are pleasant here.  If either one is small enough it could be helpful to have it being directly joined in the main from clause as filter and to leave the other one for look-up with exists in the where clause.  If both are luckily small enough it may be better to start with joining the two tables first and then join other tables in order.

                 

                Again just blank analysis.

                • 5. Re: Help needed in Query Tuning
                  rp0428
                  Can anyone help me as this is very critical and I have been on it since long.

                  It's been 9 days and you STILL haven't posted the execution plan or any of the other information needed.

                   

                  Read the FAQ on how to make a tuning request and the info you need to provide:

                  1. the DDL for all tables and indexes

                  2. the row counts of the tables

                  3. row counts for the query predicates and combinations

                  4. info on table/index statistics: are they current, what command was used to collect them?

                  5. the actual execution plan

                  As reported by the DBA the below query is taking a lot of time.

                  So? What does that mean 'taking a lot of time'. Taking a lot of time compared to what?

                   

                  Wouldn't you expect a query that returns 800 billion rows to take a lot of time?

                   

                  Performance is all about comparison. 'Take a lot of time' has no meaning unless you compare the time taken to something else; such as the time it took yesterday or last week.

                  • 6. Re: Help needed in Query Tuning
                    BrendanP

                    Agree with others that more information, including execution plan would be helpful.

                     

                    However, I notice that you are doing two scalar subqueries, both performing aggregation. One of the first things I would try would be to comment those out to see if they are the source of the problem. In a straightforward join query executed only once, returning 37K rows, even with exists subqueries and possible index suppression, I would expect Oracle to find a plan that would do better than the times indicated. That leads me to suspect a hidden row-by-row effect.