2 Replies Latest reply: Jul 18, 2014 2:40 AM by _Karthick_ RSS

    Basic approach to query tuning.

    Sid_ Z.

      Hi All,

      I have a query which is taking 30 - 40 min. of time to run.  The first thing is that it is based on the view.

       

      Could you please tell me should I have to make changes in the view to make it run faster

      Or can i use any hints for view to fast processing.

       

      Below is the query

       

      SELECT AS_ON_DATE, MONTH_NAME, BRANCH_CODE, PRODUCT,

         Categry, CHANNEL_SEGMENT, NUMBER_OF_TRANSACTION, AMOUNT,

         COMMISSION, COMMISSION_PLAN, VARIANCE_1, EXCHANGE_INCOME,

         EX_INCOME, EX_INCOME_PLAN, VARINCE

      FROM

      VW_FINCON_CONSOLIDATED

      where AS_ON_DATE < = '13-Jun-2012'  order by 1 desc;

       

      Please tell me the approach how should I dig into it? Also tell if there are any hints on view which make it some relief to this query.

       

      Regards,

       

      Sid

        • 1. Re: Basic approach to query tuning.
          KayK

          Hi Sid,

          how many rows has your table ? And how many rows has your result set ?

          If this is only a small part of your table then an index on AS_ON_DATE may be helpfull. This index will avoid additional sorts.

           

          The tablename Vxxx can lead to a view, so you have to check the view-definition.

          And finally if column AS_ON_DATE is a date you may convert the '13-Jun-2012' to a date by yourself not via implicit conversion.

           

          regards
          Kay

          • 2. Re: Basic approach to query tuning.
            _Karthick_

            Everyone want to have a single hint that could solve all there database related performance issues. But the thing is there is no such hint. Performance optimization is not some random shot you take, it a constructive step by step approach. You Need to have all possible information (Business and Technical) on hand.

             

            Oracle provides various tools to identify the performance bottle neck. When your query takes too long ... thread talks about some of the available tools. You can have a look into it. Also when you are stuck at some point in your performance tuning process and want to get help from public forum, you cant just throw in some SQL statement and say fix it. You need to provide us with details. The details that are necessary is listed in HOW TO: Post a SQL statement tuning request - template posting thread. Please go through it.