3 Replies Latest reply: Aug 18, 2013 12:56 AM by Nikolay Savvinov RSS

    Query is too slow

    user5690975

      SELECT SUM(A.NO_MONTH_CONSUMPTION),SUM(A.BASE_CONSUMPTION),SUM(A.CURRENT_DOC_AMT),SUM(A.CUR_TAX),SUM(B.CURRENT_DOC_AMT)
      FROM VW_x A,(SELECT CURRENT_DOC_AMT,DOC_NO
      FROM VW_y B
      WHERE NVL(B.VOID_STATUS,0)=0 AND B.TR_TYPE_CODE='SW' AND B.BPREF_NO=:B4 AND B.SERVICE_CODE=:B3 AND B.BIZ_PART_CODE=:B2 AND B.CONS_CODE=:B1 ) B
      WHERE A.BPREF_NO=:B4 AND A.SERVICE_CODE=:B3 AND A.BIZ_PART_CODE=:B2 AND A.CONS_CODE=:B1 AND A.BILL_MONTH >:B5 AND NVL(A.VOID_STATUS,0)=0 AND NVL(A.AVG_IND,0)= 2 AND A.DOC_NO=B.DOC_NO(+)


      the above view "VW_x" has around 40 million records from two tables and avg_ind column has only 0 and 2 values. I created a functional index on both table something like create index on x1 nvl(avg,0)

       

      TRACE OUT PUT

       

      STATISTICS

      15  recursive calls

      0  db block gets

        18  consistent gets

      4  physical reads

      0  redo size

      357 bytes sent via SQL*Net to client

      252 bytes received via SQL*Net from client

      2 SQL*Net roundtrips to/from client

      1 sorts (memory)

      0 sorts (disk)

      1 rows processed

       

       

       

      but still the query is slow...please suggest the best practise to make it fast

       

       

      thanks

        • 1. Re: Query is too slow
          saratpvv

          See you missed lot of thing here..

          1) What's your database version..?

          2) You feel this information is enough to give recommandations.?
          3) When ever you post tunning queries - Need to post entire explain plan ..
          So i suggest Run the query - then after run the below

          select * from table (dbms_xplan.display_cursor (format=>'ALLSTATS LAST'));  and post the result

          • 3. Re: Query is too slow
            Nikolay Savvinov

            Hi,

             

            "best practices" don't make queries automatically go fast. These are just very general guidelines which help you minimize chances of getting into trouble. But once you got into trouble, "best practices" aren't very helpful any longer.

             

            Consider this analogy: washing your hands often will help you minimize chances of getting an infection. But once you got infection, no matter how many times you wash your hands, you won't get better. The right thing to do is to perform diagnostic and to identify which part of the system is not functioning properly -- this is true both for medicine and engineering.

             

            Oracle database has a rich set of tools for diagnosing performance problems, including plan rowsource statistics, extended SQL trace, real-time SQL monitor, active session history, and many other. You need to learn to use them at least on some basic level, because without it you won't be able to get any external help: we can't diagnose your system even if we wanted to, but if you do diagnostic correctly, we would be able to help (and most like, you won't even be needing our help, because you'll see the problem yourself).

             

            Best regards,

            Nikolay

             

            PS And the autotrace dump you posted here -- I don't think that it's for the original query because nothing here indicates long execution time

            PPS When posting tuning requests try and avoid using vague expressions like "too slow". Be specific: tell us how long the query takes to complete, and why you think it should complete faster.