Pythian Group

In my previous post, I described the most common cause for unstable plans due to bind peeking — histograms. It is now time to move forward and take a look at another case, namely range-based predicates. Strictly speaking, the cases I’m going to describe can appear without range-based predicates as well, you just need to remember that a range-based operation doesn’t necessarily imply a range-based predicate.

How Can Range-Based Predicates Cause an Unstable Plan?

Quite easy, take the following example:

SQL> create table t as
  2  	select level n, rpad('x', 200, 'x') v
  3  		from dual
  4  		connect by level <= 100000;

Table created

SQL> create index i_t_n on t (n);

Index created

SQL> exec dbms_stats.gather_table_stats(user, 't');

PL/SQL procedure successfully completed

Now, I’ll query the table using two different conditions:

SQL> set autot traceonly explain
SQL> select * from t where n <= 100;

Execution Plan
-----------------------------------------------------
Plan hash value: 2912310446

----------------------------------------------------
| Id  | Operation                   | Name  | Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT            |       |   95 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   95 |
|*  2 |   INDEX RANGE SCAN          | I_T_N |   95 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N"<=100)

SQL> select * from t where n <= 25000;

Execution Plan
------------------------------------------
Plan hash value: 1601196873

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      | 24998 |
|*  1 |  TABLE ACCESS FULL| T    | 24998 |
------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"<=25000)

(Note that I’ve trimmed the output for the sake of readability.)

The first query was executed using INDEX RANGE SCAN, the second one using TABLE ACCESS FULL. This makes perfect sense, since the first query is going to return only a small fraction of data in a table, while the second one is going to fetch substantially more data. As you probably already guessed, if you substitute a literal value for a bind variable, your plan will depend on what value was passed during a hard parse: Read the rest of this entry . . .