Oracle重新绑定执行计划,绑定变量对执行计划的影响

sql运行在11g数据库上，做的是绑定变量对执行计划的影响测试，测试过程如下：

SQL> exec :x :=999

PL/SQL procedure successfully completed.

SQL> exec :y :=1000

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where object_id between :x and :y;

COUNT(*)

----------

2

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 2

-------------------------------------

select count(*) from t1 where object_id between :x and :y

Plan hash value: 2351893609

-----------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

3 - SEL$1 / T1@SEL$1

Outline Data

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

END_OUTLINE_DATA

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

*/

Peeked Binds (identified by position):

--------------------------------------

1 - :X (NUMBER): 999

2 - :Y (NUMBER): 1000

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2 - filter(:X<=:Y)

3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]

52 rows selected.

SQL> exec :x :=0

PL/SQL procedure successfully completed.

SQL>  exec :y :=50000

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where object_id between :x and :y;

COUNT(*)

----------

49542

SQL>  select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 1

-------------------------------------

select count(*) from t1 where object_id between :x and :y

Plan hash value: 1410530761

---------------------------------------------------------------------------------

| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |        |       |       |    46 (100)|          |

|   1 |  SORT AGGREGATE        |        |     1 |     5 |            |          |

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|*  2 |   FILTER               |        |       |       |            |          |

|*  3 |    INDEX FAST FULL SCAN| IDX_T1 | 47928 |   234K|    46   (3)| 00:00:01 |

---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

3 - SEL$1 / T1@SEL$1

Outline Data

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

END_OUTLINE_DATA

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

*/

Peeked Binds (identified by position):

--------------------------------------

1 - :X (NUMBER): 0

2 - :Y (NUMBER): 50000

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2 - filter(:X<=:Y)

3 - filter(("OBJECT_ID"<=:Y AND "OBJECT_ID">=:X))

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]

52 rows selected.

通过以上测试看到，当更改了绑定变量的值后，执行同样的sql，执行计划已经改变了。 第一次用的INDEX RANGE SCAN， 第二次使用的INDEX FAST FULL SCAN。

按照我的理解，应该是这样的一个过程： oracle在处理绑定变量的sql的时候，只会在硬解析的时候才会查看下sql中绑定变量的值，然后会根据变量的值来决定sql的执行计划。当这个sql再次被执行时，

即使传入的绑定变量的值已经发生了变化，oracle也会采取sql第一次的缓存在shared pool中的执行计划(硬解析)。

测试的结果却不是这样的呢 ，是为什么呢。