基础知识之绑定变量窥视----BIND PEEKING

SQL> set wrap off
SQL> select count(*) from test where object_id<10;

执行计划
----------------------------------------------------------
Plan hash value: 2029616900

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

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

|   0 | SELECT STATEMENT  |               |     1 |     5 |     2   (0)| 00:00:0

|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |
|*  2 |   INDEX RANGE SCAN| IDX_OBJECT_ID |    21 |   105 |     2   (0)| 00:00:0

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


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

   2 - access("OBJECT_ID"<10)

SQL> select count(*) from test where object_id<100000;

执行计划
----------------------------------------------------------
Plan hash value: 1131838604

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

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

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

|   0 | SELECT STATEMENT      |               |     1 |     5 |   136   (3)| 00:

|   1 |  SORT AGGREGATE       |               |     1 |     5 |            |
|*  2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID |   218K|  1068K|   136   (3)| 00:

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


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

   2 - filter("OBJECT_ID"<100000)

非绑定变量执行有不同的执行计划

SQL> exec :x:=10;

PL/SQL 过程已成功完成。

SQL> select count(*) from test where object_id<:x;

执行计划
----------------------------------------------------------
Plan hash value: 2029616900

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

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

|   0 | SELECT STATEMENT  |               |     1 |     5 |     6   (0)| 00:00:0

|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |
|*  2 |   INDEX RANGE SCAN| IDX_OBJECT_ID | 10937 | 54685 |     6   (0)| 00:00:0

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


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

   2 - access("OBJECT_ID"<TO_NUMBER(:X))

SQL> exec :x:=100000;

PL/SQL 过程已成功完成。

SQL> select count(*) from test where object_id<:x;

执行计划
----------------------------------------------------------
Plan hash value: 2029616900

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

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

|   0 | SELECT STATEMENT  |               |     1 |     5 |     6   (0)| 00:00:0

|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |
|*  2 |   INDEX RANGE SCAN| IDX_OBJECT_ID | 10937 | 54685 |     6   (0)| 00:00:0

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


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

   2 - access("OBJECT_ID"<TO_NUMBER(:X))

--第一次执行使用绑定变量后由于bind peeking 选择了同样的执行计划

SQL> alter session set sql_trace=true;

会话已更改。

SQL> set autotrace off
--具体信息（11g）通过如下目录查看trace文件
SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------

d:\app\gyh\diag\rdbms\orcl\orcl\trace\orcl_ora_9692.trc

SQL>