4 Replies Latest reply: Nov 14, 2012 12:36 AM by Nikolay Savvinov RSS

    sql statment is VERY slow

    user1089169
      Hi all
      I am facing a very big problem in my database.
      when I execute the below select statement, it takes to much time to finish.
      The value of cust_no is equal to 10.

      SELECT fname, lname, pcode
      FROM cust
      WHERE id = :cust_no;

      And I execute the same statement as shown below,it does NOT take too much time

      SELECT fname, lname, pcode
      FROM cust
      WHERE id = 10;
      the differnce between the above statements are WHERE id = :cust_no; and
      WHERE id = 10;


      Do you think that there is somethink change in the oracle optimizer behavior or in sql parsing?
      recently I've done an analyze for all table accept sys and system users.
      please could anyone help me?
      by.

      Edited by: user1089169 on Nov 13, 2012 11:45 AM
        • 1. Re: sql statment is VERY slow
          sb92075
          user1089169 wrote:
          Hi all
          I am facing a very big problem in my database.
          when I execute the below select statement, it takes to much time to finish.
          The value of cust_no is equal to 10.

          SELECT fname, lname, pcode
          FROM cust
          WHERE id = :cust_no;

          And I execute the same statement as shown below,it does NOT take too much time

          SELECT fname, lname, pcode
          FROM cust
          WHERE id = 10;
          the differnce between the above statements are WHERE id = :cust_no; and
          WHERE id = 10;

          please could anyone help me?
          by.
          post EXPLAIN PLAN for both SQL
          • 2. Re: sql statment is VERY slow
            rcc50886
            Hi all
            I am facing a very big problem in my database.
            when I execute the below select statement, it takes to much time to finish.
            The value of cust_no is equal to 10. 
            
            SELECT fname, lname, pcode 
            FROM cust 
            WHERE id = :cust_no;
            
            And I execute the same statement as shown below,it does NOT take too much time
            
            SELECT fname, lname, pcode 
            FROM cust 
            WHERE id = 10;
            the differnce between the above statements are WHERE id = :cust_no; and 
            WHERE id = 10;
            
            please could anyone help me?
            by.
            It may possible that using bind varaibles may cause different execution plan (read about bindvariable peeking).

            post execution plans with and without bindvariables

            -Thanks
            • 3. Re: sql statment is VERY slow
              Osama_Mustafa
              Post Explain Plan Like sb said .
              • 4. Re: sql statment is VERY slow
                Nikolay Savvinov
                Hi,

                while we cannot be 100% without seeing the execution plan, the most likely explanation for your problem is that the id column is skewed, i.e. some of the values in it are far more popular than others. Because of that, there is no one optimal plan: for popular values, a full table scan would be preferred (because it's good for handling large volumes of data), for less popular values, index access becomes a better choice.

                When you are using a literal, the optimizer has enough information to come up with a good plan. When you're using a bind variable, the optimizer has to guess (in one way or another -- depending on your settings, it may rely on built-in defaults or use so-called bind peeking), so there is a good chance that it would pick a bad plan.

                Best regards,
                Nikolay