9 Replies Latest reply: Jan 22, 2013 10:02 AM by Dom Brooks RSS

    High runtime of below query

    950732
      Below is the query which is taking 33239.33 seconds to complete.


      SELECT /*+ INDEX (H SYS_C00214809)*/
      SUM (quantity_received) quantity_received
      FROM (SELECT /*+ INDEX (XXFT_RCV_HEADERS XXFT_RCV_HEADERS_N20)*/
      *
      FROM apps.xxft_rcv_headers
      WHERE TRUNC (ft_receipt_date) <=
      NVL (TRUNC (:b1), TRUNC (ft_receipt_date))) h,
      apps.xxft_rcv_lines l
      WHERE h.ft_shipment_header_id = l.ft_shipment_header_id
      AND l.dispatch_line_id = NVL (:b2, l.dispatch_line_id)

      call count cpu elapsed disk query current rows
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      Parse 1 0.00 0.00 0 0 0 0
      Execute 99797 4.45 4.47 0 0 0 0
      Fetch 99797 32956.10 33234.86 4237 126566522 0 99797
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      total 199595 32960.55 33239.33 4237 126566522 0 99797



      Rows (1st) Rows (avg) Rows (max) Row Source Operation
      ---------- ---------- ---------- ---------------------------------------------------
      1 1 1 SORT AGGREGATE (cr=1272 pr=408 pw=0 time=932447 us)
      1 1 1 CONCATENATION (cr=1272 pr=408 pw=0 time=932405 us)
      0 0 0 FILTER (cr=0 pr=0 pw=0 time=4 us)
      0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=2981 size=131580 card=3060)
      0 0 0 TABLE ACCESS BY INDEX ROWID XXFT_RCV_HEADERS_ALL (cr=0 pr=0 pw=0 time=0 us cost=1253 size=38150 card=1526)
      0 0 0 INDEX FULL SCAN XXFT_RCV_HEADERS_N20 (cr=0 pr=0 pw=0 time=0 us cost=413 size=0 card=91530)(object id 2092443)
      0 0 0 TABLE ACCESS FULL XXFT_RCV_LINES_ALL (cr=0 pr=0 pw=0 time=0 us cost=1726 size=1586142 card=88119)
      1 1 1 FILTER (cr=1272 pr=408 pw=0 time=932283 us)
      1 1 1 NESTED LOOPS (cr=1272 pr=408 pw=0 time=932279 us cost=1257 size=43 card=1)
      1 1 1 TABLE ACCESS BY INDEX ROWID XXFT_RCV_LINES_ALL (cr=4 pr=0 pw=0 time=94 us cost=4 size=18 card=1)
      1 1 1 INDEX RANGE SCAN XXFT_RCV_LINES_N3 (cr=3 pr=0 pw=0 time=41 us cost=3 size=0 card=1)(object id 1014432)
      1 1 1 TABLE ACCESS BY INDEX ROWID XXFT_RCV_HEADERS_ALL (cr=1268 pr=408 pw=0 time=932168 us cost=1253 size=25 card=1)
      90828 90828 90828 INDEX FULL SCAN XXFT_RCV_HEADERS_N20 (cr=467 pr=408 pw=0 time=983875 us cost=413 size=0 card=91530)(object id 2092443)







      what changes can we do in order to make query effective from performance point of view.

      Edited by: 947729 on Jan 22, 2013 6:01 AM
        • 1. Re: High runtime of below query
          Dom Brooks
          See template tuning thread for information required (properly formatted please):
          [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuining request

          Tips - avoid functions on columns (e.g. TRUNC on date) and be wary of [url http://jonathanlewis.wordpress.com/category/oracle/execution-plans/conditional-sql/] NVL and concatenation plans

          You have four possible combinations of the two parameters ranging from a join of all headers and all lines, to all headers before a certain date and all lines with a specific dispatch line id.

          For example, obeying the hints and using these indexes if :b1 and :b2 are both null is likely to be inefficient.

          If possible, in this example, one approach that would be worth investigating is to simply have four individual statements using an IF statement if using PLSQL or a UNION ALL for each combination of parameter using SQL.

          Edited by: Dom Brooks on Jan 22, 2013 11:32 AM
          • 2. Re: High runtime of below query
            JohnWatson
            Your code is unreadable, if you want people to look at it, better enclose your copy/paste with
             tags.                                                                                                                                                                                                                        
            • 3. Re: High runtime of below query
              Nikolay Savvinov
              Hi,

              1) the plan in the trace file shows 1s execution time
              2) the reason it's taking so long is that the query is executed nearly 10k times
              3) the plan can probably be made much more efficient if you avoid the index fast full scan of XXFT_RCV_HEADERS_N20 (e.g. by creating a new, more efficient index) -- but I cannot tell more without seeing DDL for the views, tables and indexes involved in the query, as well as an explain plan with predicates section

              Best regards,
              Nikolay
              • 4. Re: High runtime of below query
                rahulras
                Your query is
                SELECT /*+ INDEX (H SYS_C00214809)*/
                       SUM (quantity_received) quantity_received
                FROM  (SELECT /*+ INDEX (XXFT_RCV_HEADERS XXFT_RCV_HEADERS_N20)*/ *
                       FROM   apps.xxft_rcv_headers
                       WHERE  TRUNC (ft_receipt_date) <= NVL (TRUNC (:b1), TRUNC (ft_receipt_date))
                       ) h,
                       apps.xxft_rcv_lines l
                WHERE  h.ft_shipment_header_id = l.ft_shipment_header_id
                AND    l.dispatch_line_id = NVL (:b2, l.dispatch_line_id)
                The plan is unreadable. But the hints in there are not making sense to me e.g. (1) hint in the first line which is on inline subquery (2) there is a "INDEX FULL SCAN XXFT_RCV_HEADERS_N20", which is a index promoted by the hint.

                My first feeling is, remove the hints and run the query (and check the plan). Also, the very very general comment would be, check the stats if it is good.
                • 5. Re: High runtime of below query
                  Osama_Mustafa
                  Please edit your thread and make your post is readable to get right answer, Use
                                                                                                                                                                                              
                  • 6. Re: High runtime of below query
                    950732
                    is there way to attach trace file on this thread?
                    • 7. Re: High runtime of below query
                      Nikolay Savvinov
                      Hi,

                      post the tkprof'ed version as text. The forum doesn't allow attachments.

                      Best regards,
                      Nikolay
                      • 8. Re: High runtime of below query
                        Jonathan Lewis
                        947729 wrote:
                        Below is the query which is taking 33239.33 seconds to complete.
                        SELECT /*+ INDEX (H SYS_C00214809)*/
                        SUM (quantity_received) quantity_received
                        FROM (SELECT /*+ INDEX (XXFT_RCV_HEADERS XXFT_RCV_HEADERS_N20)*/
                        *
                        FROM apps.xxft_rcv_headers
                        WHERE TRUNC (ft_receipt_date) <=
                        NVL (TRUNC (:b1), TRUNC (ft_receipt_date))) h,
                        apps.xxft_rcv_lines l
                        WHERE h.ft_shipment_header_id = l.ft_shipment_header_id
                        AND l.dispatch_line_id = NVL (:b2, l.dispatch_line_id)
                        
                        call     count       cpu    elapsed       disk      query    current        rows
                        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                        Parse        1      0.00       0.00          0          0          0           0
                        Execute  99797      4.45       4.47          0          0          0           0
                        Fetch    99797  32956.10   33234.86       4237  126566522          0       99797
                        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                        total   199595  32960.55   33239.33       4237  126566522          0       99797
                        
                        
                        
                        Rows (1st) Rows (avg) Rows (max)  Row Source Operation
                        ---------- ---------- ----------  ---------------------------------------------------
                        1          1          1  SORT AGGREGATE (cr=1272 pr=408 pw=0 time=932447 us)
                        1          1          1   CONCATENATION  (cr=1272 pr=408 pw=0 time=932405 us)
                        0          0          0    FILTER  (cr=0 pr=0 pw=0 time=4 us)
                        0          0          0     HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=2981 size=131580 card=3060)
                        0          0          0      TABLE ACCESS BY INDEX ROWID XXFT_RCV_HEADERS_ALL (cr=0 pr=0 pw=0 time=0 us cost=1253 size=38150 card=1526)
                        0          0          0       INDEX FULL SCAN XXFT_RCV_HEADERS_N20 (cr=0 pr=0 pw=0 time=0 us cost=413 size=0 card=91530)(object id 2092443)
                        0          0          0      TABLE ACCESS FULL XXFT_RCV_LINES_ALL (cr=0 pr=0 pw=0 time=0 us cost=1726 size=1586142 card=88119)
                        1          1          1    FILTER  (cr=1272 pr=408 pw=0 time=932283 us)
                        1          1          1     NESTED LOOPS  (cr=1272 pr=408 pw=0 time=932279 us cost=1257 size=43 card=1)
                        1          1          1      TABLE ACCESS BY INDEX ROWID XXFT_RCV_LINES_ALL (cr=4 pr=0 pw=0 time=94 us cost=4 size=18 card=1)
                        1          1          1       INDEX RANGE SCAN XXFT_RCV_LINES_N3 (cr=3 pr=0 pw=0 time=41 us cost=3 size=0 card=1)(object id 1014432)
                        1          1          1      TABLE ACCESS BY INDEX ROWID XXFT_RCV_HEADERS_ALL (cr=1268 pr=408 pw=0 time=932168 us cost=1253 size=25 card=1)
                        90828      90828      90828       INDEX FULL SCAN XXFT_RCV_HEADERS_N20 (cr=467 pr=408 pw=0 time=983875 us cost=413 size=0 card=91530)(object id 2092443)
                        As two people have pointed out, most of the time you spend running this query is in the index full scan of xxft_rcv_headers_n20 - and there is a hint which is probably forcing Oracle to use this index in this way, so one simple test would be to eliminate the hints from your code to see what path appears. However, as Dom Brooks has pointed out, the colum = nvl(bind,column) limits what Oracle can do to optimise a query properly, so perhaps the hints were there to modify a plan that the optimizer used to produce, and which it may still produce again some time in the future.

                        In passing, unless colums dispatch_line_id and ft_receipt_dt are declared not null (or unless your code guarantees that they never hold nulls - in which case they should be declared not null) then this query can silently return the wrong results.

                        Regards
                        Jonathan Lewis
                        • 9. Re: High runtime of below query
                          Dom Brooks
                          If possible, in this example, one approach that would be worth investigating is to simply have four individual statements using an IF statement if using PLSQL or a UNION ALL for each combination of parameter using SQL.
                          E.g.
                           SELECT SUM (quantity_received)
                           FROM   apps.xxft_rcv_headers h
                           ,      apps.xxft_rcv_lines l
                           WHERE  :b1 IS NOT NULL
                           AND    :b2 IS NOT NULL
                           AND    h.ft_receipt_date       <= :b1
                           AND    h.ft_shipment_header_id  = l.ft_shipment_header_id
                           AND    l.dispatch_line_id       = :b2
                           UNION ALL
                           SELECT SUM (quantity_received)
                           FROM   apps.xxft_rcv_headers h
                           ,      apps.xxft_rcv_lines l
                           WHERE  :b1 IS NOT NULL
                           AND    :b2 IS NULL
                           AND    h.ft_receipt_date       <= :b1
                           AND    h.ft_shipment_header_id  = l.ft_shipment_header_id
                           UNION ALL
                           SELECT SUM (quantity_received)
                           FROM   apps.xxft_rcv_headers h
                           ,      apps.xxft_rcv_lines l
                           WHERE  :b1 IS NULL
                           AND    :b2 IS NOT NULL
                           AND    h.ft_shipment_header_id  = l.ft_shipment_header_id
                           AND    l.dispatch_line_id       = :b2
                           UNION ALL
                           SELECT SUM (quantity_received)
                           FROM   apps.xxft_rcv_headers h
                           ,      apps.xxft_rcv_lines l
                           WHERE  :b1 IS NULL
                           AND    :b2 IS NULL
                           AND    h.ft_shipment_header_id  = l.ft_shipment_header_id;
                          I wouldn't encourage extending this approach to any more parameter - too cumbersome and too manu unshared codepaths.
                          Better to generate the query dynamically and specifically depending on the inputs.