4 Replies Latest reply: May 28, 2012 1:38 AM by Nikolay Savvinov RSS

    How to handle Bind peeking Issue.

      Hi I am using version of oracle.

      I got one of the sqls coming at the top most 'Elapsed time section' of the report consuming approx ~10 sec per execution and ~30000 executions with in 6 hrs. And they use bind variables too. So when i execute the sqls in my prod DB, with actual values, i found the results set with in seconds.
      So i then go to the v$sql_plan view to get the actual path of execution, and found a different set of indexes(let I2) being used for accessing
      the table which must be causing the delay.

      My sql is some what similar as below:
      SELECT COUNT (1)
      FROM a , b
      WHERE a.c1 = b.c2
      AND b.c3 = :1
      AND b.c4 = :2
      AND a.createdate BETWEEN :3 AND :4
      AND a.c2 = :5;

      I then found that the usage of index by optimizer is changed depending up on the range of values being fetched by table 'A' using clause ('a.createdate BETWEEN :3 AND :4 '), which is a bind variable. Now i want to use index I1((primary key index on c2 of table 'A') in both the scenario,(what ever may be the bind valaue of bind variable) , as i have tested and got better elapsed time using Index I1 for both the scenario.
      Note that index I2 is on (createdate,c2).

      So my question is what is the best option to go for? below are the option i can think of.
      1. using index I1 hint.
      2. purging the sql from the shared pool and let the sql hardparsed again and use index I1 for the most popular cases.

      Please suggest.