通常在OLTP系统中，我们尽可能的使用绑定变量,因为可以减少硬解析.那么有个问题,使用绑定变量就一定好么?
我们来看一个试验:

SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
DESCRI VARCHAR2(20)

SQL> select id,count(1) from t group by id;

ID COUNT(1)
---------- ----------
1 65536
99 1

可以看到,表t中id的分布严重不平衡.
SQL> create index t_inx on t (id);

索引已创建。

SQL> set autotrace traceonly
SQL> select * from t where id=1;

已选择65536行。

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

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 62797 | 1533K| 58 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 62797 | 1533K| 58 (6)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("ID"=1)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
4614 consistent gets
5 physical reads
0 redo size
883013 bytes sent via SQL*Net to client
48459 bytes received via SQL*Net from client
4371 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
65536 rows processed

SQL>

SQL> select * from t where id=99;

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

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_INX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - access("ID"=99)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
71 consistent gets
1 physical reads
0 redo size
473 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

可以看到，在上面的例子中，不使用绑定变量，当id=1时数据库选择了全表扫描，而id=99时数据库选择了索引.

我们来看使用绑定变量的情况
SQL> var id_var number

SQL> exec :id_var:=1

PL/SQL 过程已成功完成。

SQL>
SQL>
SQL>
SQL> select * from t where id=:id_var;

已选择65536行。

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

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628 | 15700 | 54 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 628 | 15700 | 54 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IX | 251 | | 64 (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

2 - access("ID"=TO_NUMBER(:ID_VAR))

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
13 recursive calls
0 db block gets
4614 consistent gets
0 physical reads
0 redo size
883013 bytes sent via SQL*Net to client
48459 bytes received via SQL*Net from client
4371 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
65536 rows processed

SQL> exec :id_var:=99

PL/SQL 过程已成功完成。

SQL>
SQL>
SQL>
SQL> select * from t where id=:id_var;

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

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628 | 15700 | 54 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 628 | 15700 | 54 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IX | 251 | | 64 (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

2 - access("ID"=TO_NUMBER(:ID_VAR))

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
239 consistent gets
0 physical reads
0 redo size
473 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

我们看到，当使用绑定变量的情况下，由于在开始时已经完成了硬解析，在之后的执行过程中都是按照硬解析生成的执行计划执行，所以不管id=1还是id=99都是用来索引.这对id=1的情况，造成了额外的IO浪费.

因此，使用绑定变量并不一定好.根据自己的情况来选择是否使用.