4 Replies Latest reply: Aug 14, 2014 5:30 AM by Sven W. RSS

    Performance Tuning SQL Query


      I have to tune this query, and I think the condition "AND ((co_cd_srt IN('12', '13', '14') AND... causing it to delay for over 14 hours


      I am using 11g version and the number of records in this table is close to 90 million. can you help me to start up with this, I am new to oracle tuning.



      SELECT   co_cd_srt,





                      Sum(tot_prem) AS tot_prem

      FROM   gkpr.ahm_prem_fact

      WHERE   wvr_prem = 'N'

        AND t_dt<= To_date('2012-03-31', 'YYYY-MM-DD')

        AND ((co_cd_srt IN('12', '13', '14') AND t_dt>= To_date('2012-11-01', 'YYYY-MM-DD')) OR (co_cd_srt IN( '1', '8', '7', '15', '16')))

      GROUP  BY co_cd_srt,








      Message was edited by: NeilCSE corrected the typo.

        • 1. Re: Performance Tuning SQL Query

          Hello Neil,


          here is your query slightly modified;  a few remarks:

          - usually we write the condition on ROWNUM at the end, to "show" that this comes at the very end (but this doesn't change anything for Oracle)

          - double check that co_cd_srt is a STRING and not a NUMBER (otherwise the conditions should not use '1', '12', ... but 1, 7, 8, ...)

          - you ask twice the field p_out, maybe it is on purpose for your output, but anyway you don't have to write it twice in the GROUP BY

          - good practice: prefix the columns names with an alias.

          - it may very well be that the best way to execute the query is to run a full scan on the table (90M rows is not that much), but 14 hours seems very very long


          SELECT a.co_cd_srt

               , a.s_num

               , a.p_out

               , a.t_dt

               , SUM( a.tot_prem ) AS tot_prem

            FROM gkpr.ahm_prem_fact a

            WHERE a.wvr_prem = 'N'

              AND a.t_dt <= TO_DATE( '2012-03-31', 'YYYY-MM-DD' )

              AND a.co_cd_srt IN ( '1', '12', '13', '14', '15', '16', '7', '8' )

              AND (    a.t_dt >= TO_DATE( '2012-11-01', 'YYYY-MM-DD' )

                    OR a.co_cd_srt IN ( '1', '8', '7', '15', '16' )


              AND ROWNUM <= 100

            GROUP BY a.co_cd_srt

                   , a.s_num

                   , a.p_out

                   , a.t_dt



          Best regards,


          Bruno Vroman.

          • 2. Re: Performance Tuning SQL Query

            Thanks Bruno,


            My mistake, I forgot to remove the ROWNUM there. I included that to see some result.


            co_cd_srt is v2(3) datatype

            it is a typo one of the p_out should be np_out, I've corrected it, thanks for pointing.



            • 3. Re: Performance Tuning SQL Query



              thank you for the feedback.


              About VARCHAR2(3), "OK" for the way the conditions are written, but at first sight a poor design... It is a pity to store numbers as chars (but this is another discussion)


              One more important remark about your query:

                AND a.t_dt <= TO_DATE( '2012-03-31', 'YYYY-MM-DD' )

              this is OK if you know for sure that t_dt is a date with "hour:min:sec" = "00:00:00", but if it is possible that t_dt is not defined at 00:00:00, the condition is maybe wrong (as I assume that if you want to accept 31-MAR-2012 00:00:00, you want also to accept for example 31-MAR-2012 16:34:55)... With DATES, it is wiser to write thing like a.t_dt < DATE '2012-03-31' + 1 to clearly include the complete day 31-MAR-2013.  (of course you might as well write: a.t_dt < DATE '2013-04-01')

              ((  FYI: a constant date (with hour:min:sec = 00:00:00) can be written as "ANSI date literal": keyword DATE and the date in single quotes in the format YYYY-MM-DD. I prefer this to the call to TO_DATE( a_string, a_format )  ))


              Best regards,



              • 4. Re: Performance Tuning SQL Query
                Sven W.

                HOW TO: Post a SQL statement tuning request - template posting


                After that: This looks like a DWH/OLAP Query (fact-Table)

                In such an environment some of the typical DWH concepts could also help. But you need to know what you are doing, before applying this to a real production table.


                a) Partition the table. Probably by MONTH

                b) Consider an local index on co_cd_srt. Maybe even an bitmap index. Or use a sub partiton on this column.